SQLite | ゼロからはじめるSQL、30日で習得するSQLite：検索力強化 － Day13 条件付き集計

Day13 後半

「怪しいグループだけを上から順に眺める」まで持っていく

前半で、HAVING が
「グループ化した“あと”の世界に条件をかけるフィルタ」
だというところまでは来ました。

後半ではそこから一歩進めて、

• HAVING と ORDER BY を組み合わせて「怪しい順」に並べる
• HAVING に複数条件を書く
• WHERE と HAVING を両方使う、実務寄りのパターン
• セキュリティ的に“優先的に見るべきグループ”をあぶり出す

ところまで持っていきます。

HAVING と 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 | 1       | 0       | 2025-04-01 10:02
 4 | 2       | 0       | 2025-04-01 10:03
 5 | 2       | 0       | 2025-04-01 10:04
 6 | 2       | 0       | 2025-04-01 10:05
 7 | 3       | 1       | 2025-04-01 10:06
...

ここでの問いはこうです。

「ログイン試行回数が 3 回以上のユーザーだけを対象にしつつ、その中で試行回数の多い順に並べたい」

日本語で分解すると、

1. user_id ごとに COUNT(*) で試行回数を数える
2. COUNT(*) が 3 以上のグループだけを HAVING で残す
3. その結果を、COUNT(*) の多い順に ORDER BY する

SQL はこうなります。

SELECT
  user_id,
  COUNT(*) AS trial_count
FROM login_logs
GROUP BY user_id
HAVING COUNT(*) >= 3
ORDER BY trial_count DESC;

SELECT
  user_id,
  COUNT(*) AS trial_count
FROM login_logs
GROUP BY user_id
HAVING COUNT(*) >= 3
ORDER BY trial_count DESC;

ここでの流れは、

GROUP BY で「ユーザーごとの試行回数」を作る
HAVING で「3回以上」のユーザーだけに絞る
ORDER BY で「試行回数の多い順」に並べる

という三段階です。

この「HAVING でふるいにかけてから ORDER BY で並べる」というパターンは、
“怪しいグループを上から順に見る”ときの定番になります。

HAVING に複数条件を書く

「合計も回数も、両方の条件を満たすグループだけ」

次に、HAVING に複数の条件を書くパターンを見てみます。

orders テーブルを使って、次の問いを考えます。

「ユーザーごとの合計購入金額が 5000 円以上、かつ注文回数が 3 回以上のユーザーだけを知りたい」

これは、

user_id ごとに
SUM(amount) と COUNT() を計算する
SUM(amount) >= 5000 かつ COUNT() >= 3 のグループだけを残す

という処理です。

SQL はこうなります。

SELECT
  user_id,
  SUM(amount)  AS total_amount,
  COUNT(*)     AS order_count
FROM orders
GROUP BY user_id
HAVING
  SUM(amount) >= 5000
  AND
  COUNT(*) >= 3;

SELECT
  user_id,
  SUM(amount)  AS total_amount,
  COUNT(*)     AS order_count
FROM orders
GROUP BY user_id
HAVING
  SUM(amount) >= 5000
  AND
  COUNT(*) >= 3;

ここでのポイントは二つあります。

一つ目は、HAVING の中では集計関数をそのまま条件に使えること。
二つ目は、「グループごとの指標を複数組み合わせて、“本当に見たいグループ”だけに絞り込める」こと。

単に「合計が大きいだけ」でもなく、
単に「回数が多いだけ」でもなく、
「たくさん買っていて、かつ回数も多いユーザー」にフォーカスできるわけです。

WHERE と HAVING を両方使う実務パターン

「対象期間を絞ってから、“怪しいグループ”だけに絞る」

もう少し現場寄りの例を出します。

問い：

「2025-04-01 以降のログインだけを対象にして、
　その中でログイン試行回数が 10 回以上のユーザーを、試行回数の多い順に見たい」

分解すると、

1. 2025-04-01 以降のログインだけに絞る（行レベルの条件）
2. user_id ごとに COUNT(*) で試行回数を数える
3. COUNT(*) >= 10 のグループだけを残す（グループレベルの条件）
4. 試行回数の多い順に並べる

SQL はこうなります。

SELECT
  user_id,
  COUNT(*) AS trial_count
FROM login_logs
WHERE date(created_at) >= '2025-04-01'
GROUP BY user_id
HAVING COUNT(*) >= 10
ORDER BY trial_count DESC;

SELECT
  user_id,
  COUNT(*) AS trial_count
FROM login_logs
WHERE date(created_at) >= '2025-04-01'
GROUP BY user_id
HAVING COUNT(*) >= 10
ORDER BY trial_count DESC;

ここでの流れは、

WHERE → GROUP BY → HAVING → ORDER BY

です。

WHERE で「どの行を対象にするか」を決める。
GROUP BY で「どの単位でグループを作るか」を決める。
HAVING で「どのグループを残すか」を決める。
ORDER BY で「どの順番で見るか」を決める。

この四つの役割が頭の中で整理できていると、
かなり複雑な条件付き集計でも、怖くなくなります。

セキュリティ寄りの具体例

「IPごとのアクセス数が多すぎるものだけ」「エラー率が高いユーザーだけ」

もう少しセキュリティっぽい例を二つだけ。

一つ目は、「IPアドレスごとのアクセス数が多すぎるものだけ」を見るパターンです。

access_logs テーブルがこうだとします。

id | ip_address   | path      | status | created_at
---+------------- +---------- +--------+-------------------
 1 | 10.0.0.1     | /login    | 200    | ...
 2 | 10.0.0.2     | /login    | 200    | ...
...

問い：

「1時間あたりのアクセス数が 1000 を超えている IP だけを知りたい」

これは本当は時間ごとの集計も絡みますが、ここではシンプルに「全期間で 1000 超え」とします。

SELECT
  ip_address,
  COUNT(*) AS access_count
FROM access_logs
GROUP BY ip_address
HAVING COUNT(*) > 1000
ORDER BY access_count DESC;

SELECT
  ip_address,
  COUNT(*) AS access_count
FROM access_logs
GROUP BY ip_address
HAVING COUNT(*) > 1000
ORDER BY access_count DESC;

これで、「アクセス数が異常に多い IP」だけを、件数の多い順に見ることができます。

二つ目は、「エラー率が高いユーザーだけ」を見るパターンです。

login_logs に success（1: 成功, 0: 失敗）がある前提で、

「ログイン試行回数が 5 回以上あり、そのうち失敗が 3 回以上のユーザーだけを知りたい」

とします。

これは少しだけ工夫が必要ですが、考え方は同じです。

成功・失敗を分けて COUNT するために、CASE 式を使います。

SELECT
  user_id,
  COUNT(*) AS trial_count,
  SUM(CASE WHEN success = 0 THEN 1 ELSE 0 END) AS fail_count
FROM login_logs
GROUP BY user_id
HAVING
  trial_count >= 5
  AND
  fail_count >= 3
ORDER BY fail_count DESC;

SELECT
  user_id,
  COUNT(*) AS trial_count,
  SUM(CASE WHEN success = 0 THEN 1 ELSE 0 END) AS fail_count
FROM login_logs
GROUP BY user_id
HAVING
  trial_count >= 5
  AND
  fail_count >= 3
ORDER BY fail_count DESC;

ここでは、HAVING の中で「trial_count」「fail_count」という別名を使っていますが、
SQLite では多くの場合これで動きます（不安なら HAVING 側も SUM(…) で書いてもよいです）。

このように、

グループごとの指標を複数作る
その指標に対して HAVING で条件をかける
ORDER BY で“怪しさ”の強い順に並べる

という流れは、セキュリティ運用の現場でかなり実用的です。

小さな練習で締める

日本語の「〜以上の〜ごと」を HAVING に翻訳してみる

頭の中で、次の問いを SQL にしてみてください。

ユーザーごとの合計購入金額が 10000 円以上のユーザーだけを、合計金額の大きい順に見たい。
日付ごとのアクセス件数が 5000 件を超えている日だけを、件数の多い順に見たい。
ユーザーごとのログイン試行回数が 20 回以上のユーザーだけを、試行回数の多い順に見たい。

どれも、

GROUP BY でグループを作る
集計関数で指標を作る
HAVING で「〜以上」を書く
ORDER BY で“多い順”に並べる

という型に当てはめれば書けるはずです。

Day13 後半のまとめ

HAVING と ORDER BY を組み合わせると、「条件を満たすグループだけを“怪しい順”に並べて見る」ことができる。
HAVING には複数の集計条件を書けるので、「合計も回数も一定以上」といった“本当に見たいグループ”だけに絞り込める。
WHERE は行レベル、HAVING はグループレベル、ORDER BY は「見る順番」を決める役割、と整理しておくと複雑なクエリも怖くない。
セキュリティの観点では、「アクセス数が多すぎる IP」「失敗が多すぎるユーザー」など、“優先的に調べるべき対象”を HAVING であぶり出せる。

ここまで来たあなたは、
単に集計するだけでなく、「どのグループを残すか」「どの順番で見るか」まで設計できる状態です。
この先は、JOIN と組み合わせて「別テーブルの情報もくっつけたうえで条件付き集計する」という、より実戦的な SQL に進んでいけます。