SQLのgroup byの構文やサンプル、集計後の条件指定やDBMSごとの拡張についてまとめています。
SQLのgroup byの構文
group byは、小計や区分ごとの最小・最大・平均などのグルーピングを行います。構文は以下の通りです。group by に続けて、グルーピング対象となるカラム名を指定するんですね。
SELECT カラム名[, カラム名, ...] FROM テーブル名 [WHERE 条件式] GROUP BY カラム名[, カラム名, ...];
【関連記事】
▶【SQL】GROUP BY句と集計関数の使い方を確認しよう！【サンプル有り】
例えば以下は、departments（部署）テーブルの社員数を、dept_name（部署名）ごとにグルーピングしてカウントするSQLです。社員数をカウントするために、dept_emp（部署名－社員紐付けテーブル）とdepartments（部署）テーブルをjoinで結合しています。where条件のto_date=”9999-01-01″は、現在在籍中の社員を対象にするための指定です。
SELECT departments.dept_name, count(*) FROM dept_emp left join departments ON dept_emp.dept_no = departments.dept_no WHERE to_date = "9999-01-01" group by departments.dept_name
実行するとこうなります。部署ごとの社員数の小計が出せました。
+--------------------+----------+ | dept_name | count(*) | +--------------------+----------+ | Development | 61386 | | Sales | 37701 | | Production | 53304 | | Human Resources | 12898 | | Research | 15441 | | Quality Management | 14546 | | Marketing | 14842 | | Customer Service | 17569 | | Finance | 12437 | +--------------------+----------+ 9 rows in set (1.10 sec)
【関連記事】
▶SQLのgroup byサンプルコード集 count、like、join等の組み合わせ例
group byの集計値に条件指定するにはhavingを使用
count(*)以外に、集合関数と呼ばれる関数を指定して、最大、最小、平均などを算出可能です。これら集合関数で算出した値に対して、havingを使って条件指定が可能です。
以下は、departments（部署）テーブルごとの平均給与を出力するSQLです。havingを組み合わせて、年収平均70,000ドル以上の部署のみを出力しています。
SELECT departments.dept_name as bushomei, avg(salary) as avg_kyuuryo FROM salaries left join dept_emp ON dept_emp.emp_no = salaries.emp_no left join departments ON departments.dept_no = dept_emp.dept_no WHERE salaries.to_date="9999-01-01" group by bushomei having avg_kyuuryo > 70000
実行するとこうなります。
+-----------+-------------+ | bushomei | avg_kyuuryo | +-----------+-------------+ | Sales | 88842.1590 | | Marketing | 80014.6861 | | Finance | 78644.9069 | +-----------+-------------+ 3 rows in set (3.49 sec)
【関連記事】
▶SQL having 集約関数の絞り込み whereよりもレスポンスが遅い理由とは？
データベースごとのgroup byの拡張
Oracleのgroup byの拡張 ROLL UP
Oracleでは、ROLL UPというgroup byの拡張が使用可能です。
ROLLUP(a, b, c) = GROUPING SETS((a,b,c), (a,b), (a), ())
rollup(a,b,c)と指定すると、以下のように指定した属性を下位から上位に向かって集計値を計算します。
- group by a,b,c
- group by a,b
- group by a
- group by 指定なし（総計）
実際のSQLのサンプルは以下の通り。
SELECT SUM(Sales) AS TotalSales FROM Resellers GROUP BY ROLLUP(Countries, States, OrderMonth)
実行すると、以下の集計を計算します。
- group by 国（Countries）,州（States）,注文月（OrderMonth）
- group by 国（Countries）,州（States）
- group by 国（Countries）
- group by なしの総計
SQL Server の拡張 ROLLUPとCUBE
SQL Serverでは、ROLLUP拡張に加えて、CUBE拡張が使用可能。
ROLLUPは、下位から上位に順に計算を行いますが、CUBEは可能な属性値の組み合わせ全てに対して集計をおこないます。BIツールのように軸を次々と変えて集計値を参照するための「多次元キューブタイプ」を生成するのに役立ちます。
SELECT Country, Region, SUM(Sales) AS TotalSales FROM Sales GROUP BY CUBE (Country, Region);
参考）GROUP BY (Transact-SQL) – SQL Server | Microsoft Docs
SQL Serverでは、さらにGROUPING SETSという、より柔軟な指定も可能です。
MySQLの拡張 ROLLUP
MySQLでもROLLUP指定が可能です。記述方式がOracleやSQL Serverとはやや異なります。
mysql> SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP; +------+-------------+ | year | SUM(profit) | +------+-------------+ | 2000 | 4525 | | 2001 | 3010 | | NULL | 7535 | +------+-------------+
参考）MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.19.2 GROUP BY 修飾子
PostgreSQLの拡張ROLLUPとCUBE、GROUPING SETS
PostgreSQLでは、group byに対してROLLUP、CUBE、GROUPING SETS指定が可能です。
=> SELECT * FROM items_sold;
brand | size | sales
-------+------+-------
Foo | L | 10
Foo | M | 20
Bar | M | 15
Bar | L | 5
(4 rows)
=> SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
brand | size | sum
-------+------+-----
Foo | | 30
Bar | | 20
| L | 15
| M | 35
| | 50
(5 rows)
上記のGROUPING SETS指定の()は、「総計」を表します。group by指定をしない値を()と指定するんですね。
まとめ
- group by はグルーピングして区分ごとの集計値を計算する指定
- having指定で、集計値に対して条件指定が可能
- 各種データベースのgroup by拡張で、ROLLUPやCUBE、GROUPING SETSなどが使用可能
