GROUP BYでグループ化
ブラウザの数だけ、同じクエリを書いている
アクセスログからブラウザ別の件数を出したい。WHERE browser = 'Chrome' で数え、Safari に書き換えてまた数え、と繰り返す。種類が 3 つならまだしも、増えるたびにクエリが増えますし、知らない値が来たら数え漏らします。
数えたい単位をデータベースに伝えれば、全部まとめて返ってきます。それが GROUP BY です。
SQL クエリ
SELECT browser, COUNT(*) AS cnt
FROM access_logs
GROUP BY browser
ORDER BY cnt DESC;GROUP BY browser は「browser の値が同じ行を 1 つの束にする」という指示です。束の数だけ行が返り、COUNT(*) はそれぞれの束の中を数えます。
ここまでの集計関数は、テーブル全体を 1 行にまとめるものでした。GROUP BY を足すと、まとめる単位を自分で決められるようになります。集計するかどうかではなく、何を 1 行にするかを決める句だと考えてください。
束から外れた列は、SELECT に書けない
最初につまずくのはここです。SELECT に書けるのは、GROUP BY に指定した列と、集計関数の結果だけです。
SQL クエリ
-- エラーになる
SELECT browser, url, COUNT(*)
FROM access_logs
GROUP BY browser;Chrome の束には何百行も入っていて、url はその中で行ごとに違います。束は 1 行になって返るのに、どの url を返せばよいのか決まりません。だからエラーになります。
直し方は 2 つです。MAX(url) のように集計関数を通して値を 1 つに決めるか、url も GROUP BY に足して束ね方そのものを変えるか。どちらを選ぶかは、1 行を何の単位にしたいかで決まります。
束ねる列を 2 つにすると、単位が細かくなる
GROUP BY には列を複数書けます。
SQL クエリ
SELECT browser, device_type, COUNT(*) AS cnt
FROM access_logs
GROUP BY browser, device_type
ORDER BY browser, device_type;今度は「browser と device_type の組み合わせ」が 1 つの束です。Chrome の行が、スマートフォンとパソコンに分かれて出ます。列を足すほど束は小さくなり、返る行数は増えます。
覚え方
GROUP BYを書く前に、「1 行を何の単位にしたいか」を日本語で言ってみます。「ブラウザごと」なら列は 1 つ、「ブラウザと端末の組み合わせごと」なら 2 つ。この一言さえ決まれば、あとはSELECTにその列と集計関数を並べるだけです。なお値が空 (NULL) の行は、空どうしでまとめて 1 つの束になります。
テーブル構造
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
category VARCHAR(30) NOT NULL,
price INT NOT NULL
);
INSERT INTO products (id, name, category, price) VALUES
(1, 'ノートPC', '家電', 128000),
(2, 'マウス', '家電', 2980),
(3, 'キーボード', '家電', 6800),
(4, '小説A', '書籍', 1800),
(5, '小説B', '書籍', 2200),
(6, '専門書A', '書籍', 4500),
(7, 'コーヒー豆', '食品', 1500),
(8, '紅茶葉', '食品', 1200);期待される出力
| category | cnt | total |
|---|---|---|
| 家電 | 3 | 137780 |
| 書籍 | 3 | 8500 |
| 食品 | 2 | 2700 |