SQL ウィンドウ関数 — PERCENT_RANK・LAG+LEADの応用

QUESTION 6

NTILE() — 購入額を4分位に分割してユーザーをセグメント化する

NTILE番号付け関数セグメンテーション

前提知識 ▼

NTILE(N) は全行を 最大 N 個のサイズ差が1行以内のグループ（バケット）に分割し、各行に 1〜N の範囲でグループ番号を割り当てます。
ROW_NUMBER / RANK / DENSE_RANK と同じ「番号付け系」関数の仲間です。

NTILE(4) OVER(ORDER BY total_purchase DESC)
-- 購入額を高い順に並べて4等分
-- 1=最高額グループ（上位25%）
-- 4=最低額グループ（下位25%）

割り切れない場合の動作：行数が N で割り切れないとき、余りの行は 先頭のグループから1行ずつ余分に割り当てられます。例：6行÷4グループ → tier1,tier2 に2行ずつ、tier3,tier4 に1行ずつ。

問題 ▼

以下の customers テーブルを使用し、購入総額(total_purchase)の高い順に全ユーザーを 4段階のティア (tier) に分類してください。
tier 1 が最高額、tier 4 が最低額グループとなるよう降順で分割すること。

使用テーブル

▸ customers

user_id	name	total_purchase
U1	田中	48000
U2	鈴木	12000
U3	佐藤	95000
U4	高橋	33000
U5	伊藤	71000
U6	渡辺	8000

期待出力 ▼

user_id	name	total_purchase	tier
U3	佐藤	95000	1
U5	伊藤	71000	1
U1	田中	48000	2
U4	高橋	33000	2
U2	鈴木	12000	3
U6	渡辺	8000	4

模範解答コード ▼

SELECT
  user_id,
  name,
  total_purchase,
  NTILE(4) OVER(
    ORDER BY total_purchase DESC
  ) AS tier                       -- 購入額が高い順に並べ、均等に4グループへ分割
FROM customers
ORDER BY total_purchase DESC;

/*
  実行順序:
  1. FROM customers  → 全6行を取得
  2. NTILE(4) OVER(ORDER BY total_purchase DESC) を評価
  3. ORDER BY total_purchase DESC で出力ソート
  */

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

SELECT
  user_id, name, total_purchase,
  NTILE(4) OVER(
    ORDER BY total_purchase DESC
  ) AS tier
FROM customers
ORDER BY total_purchase DESC;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM customersテーブルを読み込みます。ORDER BY 前の元の行順です。

1 / 3

user_id	name	total_purchase
U1	田中	48000
U2	鈴木	12000
U3	佐藤	95000
U4	高橋	33000
U5	伊藤	71000
U6	渡辺	8000

全 6行読込

学習ポイント

NTILE vs RANK の使い分け：RANK は「同じ値なら同じ順位」を返すため、順位ごとのユーザー数が不均等になることがあります。一方 NTILE は最大 N 個のグループへ、サイズ差が1行以内になるよう分割するため、「全ユーザーを4つのほぼ同数のグループに分けたい」という要件には NTILE が適切です。

実務でのセグメンテーション：CRM システムでは NTILE(4) で購入額 Tier1〜4 を定義し、「VIP顧客（Tier1）には専任サポート、Tier4 には割引クーポン」のような差別化施策を自動で振り分けます。NTILE は「行数で均等に分ける」機械的な手法なので、分析ダッシュボードの初手として非常に使いやすい関数です。

余りの配分ルール：NTILE(N) で行数が N で割り切れないとき、余り r があれば「先頭 r グループだけ1行多く」割り当てられます。10行を3分割なら：tier1=4行、tier2=3行、tier3=3行（10÷3=3余り1なのでtier1だけ+1行）。

NTILE(4) の配分ロジック — 6行を4グループに分割する仕組み

仮ソート順	name	total_purchase	NTILE(4)	グループ内の行数
1番目	佐藤	95,000	1	← 6÷4=1余り2。先頭2グループは+1行
2番目	伊藤	71,000	1	tier1 = 2行
3番目	田中	48,000	2	← 2番目のグループも+1行
4番目	高橋	33,000	2	tier2 = 2行
5番目	鈴木	12,000	3	tier3 = 1行（余りなし）
6番目	渡辺	8,000	4	tier4 = 1行（余りなし）

計算: 6行 ÷ 4グループ = 商1、余り2 → 先頭2グループ(tier1,tier2)のサイズ = 1+1 = 2行、残り(tier3,tier4) = 1行。

アンチパターン

NTILE(N) で N が行数より多い場合：NTILE(10) を3行のテーブルに適用すると、tier1〜3 のみが存在し tier4〜10 は空になります（エラーにはなりません）。NTILE(N) の N は「欲しいグループ数の上限」であり、実際のグループ数はデータ行数を超えません。

ORDER BY を省略した NTILE：ORDER BY なしで NTILE を使うと、DBエンジンが行を任意の順で処理するため、同じクエリを実行するたびにグループ割り当てが変わる可能性があります。必ず意味のある ORDER BY 列を指定してください。

QUESTION 7

PERCENT_RANK() — スコアが全体の何%に位置するかを計算する

PERCENT_RANK相対順位パーセンタイル

前提知識 ▼

PERCENT_RANK() は各行の「全体の中での相対的な位置」を 0.0〜1.0 の小数で返します。
複数行なら最小値の行は 0.0（0パーセンタイル）、最大値の行は 1.0（100パーセンタイル）になります。1行だけの場合は 0.0 です。

PERCENT_RANK() OVER(ORDER BY score)
-- 計算式: (rank - 1) / (total_rows - 1)
-- 例: 5行中3位 → (3-1)/(5-1) = 0.5 = 50パーセンタイル

CUME_DIST との違い：CUME_DIST()（累積分布）は「自分以下の行が全体の何割か」を返し、0 より大きく 1 以下の値になります。PERCENT_RANK は順位を (rank - 1) / (total_rows - 1) で正規化して 0 始まりになります。両者は同点がなくても値が異なり、同点(タイ)が生じると順位の扱いによる差がさらに重要になります。

問題 ▼

以下の exam_scores テーブルを使用し、各社員のテストスコアが全体の中で何%に位置するか（パーセンタイル）を求めてください。
出力は小数点以下1桁（%換算）で表示し、スコアの昇順で出力すること。

使用テーブル

▸ exam_scores

employee_id	name	score
E1	田中	70
E2	鈴木	90
E3	佐藤	60
E4	高橋	85
E5	伊藤	75

期待出力 ▼

name	score	percentile
佐藤	60	0.0
田中	70	25.0
伊藤	75	50.0
高橋	85	75.0
鈴木	90	100.0

模範解答コード ▼

SELECT
  name,
  score,
  ROUND(
    (PERCENT_RANK() OVER(ORDER BY score) * 100)::numeric,  -- numericへcastして2引数ROUNDを使用
    1                                           -- 小数点1桁に丸め
  ) AS percentile
FROM exam_scores
ORDER BY score;                                 -- スコア昇順で出力

/*
  計算式: PERCENT_RANK = (rank - 1) / (total_rows - 1)
  各行の内訳:
    佐藤 score=60: rank=1 → (1-1)/(5-1) = 0/4 = 0.0  →  0.0%
    田中 score=70: rank=2 → (2-1)/(5-1) = 1/4 = 0.25 → 25.0%
    伊藤 score=75: rank=3 → (3-1)/(5-1) = 2/4 = 0.5  → 50.0%
    高橋 score=85: rank=4 → (4-1)/(5-1) = 3/4 = 0.75 → 75.0%
    鈴木 score=90: rank=5 → (5-1)/(5-1) = 4/4 = 1.0  →100.0%

  実行順序:
  1. FROM exam_scores
  2. PERCENT_RANK() OVER(ORDER BY score): score昇順で相対位置を計算
  3. × 100 → ROUND(..., 1) でパーセント表示に変換
  4. ORDER BY score で出力
*/

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

SELECT
  name, score,
  ROUND(
    PERCENT_RANK() OVER(ORDER BY score) * 100)::numeric,
    1
  ) AS percentile
FROM exam_scores
ORDER BY score;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM exam_scores全5行を読み込みます。

1 / 3

employee_id	name	score
E1	田中	70
E2	鈴木	90
E3	佐藤	60
E4	高橋	85
E5	伊藤	75

全 5行読込

学習ポイント

PERCENT_RANK vs CUME_DIST の使い分け：PERCENT_RANK は順位を (rank - 1) / (n - 1) で正規化します（複数行なら最小=0、最大=1）。CUME_DIST は「自分以下の行が全体の何割か」（最大=1、最小>0）です。両者は同点がなくても分母と基準が異なるため値が異なり、同点があると差がさらに顕著になります。PERCENT_RANK は「順位の相対位置」に、CUME_DIST は「この値以下が何%か」の表現に向いています。

実務での使い方 — 偏差値・パーセンタイル分析：大学入試の偏差値に相当する指標をデータベースで出したいとき、PERCENT_RANK と NTILE を組み合わせます。「全商品のうち上位10%に入る商品」を抽出する場合は WHERE PERCENT_RANK() OVER(...) >= 0.9（サブクエリ内で計算後にフィルタ）というパターンを使います。

PERCENT_RANK vs CUME_DIST — タイ(同点)がない場合の比較

name	score	PERCENT_RANK() = (rank-1)/(n-1)	CUME_DIST() = rank/n	読み取り方
佐藤	60	0.0 (0%)	0.2 (20%)	CUME_DIST は「自分=60以下」= 1人 = 1/5
田中	70	0.25 (25%)	0.4 (40%)	「70以下」= 2人 = 2/5
伊藤	75	0.5 (50%)	0.6 (60%)	「75以下」= 3人 = 3/5
高橋	85	0.75 (75%)	0.8 (80%)
鈴木	90	1.0 (100%)	1.0 (100%)	最大値は両者とも必ず1.0

タイなしで昇順の i 行目なら、PERCENT_RANK は (i-1)/(n-1)、CUME_DIST は i/n。差は行ごとに変わり、最小行では PERCENT_RANK=0、CUME_DIST=1/n、最大行では両者とも1になる。

アンチパターン

ORDER BY の向きを間違える：ORDER BY score ASC では最低スコアが 0.0 になりますが、ORDER BY score DESC にすると最高スコアが 0.0 になります。「上位〇%」を表したい場合は ORDER BY DESC で PERCENT_RANK を使い、「1 - PERCENT_RANK」で上位パーセンタイルを計算する場合もあります。用途に合わせて昇降順を意識しましょう。

QUESTION 8

WITH句（CTE）— ウィンドウ関数をモジュール化して累計・構成比を求める

CTE / WITH句SUM OVER累計・構成比可読性向上

前提知識 ▼

ウィンドウ関数を含む長いクエリをサブクエリで書くと、ネストが深くなり読みづらくなります。WITH 句（CTE: Common Table Expression）を使うと、「まずウィンドウ関数で中間テーブルを作り、次にそれを使って計算する」という2ステップを上から下へ自然に読めるモダンな書き方になります。

WITH cte_name AS (
  -- ① ウィンドウ関数で中間テーブルを作成
  SELECT ..., SUM(col) OVER(...) AS running_total
  FROM tbl
)
-- ② 中間テーブルを参照して追加計算
SELECT ..., running_total / total * 100 AS pct
FROM cte_name;

OVER() の意味（引数なし）：SUM(revenue) OVER() のように OVER 内に何も書かないと、全行を対象にした集計が計算されます（= テーブル全体の合計）。これを利用して「全体合計」と「累計」を1つのCTE内で同時に計算できます。

問題 ▼

以下の monthly_revenue テーブルを使用し、WITH 句を活用して月ごとに以下の3つの値を出力してください。
1. running_total — 1月からの累計売上
2. monthly_pct — その月の売上が全体に占める割合（%、小数点1桁）

使用テーブル

▸ monthly_revenue

month	revenue
01月	200
02月	250
03月	180
04月	320
05月	290

期待出力 ▼

month	revenue	running_total	monthly_pct
01月	200	200	16.1
02月	250	450	20.2
03月	180	630	14.5
04月	320	950	25.8
05月	290	1240	23.4

模範解答コード ▼

WITH monthly_agg AS (
  SELECT
    month,
    revenue,
    SUM(revenue) OVER(ORDER BY CASE month
      WHEN '01月' THEN 1 WHEN '02月' THEN 2
      WHEN '03月' THEN 3 WHEN '04月' THEN 4
      WHEN '05月' THEN 5 END) AS running_total,
    SUM(revenue) OVER() AS total_revenue                    -- ② 全体合計: OVER() のみ = 全行が対象 = テーブル合計
  FROM monthly_revenue
)
SELECT
  month,
  revenue,
  running_total,
  ROUND(revenue * 100.0 / total_revenue, 1) AS monthly_pct  -- CTE 内の total_revenue を使って構成比(%)を計算
FROM monthly_agg
ORDER BY CASE month
  WHEN '01月' THEN 1 WHEN '02月' THEN 2
  WHEN '03月' THEN 3 WHEN '04月' THEN 4
  WHEN '05月' THEN 5 END;

/*
  実行順序:
  1. CTE(monthly_agg)内で FROM monthly_revenue を評価
  2. CTE内で2つのウィンドウ関数を同時実行
  3. 外側 SELECT で total_revenue を使って monthly_pct を計算し、total_revenue 自体は出力から除外
  4. ORDER BY CASE month WHEN '01月' THEN 1 WHEN '02月' THEN 2 WHEN '03月' THEN 3 WHEN '04月' THEN 4 WHEN '05月' THEN 5 END→05月の論理順に出力
  */

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

WITH monthly_agg AS (
  SELECT month, revenue,
    SUM(revenue) OVER(ORDER BY CASE month WHEN '01月' THEN 1 WHEN '02月' THEN 2 WHEN '03月' THEN 3 WHEN '04月' THEN 4 WHEN '05月' THEN 5 END) AS running_total,
    SUM(revenue) OVER() AS total_revenue
  FROM monthly_revenue
)
SELECT
  month, revenue,
  running_total,
  ROUND(revenue * 100.0 / total_revenue, 1)
  AS monthly_pct
FROM monthly_agg
ORDER BY CASE month WHEN '01月' THEN 1 WHEN '02月' THEN 2 WHEN '03月' THEN 3 WHEN '04月' THEN 4 WHEN '05月' THEN 5 END;

LEGEND ▼

評価対象の列・キー

① CTE 定義（WITH句） ▼

WITH monthly_agg AS ( SELECT ... FROM monthly_revenue )CTE は「名前付きの一時的なサブクエリ」です。ここで monthly_revenue テーブルから論理月順の SUM OVER(ORDER BY CASE ...) と SUM OVER() の2つのウィンドウ関数を計算し、中間テーブル monthly_agg を作ります。

1 / 3

month	revenue	running_total (論理月順)	total_revenue (SUM OVER())
01月	200	200	1240
02月	250	450	1240
03月	180	630	1240
04月	320	950	1240
05月	290	1240	1240

CTE = 中間テーブル完成（5行）

学習ポイント

サブクエリとの可読性の差：同じロジックをサブクエリで書くと SELECT ... FROM (SELECT ... FROM ...) t WHERE ... とネストが深くなります。CTE は「上から下へ」読めるため、複数の集計ステップが必要な実務クエリでは CTE が事実上の標準です。コードレビューでも好まれます。

OVER() の意味を押さえる：SUM(col) OVER() は「パーティション=全行、フレーム=全行」を意味します。この結果は 全行に同じ値が付与されるので、「全体合計に対する比率」を計算する際の定番パターンです。GROUP BY を使わず行を潰さないことが重要です。

複数 CTE を連結する：WITH cte1 AS (...), cte2 AS (SELECT ... FROM cte1) のように、複数の CTE をカンマで繋いで順番に定義できます。BI ツールやデータマート構築では「前処理 CTE → 集計 CTE → 最終出力」という3層構造が頻繁に使われます。

OVER() の引数ありなしで何が変わるか — 並べて比較

記法	意味	01月	02月	03月	04月	05月
SUM OVER()	全行合計（固定）	1240	1240	1240	1240	1240
SUM OVER(論理月順)	累計（行ごとに増加）	200	450	630	950	1240

最終行(05月)では両者とも 1240 になる。CTE でこの2値を同じ行に並べることで、running_total / total_revenue = 「5月までの累計が全体に占める割合」も計算可能になる。

アンチパターン

CTE 内でウィンドウ関数の結果を WHERE で使おうとする：CTE 内でも「同じ SELECT 内のウィンドウ関数の結果を WHERE で直接使う」ことはできません。WHERE SUM(revenue) OVER() > 1000 とは書けません。必ず「CTE でウィンドウ関数を計算 → 外側クエリで WHERE」という2層構造にしてください（パターンと同様）。

整数同士の除算に注意：PostgreSQL など整数除算を切り捨てるDBでは、revenue * 100 / total_revenue の小数部が失われます。DB間で意図を明確にするため、revenue * 100.0 / total_revenue のように .0 を付けるか、CAST(revenue AS DECIMAL) で実数に変換してから除算してください。

QUESTION 9

LAG() + LEAD() 同時活用 — 前後の値を比較して「ピーク日」を自動検出する

LAG + LEAD前後比較CASE WHEN異常検知

前提知識 ▼

LAG（前の行）と LEAD（次の行）を 同時に 使うことで、「前後の値との3点比較」が可能になります。これはデータのピーク（極大値）・谷（極小値）検出、急変検知など、時系列分析で頻出のパターンです。

LAG(amount)  OVER(ORDER BY date)  AS prev_amount  -- 1行前の値
LEAD(amount) OVER(ORDER BY date)  AS next_amount  -- 1行後の値

-- CASE WHEN で3点比較 → ピーク判定
CASE
  WHEN amount > prev_amount AND amount > next_amount THEN 'PEAK'
END

先頭行・末尾行の扱い：先頭行の LAG は NULL、末尾行の LEAD は NULL を返します。NULL との比較（> NULL）は UNKNOWN となり、CASE WHEN では真として扱われないため、先頭・末尾行は自動的にピーク判定から除外されます。これは意図通りの動作です。

問題 ▼

以下の daily_sales テーブルを使用し、各日付の「前日の売上 prev_amount」「翌日の売上 next_amount」を横に並べ、さらに前後両方の日より売上が高い日に 'PEAK' を、それ以外は空文字 '' を is_peak カラムとして出力してください。

使用テーブル

▸ daily_sales

date	amount
04-01	80
04-02	160
04-03	110
04-04	190
04-05	130
04-06	175

期待出力 ▼

date	amount	prev_amount	next_amount	is_peak
04-01	80	NULL	160
04-02	160	80	110	PEAK
04-03	110	160	190
04-04	190	110	130	PEAK
04-05	130	190	175
04-06	175	130	NULL

模範解答コード ▼

SELECT
  date,
  amount,
  LAG(amount)  OVER(ORDER BY date) AS prev_amount,
  LEAD(amount) OVER(ORDER BY date) AS next_amount,   -- 1行前の売上 (先頭行はNULL)
  CASE                                               -- 1行後の売上 (末尾行はNULL)
    WHEN amount > LAG(amount)  OVER(ORDER BY date)   -- 前後どちらの日よりも高い場合のみ PEAK と判定
     AND  amount > LEAD(amount) OVER(ORDER BY date)
    THEN 'PEAK'
    ELSE ''
  END AS is_peak

FROM daily_sales
ORDER BY date;

/*
  実行順序:
  1. FROM daily_sales   → 全6行取得
  2. LAG と LEAD を同時に付与  → 前後の値を取得
  3. CASE WHEN で3点比較    → ピークを判定
  4. ORDER BY date      → 出力
  */

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

SELECT
  date, amount,
  LAG(amount) OVER(ORDER BY date) AS prev_amount,
  LEAD(amount) OVER(ORDER BY date) AS next_amount,
  CASE
    WHEN amount > LAG(amount) OVER(ORDER BY date)
     AND amount > LEAD(amount) OVER(ORDER BY date)
    THEN 'PEAK'
    ELSE ''
  END AS is_peak
FROM daily_sales
ORDER BY date;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM daily_sales全6行を読み込みます。

1 / 3

date	amount
04-01	80
04-02	160
04-03	110
04-04	190
04-05	130
04-06	175

全 6行読込

学習ポイント

NULL との比較は UNKNOWN — 設計上の恩恵：先頭行は LAG = NULL なので amount > NULL が UNKNOWN になり、CASE WHEN では真として扱われず、ピーク判定から自動除外されます。これは「端点はピークになりえない」という直感に合致した動作で、COALESCE で補正する必要はありません。ただし「端点もピーク候補にしたい」なら COALESCE(LAG(...), -1) のようにデフォルト値を与えて比較条件を満たせるようにします。

LAG/LEAD の offset 引数：LAG(amount, 2) と書くと「2行前の値」、LEAD(amount, 3) は「3行後の値」を取得できます。第3引数にデフォルト値を指定でき、LAG(amount, 1, 0) なら NULL の代わりに 0 が返ります。

実務での活用 — KPI 異常検知：日次売上やサーバーメトリクスの監視で、「前後の平均より X% 以上乖離している行」をこのパターンで検出します。また「連続して前日より上昇している期間（上昇トレンド）」の検出には CASE WHEN amount > LAG(amount)... AND LAG(amount) > LAG(amount,2)... THEN '上昇' という連鎖比較も使えます。

LAG + LEAD の3点比較 — 各行が前後を同時参照する仕組み

date	amount	prev (LAG)	next (LEAD)	prev比較	next比較	is_peak
04-01	80	NULL	160	80 > NULL → UNKNOWN		—
04-02	160	80	110	160 > 80 ✓	160 > 110 ✓	PEAK
04-03	110	160	190	110 > 160 ✗		—
04-04	190	110	130	190 > 110 ✓	190 > 130 ✓	PEAK
04-05	130	190	175	130 > 190 ✗		—
04-06	175	130	NULL	175 > 130 ✓	175 > NULL → UNKNOWN	—

PEAK 判定は「前後どちらの比較も TRUE」の AND 条件。先頭・末尾行の NULL 比較は UNKNOWN となり CASE WHEN では条件を満たさないため、端点は安全に除外される。

アンチパターン

CASE WHEN 内でウィンドウ関数を重複記述する非効率：このクエリでは LAG(amount) OVER(ORDER BY date) と LEAD(amount) OVER(ORDER BY date) がそれぞれ2回書かれています（SELECT 列と CASE WHEN の中で各1回ずつ）。パフォーマンスが気になる場合は CTE でウィンドウ関数を先に計算しておき、外側 WHERE / CASE でその列を参照する書き方に変えると、ウィンドウ評価の重複を避けられます。

同スコアのピーク検出漏れ：amount > LAG は厳密な不等号なので、前日と同じ売上の場合は PEAK にならず谷（valley）にもなりません。「同点以上でもピーク扱いにしたい」なら >= に変更しますが、その場合は連続する同値が全てPEAKになるトレードオフがあります。要件に合わせて使い分けましょう。

QUESTION 10

PARTITION BY × フレーム指定 — 店舗ごとに独立した移動平均を計算する

PARTITION BYROWS BETWEENグループ別移動平均実務最頻出

前提知識 ▼

PARTITION BY（グループ分割）と ROWS BETWEEN（フレーム指定）を組み合わせることで、「各グループの中だけで動くウィンドウ」を実現できます。これはウィンドウ関数の中でも最も実務的なパターンのひとつです。

AVG(amount) OVER(
  PARTITION BY store_id       -- 店舗単位でウィンドウを区切る
  ORDER BY date               -- 各パーティション内で日付順に並べる
  ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  -- 直前1行 + 現在行 = 2日間平均
)

PARTITION BY の「リセット」効果：PARTITION BY を指定すると、パーティションが切り替わるたびにフレームが 必ずリセット（先頭から再開）されます。別の店舗の行を誤って巻き込む「クロスパーティション汚染」が自動的に防がれます。

問題 ▼

以下の store_daily_sales テーブルを使用し、店舗（store_id）ごとに独立した「直近2日間の移動平均（moving_avg_2d）」を計算してください。
つまり、店舗が切り替わった時点でウィンドウが必ずリセットされる必要があります。

使用テーブル

▸ store_daily_sales

store_id	date	amount
S1	04-01	100
S1	04-02	140
S1	04-03	120
S1	04-04	160
S2	04-01	200
S2	04-02	180
S2	04-03	220
S2	04-04	190

期待出力 ▼

store_id	date	amount	moving_avg_2d
S1	04-01	100	100.0
S1	04-02	140	120.0
S1	04-03	120	130.0
S1	04-04	160	140.0
S2	04-01	200	200.0
S2	04-02	180	190.0
S2	04-03	220	200.0
S2	04-04	190	205.0

模範解答コード ▼

SELECT
  store_id,
  date,
  amount,
  AVG(amount) OVER(
    PARTITION BY store_id
    ORDER BY date                             -- 店舗ごとにウィンドウを独立。別店舗の行は絶対に混入しない
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW  -- パーティション内で日付順にソート
  ) AS moving_avg_2d                          -- 直前1行 + 現在行 = 最大2行で AVG を計算
FROM store_daily_sales
ORDER BY store_id, date;

/*
  実行順序:
  1. FROM store_daily_sales        → 全8行取得
  2. PARTITION BY store_id         → 店舗で分割
  3. ORDER BY date + ROWS BETWEEN  → 区画内で移動平均を計算
  4. ORDER BY store_id, date       → 出力
  */

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

SELECT
  store_id, date, amount,
  AVG(amount) OVER(
    PARTITION BY store_id
    ORDER BY date
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS moving_avg_2d
FROM store_daily_sales
ORDER BY store_id, date;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM store_daily_sales全8行を読み込みます。S1とS2が混在しています。

1 / 3

store_id	date	amount
S1	04-01	100
S1	04-02	140
S1	04-03	120
S1	04-04	160
S2	04-01	200
S2	04-02	180
S2	04-03	220
S2	04-04	190

全 8行読込

学習ポイント

PARTITION BY がないとどうなるか：PARTITION BY を外して ORDER BY store_id, date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW だけにすると、S1の04-04(160)とS2の04-01(200)が同じフレームに入り、意図しない店舗をまたいだ平均が計算されます。ORDER BY date だけなら同日の店舗間順序も不定です。複数エンティティ（店舗、商品、ユーザー等）を1テーブルで扱うときは必ず PARTITION BY を使いましょう。

PARTITION BY × ORDER BY × ROWS BETWEEN の3セット：3つを組み合わせた完全形 OVER(PARTITION BY x ORDER BY y ROWS BETWEEN n PRECEDING AND CURRENT ROW) が「ウィンドウ関数の最終形」です。これを読めるようになると、実務のほぼ全てのウィンドウ関数クエリが理解できます。

BigQuery・Redshift での活用：データウェアハウスで「7日移動平均を店舗別・商品別に計算してダッシュボードへ流す」という処理は毎日実行されます。PARTITION BY store_id, product_id ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW のような組み合わせで、数十万行のデータも高速に処理できます。

PARTITION BY あり vs なし — ORDER BY store_id, date での S1→S2 境界比較

store_id	date	amount	PARTITION BYなし ORDER BY store_id, date（全行対象）	PARTITION BY store_id 1 PRECEDING (店舗内のみ)
S1	04-03	120	AVG(140, 120) = 130 ✓	AVG(140, 120) = 130 ✓
S1	04-04	160	AVG(120, 160) = 140 ✓	AVG(120, 160) = 140 ✓
S2	04-01	200	AVG(160, 200) = 180 ✗ ← S1の04-04を誤って参照!	AVG(200) = 200 ✓ ← リセット。S1と無関係
S2	04-02	180	AVG(200, 180) = 190 ✓	AVG(200, 180) = 190 ✓

比較用に全体を store_id, date 順に並べると、S2の04-01 が境界になる。PARTITION BY なしでは「S1-04-04の160」が S2-04-01 の1行前として扱われ、誤った平均(180)が計算される。PARTITION BY store_id があれば「S2の先頭行」として正しくリセットされる。

アンチパターン

PARTITION BY なしで複数グループを処理する：最も頻繁に起きるバグです。複数の店舗・ユーザー・商品が混在するテーブルで PARTITION BY を忘れると、グループ境界をまたいだフレームが作られてしまい、誤った計算結果が静かに出力されます（エラーは出ません）。GROUP BY と違い、WHERE で特定の store_id だけを絞って回避しようとすると他店舗の分析ができなくなります。

RANGE BETWEEN と ROWS BETWEEN の違いを混同する：RANGE BETWEEN 1 PRECEDING AND CURRENT ROW の RANGE は「値の範囲」でフレームを定義します。ORDER BY が date（日付型）の場合、DB方言ごとの interval 構文や同値行の扱いにより、同日・隣接日のデータが想定外にまとめて集計される場合があります。行数で明確に範囲を指定したいときは必ず ROWS BETWEEN を使いましょう。