SQL Lab — MySQL を大容量で叩いて学ぶ

補足トピック。飛ばして第 7 章へ進んでも、第二部には影響しない。

この章で答える問い

行を減らさずに集計値を付けるには
グループごとの「1 番目」を取るには
累積や前の行との差を出すには

実物: 行が減っていない集計

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

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

⌘/Ctrl + Enter で実行

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

200 行のままだ。AVG(...) OVER (PARTITION BY product_id) は「product_id ごとの窓の中で平均を計算し、その値を各行に付ける」。窓(ウィンドウ)を作って集計するのでウィンドウ関数と呼ぶ。GROUP BY が束ねて潰すのに対し、こちらは束ねて付ける。同じ 4 行に GROUP BY と OVER を当てて、行数がどうなるかを並べる。

GROUP BY は窓ごとに 1 行へ潰す。OVER (PARTITION BY) は窓ごとの値を、窓の中の各行にそのまま付ける。

順位: ROW_NUMBER

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

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

⌘/Ctrl + Enter で実行

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 行(注文のある顧客の数)

⌘/Ctrl + Enter で実行

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

「グループごとの上位 N 件」は、ウィンドウ関数 + WITH の定番だ。ROW_NUMBER は同点でも 1, 2, 3 と振る。同点を同じ順位にしたいなら RANK(1, 1, 3)か DENSE_RANK(1, 1, 2)。

累積と、前の行との差

日ごとの注文数と累積

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

⌘/Ctrl + Enter で実行

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

⌘/Ctrl + Enter で実行

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 で窓の幅を指定

⌘/Ctrl + Enter で実行

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 を使う。