【SQL上級者向け】GROUP BY句とサブクエリ、CUBE、GROUPING SETSを使いこなして複雑な集計を制覇

MariaDBのGROUP BY句におけるWITH ROLLUPオプションは、集計結果に中間的な集計値を追加する機能です。しかし、2つの類似クエリにおいて、WITH ROLLUPの動作が異なる場合があります。

例

以下の2つのクエリ例を見てみましょう。

-- クエリ1
SELECT
  department,
  job_title,
  COUNT(*) AS employee_count
FROM employees
GROUP BY department, job_title
WITH ROLLUP;

-- クエリ2
SELECT
  department,
  COUNT(*) AS employee_count
FROM employees
GROUP BY department
WITH ROLLUP;

クエリ1の説明

このクエリは、従業員テーブル（employees）に対して、部門（department）と役職（job_title）ごとに従業員数を集計します。WITH ROLLUPオプションにより、各部門全体の従業員数も集計結果に含まれます。

動作の違い

2つのクエリは一見似ていますが、WITH ROLLUPの動作が異なります。

• クエリ1：各部門・役職ごとの従業員数に加え、各部門全体の従業員数、全ての従業員数を集計します。

詳細

WITH ROLLUPオプションは、GROUP BY句で指定された列の組み合わせごとに中間的な集計値を追加します。具体的には、以下のようになります。

• 各列の組み合わせ：各列の組み合わせごとに集計値を追加します。
• 1つ前の列を除いた組み合わせ：各列の組み合わせから1つ前の列を除いた組み合わせごとに集計値を追加します。
• ：同様に、列を1つずつ削除しながら、組み合わせごとに集計値を追加していきます。

上記の例では、

• クエリ1：部門と役職の組み合わせ、部門のみの組み合わせ、全ての列を省略した組み合わせ（つまり、全ての従業員数）

に対して集計値が追加されます。

WITH ROLLUPオプションは、集計結果に中間的な集計値を追加する便利な機能です。しかし、GROUP BY句で指定された列の組み合わせによって、追加される集計値が異なることに注意する必要があります。

補足

• WITH ROLLUPオプションは、GROUP BY句と組み合わせて使用します。
• WITH ROLLUPオプションは、LIMIT句と組み合わせて使用できますが、LIMIT句はWITH ROLLUPで追加された行に適用されます。

SELECT
  department,
  job_title,
  COUNT(*) AS employee_count
FROM employees
GROUP BY department, job_title
WITH ROLLUP;

出力例

department | job_title    | employee_count
-----------+-------------+--------------
Accounting | Accountant    | 10
Accounting | Manager     | 5
Accounting | NULL        | 15
NULL        | NULL        | 50

SELECT
  department,
  COUNT(*) AS employee_count
FROM employees
GROUP BY department
WITH ROLLUP;

department | employee_count
-----------+--------------
Accounting | 15
Sales      | 20
NULL        | 50

説明

• 例1：
  • 全ての列がNULLである行は、全ての従業員数を表します。
• 例2：

• 上記のコードは、MySQLとMariaDBで動作します。
• 実際の結果は、使用しているデータベースとデータの内容によって異なる場合があります。

他の方法

GROUP BY句とWITH ROLLUPの代わりに、サブクエリを使用して集計結果を階層的に表示する方法があります。

SELECT
  department,
  job_title,
  (
    SELECT COUNT(*)
    FROM employees
    WHERE department = e.department
      AND (job_title = e.job_title OR job_title IS NULL)
  ) AS employee_count
FROM employees AS e
GROUP BY department, job_title
ORDER BY department, job_title IS NULL, job_title;

このクエリは、以下の3つのサブクエリで構成されています。

1. 外部クエリ：従業員テーブル（employees）に対して、部門（department）と役職（job_title）ごとに集計を行います。
2. 中間クエリ：外部クエリで選択された部門に属する従業員数を集計します。

department | job_title    | employee_count
-----------+-------------+--------------
Accounting | Accountant    | 10
Accounting | Manager     | 5
Accounting | NULL        | 15
NULL        | NULL        | 50

利点

• サブクエリを使用することで、より柔軟な集計結果を生成できます。
• WITH ROLLUPオプションよりも、より詳細な制御が可能です。

欠点

• サブクエリを使用すると、クエリが複雑になり、読みづらくなる可能性があります。
• サブクエリを使用すると、パフォーマンスが低下する可能性があります。

CUBEを使用する

SELECT
  department,
  job_title,
  COUNT(*) AS employee_count
FROM employees
GROUP BY department, job_title
WITH CUBE;

このクエリは、GROUP BY句で指定された全ての列の組み合わせに対して集計を行います。

department | job_title    | employee_count
-----------+-------------+--------------
Accounting | Accountant    | 10
Accounting | Manager     | 5
Accounting | NULL        | 15
NULL        | NULL        | 50
NULL        | Accountant    | 0
NULL        | Manager     | 0

• CUBEを使用することで、全ての列の組み合わせに対して集計結果を生成できます。

GROUPING SETSを使用する

SELECT
  department,
  job_title,
  COUNT(*) AS employee_count,
  GROUPING(department, job_title) AS grouping_level
FROM employees
GROUP BY GROUPING SETS (department, job_title, (), ());

このクエリは、GROUPING SETS句を使用して、集計結果にグループ化レベル情報を追加します。

department | job_title    | employee_count | grouping_level
-----------+-------------+--------------+--------------
Accounting | Accountant    | 10            | 2
Accounting | Manager     | 5             | 2
Accounting | NULL        | 15            | 1
NULL        | NULL        | 50            | 0
NULL        | Accountant    | 0             | 1
NULL        | Manager     | 0             | 1

GROUP BY句とWITH ROLLUP以外にも、集計結果を階層的に表示する方法があります。それぞれの方法には利点と欠点があるため、状況に合わせて適切な方法を選択する必要があります。

• [Maria