SQL ウィンドウ関数 — NTILE・PERCENT

QUESTION 6

NTILE() — データをN個のグループに均等に分割する

NTILEORDER BYRFM分析/ランク付け

前提知識 ▼

NTILE(N) は、ORDER BY で決めた順序に沿って行を最大N個のバケット（グループ）へできるだけ均等に分割し、各行に1からNまでの番号を付けます。顧客を購買金額順に上位層・中位層・下位層へ分類する場合などに利用できます。

NTILE(3) OVER(
  ORDER BY total_amount DESC
) AS tier

均等に割り切れない場合：余った行は番号の小さいバケットから1行ずつ配られます。たとえば5行を3分割すると、各バケットの行数は2・2・1になります。行数がN未満なら、作られるバケット数は行数までです。

問題 ▼

以下の customer_sales テーブルから、顧客を売上(total_amount)が高い順に「3つ」のグループ（1, 2, 3）に分割し、そのグループ番号（tier）を出力してください。

使用テーブル

▸ customer_sales

customer_id	total_amount
C1	50000
C2	40000
C3	30000
C4	20000
C5	10000

期待出力 ▼

customer_id	total_amount	tier
C1	50000	1
C2	40000	1
C3	30000	2
C4	20000	2
C5	10000	3

模範解答コード ▼

SELECT
  customer_id,
  total_amount,
  NTILE(3) OVER(ORDER BY total_amount DESC) AS tier
FROM customer_sales                                  -- 売上降順に並べ、全体を3つのグループに均等分割する
ORDER BY total_amount DESC;

/*
  実行順序:
  1. FROM customer_sales (全5行)
  2. ORDER BY total_amount DESC で売上降順にソート
  3. NTILE(3) が行を3バケットに分割
  4. SELECT 出力
  */

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

SELECT
  customer_id, total_amount,
  NTILE(3) OVER(ORDER BY total_amount DESC) AS tier
FROM customer_sales
ORDER BY total_amount DESC;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM customer_salesテーブルを読み込みます。5名の顧客データがあります。NTILE() は「順序」に基づいてバケットを割り振るため、次のステップで ORDER BY によるソートが必要です。

1 / 4

customer_id	total_amount
C1	50000
C2	40000
C3	30000
C4	20000
C5	10000

全 5行読込

学習ポイント

分析での活用：NTILE(10) を使えば「デシル分析（10等分）」、NTILE(4) を使えば「四分位数」のグループ分けが一瞬で完了します。「上位10%の顧客群と下位10%の顧客群で、行動にどんな違いがあるか」を比較するデータセットを作る際によく使われます。

アンチパターン

同値の境界またぎ：NTILE は「行数」だけで機械的に分割するため、同じ売上の人が境界線をまたいで別々のグループに分かれてしまうことがあります。厳密に「同じ値なら同じグループ」にしたい場合は、NTILE ではなく PERCENT_RANK や RANK などを利用して自前で分割ロジックを組む必要があります。

同値が境界をまたぐ例（4件を2分割すると同額が別グループに）

NTILE(3) — 5件・3分割（問題なし）

customer_id	total_amount	tier
C1	50000	1
C2	40000	1
C3	30000	2
C4	20000	2
C5	10000	3

全員が異なる値のため境界またぎは発生しない

✗ NTILE(2) — 同額 30,000 が別グループに

customer_id	total_amount	tier
C1	50000	1
C2	30000	1
C3	30000	2
C4	10000	2

同額 30,000 の C2・C3 が tier 1 と 2 に分かれてしまう

QUESTION 7

PERCENT_RANK() — 自分が全体の上位何パーセントか（相対順位）

PERCENT_RANKORDER BYパーセンタイル

前提知識 ▼

PERCENT_RANK() は、データの相対的な位置を 0.0（1位）から 1.0（最下位）の範囲で計算します。
計算式は (自身のRANK - 1) / (全体の行数 - 1) です。「上位20%に入る優秀なデータだけを抽出したい」といった要件で活躍します。

SELECT id_col,
       PERCENT_RANK() OVER (ORDER BY num_col DESC) AS pct_rank  -- 0.0（先頭）〜 1.0（末尾）
FROM   table_name;

両端の値と分母：分母は「行数 − 1」なので、両端の行はちょうど 0.0 と 1.0 になります。行が1行しかないグループでは 0.0 が返ります。同率の行は順位を共有するため、同じ値になります。

問題 ▼

以下の exam_scores テーブルから、各生徒の点数と、「その点数が全体の中で上位何パーセントに位置するか（pct_rank）」を計算してください。

使用テーブル

▸ exam_scores

student_id	score
S1	95
S2	88
S3	75
S4	60

期待出力 ▼

student_id	score	pct_rank
S1	95	0
S2	88	0.3333
S3	75	0.6667
S4	60	1

模範解答コード ▼

SELECT
  student_id,
  score,
  ROUND((PERCENT_RANK() OVER(ORDER BY score DESC))::numeric, 4) AS pct_rank
FROM exam_scores
ORDER BY score DESC;

/*
  実行順序:
  1. FROM exam_scores (全4行)
  2. ORDER BY score DESC  → 順位を確定
  3. PERCENT_RANK() が (RANK - 1) / (4 - 1) を各行で計算
  4. SELECT 出力
  */

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

SELECT
  student_id, score,
  PERCENT_RANK() OVER(ORDER BY score DESC) AS pct_rank
FROM exam_scores
ORDER BY score DESC;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM exam_scoresテーブルを読み込みます。4名の点数データがあります。

1 / 4

student_id	score
S1	95
S2	88
S3	75
S4	60

全 4行読込

学習ポイント

実務での絞り込み：「上位20%のユーザーを抽出したい」場合は、サブクエリ（またはCTE）と組み合わせて WHERE pct_rank <= 0.2 と指定します。全体の人数が100人でも10000人でも、クエリを変えずに常に上位20%を動的に抽出できるのが強みです。

CUME_DIST() との違い：似た関数に CUME_DIST()（累積分布）があります。PERCENT_RANK が「1位を0とする」のに対し、CUME_DIST は「1位の時点で 1/N」からスタートし、最後が必ず1になります。要件によって使い分けます。

☷ PERCENT_RANK と CUME_DIST の数値比較（4名の例）

student_id	score	RANK	PERCENT_RANK (RANK−1) / (N−1)	CUME_DIST RANK / N
S1	95	1位	0.000　← 必ず0から開始	0.250
S2	88	2位	0.333	0.500
S3	75	3位	0.667	0.750
S4	60	4位	1.000　← 必ず1で終了	1.000

「上位N%以内」をフィルタする用途では PERCENT_RANK が直感的（0から始まるので <= 0.2 = 上位20%）。CUME_DIST は「全体の何%がこの値以下か」という累積頻度の表現に向きます。

QUESTION 8

ROWS BETWEEN 応用 — 「自身を含まない」直前の行までの過去累計

フレーム指定PRECEDING過去累計

前提知識 ▼

フレーム指定（ROWS BETWEEN）を活用すると、「先頭行から現在の行まで」だけでなく、「先頭行から1つ前の行まで（現在の行を含まない）」といった柔軟な集計範囲を定義できます。

SUM(sales) OVER(
  ORDER BY month
  ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
)

現在行を含まないフレーム：フレームの終端を 1 PRECEDING にすると、現在行そのものは集計対象から外れます。先頭行ではフレームに含まれる行が1行もないため、結果は 0 ではなく NULL になります。

問題 ▼

以下の monthly_targets テーブルから、各月の売上と、「前月までの売上累計（prev_running_total）」を計算してください。

※ 最初の月（2026-01）は「前月までの累計」が存在しないため、NULL になります。

使用テーブル

▸ monthly_targets

month	sales
2026-01	10
2026-02	20
2026-03	30
2026-04	40

期待出力 ▼

month	sales	prev_running_total
2026-01	10	NULL
2026-02	20	10
2026-03	30	30
2026-04	40	60

模範解答コード ▼

SELECT
  month,
  sales,
  SUM(sales) OVER(
    ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
  ) AS prev_running_total  -- 先頭〜1行前まで（現在行を含めない）
FROM monthly_targets
ORDER BY month;

/*
  フレームの評価:
  2026-01: 「1つ前」が存在しないため対象範囲なし → NULL
  2026-02: [2026-01 のみ] の合計 → 10
  2026-03: [2026-01, 2026-02] の合計 → 10 + 20 = 30
  2026-04: [2026-01, 2026-02, 2026-03] の合計 → 10 + 20 + 30 = 60
*/

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

SELECT
  month, sales,
  SUM(sales) OVER(
    ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
  ) AS prev_running_total
FROM monthly_targets
ORDER BY month;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM monthly_targetsテーブルを読み込みます。4ヶ月分の売上データがあります。

1 / 4

month	sales
2026-01	10
2026-02	20
2026-03	30
2026-04	40

全 4行読込

学習ポイント

実務でのユースケース：「当月の目標達成率」を出す際、当月の売上に加えて「前月までの未達分（負債）」を足し合わせて目標を再計算するような、複雑な予実管理ダッシュボードでこのフレーム指定が活躍します。

フレームのずらし方まとめ：終端を CURRENT ROW（デフォルト）→ 現在行を含む累計。1 PRECEDING → 現在行を除いた「前まで」の累計。1 FOLLOWING → 現在行の「次の行まで」を含む累計。このように終端・始端を自由に動かせるのがフレーム指定の真価です。

☷ 主なフレーム指定パターン早見表

ROWS BETWEEN … AND …	集計対象の範囲	代表的な用途
`UNBOUNDED PRECEDING`〜 `CURRENT ROW`	先頭行〜現在行（自身を含む）	累計売上・累計件数
`UNBOUNDED PRECEDING` 〜 `1 PRECEDING`	先頭行〜1行前（自身を含まない）	前期までの累計（今期を除く）
`1 PRECEDING` 〜 `1 FOLLOWING`	前後1行ずつ（計3行）	3期移動平均
`UNBOUNDED PRECEDING` 〜 `UNBOUNDED FOLLOWING`	パーティション全体	グループ合計を全行に付与（ORDER BY なし OVER と同等）

QUESTION 9

RANGE vs ROWS — 同じ日付が存在する時の累計の罠

RANGEROWS同値の扱い超重要

前提知識 ▼

ORDER BY を指定してフレーム句を省略した場合、デフォルトで RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW が適用されます。
ROWS は物理的な「行」単位で処理しますが、RANGE は論理的な「値」単位で処理するため、ソートキーに同じ値（同日など）が存在した場合の挙動に決定的な違いが出ます。

SELECT date_col,
       SUM(num_col) OVER (ORDER BY date_col)                    -- フレーム省略時の既定は RANGE
         AS default_frame,
       SUM(num_col) OVER (ORDER BY date_col
             RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -- 同値の行をまとめて含む
         AS range_frame,
       SUM(num_col) OVER (ORDER BY date_col
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  -- 物理的な行単位で含む
         AS rows_frame
FROM   table_name;

違いが出るのは同値の行があるときだけ：ROWS は物理的な行数で範囲を決め、RANGE は ORDER BY の値で範囲を決めます。並び順の値が等しい行（ピア）は RANGE では同じフレームに入るため、同値の行が複数あるときにだけ両者の結果が食い違います。

問題 ▼

以下の sales_records テーブルには、04-02 のデータが2件存在します。
単に ORDER BY だけを指定したデフォルトの累計（RANGE相当）と、ROWS BETWEEN... を明示的に指定した累計（ROWS）の2つを出力し、同日のデータにおける挙動の違いを確認してください。

使用テーブル

▸ sales_records

sale_date	amount
04-01	10
04-02	20
04-02	30
04-03	40

期待出力 ▼

sale_date	amount	range_total	rows_total
04-01	10	10	10
04-02	20	60	30
04-02	30	60	60
04-03	40	100	100

模範解答コード ▼

SELECT
  sale_date,
  amount,
  SUM(amount) OVER(                                   -- 省略時 (暗黙の RANGE BETWEEN...)
    ORDER BY sale_date
  ) AS range_total,
  SUM(amount) OVER(                                   -- ROWS を明示的に指定
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS rows_total

FROM sales_records
ORDER BY sale_date, amount;

/*
  【RANGEの場合】"値" で判断する。
  04-02の行が来た時、「04-02以下のすべての行」をまとめて足す。
  04-02の2行は「ピアグループ」として扱われ、
  (10 + 20 + 30 = 60) が両方の行に付与される。

  【ROWSの場合】"行" で判断する。
  04-02(20)の行: [10, 20] = 30
  04-02(30)の行: [10, 20, 30] = 60
*/

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

SELECT
  sale_date, amount,
  SUM(amount) OVER(ORDER BY sale_date) AS range_total,
  SUM(amount) OVER(ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total
FROM sales_records
ORDER BY sale_date, amount;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM sales_recordsテーブルを読み込みます。04-02 のデータが2行あることに注目してください。この「同じ値が複数行」という状況が RANGE と ROWS の挙動を分けるポイントです。

1 / 4

sale_date	amount
04-01	10
04-02	20
04-02	30
04-03	40

全 4行読込（04-02 が2行）

学習ポイント

実務での使い分け：「その日が終わった時点での累計売上」を出したい場合は、同日の売上をまとめる RANGE（デフォルト）の挙動が正解です。一方で、「1件1件の注文に対して、その時点での累計額を厳密に出したい」場合は、必ず ORDER BY date, id のように一意になるキーを追加するか、ROWS を明示的に指定する必要があります。

❖ 「ピアグループ」の概念：RANGE が同値の行をどう扱うか

RANGE：04-02 行を評価するとき、同日の行を「ピアグループ」としてまとめる

sale_date	amount	フレームに含まれる行	range_total
04-01	10	[04-01のみ]	10
04-02	20	[04-01, 04-02×2行] ← ピアグループ	60
04-02	30	[04-01, 04-02×2行] ← ピアグループ	60
04-03	40	[04-01〜04-03すべて]	100

同日の2行は「値が同じ」ためピアグループになり、両方合算した 60 が付与される

ROWS：同じ日付でも「物理的な行位置」で独立処理

sale_date	amount	フレームに含まれる行	rows_total
04-01	10	[04-01のみ]	10
04-02	20	[04-01, この行(20)]	30
04-02	30	[04-01, 20, この行(30)]	60
04-03	40	[04-01〜04-03すべて]	100

各行を個別にカウントするため、同日でも 30・60 と異なる値が付与される

アンチパターン

意図せず RANGE になっている：移動平均を出そうとして ORDER BY date しか書かなかった場合、暗黙的に RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW になり、平均ではなく「その日までの累計平均」という謎の数値になってしまいます。フレームは常に意識しましょう。

✓ 迷ったときの判断基準

やりたいこと	推奨する書き方	理由
「その日が終わった時点での累計」	ORDER BY date のみ（RANGE デフォルト）	同日まとめが自然な場合
「1件ずつ厳密に累計を出す」	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW を明示	行の独立処理を保証
「移動平均を出す」	ROWS BETWEEN N-1 PRECEDING AND CURRENT ROW	同値で範囲が予期せず広がらない

QUESTION 10

実務総まとめ — 顧客の購買トラッキング（回数・累計・前回履歴）

総まとめデータマート実務レポート複合

前提知識 ▼

実際のデータ基盤（データマート）構築では、1行のイベントデータに対して「その時点での顧客のステータス」を横付けする処理が頻繁に行われます。
これまでに学んだ ROW_NUMBER(), SUM(), LAG() を組み合わせて、強力なトラッキングクエリを作成しましょう。

SELECT t.*,
       ROW_NUMBER() OVER (PARTITION BY key_col ORDER BY ts_col) AS seq,      -- 何件目か
       SUM(num_col) OVER (PARTITION BY key_col ORDER BY ts_col) AS running,  -- その時点の累計
       LAG(num_col) OVER (PARTITION BY key_col ORDER BY ts_col) AS prev_val  -- 前回の値
FROM   table_name t;

同じウィンドウを3つの指標で共有する：3つの指標はいずれも「ユーザーごと・時系列順」という同一のウィンドウ定義の上で評価できます。PARTITION BY と ORDER BY が同じなら同じ枠が使われます。並び順が同値になりうるときは、ORDER BY にタイブレーカーを足して結果を確定させます。

問題 ▼

以下の order_history テーブルから、各注文行に対して以下の3つの指標を付与してください。

1. order_num：そのユーザーにとって「何回目の注文か」（ROW_NUMBER）
2. running_amt：そのユーザーの「その時点までの累計購入金額」（SUM）
3. prev_date：そのユーザーの「前回の購入日」（LAG。初回はNULL）

使用テーブル

▸ order_history

user_id	order_date	amount
U1	04-01	1000
U2	04-02	1500
U1	04-05	2000
U1	04-10	3000

期待出力 ▼

user_id	order_date	amount	order_num	running_amt	prev_date
U1	04-01	1000	1	1000	NULL
U1	04-05	2000	2	3000	04-01
U1	04-10	3000	3	6000	04-05
U2	04-02	1500	1	1500	NULL

模範解答コード ▼

SELECT
  user_id,
  order_date,
  amount,
  ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_date) AS order_num,    -- ① 何回目の注文か (日付順の連番)
  SUM(amount) OVER(PARTITION BY user_id ORDER BY order_date) AS running_amt,   -- ② その時点までの累計購入金額
  LAG(order_date) OVER(PARTITION BY user_id ORDER BY order_date) AS prev_date  -- ③ 前回の購入日

FROM order_history
ORDER BY user_id, order_date;

/*
  実行順序:
  1. FROM order_history (U1とU2のデータが混在)
  2. PARTITION BY user_id でU1・U2グループに分割し
  3. 3つのWindow関数が、同じパーティション・順序で同時に評価される
  4. ORDER BY user_id, order_date で最終ソート出力
  */

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

SELECT
  user_id, order_date, amount,
  ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_date) AS order_num,
  SUM(amount) OVER(PARTITION BY user_id ORDER BY order_date) AS running_amt,
  LAG(order_date) OVER(PARTITION BY user_id ORDER BY order_date) AS prev_date
FROM order_history
ORDER BY user_id, order_date;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM order_historyテーブルを読み込みます。4行がありますが、U1とU2のデータが入り混じった状態です（挿入順）。この時点ではまだグループも順序も確定していません。

1 / 6

user_id	order_date	amount
U1	04-01	1000
U2	04-02	1500
U1	04-05	2000
U1	04-10	3000

全 4行読込（挿入順・混在）

学習ポイント

WINDOW句で重複をなくす：同じ PARTITION BY ... ORDER BY ... を何度も書くのが面倒な場合、モダンなSQLでは WINDOW w AS (PARTITION BY user_id ORDER BY order_date) と下部に定義し、SELECT内では SUM(amount) OVER w のようにスッキリ書くことができます（DRY原則）。

データエンジニアリングの要：「顧客の育成状況（CRM）」を分析する際、このクエリで作ったテーブルがあれば、「2回目の購入までの平均日数は？」「累計5000円を超えた顧客の離脱率は？」といった高度な分析が、シンプルな GROUP BY だけで可能になります。ウィンドウ関数は複雑なビジネスロジックを解きほぐす最高のツールです。

☷ 今回使った3関数の役割と挙動のまとめ

関数	付与する指標	先頭行の値	フレーム依存
`ROW_NUMBER()`	order_num（何回目の注文か）	必ず 1 から始まる	なし（行番号のみ）
`SUM(amount)`	running_amt（累計購入金額）	初回注文の金額のみ	あり（先頭〜現在行が暗黙のデフォルト）
`LAG(order_date)`	prev_date（前回購入日）	NULL（前の行が存在しない）	なし（行参照のみ）

❖ このデータマートで可能になる次の分析（GROUP BY のみで実現）

分析テーマ	必要な追加クエリ
2回目購入までの平均日数	`WHERE order_num = 2 → AVG(DATEDIFF(order_date, prev_date))`
累計 5,000円を初めて突破した注文回数	`WHERE running_amt >= 5000 → MIN(order_num) per user`
初回購入（ファースト）のみ抽出	`WHERE order_num = 1`
ユーザーごとの平均再購入間隔	`AVG(DATEDIFF(order_date, prev_date)) GROUP BY user_id`

1回のウィンドウ関数クエリで作ったデータマートが、その後の複雑な分析を単純な GROUP BY + WHERE で実現できるようにします。これがウィンドウ関数を「データエンジニアリングの要」と呼ぶ理由です。