SQL Scale Lab｜3,200万行で学ぶMySQL・SQL実践教材

※ 本章は補足トピックです。必要に応じてスキップして第 7 章へ進んでも、第二部の学習に影響はありません。

この章で答える問い

行数を減らさずに集計値を付加するにはどうするか
グループごとの「最新の1件」を取り出すにはどう書くか
累計や前後の行との差分を計算するにはどうするか

ウィンドウ関数を体験: 個別の行を維持したまま集計する

各明細の横に、商品ごとの平均単価

期待: 200 行(減っていない)。同じ product_id の行は同じ平均

SELECT id, product_id, unit_price,
       AVG(unit_price) OVER (PARTITION BY product_id) AS 商品平均
FROM order_items
ORDER BY product_id, id

全 200 行が 1 行も減らずにそのまま出力されています。AVG(...) OVER (PARTITION BY product_id) は、「product_id が同じグループ（窓）の中で平均値を計算し、その結果を行を潰さずに各行の新しい列として付加する」という処理を行います。このように行の枠組み（ウィンドウ）を定義して計算を行うため、ウィンドウ関数（分析関数）と呼ばれます。 GROUP BY が行をグループごとに集約して 1 行にまとめるのに対し、ウィンドウ関数は元の明細行を維持したまま集計値を横に添えるのが特徴です。

GROUP BY はグループごとに 1 行へ集約します。一方、OVER (PARTITION BY) はグループごとの計算結果を、グループ内の各行にそのまま付加します。

順位付け: ROW_NUMBER

顧客ごとに、注文の新しい順の番号

期待: 50 行。各顧客の中で 1, 2, 3 …

SELECT customer_id, id, ordered_at,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) AS 順位
FROM orders
ORDER BY customer_id, 順位

顧客ごとの最新の注文だけ(チケット完了)

期待: 8 行(注文のある顧客の数)

WITH ranked AS (
  SELECT customer_id, id, ordered_at,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) AS 順位
  FROM orders
)
SELECT customer_id, id, ordered_at FROM ranked WHERE 順位 = 1 ORDER BY customer_id

「グループごとの最新 1 件」や「各カテゴリの売上上位 3 件」を取得する処理は、実務でウィンドウ関数と WITH 句を組み合わせる定番のパターンです。ROW_NUMBER() は値が重複していても必ず一意の連番（1, 2, 3…）を振ります。同順位を同列として扱いたい場合は RANK()（1, 1, 3…）や DENSE_RANK()（1, 1, 2…）を使用します。

累計計算と前後の行との差分比較

日ごとの注文数と累積

期待: 日付順に累積が増えていく

WITH daily AS (
  SELECT DATE(ordered_at) AS 日, COUNT(*) AS 件数 FROM orders GROUP BY 日
)
SELECT 日, 件数, SUM(件数) OVER (ORDER BY 日) AS 累積
FROM daily ORDER BY 日

前日との差(LAG)

期待: 最初の行の前日は NULL

WITH daily AS (
  SELECT DATE(ordered_at) AS 日, COUNT(*) AS 件数 FROM orders GROUP BY 日
)
SELECT 日, 件数, LAG(件数) OVER (ORDER BY 日) AS 前日, 件数 - LAG(件数) OVER (ORDER BY 日) AS 差
FROM daily ORDER BY 日

7 日移動平均

期待: ROWS BETWEEN で窓の幅を指定

WITH daily AS (
  SELECT DATE(ordered_at) AS 日, COUNT(*) AS 件数 FROM orders GROUP BY 日
)
SELECT 日, 件数,
       AVG(件数) OVER (ORDER BY 日 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS 移動平均
FROM daily ORDER BY 日

ウィンドウ関数は行を潰さずに、窓ごとの集計値・順位・前後の行の値を各行に付ける。

確認

同じ列で束ねても、GROUP BY と OVER (PARTITION BY) で結果の行数が違うのはなぜか。

GROUP BY は窓ごとに 1 行へ潰すが、OVER は窓ごとに計算した値を窓の中の各行にそのまま付けるから。だから OVER では元の行が残る。

グループごとの最新の 1 件だけを取り出すには、どう組み立てるか。

窓の中を並べて番号を振り、その結果に WITH で名前を付けてから、番号が先頭の行だけを残す。番号を振る段階では行が減らないので、絞るのは後段になる。

順位を付けるとき、ROW_NUMBER と RANK と DENSE_RANK は何が違うか。

ROW_NUMBER は同点でも別々の番号を振り、RANK は同点を同じ順位にして次の順位を飛ばし、DENSE_RANK は飛ばさない。グループごとに 1 行だけ欲しいときは、必ず 1 つに決まる ROW_NUMBER を使う。