GROUP BYとDISTINCT句

概要

仕事でたまたまSQLを書かないといけない機会があり、SQLへの理解の浅さを痛感しました、、なので今回はSQLの知識補強をします。特にgroup by句を取り上げます。

この記事のゴール

GROUP BYと集約関数の使い所を理解します。またその際select句に余計な列を書いたことでエラーが起こる理由を理解します。またGROUP BYと混同しやすいDISTINCT、DISTINCT ONの使い分けをすることをゴールとします。

GROUP BYとは

まず下準備として、テーブルを作成します

CREATE TABLE companies (
    id INT,
    name TEXT,
    prefecture TEXT,
    industry TEXT,
    established_year INT
);

INSERT INTO companies (id, name, prefecture, industry, established_year) VALUES
(1, '株式会社A', '群馬県', '小売業', 1973),
(2, '株式会社B', '愛知県', '自動車製造', 1937),
(3, '株式会社C', '東京都', '電機製造', 1946),
(4, '株式会社D', '京都府', 'ゲーム', 1889),
(5, '株式会社E', '東京都', '小売業', 2005),
(6, '株式会社F', '東京都', '自動車製造', 2005),
(7, '株式会社H', '京都府', '小売業', 1889);

GROUP BYはケーキを切り分ける(カット)ようにテーブルをグループ分けします。companiesテーブルなら、所在地の都道府県、業種のindustryなどで分けるのが適切でしょう。

SELECT prefecture, COUNT(*) 
  FROM companies 
  GROUP BY prefecture

上記を実行してみると以下の結果になります.

prefecture	count
群馬県	1
東京都	3
愛知県	1
京都府	2

これは都道府県でグループ分けして、全カラムのレコード総数を数えたものになります。

次に少し変えます。

SELECT prefecture, COUNT(*), industry
  FROM companies 
  GROUP BY prefecture

"ERROR: column "companies.industry" must appear in the GROUP BY clause or be used in an aggregate function "と。companies.industryはgroup by句に出現しなければならない、あるいは集約関数の中で使われないといけないと、ふむふむ。
ここでこのようなエラーになった理由について考えてみたいと思います。これは上記の例を考えればすぐわかりますが、prefectureで集約した結果、industry列が一意に定まらないのです。東京都なら、電機、小売、自動車製造業の三つの選択肢がありDBは選択しかねるためエラーになります。
industryを集約関数か、GROUP BY句で使ってあげると一意に定まるためエラーが解消されるというわけです。
ちなみにprefecture, industryでGROUP BYした場合は、都道府県と産業の組み合わせの数だけ、groupができます。

SELECT prefecture, COUNT(*), industry
  FROM companies 
  GROUP BY prefecture, industry

prefecture	count	industry
東京都	1	電機製造
東京都	1	自動車製造
愛知県	1	自動車製造
東京都	1	小売業
京都府	1	ゲーム
群馬県	1	小売業
京都府	1	小売業

GROUP BYとSELECT文の実行順序

SELECT prefecture, COUNT(*)
  FROM companies
  WHERE industry = '自動車製造'
  GROUP BY prefecture;

SQLの記述順序と、実行の順序が違うことにも注意が必要です。
上記の実行順序は、
FROM → WHERE → GROUP BY → SELECTという順番になります。

DISTINCTについて

DISTINCTはGROUP BYと同じく、重複を削除するという処理を行います。例えば下記は全く同じ結果になります。

SELECT prefecture
FROM companies c
GROUP BY prefecture;

SELECT DISTINCT prefecture
FROM companies c;

prefecture
群馬県
東京都
愛知県
京都府

ではどう使い分けるかという話ですが、「重複を除外したい」という時はDISTINCTを使い、「グループごとの集約した結果を求めたい」という時はGROUP BYを使います。なので、上の例にあるような単に都道府県で重複削除した結果を求めたいケースは本来のGROUP BY句の使い方としては適切ではないと言えます。

DISTINCT ONについて

postgreSQLにはDISTINCTと似た、DISTINCT ON句があります。
ある列の重複は削除した上で、他の列も取得したいというケースに有効です。

 SELECT DISTINCT ON (established_year) *
 FROM companies
 ORDER BY established_year ASC

id	name	prefecture	industry	established_year
4	株式会社D	京都府	ゲーム	1889
2	株式会社B	愛知県	自動車製造	1937
3	株式会社C	東京都	電機製造	1946
1	株式会社A	群馬県	小売業	1973
5	株式会社E	東京都	小売業	2005

上記の実行順序は、
1 FROM
2 ORDER BY
3 SELECT DISTINCT ON
という順番になります。つまり設立年度の昇順で並び替えた後に、設立年度の重複削除した上で１番はじめに見つけたレコードの全カラムを取得するという挙動になります。
distinctでは、このようにあるカラムの重複削除して、さらに他カラムも取得ということはできません。

 SELECT DISTINCT established_year, prefecture
 FROM companies
 ORDER BY established_year ASC

上記のように、distinctで複数カラムを指定できますが、あくまでdistinctに続くカラムの組み合わせで重複削除してそれらのカラムを取得するという挙動になります。

以上、GROUP BYとそれに関連するSQLについてのまとめでした。