SQL 異常検知 — ローリングz-score・複合シグナルの応用

QUESTION 6

ローリングz-score — 直近N日の動的基準で異常を測り、当日除外と最小期間で誤検知を防ぐ

ROWS BETWEEN n PRECEDING当日除外 / leakageローリングz-score動的基準の異常検知

前提知識 ▼

z-score は全期間の固定平均・SDを基準にしました。しかし系列にトレンドや水準変化があると固定基準はすぐ陳腐化します。実務では「直近N日だけ」を基準にする移動z-score（rolling z）が定番です。鍵は2つ——当日を基準から除外すること（自分自身で自分を評価しない＝leakage回避）と、基準が十分に貯まるまで判定を保留すること（min_periods）です。

AVG(amount)    OVER (ORDER BY dt ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING)
-- 「3 PRECEDING AND 1 PRECEDING」= 当日を含めず、直前3日だけを基準にする
-- これが「... AND CURRENT ROW」だと当日(=評価対象)が基準に混入してしまう

当日除外（leakage回避）が肝：フレーム終端を CURRENT ROW ではなく 1 PRECEDING にすると、当日の値は基準計算から外れます。異常な当日値が自分の基準平均・SDを押し上げて自分を正常に見せてしまう事故を防げます（フレーム指定の応用）。

問題 ▼

sensor_readings から、直前3日（当日除外）の移動平均 roll_avg・移動標準偏差 roll_std を求め、z =(amount − roll_avg)/roll_std を計算してください。ただし基準に使えた過去日数 n_base が3未満の行は判定保留（'warmup'）とし、それ以外は |z|≥2 で 'anomaly'、それ未満を 'normal' とします。出力列は dt, amount, roll_avg, roll_std, z_score, status、dt 昇順。平均・SD・z は小数第2位まで。

使用テーブル

► sensor_readings（9行）

dt	amount
2024-01-01	100
2024-01-02	96
2024-01-03	104
2024-01-04	100
2024-01-05	96
2024-01-06	200
2024-01-07	100
2024-01-08	96
2024-01-09	104

期待出力 ▼

dt	amount	roll_avg	roll_std	z_score	status
2024-01-01	100	NULL	NULL	NULL	warmup
2024-01-02	96	100.00	NULL	NULL	warmup
2024-01-03	104	98.00	2.83	2.12	warmup
2024-01-04	100	100.00	4.00	0.00	normal
2024-01-05	96	100.00	4.00	-1.00	normal
2024-01-06	200	100.00	4.00	25.00	anomaly
2024-01-07	100	132.00	58.92	-0.54	normal
2024-01-08	96	132.00	58.92	-0.61	normal
2024-01-09	104	132.00	58.92	-0.48	normal

模範解答コード ▼

WITH rolling AS (
  SELECT
    dt, amount,
    AVG(amount)    OVER w AS roll_avg,   -- 直前3日の移動平均
    STDDEV(amount) OVER w AS roll_std,   -- 直前3日の移動SD（=STDDEV_SAMP）
    COUNT(*)       OVER w AS n_base     -- 基準に使えた過去日数
  FROM sensor_readings
  WINDOW w AS (
    ORDER BY dt
    ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING    -- 当日を除外した直前3日
  )
)
SELECT
  dt, amount,
  ROUND(roll_avg, 2) AS roll_avg,
  ROUND(roll_std, 2) AS roll_std,
  ROUND((amount - roll_avg) / NULLIF(roll_std, 0), 2) AS z_score,
  CASE
    WHEN n_base < 3 THEN 'warmup'                          -- 基準不足なら判定保留
    WHEN ABS((amount - roll_avg) / NULLIF(roll_std, 0)) >= 2
      THEN 'anomaly'
    ELSE 'normal'
  END AS status
FROM rolling
ORDER BY dt;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM sensor_readings   → 行を読み込む
  2. WINDOW w を定義        → 直前3日の窓を定義
  3. AVG/STDDEV/COUNT OVER  → ウィンドウ関数を評価（行数は保持）
  4. SELECT                 → 列を評価（移動z-score）
  5. CASE                   → 列を評価（warmup / anomaly 判定）
  6. ORDER BY dt            → 並び替えて出力
*/

解説（テーブル変化・ポイント） ▼

WITH rolling AS (
  SELECT
    dt, amount,
    AVG(amount) OVER w AS roll_avg,
    STDDEV(amount) OVER w AS roll_std,
    COUNT(*) OVER w AS n_base
  FROM sensor_readings
  WINDOW w AS (
    ORDER BY dt
    ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
  )
)
SELECT
  dt, amount,
  ROUND(roll_avg, 2) AS roll_avg,
  ROUND(roll_std, 2) AS roll_std,
  ROUND((amount - roll_avg) / NULLIF(roll_std, 0), 2) AS z_score,
  CASE
    WHEN n_base < 3 THEN 'warmup'
    WHEN ABS((amount - roll_avg) / NULLIF(roll_std, 0)) >= 2
      THEN 'anomaly'
    ELSE 'normal'
  END AS status
FROM rolling
ORDER BY dt;

LEGEND ▼

データ取得・読込対象

① FROM sensor_readings（9行） ▼

FROM sensor_readings通常は 96/100/104 を循環する系列で、01-06(200)だけが急騰。固定の全期間平均ではなく『直前3日』を動く基準にして、その時々の正常帯から外れた行を検知します。

1 / 6

dt	amount
01-01	100
01-02	96
01-03	104
01-04	100
01-05	96
01-06	200
01-07	100
01-08	96
01-09	104

9行読込

学習ポイント

固定基準 vs 移動基準：AVG(amount) OVER () は全期間の固定基準でしたが、トレンドや水準変化に追従できません。OVER (ORDER BY dt ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) は直近だけの局所基準になり、緩やかな変化を正常として吸収しつつ突発的な逸脱だけを拾えます。

終端を 1 PRECEDING にして当日を除外（leakage回避）：フレーム終端が CURRENT ROW だと評価対象の当日値が基準に混入し、異常値が自分の平均・SDを押し上げて自分を正常化します。1 PRECEDING で当日を外すのが鉄則。これはフレーム指定の直接的な応用です。

min_periods で立ち上がりの誤検知を抑える：基準が1〜2日しか無い時期は SD が過小で、わずかな変動でも z が暴れます。COUNT(*) OVER w で基準日数を数え、n_base < 3 を warmup として保留すれば、本問の 01-03(z=2.12) のような過少サンプル由来の偽陽性を防げます。

アンチパターン

当日を基準に含める：ROWS BETWEEN 3 PRECEDING AND CURRENT ROW とすると、急騰した当日が基準平均・SDを引き上げ、自分の z を不当に小さくします。評価対象は基準から必ず外してください。

立ち上がり期間を無条件に判定する：基準が貯まっていない序盤を warmup で除外しないと、移動z-score は冒頭で誤報を量産します。最低基準日数（min_periods）を設けてから判定してください。逆に急騰の直後は基準SDが膨張し（本問の58.92）次の異常を見逃しやすくなる点にも注意。

実務コラム：移動z-scoreと「自己汚染」問題

移動基準は強力ですが、検知した異常がそのまま後続の基準に取り込まれて基準を歪める「自己汚染」という弱点があります（本問で01-06の200がその後のSDを58.92へ膨張させたのがそれ）。対策として、異常と判定した値を基準計算から除外する／中央値とMAD（中央絶対偏差）のような外れ値に頑健な統計量を使う、といった発展形があります。まずは「当日除外＋min_periods」という土台を固めるのが実務の第一歩です。

QUESTION 7

連続異常の検知 — Gaps & Islands で『N日連続の異常』だけを束ねて拾う

ROW_NUMBER 差分トリック日付 − 連番Gaps & Islands持続的障害の検知

前提知識 ▼

単発の異常（点異常）と、「異常が何日も連続した＝持続的な障害」は意味が違います。後者こそ本当に対応すべきインシデントです。連続する行を1つの塊（島=island）として束ねる定番技法が Gaps & Islands。核心は「連番との差分は、連続している間だけ一定になる」という性質です。

-- 異常日だけを日付順に並べ、連番(rn)を振ると…
dt:   01-05  01-06  01-07     01-10  01-11
rn:     1      2      3         4      5
dt-rn: 01-04  01-04  01-04     01-06  01-06   ← 連続する塊ごとに同じ値！

「日付 − 連番」が島キーになる仕組み：連続した日付は1日ずつ増え、連番も1ずつ増えるので、その差は一定になります。間が空く（gap）と差がズレて新しい島が始まります。この差分値で GROUP BY すれば、連続ランごとに集約できます（ROW_NUMBER と GROUP BY/HAVING の合わせ技）。

問題 ▼

service_health（日次のエラー率）から、error_rate > 5 を異常日と定義し、異常日が3日以上連続したラン（持続的障害）だけを抽出してください。各ランについて 開始日 start_dt・終了日 end_dt・連続日数 run_days・期間中の最大エラー率 peak_error を求め、start_dt 昇順で返してください。

使用テーブル

► service_health（12行）

dt	error_rate
2024-01-01	2
2024-01-02	8
2024-01-03	3
2024-01-04	2
2024-01-05	9
2024-01-06	7
2024-01-07	6
2024-01-08	1
2024-01-09	2
2024-01-10	10
2024-01-11	12
2024-01-12	3

期待出力 ▼

start_dt	end_dt	run_days	peak_error
2024-01-05	2024-01-07	3	9

模範解答コード ▼

WITH flagged AS (              -- ① 異常日だけに絞る
  SELECT dt, error_rate
  FROM service_health
  WHERE error_rate > 5
),
grouped AS (                       -- ② 「日付 − 連番」で島キーを作る
  SELECT
    dt, error_rate,
    dt - INTERVAL '1 day' * ROW_NUMBER() OVER (ORDER BY dt) AS grp
  FROM flagged
)
SELECT                            -- ③ 島ごとに集約し3日以上だけ残す
  MIN(dt)         AS start_dt,
  MAX(dt)         AS end_dt,
  COUNT(*)        AS run_days,
  MAX(error_rate) AS peak_error
FROM grouped
GROUP BY grp
HAVING COUNT(*) >= 3             -- 3日以上連続した島のみ
ORDER BY start_dt;

/*
  実行順序（SQLの論理的な評価順）:
  1. WHERE error_rate > 5    → 行を絞り込む
  2. ROW_NUMBER() で連番     → ウィンドウ関数を評価（行数は保持）
  3. GROUP BY grp            → グループ化
  4. MIN/MAX/COUNT           → 集計関数を評価
  5. HAVING COUNT(*) >= 3    → グループを絞り込む
  6. ORDER BY start_dt       → 並び替えて出力
*/

解説（テーブル変化・ポイント） ▼

WITH flagged AS (
  SELECT dt, error_rate
  FROM service_health
  WHERE error_rate > 5
),
grouped AS (
  SELECT
    dt, error_rate,
    dt - INTERVAL '1 day' * ROW_NUMBER() OVER (ORDER BY dt) AS grp
  FROM flagged
)
SELECT
  MIN(dt) AS start_dt,
  MAX(dt) AS end_dt,
  COUNT(*) AS run_days,
  MAX(error_rate) AS peak_error
FROM grouped
GROUP BY grp
HAVING COUNT(*) >= 3
ORDER BY start_dt;

LEGEND ▼

データ取得・読込対象

① FROM service_health（12行）— 異常日(>5)に着色 ▼

FROM service_health日次エラー率。閾値 error_rate>5 で各日を異常/正常に分けると、異常日が単発・3連続・2連続と散らばっています。1行ずつ見ても『連続性』は分かりません。

1 / 7

dt	error_rate
01-01	2
01-02	8
01-03	3
01-04	2
01-05	9
01-06	7
01-07	6
01-08	1
01-09	2
01-10	10
01-11	12
01-12	3

12行読込（ハイライト=異常 >5）

学習ポイント

「日付 − 連番」が島キーになる：連続した日付は1日刻み、連番も1刻みなのでその差は連続区間で不変になります。間が空くと差がジャンプして新しい島が始まる——この性質を使えば、連続するレコードのグループ化を1つの式で表現できます。Gaps & Islands の心臓部です。

点異常と持続異常を区別する：単発のスパイクと『N日連続の逸脱』は対応の緊急度が違います。連続ランを束ねて HAVING COUNT(*) >= N で絞れば、ノイズ的な単発を除き本物のインシデントだけを抽出できます（HAVING の応用）。

整数の連番なら型に依存しない：本問は日付で dt - INTERVAL '1 day' * rn としましたが、日付に欠損が無い連番列があるなら day_no - rn という整数の引き算でも同じ島キーが作れます。日付の歯抜けが許される定義なら、後者のほうが移植性が高いこともあります。

アンチパターン

LAG で1個前との連続だけ見て、ランの長さを取りこぼす：LAG で「前日も異常か」を判定するだけでは連続が何日続いたかを集計できません。ランの長さ・開始終了が欲しいなら島キーで GROUP BY してください。

絞り込み前に連番を振る：異常日に限定する前（=正常日を含んだまま）に ROW_NUMBER を振ると、連番が正常日でも増えてしまい島キーが崩れます。必ず WHERE で異常日に絞ってから連番を付与してください。

実務コラム：Gaps & Islands はSQLの万能ナイフ

この技法は異常検知に限らず、「連続した期間」を扱うあらゆる場面で登場します——ユーザーの連続ログイン日数（ストリーク）、センサーが同じ状態を続けた区間、在庫切れが続いた日数、契約が連続していた期間…。「連続を1ずつ進む連番との差で表す」という発想を一度押さえると、複雑に見える期間集計が驚くほど短いSQLで書けるようになります。異常検知では「持続＝深刻」という直感を、そのままクエリに落とし込める武器です。

QUESTION 8

季節性ベースライン — LAG(amount, 7) で『前週の同じ曜日』と比べ周期の罠を回避する

LAG(col, n)オフセット参照週次季節性周期を考慮した検知

前提知識 ▼

多くの指標は曜日の周期（週次季節性）を持ちます。平日は低く週末は高い、のような。これを「前日比」で監視すると、毎週末や毎週月曜に正常な周期変動が異常として誤報されます。対策は単純で、比較相手を「昨日」ではなく「1週間前の同じ曜日」にすること。LAG(amount, 7) で7行前を引いてくるだけです。

LAG(amount, 7) OVER (ORDER BY dt)   -- 7日前（前週の同じ曜日）の値
LAG(amount)    OVER (ORDER BY dt)   -- 第2引数省略時は1（=前日）
-- オフセットを変えるだけで「比較の基準」を昨日↔前週同曜日に切替えられる

LAG の第2引数＝何行前か：LAG(col, n) は現在行からn行前の値を返します（既定 n=1）。日次データを日付順に並べれば 7行前=前週同曜日。先頭7日は遡る相手が無く NULL になるため、判定対象から外す（no_baseline）必要があります。

問題 ▼

daily_traffic（14日分・週末は高水準の週次季節性あり）から、各日の値を「7日前（前週同曜日）」と比較し、週次変化率 wow_pct =(amount − base_7d)/base_7d×100 を計算してください。7日前が無い先頭7日は 'no_baseline'、|wow_pct| が 50% を超えたら 'anomaly'、それ以外は 'normal' の status を付与。出力列は dt, amount, base_7d, wow_pct, status、dt 昇順。pct は小数第1位まで。

使用テーブル

► daily_traffic（14行）

dt	amount
2024-01-01	100
2024-01-02	110
2024-01-03	120
2024-01-04	130
2024-01-05	140
2024-01-06	300
2024-01-07	310
2024-01-08	105
2024-01-09	115
2024-01-10	125
2024-01-11	135
2024-01-12	500
2024-01-13	305
2024-01-14	315

期待出力 ▼

dt	amount	base_7d	wow_pct	status
2024-01-01	100	NULL	NULL	no_baseline
2024-01-02	110	NULL	NULL	no_baseline
2024-01-03	120	NULL	NULL	no_baseline
2024-01-04	130	NULL	NULL	no_baseline
2024-01-05	140	NULL	NULL	no_baseline
2024-01-06	300	NULL	NULL	no_baseline
2024-01-07	310	NULL	NULL	no_baseline
2024-01-08	105	100	5.0	normal
2024-01-09	115	110	4.5	normal
2024-01-10	125	120	4.2	normal
2024-01-11	135	130	3.8	normal
2024-01-12	500	140	257.1	anomaly
2024-01-13	305	300	1.7	normal
2024-01-14	315	310	1.6	normal

模範解答コード ▼

WITH wow AS (
  SELECT
    dt, amount,
    LAG(amount, 7) OVER (ORDER BY dt) AS base_7d   -- 7日前=前週同曜日
  FROM daily_traffic
)
SELECT
  dt, amount, base_7d,
  ROUND(100.0 * (amount - base_7d) / NULLIF(base_7d, 0), 1) AS wow_pct,
  CASE
    WHEN base_7d IS NULL THEN 'no_baseline'          -- 比較相手が無い先頭7日
    WHEN ABS(100.0 * (amount - base_7d) / NULLIF(base_7d, 0)) > 50
      THEN 'anomaly'
    ELSE 'normal'
  END AS status
FROM wow
ORDER BY dt;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM daily_traffic   → 行を読み込む
  2. LAG OVER (ORDER BY)  → ウィンドウ関数を評価（行数は保持）
  3. SELECT               → 列を評価（wow_pct, anomaly 判定）
  4. ORDER BY dt          → 並び替えて出力
*/

解説（テーブル変化・ポイント） ▼

WITH wow AS (
  SELECT
    dt, amount,
    LAG(amount, 7) OVER (ORDER BY dt) AS base_7d
  FROM daily_traffic
)
SELECT
  dt, amount, base_7d,
  ROUND(100.0 * (amount - base_7d) / NULLIF(base_7d, 0), 1) AS wow_pct,
  CASE
    WHEN base_7d IS NULL THEN 'no_baseline'
    WHEN ABS(100.0 * (amount - base_7d) / NULLIF(base_7d, 0)) > 50
      THEN 'anomaly'
    ELSE 'normal'
  END AS status
FROM wow
ORDER BY dt;

LEGEND ▼

データ取得・読込対象

① FROM daily_traffic（14行） ▼

FROM daily_traffic平日100台・週末300台という強い週次周期を持つ14日。01-12(500)が唯一の真の異常。周期を無視した『前日比』では週末・週明けが軒並み誤報になってしまいます。

1 / 5

dt	amount
01-01	100
01-02	110
01-03	120
01-04	130
01-05	140
01-06	300
01-07	310
01-08	105
01-09	115
01-10	125
01-11	135
01-12	500
01-13	305
01-14	315

14行読込

学習ポイント

比較の基準を周期に合わせる：季節性のある系列で「前日比」を使うと、毎週末・毎週明けの正常な周期変動が異常に化けます。LAG(amount, 7) で前週同曜日と比べれば、曜日要因が相殺され純粋な異常だけが残ります。前日比を周期対応に拡張したのが本問です。

LAG の第2引数で「何行前」を自在に：LAG(col, n) の n を変えるだけで比較相手を切替えられます。日次なら 7=前週、時間粒度なら 24=前日同時刻、月次なら 12=前年同月。データの周期に合わせて n を選ぶのが季節性検知の勘所です。

基準が無い期間は明示的に除外：先頭7日は LAG の遡り先が無く base_7d IS NULL になります。これを CASE WHEN base_7d IS NULL で no_baseline に分類し、誤って正常/異常と断じないようにします（warmup と同じ思想）。

アンチパターン

季節性を無視して前日比で監視する：週末に跳ね・週明けに落ちる系列で LAG(amount)（前日比）を使うと、本問なら毎週末(+114%)と週明け(−66%)が誤報になります。周期があるなら必ず周期長ぶんオフセットした比較にしてください。

並び順を指定せず LAG を使う：LAG は OVER (ORDER BY dt) の順序が前提です。ORDER BY を欠くと「7行前」がどの行か不定になり結果が壊れます。LAG/LEAD には必ず ORDER BY を付けてください。

実務コラム：季節性は「引き算」で消すか「割り算」で消すか

本問は前週同曜日との変化率で周期を相殺しました。これは季節性除去の最も手軽な形です。より厳密には、曜日ごとの平均を引く（加法的）／曜日係数で割る（乗法的）といった季節調整を行い、調整後の残差に移動z-scoreを当てる、という二段構えが定番です。「まず周期を取り除き、残った変動だけを異常検知する」——この分離の発想が、季節性を持つ実データを扱う上での王道です。

QUESTION 9

複合シグナルの重大度判定 — 水準と急変の2軸を組み合わせCASEで深刻度を分類する

複数CASE分岐真偽値の評価順複合シグナル重大度スコアリング

前提知識 ▼

実務のアラートは「異常か否か」の2値では足りません。複数のシグナルを組み合わせ、深刻度（severity）でランク分けするのが定番です。本問は2つの独立した観点——水準（値が高いか）と急変（前日から急に変わったか）——を掛け合わせ、4段階に分類します。鍵は CASE の分岐の順序と、真偽値（boolean）をそのまま条件に使う書き方です。

CASE
  WHEN impact_high AND urgency_high THEN 'BLOCKER'  -- 影響・緊急度とも高い
  WHEN urgency_high                 THEN 'URGENT'   -- 緊急度だけ高い
  WHEN impact_high                  THEN 'REVIEW'   -- 影響だけ高い
  ELSE 'NORMAL'
END

CASE は上から順に評価され、最初に真になった枝で確定：そのため複合条件など最も厳しい条件を一番上に置きます。単独条件を先に置くと、複合条件に該当する行が途中の枝で確定し、重大度を取りこぼします。

問題 ▼

metric_stream から、2つのシグナルを計算してください：水準シグナル lvl_hi =(amount > 200) と 急変シグナル jump_hi =(amount − 前日値 ≥ 100)。これらを CASE で組み合わせ、両方真→'CRITICAL'／水準のみ→'WARNING'／急変のみ→'WATCH'／どちらも偽→'OK' の severity を付与します。出力列は dt, amount, delta, severity、dt 昇順（delta = amount − 前日値）。

使用テーブル

► metric_stream（8行）

dt	amount
2024-01-01	100
2024-01-02	105
2024-01-03	215
2024-01-04	220
2024-01-05	120
2024-01-06	235
2024-01-07	90
2024-01-08	195

期待出力 ▼

dt	amount	delta	severity
2024-01-01	100	NULL	OK
2024-01-02	105	+5	OK
2024-01-03	215	+110	CRITICAL
2024-01-04	220	+5	WARNING
2024-01-05	120	-100	OK
2024-01-06	235	+115	CRITICAL
2024-01-07	90	-145	OK
2024-01-08	195	+105	WATCH

模範解答コード ▼

WITH signals AS (
  SELECT
    dt, amount,
    amount - LAG(amount) OVER (ORDER BY dt) AS delta,           -- 前日差（急変の度合い）
    (amount > 200) AS lvl_hi,                                   -- 水準シグナル（真偽値）
    (amount - LAG(amount) OVER (ORDER BY dt) >= 100) AS jump_hi -- 急変シグナル（真偽値）
  FROM metric_stream
)
SELECT
  dt, amount, delta,
  CASE
    WHEN lvl_hi AND jump_hi THEN 'CRITICAL'   -- 厳しい条件から先に判定
    WHEN lvl_hi             THEN 'WARNING'
    WHEN jump_hi            THEN 'WATCH'
    ELSE 'OK'
  END AS severity
FROM signals
ORDER BY dt;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM metric_stream     → 行を読み込む
  2. LAG OVER (ORDER BY dt) → ウィンドウ関数を評価（行数は保持）
  3. SELECT                 → 列を評価（シグナル, CASE で深刻度判定）
  4. ORDER BY dt            → 並び替えて出力
*/

解説（テーブル変化・ポイント） ▼

WITH signals AS (
  SELECT
    dt, amount,
    amount - LAG(amount) OVER (ORDER BY dt) AS delta,
    (amount > 200) AS lvl_hi,
    (amount - LAG(amount) OVER (ORDER BY dt) >= 100) AS jump_hi
  FROM metric_stream
)
SELECT
  dt, amount, delta,
  CASE
    WHEN lvl_hi AND jump_hi THEN 'CRITICAL'
    WHEN lvl_hi             THEN 'WARNING'
    WHEN jump_hi            THEN 'WATCH'
    ELSE 'OK'
  END AS severity
FROM signals
ORDER BY dt;

LEGEND ▼

データ取得・読込対象

① FROM metric_stream（8行） ▼

FROM metric_stream単一系列の値。これを『水準が高いか』『前日から急変したか』の2軸で評価し、組み合わせで深刻度を4段階に分けます。単純な閾値1本では表せないニュアンスを表現します。

1 / 5

dt	amount
01-01	100
01-02	105
01-03	215
01-04	220
01-05	120
01-06	235
01-07	90
01-08	195

8行読込

学習ポイント

CASE は『厳しい条件を上に』が鉄則：CASE は上から順に評価し、最初に真になった枝で確定します。CRITICAL（両条件）を先頭に置かないと、CRITICAL 該当行が先に WARNING で確定してしまいます。包含関係のある条件は、狭い（厳しい）ものから書くのが定石です。

比較式はそのまま真偽値になる：(amount > 200) AS lvl_hi のように、比較の結果を boolean 列として持てます。これを WHEN lvl_hi AND jump_hi と組み合わせれば、複数シグナルの論理合成が読みやすく書けます。中間結果を CTE で名付けると、検知ロジックの意図が明確になります。

複合シグナルで誤報を減らす：単一閾値（amount>200）だけだと、緩やかに高いだけの行も急騰した行も同じ扱いになります。水準×急変のように観点を掛け合わせると、対応優先度の高い『高水準かつ急騰』を切り出せ、アラート疲れを防げます。

アンチパターン

CASE の枝を緩い順に並べる：WHEN lvl_hi THEN 'WARNING' を CRITICAL より上に書くと、両条件を満たす行も WARNING で確定し、CRITICAL に永遠に到達しません。網羅性と排他性を意識し、厳しい条件を先に書いてください。

NULL を含む真偽値の扱いを忘れる：先頭行は delta が NULL のため jump_hi も NULL になります。WHEN jump_hi は NULL を真として扱わない（=スキップ）ので本問は ELSE の OK に落ちますが、NULL を明示的に偽にしたい場合は COALESCE(jump_hi, false) を検討します。三値論理（true/false/NULL）の挙動を常に意識してください。

実務コラム：スコアリングと「アラート疲れ」

本問は2軸4段階でしたが、実務ではさらに多くのシグナル（z-score・ドリフト・季節残差・件数比…）を重み付けで合算した連続スコアにし、閾値で CRITICAL/WARNING/INFO に切るのが一般的です。重要なのは「すべてを同じ重大度で鳴らさない」こと。シグナルが多いほど誤報も増えるため、複数の観点が同時に立った時だけ最上位で鳴らす設計が、運用者の信頼を保つ鍵になります。検知の精度と同じくらい、深刻度の設計が現場では効きます。

QUESTION 10

アラートサマリの集約 — 多段 FILTER と GROUP BY ROLLUP で『カテゴリ別＋全体』を一枚に【総まとめ】

COUNT(*) FILTER ×複数GROUP BY ROLLUP重大度別の集計サマリ＋小計総まとめ

前提知識 ▼

応用編の締めは運用に渡す『アラートサマリ』の作成です。基礎編の COUNT(*) FILTER を発展させ、重大度ごとに複数の FILTER 列を並べて深刻度別件数を一度に数え、さらに GROUP BY ROLLUP でカテゴリ別の明細と全体の総計を同じ結果に同居させます。1クエリでダッシュボードの一枚が完成します。

COUNT(*) FILTER (WHERE amount >= 300)                  -- critical 件数
COUNT(*) FILTER (WHERE amount >= 200 AND amount < 300)  -- warning 件数
GROUP BY ROLLUP (category)   -- カテゴリ別の各行 ＋ 全体の総計行(category=NULL) を生成

ROLLUP は「明細＋小計」を一発で：GROUP BY ROLLUP (category) は通常のカテゴリ別グループに加え、全カテゴリを束ねた総計行を自動で1行追加します。その総計行では category が NULL になるので、COALESCE(category,'(ALL)') でラベル化し、GROUPING(category) で並び順を制御します。

問題 ▼

event_log から、amount>=300 を critical、200≤amount<300 を warning と定義し、カテゴリ別に総件数・critical件数・warning件数・critical率% を集計してください。さらに ROLLUP で全カテゴリの総計行も同じ結果に含め、総計行のラベルは (ALL) とします。出力列は category, total_events, critical, warning, crit_pct、カテゴリ行を category 昇順、総計行を最後に。pct は小数第1位まで。

使用テーブル

► event_log（12行）

category	dt	amount
api	2024-01-01	350
api	2024-01-02	120
api	2024-01-03	130
api	2024-01-04	95
auth	2024-01-01	260
auth	2024-01-02	280
auth	2024-01-03	240
auth	2024-01-04	90
web	2024-01-01	100
web	2024-01-02	250
web	2024-01-03	300
web	2024-01-04	320

期待出力 ▼

category	total_events	critical	warning	crit_pct
api	4	1	0	25.0
auth	4	0	3	0.0
web	4	2	1	50.0
(ALL)	12	3	4	25.0

模範解答コード ▼

SELECT
  COALESCE(category, '(ALL)') AS category,        -- 総計行(category=NULL)を(ALL)に
  COUNT(*) AS total_events,                       -- 総件数（母数）
  COUNT(*) FILTER (WHERE amount >= 300) AS critical,                  -- critical件数
  COUNT(*) FILTER (WHERE amount >= 200 AND amount < 300) AS warning,  -- warning件数
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE amount >= 300) / COUNT(*)
  , 1) AS crit_pct
FROM event_log
GROUP BY ROLLUP (category)                       -- カテゴリ別 ＋ 全体総計行
ORDER BY GROUPING(category), category;            -- 明細(0)を先・総計(1)を最後に

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM event_log               → 行を読み込む
  2. GROUP BY ROLLUP (category)   → グループ化（カテゴリ別＋総計）
  3. COUNT(*) と FILTER           → 集計関数を評価
  4. SELECT                       → 列を評価（crit_pct, ラベル整形）
  5. ORDER BY GROUPING(category), category  → 並び替えて出力
*/

解説（テーブル変化・ポイント） ▼

SELECT
  COALESCE(category, '(ALL)') AS category,
  COUNT(*) AS total_events,
  COUNT(*) FILTER (WHERE amount >= 300) AS critical,
  COUNT(*) FILTER (WHERE amount >= 200 AND amount < 300) AS warning,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE amount >= 300) / COUNT(*)
  , 1) AS crit_pct
FROM event_log
GROUP BY ROLLUP (category)
ORDER BY GROUPING(category), category;

LEGEND ▼

データ取得・読込対象

① FROM event_log（12行）— 赤=critical / 橙=warning ▼

FROM event_log3カテゴリ×4件のイベント。amount>=300 を critical、200〜299 を warning と定義します。この明細から『カテゴリ別の深刻度内訳』と『全体の総計』を1クエリで作ります。

1 / 6

category	dt	amount
api	01-01	350
api	01-02	120
api	01-03	130
api	01-04	95
auth	01-01	260
auth	01-02	280
auth	01-03	240
auth	01-04	90
web	01-01	100
web	01-02	250
web	01-03	300
web	01-04	320

12行読込（赤=crit/橙=warn）

学習ポイント

多段 FILTER で重大度の内訳を一度に：COUNT(*) FILTER (WHERE amount >= 300) と ... (WHERE amount >= 200 AND amount < 300) を並べると、1グループからcritical と warning を同時に取れます。基礎編の単一 FILTER を重大度の段数だけ並べるのが応用形です。

ROLLUP は「明細＋小計」を同じ結果に：GROUP BY ROLLUP (category) は通常のカテゴリ別行に加え、全体を束ねた総計行を自動生成します。アプリ側で別途「合計クエリ」を投げなくても、1クエリでダッシュボードの1枚（明細＋全体）が完成します。複数列を渡せば小計の階層も作れます。

総計行の NULL は COALESCE と GROUPING で扱う：ROLLUP の総計行は集約キーが NULL になります。表示は COALESCE(category,'(ALL)') でラベル化し、並び順は GROUPING(category)（明細=0／総計=1）で制御します。NULL が「データ欠損」なのか「総計行」なのかを区別するのが ROLLUP の作法です。

アンチパターン

全体合計を別クエリ＋UNIONで継ぎ足す：明細クエリと合計クエリを UNION ALL でつなぐのは冗長で、集計定義の二重管理になりミスの温床です。明細と総計が欲しいなら ROLLUP 一発で書いてください。

warning の上限を区切り忘れて二重計上する：FILTER (WHERE amount >= 200) だけだと critical(≥300)まで warning に含まれ、件数が重複します。区間で数えるなら amount >= 200 AND amount < 300 と上限を必ず閉じてください。

実務コラム：検知から「経営に届くサマリ」へ — Q6〜Q10 応用編の総まとめ

応用編を振り返ります。①動的基準でローリングz-score（Q6）、②持続性を Gaps & Islands（Q7）、③周期を LAG オフセットで除去（Q8）、④複合シグナルで重大度を判定（Q9）。そして本問の⑤多段 FILTER ＋ ROLLUPで、それらの検知結果を『カテゴリ別の内訳と全体の総計が一目で分かる一枚のサマリ』に集約しました。検知ロジックがどれだけ高度でも、最後に人や経営を動かすのは「集約され、優先度が付き、全体像が見える一枚」です。基礎編で文法を、応用編で実務的な検知設計を学んだ今、専用ツールに頼らずとも実務水準の異常監視がSQLだけで組めるはずです。