MariaDB講座 / GROUP BYでまとめ、HAVINGでグループを絞る

GROUP BYは同じキーの行をグループとして集計し、HAVINGはできたグループを絞ります。WHEREは集計前の元行を対象にするため、条件を移すと母集団や結果が変わる場合があります。

一出力行の単位を定める

商品一覧は一商品一行、分類レポートは一分類一行です。分類だけでまとめながら任意の商品名を選んでも、どの商品が代表かは決まりません。SELECTの非集計値は、その単位で意味が定まる必要があります。

WHEREは元行、GROUP BYは同じキー、HAVINGは集計済みグループ、ORDER BYは最終表示を扱います。集計自体は集計関数、グループ内条件はCASEを参照できます。

分類のデータを作成する

course_userでshopcourseへ接続し、SELECT VERSION(), DATABASE();とSHOW TABLES;で環境と同名表を確認します。通常例をUTF-8ファイルに保存してSOURCEで実行できます。既存の同名表は用途・定義・行を調べ、不要と確認できるまでは置き換えません。

USE shopcourse;
CREATE TABLE products_23_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p23_price CHECK (price >= 0),
  CONSTRAINT ck_p23_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_23_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

SQL内の韓国語サンプルは文字コードと結果を原文にそろえたものです。ID1～6はノート、ペン、マグカップ、ライト、ファイル、ケーブル、分類は文具・生活用品・電子用品です。数値とIDで結果を比較します。noteは1・4がNULL、2・5が空文字、他は記入済みで、在庫は20・0・12・5・50・0です。

文具は三商品、生活用品は二商品、電子用品は一商品です。最初は在庫0も除外しません。

分類ごとに一行へまとめる

次を実行します。

SELECT category,COUNT(*) AS product_count,SUM(stock) AS total_stock,
 SUM(price*stock) AS inventory_value
FROM products_23_lab GROUP BY category ORDER BY category;

文具は件数3・在庫70・金額145000、生活用品は2・17・221000、電子用品は1・0・0です。SQLの分類名は韓国語ですが、数値で照合できます。グループごとにCOUNTやSUMを別々に計算します。観察したGROUP BYの順番に依存せず、ORDER BYを明示します。文字キーの同一性には照合順序も関係します。

HAVINGとWHEREを比較する

件数2以上のグループを残します。

SELECT category,COUNT(*) AS product_count
FROM products_23_lab
GROUP BY category HAVING COUNT(*) >= 2 ORDER BY category;

文具3と生活用品2です。COUNT(*)は集計結果なので、この条件を通常の元行WHEREへ置けません。次は先に在庫ありだけを選びます。

SELECT category,COUNT(*) AS in_stock_products
FROM products_23_lab WHERE stock > 0
GROUP BY category HAVING COUNT(*) >= 2 ORDER BY category;

ペンとケーブルが除かれ、文具2と生活用品2になります。電子用品は元行がなくなりグループ自体がありません。HAVINGはWHEREで落とした行を復活できないので、総商品数と在庫あり商品数を同じ指標にしません。

非集計列とNULLのグループ

分類だけでまとめてproduct_nameを自由に選ぶ問い合わせはONLY_FULL_GROUP_BYで拒否されます。モードを外すのではなく、名前もグループ化して単位を変えるか、分類単位で定まる値だけを返します。

NULLのグループキーは一つのグループになります。WHEREの通常のNULL比較をそのまま当てはめません。JOINの後なら、集計前に元行が増えていないか調べ、少量データでグループごとの元行と合計を照合します。

再実習では選択中のDB、定義、保存行を確認し、必要なものを保存した後、削除可能なこの記事の実習表だけを削除します。DROP TABLEは定義と行を消し、通常のROLLBACKでは戻せません。他の記事や実アプリケーションの表へ範囲を広げないでください。

DROP TABLE shopcourse.products_23_lab;

関連する公式リファレンス

• group by（公式ドキュメント）