ウィンドウ関数 ① — OVER と PARTITION BY

本記事で使うデータ — sales と employee

ウィンドウ関数（window function）では、「グループ毎の合計」「順位」「累計」などを行単位で計算して、列を 1 つ追加できる関数です。

GROUP BYだと全行に対して計算をしますが、ウィンドウ関数は一部の行に対して累計値を計算します。

演習に入る前に、本記事で使う 2 つのテーブル — salesとemployee — の列定義とデータのサンプルを確認しておきます。

① PRAGMA table_info(sales);とPRAGMA table_info(employee);で両テーブルの列定義を確認してください。

② SELECT * FROM sales LIMIT 5;とSELECT * FROM employee LIMIT 5;で先頭 5 行のデータをプレビューしてください。employeeのdept_id列に NULL が入る社員がいる点も観察してください。

OVER () — 全行に対して計算を行い列を 1 つ足す

まずは最もシンプルな形集計関数(列) OVER ()から見ましょう。

OVERのカッコを空にすると、集計対象は結果セット全体（取り出した全行）になります。

SUM(amount) OVER ()を 4 列目に書くと、新しくgrand_totalというすべての行に同じ値（全体合計）の列が増えます。

行の数は変わらず、列が 1 つ増えます。

-- OVER () のカッコを空にすると、対象は結果セット全体
-- 5 行の明細はそのまま 5 行残り、grand_total 列に全行同じ値が並ぶ
SELECT sale_id, emp_id, amount,
  SUM(amount) OVER ()   AS grand_total,
  COUNT(*)    OVER ()   AS row_count,
  AVG(amount) OVER ()   AS grand_avg
FROM sales;

「売上明細を 1 行ずつ表示しつつ、その横に売上全体の合計と、各明細が全体の何 % を占めるかを併記したい」という要件を想定します。（正しく実行できれば解説が表示されます）

① salesテーブルからsale_id、emp_id、amountの 3 列を取り出してください。

② SUM(amount) OVER ()を 4 列目に追加し、grand_totalという別名を付けてください。すべての行に売上全体の合計が同じ値で並びます。

③ 5 列目に *amount 100.0 / SUM(amount) OVER ()** を追加し、pct_of_totalという別名を付けてください。各明細が売上全体の何 % を占めるかが計算されます。

④ 判定は行の並び順を見るため、sale_idの昇順で並べてください。

GROUP BY との比較 — 行が畳まれるか / 残るか

同じ「合計を出す」処理を、GROUP BYとウィンドウ関数で並べて比較すると違いが分かりやすくなります。

GROUP BY emp_idとして、SUM(amount)を計算すれば、社員ごとに行を1 つにまとめて合計だけを返します。

一方、SUM(amount) OVER ()は、明細をすべて残したまま、各行に集計値を 1 列追加します。

「合計だけ知りたいか / 合計と明細の両方を 1 つの表で見たいか」で使い分けます。

-- GROUP BY: 社員ごとに 1 行に畳まれ、明細は消える
SELECT emp_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM sales
WHERE emp_id = 2
GROUP BY emp_id;
-- 結果: 1 行 (emp_id=2, cnt=5, total=2150000)

-- ウィンドウ関数: 5 行の明細を残し、横に「全体合計」を付ける
SELECT sale_id, emp_id, amount,
  SUM(amount) OVER () AS grand_total
FROM sales
WHERE emp_id = 2;
-- 結果: 5 行、各行の grand_total 列に 2150000 が並ぶ

同じ「合計を出す」処理を、GROUP BYとOVER ()の両方で書いて、結果の違いを並べて確認します。

① まずGROUP BY版として、emp_id = 2 の売上を社員単位で集計してください。salesからemp_id = 2の行を絞り、emp_id、COUNT(*) AS cnt、SUM(amount) AS totalの 3 列を取り出し、GROUP BY emp_idで集計します。1 行に畳まれる動きを確認します。

② 続けてウィンドウ関数版として、同じemp_id = 2の売上明細を残したまま、SUM(amount) OVER () AS grand_totalを付けてください。sale_id、emp_id、amount、grand_totalの 4 列を取り出します。5 行のままで、各行に同じ合計値が並びます。

③ 2 つのクエリを 1 つのコンソールに並べて実行 し、結果が 「1 行」 vs 「5 行」 で異なることを目で確かめてください。判定はウィンドウ関数側の結果を見るため、ORDER BY sale_idで並びを固定します。

PARTITION BY — ウィンドウを「グループごと」に区切る

OVER ()は結果全体が 1 つのウィンドウ（集計対象範囲）でしたが、OVER (PARTITION BY 列)と書くと、その列の値ごとにウィンドウを区切って集計します。

PARTITION BY emp_idなら「社員ごと」にウィンドウが分かれ、各行にはその行が属する社員の合計が計算されます。

例えば下の図で、emp_id=1 の 4 行にはすべて「emp_id=1 の合計 970,000」が追加され、emp_id=2 の 5 行にはすべて「emp_id=2 の合計 2,150,000」が追加されます。

-- 社員ごとの合計・平均・件数を、明細を残したまま各行に付ける
SELECT sale_id, emp_id, amount,
  SUM(amount)   OVER (PARTITION BY emp_id) AS emp_total,
  AVG(amount)   OVER (PARTITION BY emp_id) AS emp_avg,
  COUNT(*)      OVER (PARTITION BY emp_id) AS emp_count
FROM sales
ORDER BY emp_id, sale_id;

-- 構成比: その明細が「その社員の合計」の何 % か
SELECT sale_id, emp_id, amount,
  amount * 100.0 / SUM(amount) OVER (PARTITION BY emp_id) AS pct_of_emp
FROM sales
ORDER BY emp_id, sale_id;

「売上明細を 1 行ずつ表示しつつ、その社員の合計・平均・件数を横に併記したい」という要件を想定します。

① salesテーブルからsale_id、emp_id、amountの 3 列を取り出してください。

② 社員ごとに区切ったウィンドウで、合計をemp_total、平均をemp_avg、件数をemp_countという別名で 3 列追加してください。

③ 判定は行の並び順を見るため、emp_idの昇順、同じ社員内はsale_idの昇順で並べてください。

JOIN と組み合わせて部署内集計を付ける

ウィンドウ関数はJOIN した結果に対しても使えます。

salesをemployeeと結合してdept_id（部署 ID）を取り出し、PARTITION BY e.dept_idとすれば、各売上明細にその社員が属する部署全体の売上合計を付けられます。

PARTITION BYの列は JOIN で持ってきた他テーブルの列でも構いません。

ウィンドウ関数の評価はFROM / JOIN / WHEREのあと、結果がそろってから行われます。

つまり「JOIN して必要な行をそろえる → そろった行集合に対してウィンドウ集計を配る」という順番です。

-- sales を employee と JOIN し、部署ごとの売上合計・件数を各明細に付ける
SELECT s.sale_id, e.name, e.dept_id, s.amount,
  SUM(s.amount) OVER (PARTITION BY e.dept_id) AS dept_total,
  COUNT(*)      OVER (PARTITION BY e.dept_id) AS dept_sales
FROM sales AS s
JOIN employee AS e ON s.emp_id = e.emp_id
ORDER BY e.dept_id, s.sale_id;

「売上明細に、その社員の名前と所属部署、そして部署全体の売上合計を併記したレポートを作りたい」という要件を想定します。

① sales（別名s）をemployee（別名e）とs.emp_id = e.emp_idでINNER JOINしてください。

② s.sale_id、e.name、e.dept_id、s.amountを取り出してください。

③ 部署ごとに区切ったウィンドウ（PARTITION BY e.dept_id）で、部署の売上合計をdept_totalという別名で 5 列目に追加してください。

④ 判定は行の並び順を見るため、e.dept_idの昇順、同じ部署内はs.sale_idの昇順で並べてください。