ウィンドウ関数

概要

一連のクエリー行に対して集計のような操作を実行する
※特定のカラムの値が同じ行毎に集計したい場合等に使う
※集計操作ではクエリー行が単一の結果行にグループ化される

ROW_NUMBER()

# カラムAのランキング
ROW_NUMBER() OVER (ORDER BY カラムA)

LAG(), LEAD()

# 前の行の値との比較での絞り込み
LAG(カラムA, 1) OVER (ORDER BY カラムB) PREV_カラムA

# 次の行の値との比較での絞り込み
LEAD(カラムA, 1) OVER (ORDER BY カラムB) NEXT_カラムA

※第2引数デフォルトで1行前か後
※カラムの値に重複がある場合の挙動も確認する

OVER (), RANK()

# カラムAの合計毎のランキング
※GROUP BY指定がある場合はGROUP BY毎の合計のランキング
RANK() OVER (ORDER BY sum(カラムA) DESC)

# 日時のカラムの年・月毎の行数のランキング
※同一順があった場合には1,2,3,3,5のようになる
※同一順があった場合でDENSE_RANK()の場合は1,2,3,3,4のようになる
RANK() OVER (PARTITION BY YEAR(日時カラム), MONTH(日時カラム) ORDER BY COUNT(*) DESC)

# その年月までの累計
SUM(SUM(集計対象カラム)) OVER (ORDER BY YEAR(日時カラム), MONTH(日時カラム) ROWS UNBOUNDED PRECEDING) rolling_sum
# その週までの累計
SUM(SUM(集計対象カラム)) OVER (ORDER BY YEARWEEK(日時カラム) ROWS UNBOUNDED PRECEDING) rolling_sum

# 全体の合計に対する割合の算出
ROUND(SUM(集計対象カラム) / SUM(SUM(集計対象カラム)) OVER () * 100, 2) percent

# グループ毎の最新の日付のカラムの値
MAX(LATEST_値カラム) OVER (PARTITION BY グループカラム) LATEST_値カラム
※MAXで0を除く

CASE
  WHEN 日付カラム = MAX(日付カラム) OVER(PARTITION BY グループカラム)
  THEN 値カラム ELSE 0
END LATEST_値カラム
※LATEST_値カラムは日付が最新の値カラム or 0になる

例: その年の週毎の売上合計と年初からの累積売上

SELECT
  YEARWEEK(payment_date) year_week,
  SUM(amount) weekly_sum_amount,
  RANK() OVER (ORDER BY SUM(amount) DESC) week_sales_rank,
  SUM(SUM(amount)) OVER (ORDER BY YEARWEEK(payment_date) ROWS UNBOUNDED PRECEDING) rolling_sum_amount,
  ROUND(SUM(amount) / SUM(SUM(amount)) OVER () * 100, 2) percent
FROM
  payment p
GROUP BY
  year_week
ORDER BY
  year_week;

例: 年月毎の売上集計と累積売上

SELECT
  YEAR(payment_date) year,
  MONTH(payment_date) month,
  SUM(amount) monthly_sum_amount,
  RANK() OVER (ORDER BY SUM(amount) DESC) month_sales_rank,
  SUM(SUM(amount)) OVER (ORDER BY YEAR(payment_date), MONTH(payment_date) ROWS UNBOUNDED PRECEDING) rolling_sum_amount,
  ROUND(SUM(amount) / SUM(SUM(amount)) OVER () * 100, 2) percent
FROM
  payment p
WHERE
  YEAR(payment_date) = 2005
GROUP BY
  year, month
ORDER BY
  year, month;

例: 年月毎のユーザー毎のレンタル数ランキング

SELECT
  *
FROM (
  SELECT
    customer_id,
    YEAR(rental_date) rental_year,
    MONTH(rental_date) rental_month,
    COUNT(*) year_month_rental_count,
    RANK() OVER (PARTITION BY YEAR(rental_date), MONTH(rental_date) ORDER BY COUNT(*) DESC) year_month_rental_count_rank
  FROM
    rental
  GROUP BY
    customer_id, rental_year, rental_month
  ORDER BY
    rental_year, rental_month, year_month_rental_count DESC
) cust_rankings
WHERE
  year_month_rental_count_rank <=5
ORDER BY
  rental_year, rental_month, year_month_rental_count DESC, year_month_rental_count_rank;

例: 各年の四半期毎の売上集計

SELECT
  YEAR(payment_date) year,
  QUARTER(payment_date) quarter,
  SUM(amount) monthly_sum_amount,
  RANK() OVER (ORDER BY SUM(amount) DESC) quarter_sales_rank
FROM
  payment p
GROUP BY
  YEAR(payment_date), QUARTER(payment_date)
ORDER BY
  year, quarter;

例: 映画毎の俳優一覧(CSV)

SELECT
  f.title,
  GROUP_CONCAT(a.actor_id ORDER BY a.actor_id SEPARATOR ',') actor_ids,
  GROUP_CONCAT(CONCAT(a.first_name, ' ', a.last_name) ORDER BY CONCAT(a.first_name, ' ', a.last_name) SEPARATOR ',') actors
FROM
  film f
  INNER JOIN
    film_actor fa ON f.film_id = fa.film_id
  INNER JOIN
    actor a ON fa.actor_id = a.actor_id
GROUP BY
  f.title;