Day12 後半
「グループごとの数字」を“並べて読む”ところまでいく
前半で、
「user_id ごとに合計」
「user_id ごとに件数」
といった、基本的な GROUP BY のイメージはつかめました。
後半では、そこから一歩進んで、
- 複数列での GROUP BY
- GROUP BY と ORDER BY の組み合わせ
- 「集計結果をどう読むか」という視点
- セキュリティ的に“怪しいグループ”をあぶり出す使い方
まで踏み込みます。
複数列で GROUP BY するとはどういうことか
「誰 × いつ」のように、軸を増やしてグループを作る
まず、次のような orders テーブルを考えます。
id | user_id | amount | order_date
---+---------+--------+-----------
1 | 1 | 1200 | 2025-04-01
2 | 1 | 3000 | 2025-04-01
3 | 2 | 500 | 2025-04-01
4 | 2 | 1500 | 2025-04-02
5 | 3 | 8000 | 2025-04-02
ここで、次の問いを考えます。
「ユーザーごと、日付ごとに、合計購入金額を出したい」
日本語で分解すると、
user_id ごと
かつ
order_date ごと
という二つの軸でグループを作り、そのグループごとに SUM(amount) を出したい、ということです。
SQL はこうなります。
SELECT
user_id,
order_date,
SUM(amount) AS total_amount
FROM orders
GROUP BY user_id, order_date;
ここで起きていることを、丁寧に言葉にするとこうです。
まず、(user_id, order_date) の組み合わせごとにグループを作る。
user_id=1 かつ order_date=2025-04-01 のグループ。
user_id=2 かつ order_date=2025-04-01 のグループ。
user_id=2 かつ order_date=2025-04-02 のグループ。
user_id=3 かつ order_date=2025-04-02 のグループ。
それぞれのグループの中で SUM(amount) を計算する。
結果のイメージはこうです。
user_id | order_date | total_amount
--------+-------------+------------
1 | 2025-04-01 | 4200
2 | 2025-04-01 | 500
2 | 2025-04-02 | 1500
3 | 2025-04-02 | 8000
ここでのポイントは、
「GROUP BY に複数列を書くと、“その組み合わせ”ごとにグループができる」
ということです。
GROUP BY と ORDER BY を組み合わせる
「集計結果を、意味のある順番で並べて読む」
GROUP BY で「グループごとの数字」を作ったら、
次はそれを「どの順番で並べるか」が大事になります。
さきほどの結果を、「合計金額の大きい順」に並べたいとします。
SELECT
user_id,
order_date,
SUM(amount) AS total_amount
FROM orders
GROUP BY user_id, order_date
ORDER BY total_amount DESC;
ここでの流れはこうです。
GROUP BY で「user_id × order_date ごとの合計」を計算する。
その結果（集計された表）に対して、ORDER BY で並び順をつける。
結果のイメージはこうなります。
user_id | order_date | total_amount
--------+-------------+------------
3 | 2025-04-02 | 8000
1 | 2025-04-01 | 4200
2 | 2025-04-02 | 1500
2 | 2025-04-01 | 500
このように、
「どの単位でグループを作るか」 → GROUP BY
「そのグループをどう並べるか」 → ORDER BY
という役割分担を意識すると、SQL の読み書きがかなりスッキリします。
「集計結果をどう読むか」を意識してみる
「上から順に見ると、何が分かるか」を言葉にしてみる
さきほどの結果を、上から順に眺めてみます。
user_id=3, 2025-04-02, total_amount=8000
user_id=1, 2025-04-01, total_amount=4200
user_id=2, 2025-04-02, total_amount=1500
user_id=2, 2025-04-01, total_amount=500
ここから、こんなことが読み取れます。
2025-04-02 に user_id=3 が 8000 円と、かなり大きな購入をしている。
user_id=1 は 2025-04-01 に 4200 円と、そこそこ大きい。
user_id=2 は 2日間に分けて、少しずつ買っている。
この「集計結果を見て、自然言語で説明できるかどうか」が、
SQL を“道具として使えているか”の一つの目安になります。
GROUP BY と HAVING の入り口だけ触れておく
「グループに対する条件」は WHERE ではなく HAVING
本格的には次のステップですが、
GROUP BY を理解するうえで、HAVING というキーワードだけ触れておきます。
たとえば、
「合計購入金額が 5000 円以上のユーザーだけを見たい」
という問いを考えます。
これは、
user_id ごとに SUM(amount) を出す。
その合計が 5000 以上のグループだけを残す。
という二段階の処理です。
SQL はこうなります。
SELECT
user_id,
SUM(amount) AS total_amount
FROM orders
GROUP BY user_id
HAVING SUM(amount) >= 5000;
ここでのポイントは、
WHERE は「グループ化する前の行」に対する条件。
HAVING は「グループ化したあとのグループ」に対する条件。
という役割分担です。
Day12 では、「グループに条件をかけるときは HAVING というものがある」くらいの理解で十分です。
セキュリティの視点から見る GROUP BY × ORDER BY
「怪しいグループを上から順に見る」という発想
GROUP BY と ORDER BY を組み合わせると、
「怪しそうなグループを上から順に見る」ということができます。
たとえば、login_logs テーブルがあるとします。
id | user_id | success | created_at
---+---------+---------+-------------------
1 | 1 | 1 | 2025-04-01 10:00
2 | 1 | 0 | 2025-04-01 10:01
3 | 2 | 0 | 2025-04-01 10:02
...
ここで、「ユーザーごとのログイン試行回数」を数え、
試行回数の多い順に並べたいとします。
SELECT
user_id,
COUNT(*) AS trial_count
FROM login_logs
GROUP BY user_id
ORDER BY trial_count DESC;
これで、
ログイン試行回数が多いユーザーから順に並んだ一覧
が得られます。
上位にいるユーザーは、
パスワードを何度も間違えているだけかもしれないし、
誰かにアカウントを狙われているのかもしれない。
いずれにせよ、「優先的に目を向けるべきグループ」として浮かび上がります。
このように、
どの単位でグループを作るか（user_id, ip_address, 日付など）
どの指標で並べるか（COUNT, SUM, MAX など）
を設計することは、そのまま「どこから異常を探すか」の設計にもなります。
小さな練習で締める
日本語の問いを GROUP BY に翻訳してみる
頭の中で、次の問いを SQL にしてみてください。
ユーザーごとの合計購入金額と注文回数を知りたい。
日付ごとのアクセス件数を知りたい。
ユーザーごと・日付ごとの合計購入金額を出し、それを合計金額の大きい順に並べたい。
どれも、
SELECT グループのキー, 集計関数
FROM テーブル
[WHERE 条件]
GROUP BY グループのキー
[ORDER BY 集計結果]
という型に当てはめれば書けるはずです。
Day12 後半のまとめ
GROUP BY は、1列だけでなく複数列（user_id × 日付など）でもグループを作れる。
GROUP BY の結果に対して ORDER BY をかけることで、「グループごとの数字」を意味のある順番で読める。
WHERE は「グループ化前の行」に対する条件、HAVING は「グループ化後のグループ」に対する条件。
セキュリティの観点では、「ユーザーごと」「IPごと」「日付ごと」に COUNT / SUM して、多い順に並べることで“怪しいグループ”をあぶり出せる。
ここまで来たあなたは、
「1行ずつ見る」「テーブル全体を数字で見る」に加えて、
「グループごとに数字を出して、並べて読む」という強力な視点を手に入れています。
この先は、GROUP BY と JOIN を組み合わせたり、集計結果をさらに条件で絞ったりしながら、より現実のシステムに近い SQL へ進んでいけます。