開発メモプログラミングの学習記録
SQL学習記録

【SQL】GROUP BY：グループに切り分ける

  • #SQL
  • 「〜によってグループ分けする」という意味
/* 商品分類が入っている行の数 */
SELECT COUNT(shohin_bunrui)
FROM Shohin;

 count 
-------
     8
     
/* 商品分類ごとにグループ分け */
SELECT shohin_bunrui, COUNT(*)
FROM Shohin
GROUP BY shohin_bunrui;

 shohin_bunrui | count 
---------------+-------
 キッチン用品  |     4
 衣服          |     2
 事務用品      |     2
  • GROUP BY句に指定する列：集約キー、グループ化列と呼ぶ

関連記事：【SQL】集約キー：GROUP BY句に書くもの

NULLが含まれていた場合

例：集約キー（shiire_tanka）にはNULLが含まれている

SELECT shiire_tanka
FROM Shohin;

/* NULLが2つ含まれている */
 shiire_tanka 
--------------
          500
          320
         2800
         2800
         5000
              -- NULL
          790
              -- NULL
(8 rows)

/* GROUP BYを使わない場合 */
SELECT COUNT(shiire_tanka)
FROM Shohin;

 count 
-------
     6
     
/* GROUP BYを使った場合 */
SELECT shiire_tanka, COUNT(*)
FROM Shohin
GROUP BY shiire_tanka;

 shiire_tanka | count 
--------------+-------
              |     2 -- NULL
          320 |     1
          500 |     1
         2800 |     2
         5000 |     1
          790 |     1

「NULL」は1つのグループとして分類されるようになっている

注意：COUNT(*)はNULLを含み、COUNT(列名)はNULLを含まない

WHERE句と使う

【実行順序】

  1. FROM テーブルを選ぶ
  2. WHERE 行を絞り込む ← 集約前。集約関数は書けない
  3. GROUP BY グループに分ける
  4. HAVING グループを絞り込む ← 集約後。集約関数が書ける
  5. SELECT 列を選ぶ ← ここで初めて別名が付く
  6. ORDER BY 並べ替える
SELECT shiire_tanka, COUNT(*)
FROM Shohin
WHERE shohin_bunrui = '衣服'
GROUP BY shiire_tanka;

 shiire_tanka | count 
--------------+-------
          500 |     1
         2800 |     1

よくある間違い

①SELECT文に余計な列を書く

COUNTのような集約関数を使った場合、SELECT句に書ける要素は限定される

【OKな要素】

  • 定数
  • 集約関数
  • GROUP BY句で指定した列名（つまり集約キー）
/* NG */
SELECT shohin_mei, shiire_tanka, COUNT(*)
FROM Shohin
GROUP BY shiire_tanka;

ERROR:  column "shohin.shohin_mei" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT shohin_mei, shiire_tanka, COUNT(*)
/* エラー内容
 列"shohin.shohin_mei"は`GROUP BY`句で出現しなければならないか
 集約関数内で使用しなければなりません */

②GROUP BY句で列の別名を書く

  • SELECT句に「AS」で表示用の別名をつけることができる
  • GROUP BY句でこの別名を使うことはできない（エラーになる）

PostgreSQLでは実行できるけど、他のDBMSではエラーとなるので使わない

SELECT shohin_bunrui AS sb, COUNT(*)
FROM Shohin
GROUP BY sb;

/* PostgreSQLの場合のみ */
      sb      | count 
--------------+-------
 キッチン用品 |     4
 衣服         |     2
 事務用品     |     2

③GROUP BY句は結果の順序をソートする？

  • 取得結果の順序は保証されていない
  • 同じSELECT文を実行しても、同じように並ばないことがある
  • 結果のレコード順がどんな規則に従っているのかはまったくわからない

④WHERE句に集約関数を書く

これは初心者が陥りがちな間違いらしい

/* 例：商品分類ごとにグループ化する */
SELECT shohin_bunrui, COUNT(*)
FROM Shohin
GROUP BY shohin_bunrui;

 shohin_bunrui | count 
---------------+-------
 キッチン用品  |     4
 衣服          |     2
 事務用品      |     2

↑ここから、count = 2 のレコードを取得したい。。。

/* NG */
SELECT shohin_bunrui, COUNT(*)
FROM Shohin
WHERE COUNT(*) = 2
GROUP BY shohin_bunrui;

ERROR:  aggregate functions are not allowed in WHERE
LINE 3: WHERE COUNT(*) = 2
/* エラー内容
`WHERE`句では集約を使用できません */

集約関数が書けるのは、SELECT句、HAVING句、ORDER BY句のみ

  • GROUP BY のあとで、 count = 2 の条件にあったレコードを選択したい

HAVING句で条件指定できる！

/* HAVINGで条件指定（2行の商品分類を選択） */
SELECT shohin_bunrui, COUNT(*)
FROM Shohin
GROUP BY shohin_bunrui
HAVING COUNT(*) = 2;

/* キッチン用品が正しく除外された */
 shohin_bunrui | count 
---------------+-------
 衣服          |     2
 事務用品      |     2

関連記事：【SQL】HAVING：集約後の条件指定

参考書籍：ミック『SQL 第2版 ゼロからはじめるデータベース操作』（翔泳社）