概要
- SQL の GROUP BY を使うサンプルを書く
- 環境: PostgreSQL 12.4 + macOS Catalina
GROUP BY とは
指定したカラムの値をキーとしてレコードをグループ化することができる機能。
グループ化したレコードは、集約関数を用いることでグループごとに数値などの値を集計することが可能。
GROUP BY句は、グループ化する列名または列名を含んだ式を指定する。
PostgreSQL 12.3文書 - 7.2. テーブル式
GROUP BY句は、GROUP BY句で列挙されたすべての列で同じ値を所有する行をまとめてグループ化するために使用されます。 列の列挙順は関係ありません。 これは共通する値を持つそれぞれの行の集合をグループ内のすべての行を代表する１つのグループ行にまとめるものです。 これは、出力の冗長度を排除したり、それぞれのグループに適用される集約計算を行うためのものです。
サンプルデータを準備
買ったお菓子テーブルを作成。
testdb=# CREATE TABLE katta_okashi (
testdb(# number INTEGER,
testdb(# name VARCHAR(128),
testdb(# okashi VARCHAR(128),
testdb(# price INTEGER
testdb(# );
CREATE TABLE
買ったお菓子データを追加。
testdb=# INSERT INTO katta_okashi VALUES
testdb-# (1, 'Alice', 'candy', 100),
testdb-# (2, 'Alice', 'candy', 50),
testdb-# (3, 'Alice', 'cookie', 30),
testdb-# (4, 'Bob', 'chocolate', 200),
testdb-# (5, 'Bob', 'chocolate', 20),
testdb-# (6, 'Bob', 'cookie', 20);
INSERT 0 6
買ったお菓子データ一覧を出力。
testdb=# SELECT * FROM katta_okashi;
number | name | okashi | price
--------+-------+-----------+-------
1 | Alice | candy | 100
2 | Alice | candy | 50
3 | Alice | cookie | 30
4 | Bob | chocolate | 200
5 | Bob | chocolate | 20
6 | Bob | cookie | 20
(6 rows)
カラムを指定してグループ化する
カラム内で同じ値となるレコードをひとつにまとめて出力できる。
name には何パターンのデータが入っているか。
testdb=# SELECT name FROM katta_okashi GROUP BY name;
name
-------
Alice
Bob
(2 rows)
okashi には何パターンのデータが入っているか。
testdb=# SELECT okashi FROM katta_okashi GROUP BY okashi;
okashi
-----------
candy
cookie
chocolate
(3 rows)
複数カラムを指定
それぞれのカラム内で同じ値となるレコードをひとつにまとめて出力できる。
指定したカラムの組み合わせパターン分が出力される。
testdb=# SELECT name, okashi FROM katta_okashi GROUP BY name, okashi;
name | okashi
-------+-----------
Bob | cookie
Alice | cookie
Bob | chocolate
Alice | candy
(4 rows)
集約関数
集約関数を使うとグループ化したレコードに対して数値などを集計することができる。
グループごとにレコード数、合計値、最小値、最大値などを出力できる。
testdb=# SELECT name, COUNT(price), SUM(price), MIN(price), MAX(price)
testdb-# FROM katta_okashi
testdb-# GROUP BY name;
name | count | sum | min | max
-------+-------+-----+-----+-----
Alice | 3 | 180 | 30 | 100
Bob | 3 | 240 | 20 | 200
(2 rows)
WHERE と HAVING
WHERE はグループ化の前に条件で絞り込む。
HAVING はグループ化の後に条件で絞り込む。
PostgreSQL 12.3文書 - 7.2. テーブル式
GROUP BYを使ってグループ化されたテーブルで特定のグループのみ必要な場合、結果から不要なグループを除くのに、WHERE句のようにHAVING句を使うことができます。
WHEREとHAVINGの基本的な違いは、WHEREが、グループや集約を演算する前に入力行を選択する（したがって、これはどの行を使用して集約演算を行うかを制御します）のに対し、HAVINGは、グループと集約を演算した後に、グループ化された行を選択する、ということです。
GROUP BY と WHERE を組み合わせる
name = 'Alice' の条件に合致したレコードを抽出した後に、name カラムでグループ化して、price カラムの値を合計する。
testdb=# SELECT name, SUM(price)
testdb-# FROM katta_okashi
testdb-# WHERE name = 'Alice'
testdb-# GROUP BY name;
name | sum
-------+-----
Alice | 180
(1 row)
GROUP BY と HAVING を組み合わせる
name カラムでグループ化した後に、SUM(price) >= 200 の条件に合致したものを出力する。
testdb=# SELECT name, SUM(price)
testdb-# FROM katta_okashi
testdb-# GROUP BY name
testdb-# HAVING SUM(price) >= 200;
name | sum
------+-----
Bob | 240
(1 row)