GROUP BYは集計開始の合図ということを意識した話

はじめに

皆さんはSQLを書くときに処理の順番を意識したことはありますでしょうか？？私はSQLを使い始めて間もないのであまり意識して書くことはないのですが（思いつくままに書いてしまう）、今回のケースで処理の順番を意識してみようと思いました。というのも意識をしないと正しい結果が出力できないからです。基本的なことではありますが、うっすらと聞いたことはありました。しかし、いつか理解できるだろうと後回しにしていたため、今回の出来事を機会に、理解を深めて、意識高くいこうと思います。

なにが起きたか

今回起こったことはTOPSICさんの[SQL CONTEST][1]の過去問を解いているときでした。第一回SQLコンテストの問題2 睡眠時間帯別集計の問題に関してです。
[1]:https://topsic-contest.jp/

私の愚かなコード

Q2.sql

SELECT ag.AGE_CODE AS 年齢コード
   , ag.AGE_NAME AS 年齢階層名
   , SUM(std.SP_TIME_5) AS '5時間未満'
   , SUM(std.SP_TIME_6) AS '5時間以上6時間未満'
   , SUM(std.SP_TIME_7) AS '6時間以上7時間未満'
   , SUM(std.SP_TIME_8) AS '7時間以上8時間未満'
   , SUM(std.SP_TIME_9) AS '8時間以上9時間未満'
   , SUM(std.SP_TIME_9OVER) AS '9時間以上'
FROM SLEEP_TIME_DTL AS std
INNER JOIN AGE_GRP AS ag
   ON std.AGE_CODE = ag.AGE_CODE
INNER JOIN PREFECTURE AS p
   ON std.PF_CODE = p.PF_CODE
GROUP BY ag.AGE_CODE
HAVING p.PF_NAME IN('北海道', '青森県', '岩手県', '宮城県', '福島県')
ORDER BY ag.AGE_CODE ASC;

出力結果

年齢コード	年齢階層名	5時間未満	5時間以上6時間未満	6時間以上7時間未満	7時間以上8時間未満	8時間以上9時間未満	9時間以上
200	20～24歳	19	100	138	103	26	7
210	25～29歳	27	120	148	91	26	8
240	30～34歳	34	156	205	122	31	7
250	35～39歳	43	202	249	143	32	5
280	40～44歳	63	252	272	181	30	5

もちろんWAでした。

解答のコード

解答のコードと出力結果はすぐに見れないように隠します。

Q2.sql

SELECT ag.AGE_CODE AS 年齢コード
    , ag.AGE_NAME AS 年齢階層名
    , SUM(std.SP_TIME_5) AS '5時間未満'
    , SUM(std.SP_TIME_6) AS '5時間以上6時間未満'
    , SUM(std.SP_TIME_7) AS '6時間以上7時間未満'
    , SUM(std.SP_TIME_8) AS '7時間以上8時間未満'
    , SUM(std.SP_TIME_9) AS '8時間以上9時間未満'
    , SUM(std.SP_TIME_9OVER) AS '9時間以上'
FROM SLEEP_TIME_DTL AS std
INNER JOIN AGE_GRP AS ag
    ON std.AGE_CODE = ag.AGE_CODE
INNER JOIN PREFECTURE AS p
    ON std.PF_CODE = p.PF_CODE
WHERE p.PF_NAME IN('北海道', '青森県', '岩手県', '宮城県', '福島県')
GROUP BY ag.AGE_CODE
ORDER BY ag.AGE_CODE ASC;

出力結果

年齢コード	年齢階層名	5時間未満	5時間以上6時間未満	6時間以上7時間未満	7時間以上8時間未満	8時間以上9時間未満	9時間以上
200	20～24歳	19	94	130	97	24	6
210	25～29歳	25	111	137	84	24	8
240	30～34歳	32	144	189	112	29	7
250	35～39歳	40	189	231	134	30	5
280	40～44歳	58	234	249	169	27	5

なにが違うのか

まず、出力結果が違います。なぜ違うのでしょう。JOINするところまでは同じですが、

• GROUP BYしてからHAVINGで条件を絞る（不正解）
• WHEREで条件を絞ってからGROUP BYをする（正解）

この点が圧倒的に違います。都道府県名で絞ってから、年齢コードでグループ化します。では年齢コードでグループ化してから都道府県で絞るのは何が悪いのか。

グループ化をするということ

どちらのコードも年齢コード（AGE_CODE）でグループ化しています。このグループ化をするタイミングで何が起こっているのかというと、このタイミングで集計の計算をしています。つまり、SUM(std.SP_TIME_5) AS '5時間未満'などはGROUP BYのタイミングで集計されているのです。このタイミングで正しい条件に絞れていない場合、意図しない数値を集計してしまう可能性があります。

今回の場合

県ごとの条件の値を集計すべきですが、条件でつける前にGROUP BYで集計をしてしまったためにすべての値の集計をおこなってしまっています。そのあとに県名で条件を絞っても時すでに遅し、、、その県の情報を残っていません。その証拠に間違っているコードのHAVING句を外しても同じ出力結果になります。

まとめ

パパっとSQLを書くとこのようなことが起きるかもしれません。常識的かもしれませんが、グループ化したタイミングで集計が走るということを実感した良い例になりました。私のようなSQL初心者は意識すると集計関数がより身近になるかもしれません。