SQL Quiz Book

SQL ウィンドウ関数 — OVER句・PARTITION BY・累計の基礎

基礎ウィンドウ関数分析・集計行単位処理PostgreSQL/MySQL8.0+/BigQuery対応全5問
1 / 5 · 学習中 0 / 5 問完了
QUESTION 1

OVER() の基本 — 「行を潰さずに」全体の集計値を各行に付与する

OVERSUM構成比基本
前提知識 ▼

ウィンドウ関数は、GROUP BY のように「データをグループ化して集計」しますが、「行をまとめず（潰さず）に、元の行数のまま集計結果を返す」という非常に強力な特徴があります。

SELECT
  key_col,
  num_col,
  SUM(num_col) OVER() AS total_all  -- OVER()がウィンドウ関数の合図
FROM table_name;
OVER() とは：関数（SUMやAVGなど）の後ろに OVER() をつけると、「結果セット全体をひとつのウィンドウ（窓）として扱い、その集計値を全ての行に同じ値として付与」します。
問題 ▼

以下の sales テーブル（個別の売上データ）を使い、各担当者の「売上金額」「全体の売上合計」「全体の売上に対する構成比（割合）」を出力してください。

※構成比は amount / 全体の合計 で計算して小数第4位に丸め、金額の高い順に並べること。

使用テーブル
▸ sales
emp_idemp_nameamount
E1田中150000
E2佐藤200000
E3鈴木120000
E4高橋180000
期待出力 ▼
emp_nameamounttotal_amountratio
佐藤2000006500000.3077
高橋1800006500000.2769
田中1500006500000.2308
鈴木1200006500000.1846
模範解答コード ▼ 
SELECT
  emp_name,
  amount,
  SUM(amount) OVER() AS total_amount,   -- SUM(amount) OVER(): テーブル全体の amount の合計(650,000)を、各行に同じ値として付与する
  ROUND(amount * 1.0 / SUM(amount) OVER(), 4) AS ratio  -- 構成比を小数第4位に丸める
FROM sales
ORDER BY amount DESC;

/*
  実行順序（超重要）:
  1. FROM sales               → 4行を取得
  2. (WHERE/GROUP BY があれば実行)  → 行を絞り込み
  3. Window関数                 → 全体を計算し各行に付与
  4. SELECT                   → 構成比を計算して出力
  5. ORDER BY                 → 売上降順に並べ替え
  */
解説（テーブル変化・ポイント） ▼
SELECT emp_name, amount, SUM(amount) OVER() AS total_amount, ROUND(amount * 1.0 / SUM(amount) OVER(), 4) AS ratio FROM sales ORDER BY amount DESC;
LEGEND ▼
データ取得・読込対象
① FROM ▼
FROM salessales テーブル全体（4行）を読み込みます。
1 / 3
emp_nameamount
田中150000
佐藤200000
鈴木120000
高橋180000
全 4行 読込
学習ポイント
GROUP BYとの違い：GROUP BY を使うと行がグループ単位（1行）に潰れてしまうため、「個別の売上」と「全体の売上」を同時に出すには、サブクエリで集計した結果をJOINし直す必要がありました。OVER() を使えばクエリ一発で取得できます。
実行順序：ウィンドウ関数は WHEREやGROUP BYによる絞り込み・集約が全て終わった後、最後の SELECT の直前で評価されます。したがって WHERE amount > 10000 があれば、「1万以上の行だけの合計」が計算されます。
▤ GROUP BY vs OVER() — 同じ sales テーブルへの問い方の違い
✗ GROUP BY （行が潰れる）
SUM(amount)
650,000
4行 → 1行に集約
個人の売上情報が消える
→
✓ OVER() （行数を保持）
emp_nameamountSUM(amount) OVER()
田中150,000650,000
佐藤200,000650,000
鈴木120,000650,000
高橋180,000650,000
4行を保持したまま全体合計が付与
構成比の計算もこの行でそのまま行える
アンチパターン
WHERE句でウィンドウ関数を使う：WHERE SUM(amount) OVER() > 500000 はエラーになります。WHEREはウィンドウ関数より「前」に評価されるためです。ウィンドウ関数の結果で絞り込みたい場合は、Q8で解説する「サブクエリ（CTE）」を使います。
1 / 5
問題を選択
QUESTION 2

PARTITION BY — 部署ごと・カテゴリごとにウィンドウ（窓）を区切る

PARTITION BYグループ別部門計
前提知識 ▼

OVER() のカッコ内に PARTITION BY 列名を書くと、指定した列の値ごとに「ウィンドウ（集計範囲）」を区切ることができます。GROUP BY に似ていますが、やはり行は潰れません。

SELECT
  group_col, key_col, num_col,
  SUM(num_col) OVER(PARTITION BY group_col) AS group_total
FROM table_name;
PARTITION BY の役割：対象データ全体を「部署ごと」などの小さなまとまり（パーティション）に分割し、それぞれのまとまりの中で集計を行います。結果は元の各行の横に付与されます。
問題 ▼

以下の employees テーブルから、各従業員の「名前」「部署」「給与」と、「その人が所属する部署の給与合計」を出力してください。

使用テーブル
▸ employees
emp_namedeptsalary
田中営業300000
佐藤営業280000
鈴木開発400000
高橋開発350000
伊藤人事320000
期待出力 ▼
emp_namedeptsalarydept_total
田中営業300000580000
佐藤営業280000580000
鈴木開発400000750000
高橋開発350000750000
伊藤人事320000320000
模範解答コード ▼ 
SELECT
  emp_name,
  dept,
  salary,
  SUM(salary) OVER(PARTITION BY dept) AS dept_total  -- 部署ごとの給与合計を各行に付与
FROM employees
ORDER BY
  CASE dept
    WHEN '営業' THEN 1
    WHEN '開発' THEN 2
    WHEN '人事' THEN 3
  END, salary DESC, emp_name;

/*
  実行順序:
  1. FROM employees → 5行取得
  2. Window関数 → deptごとにパーティションを作成し、各パーティション内でSUMを計算
  3. SELECT → 各行に計算した dept_total をアタッチして出力
  4. ORDER BY CASE dept WHEN '営業' THEN 1 WHEN '開発' THEN 2 WHEN '人事' THEN 3 END, salary DESC, emp_name → 営業・開発・人事の部署順、部署内は salary 降順、同額は emp_name 昇順
*/
解説（テーブル変化・ポイント） ▼
SELECT emp_name, dept, salary, SUM(salary) OVER(PARTITION BY dept) AS dept_total FROM employees ORDER BY CASE dept WHEN '営業' THEN 1 WHEN '開発' THEN 2 WHEN '人事' THEN 3 END, salary DESC, emp_name;
LEGEND ▼
データ取得・読込対象
① FROM ▼
FROM employeesemployees テーブル全体（5行）を読み込みます。
1 / 3
emp_namedeptsalary
田中営業300000
佐藤営業280000
鈴木開発400000
高橋開発350000
伊藤人事320000
全 5行 読込
学習ポイント
PARTITION BY はウィンドウ関数版の GROUP BY：集計の単位（グループ）を決める役割は同じですが、集約が行われない（詳細行がそのまま残る）ため、「個人の給与と、部署の給与平均を比較して、部署内で平均以上か判定する」といった相対的な分析が驚くほど簡単に書けます。
複数列の指定も可能：PARTITION BY year, month のように複数列を指定すれば、「年月ごとの合計」を各行に付与できます。
▤ PARTITION BY dept の効果 — データが3つのパーティションに分割される
dept（パーティションキー） emp_name salary dept_total（パーティション内SUM）
営業 田中300,000 580,000
営業 佐藤280,000 580,000
開発 鈴木400,000 750,000
開発 高橋350,000 750,000
人事 伊藤320,000 320,000
点線 = パーティション境界。部署が変わると集計ウィンドウがリセットされ、新しいパーティション内のみで合計が再計算される。
行数は5行のまま変化なし。
アンチパターン
GROUP BY とウィンドウ関数を混同する：SELECT dept, SUM(salary) OVER(PARTITION BY dept) FROM employees GROUP BY dept; のように書くのは誤りです。GROUP BY は行を潰す処理なので、同時に OVER(PARTITION BY) を使うと意図しない結果（またはエラー）になります。単純なグループ合計なら通常の SUM() ... GROUP BY を使いましょう。
2 / 5
問題を選択
QUESTION 3

OVER(ORDER BY) — 日付順に並べて「累計」を計算する

ORDER BY累計推移
前提知識 ▼

OVER句の中に ORDER BY 列名を指定すると、単なる全体集計ではなく「先頭行から現在の行まで」の範囲（累計）で集計が行われます。

SELECT
  sort_col, num_col,
  SUM(num_col) OVER(ORDER BY sort_col ASC) AS running_total
FROM table_name;
暗黙のフレーム指定：OVER句に ORDER BY を書くと、既定では RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（先頭から現在行と同じ並び順値を持つ行まで）という枠組みが適用されるため、累計計算になります。同じ日付の行も現在行と同じフレームに含まれます。
問題 ▼

以下の daily_sales テーブルから、日付ごとの売上 amount と、「その日までの売上累計（running_total）」を取得してください。

使用テーブル
▸ daily_sales
sale_dateamount
04-0110000
04-0215000
04-0312000
04-0420000
期待出力 ▼
sale_dateamountrunning_total
04-011000010000
04-021500025000
04-031200037000
04-042000057000
模範解答コード ▼ 
SELECT
  sale_date,
  amount,
  SUM(amount) OVER(ORDER BY sale_date ASC) AS running_total  -- 日付順に先頭〜現在行を累計
FROM daily_sales
ORDER BY sale_date;

/*
  実行順序:
  1. FROM daily_sales
  2. Window関数が sale_date 順に行を評価
  3. SELECT 出力
  */
解説（テーブル変化・ポイント） ▼
SELECT sale_date, amount, SUM(amount) OVER(ORDER BY sale_date ASC) AS running_total FROM daily_sales ORDER BY sale_date;
LEGEND ▼
データ取得・読込対象
① FROM ▼
FROM daily_salesテーブルを読み込みます。
1 / 3
sale_dateamount
04-0110000
04-0215000
04-0312000
04-0420000
全 4行 読込
学習ポイント
PARTITION BY と組み合わせた「部門別累計」：SUM(amount) OVER(PARTITION BY dept ORDER BY date) と書けば、「部署ごとの売上累計」を算出できます。この複合技は実務ダッシュボード用クエリで必須です。
同値（タイ）の挙動に注意：ORDER BY に指定した列に同じ値（同じ日付など）が複数あると、それらは同じブロックとしてまとめて処理され、累計も同じ値になります。行ごとに一意にしたい場合は ORDER BY date, id のように一意キーを追加します。
▤ ORDER BY による累計 — ウィンドウフレームが行ごとに拡張される
sale_date amount 集計フレーム（先頭 〜 現在行） running_total
04-0110,000 10,000 10,000
04-0215,000 10,000 + 15,000 25,000
04-0312,000 10,000 + 15,000 + 12,000 37,000
04-0420,000 10,000 + 15,000 + 12,000 + 20,000 57,000
青太字 = 現在行（CURRENT ROW）　薄青 = 過去行（UNBOUNDED PRECEDING）
行が下に進むほどフレームが1行ずつ拡張され、全て足し合わせることで累計が得られる。
アンチパターン
ORDER BY に一意にならない列だけを指定する：たとえば「月」だけで ORDER BY month として複数の日次データがあると、同じ月のデータはすべてまとめて足されてしまい、意図した「行ごとの累計」になりません。常に一意に決まるようにソートキーを指定してください。
3 / 5
問題を選択
QUESTION 4

ROW_NUMBER() — グループ内で行番号（連番）を振る

ROW_NUMBERPARTITION BY重複排除の布石
前提知識 ▼

ROW_NUMBER() は、指定した順序に従って行に「1, 2, 3...」と一意の連番を振るウィンドウ関数です。引数は取りません。

SELECT
  group_col, ts_col,
  ROW_NUMBER() OVER(
    PARTITION BY group_col     -- グループごとにリセット(1から振り直し)
    ORDER BY ts_col DESC       -- 新しい順に振る
  ) AS rn
FROM table_name;
実務での超重要テクニック：「ユーザーごとに一番新しいアクセス履歴だけを取得したい」場合、上記のクエリで rn=1 の行だけを絞り込むのがSQLの定番・最強パターンです。まずは連番の振り方を完璧にしましょう。
問題 ▼

以下の logins テーブルには、ユーザーのログイン履歴が入っています。
ユーザー(user_id)ごとに、ログイン日時(login_time)が新しい順（降順）に「1, 2, 3...」と行番号（rn）を振ってください。

使用テーブル
▸ logins
user_idlogin_time
U12024-04-01 10:00
U12024-04-02 12:00
U12024-04-03 09:00
U22024-04-01 11:00
U22024-04-04 15:00
期待出力 ▼
user_idlogin_timern
U12024-04-03 09:001
U12024-04-02 12:002
U12024-04-01 10:003
U22024-04-04 15:001
U22024-04-01 11:002
模範解答コード ▼ 
SELECT
  user_id,
  login_time,
  ROW_NUMBER() OVER(
    PARTITION BY user_id          -- user_idごとに独立して番号を振る
    ORDER BY     login_time DESC  -- 時間の降順(最新が1になる)
  ) AS rn
FROM logins
ORDER BY user_id, rn;

/*
  実行順序:
  1. FROM logins
  2. user_id 'U1' のグループを作る
  3. user_id 'U2' のグループを作る
  4. SELECT 出力
  */
解説（テーブル変化・ポイント） ▼
SELECT user_id, login_time, ROW_NUMBER() OVER( PARTITION BY user_id ORDER BY login_time DESC ) AS rn FROM logins ORDER BY user_id, rn;
LEGEND ▼
データ取得・読込対象
① FROM ▼
FROM loginsテーブルを読み込みます。
1 / 3
user_idlogin_time
U104-01 10:00
U104-02 12:00
U104-03 09:00
U204-01 11:00
U204-04 15:00
全 5行 読込
学習ポイント
なぜ ROW_NUMBER なのか：グループごとの最新日時を取るだけなら MAX(login_time) GROUP BY user_id でも可能ですが、その場合「ログインしたデバイス（IPアドレス等）などの他の列を同時に取得できない」という致命的な弱点があります。ROW_NUMBER で行番号を振り、後で WHERE rn = 1 で行ごと抽出することでこの問題を完全に解決できます。
▤ GROUP BY MAX() vs ROW_NUMBER() — 「最新行の全列」を取る手段の比較
✗ GROUP BY user_id — 他の列が取れない
SELECT user_id, MAX(login_time)
FROM logins
GROUP BY user_id;
user_idMAX(login_time)
U104-03 09:00
U204-04 15:00
デバイス種別や他の列が取れない！
✓ ROW_NUMBER + WHERE rn=1 — 全列取得できる
SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER(
      PARTITION BY user_id
      ORDER BY login_time DESC
    ) AS rn
  FROM logins
) t WHERE rn = 1;
user_idlogin_timern
U104-03 09:001
U204-04 15:001
✓ 最新行の全列を行ごと取得できる（Q8で詳解）
アンチパターン
PARTITION BY / ORDER BY を書き忘れる：ROW_NUMBER() OVER() とだけ書くと、テーブル全体に対して適当な順序で連番が振られてしまいます。「グループごとに」「〜の順で」という意図がある場合は、必ず PARTITION BY と ORDER BY をセットで指定してください。
4 / 5
問題を選択
QUESTION 5

RANK / DENSE_RANK — 順位付けと同順位（タイ）の扱い

RANKDENSE_RANK順位付け
前提知識 ▼

順位を付ける関数には3種類あり、同順位（同じ点数など）が発生したときの次の番号の飛び方が異なります。

  • ROW_NUMBER(): 無条件で 1, 2, 3, 4 と連番を振る（同点でも差がつく）
  • RANK(): 同点は同じ順位になり、次の順位が飛ぶ（1, 1, 3, 4）
  • DENSE_RANK(): 同点は同じ順位になり、次は飛ばずに詰める（1, 1, 2, 3）
SELECT id_col,
       ROW_NUMBER() OVER (ORDER BY num_col DESC) AS rn,        -- 連番
       RANK()       OVER (ORDER BY num_col DESC) AS rnk,       -- 同順位あり・次は飛ぶ
       DENSE_RANK() OVER (ORDER BY num_col DESC) AS dense_rnk  -- 同順位あり・次は詰める
FROM   table_name;
同点行の扱いだけが違う：3つの関数は同じ ORDER BY を与えても、同点の行に番号を振るところだけが異なります。ROW_NUMBER() は同点でも必ず一意の連番を振るため、どちらの行が先になるかは並び順が同値のままだと決まりません。確定させたい場合は ORDER BY の末尾に一意な列を足します。
問題 ▼

以下の scores テーブル（生徒のテスト点数）を使い、点数が高い順に RANK(), DENSE_RANK(), ROW_NUMBER() の3つの順位を算出し、違いを確認してください。

使用テーブル
▸ scores
studentscore
Aさん95
Bさん95
Cさん88
Dさん88
Eさん70
期待出力 ▼
studentscorernkdense_rnkrow_num
Aさん95111
Bさん95112
Cさん88323
Dさん88324
Eさん70535
模範解答コード ▼ 
SELECT
  student,
  score,
  RANK()       OVER(ORDER BY score DESC) AS rnk,        -- 同順位の次を飛ばす（標準的な順位）
  DENSE_RANK() OVER(ORDER BY score DESC) AS dense_rnk,  -- 同順位の次を詰める（飛ばさない）
  ROW_NUMBER() OVER(ORDER BY score DESC, student) AS row_num  -- 同点は student 順で一意にする
FROM scores                                          -- 同順位でも強引に一意の連番を振る
ORDER BY score DESC, student;

/*
  実行順序:
  1. FROM scores
  2. Window関数が score 降順で評価
  3. SELECT 出力
  */
解説（テーブル変化・ポイント） ▼
SELECT student, score, RANK() OVER(ORDER BY score DESC) AS rnk, DENSE_RANK() OVER(ORDER BY score DESC) AS dense_rnk, ROW_NUMBER() OVER(ORDER BY score DESC, student) AS row_num FROM scores ORDER BY score DESC, student;
LEGEND ▼
データ取得・読込対象
① FROM ▼
FROM scoresテーブルを読み込みます。
1 / 3
studentscore
Aさん95
Bさん95
Cさん88
Dさん88
Eさん70
全 5行 読込
学習ポイント
実務での使い分け：「トップ3」のリストを作るとき、RANKを使うと同点がいた場合「1位,1位,3位」となり3人抽出されます。DENSE_RANKを使うと「1位,1位,2位,3位」となり4人以上抽出される可能性があります。ROW_NUMBERなら必ず3人になります。「要件として同点をどう扱うか」で使い分けます。
▤ 3関数の違い — 同点(95点×2, 88点×2)があったとき何が起きるか
student score RANK()
同順位の次を飛ばす		 DENSE_RANK()
同順位でも詰める		 ROW_NUMBER()
必ず一意の連番
Aさん95 1 1 1
Bさん95 1 ← 同点 1 ← 同点 2 ← 強制区別
Cさん88 3 ← 2が欠番! 2 ← 詰める 3
Dさん88 3 ← 同点 2 ← 同点 4 ← 強制区別
Eさん70 5 ← 4が欠番! 3 5
RANK：同点2人いたら「2位は欠番」→ 次は3位 DENSE_RANK：欠番なし、常に連続した整数 ROW_NUMBER：同点でも強制的に異なる番号
5 / 5
問題を選択
次のセット →