🤖

SQL ウィンドウ関数

に公開
SQL
ウィンドウ関数
tech

ウィンドウ関数とは？

元の行を保持した状態で「累積」「ランキング」「比較」などが行える仕組み。

  • 構文にOVER() を使うのが特徴
  • SELECT句で使用する

基本構文

SELECT 関数名(...) OVER (
  PARTITION BY グループ化する列
  ORDER BY 並び順の列
)

よく使うウィンドウ関数一覧

関数 用途 例
ROW_NUMBER() 完全な連番 順位づけ（同点なし）
RANK() 順位（同点あり、飛び級） 1位,1位,3位
DENSE_RANK() 順位（同点あり、飛び級なし） 1位,1位,2位
LAG() 前の行の値を取得 前月の売上との比較
LEAD() 次の行の値を取得 次回予定の確認
SUM(), AVG() など 累積・グループ内平均 売上推移など

実践的なウィンドウ関数の使用例

使用するテーブル

id user_name sales_date amount
1 Taro 2024-01-01 1000
2 Taro 2024-02-01 1200
3 Taro 2024-03-01 1500
4 Hanako 2024-01-15 2000
5 Hanako 2024-02-15 2500
6 Hanako 2024-03-15 1000

使い所①：ユーザーごとの累積売上を売上順に出す

select
  user_name,
  sales_date,
  amount,
  sum(amount) over(
    partition user_name
    order by sales_date desc
  ) as running_total
from
  sales;

出力結果

user_name sales_date amount running_total
Taro 2024-01-01 1000 1000
Taro 2024-02-01 1200 2200
Taro 2024-03-01 1500 3700
Hanako 2024-01-15 2000 2000
Hanako 2024-02-15 2500 4500
Hanako 2024-03-15 1000 5500

使い所②：ユーザーごとの前回との差分を出したい

select
  user_name,
  sales_date,
  amount,
  amount - lag(amount) over(
    partition user_name
    order by sales_date desc
  ) as diff_from_prev
from
  sales;

出力結果

user_name sales_date amount diff_from_prev
Taro 2024-01-01 1000 NULL
Taro 2024-02-01 1200 200
Taro 2024-03-01 1500 300
Hanako 2024-01-15 2000 NULL
Hanako 2024-02-15 2500 500
Hanako 2024-03-15 1000 -1500

使い所③：ユーザーごとの売上順位を出したい

select
  user_name,
  sales_date,
  amount,
  rank() over(
    partition user_name
    order by sales_date desc
  ) as rank
from
  sales;

出力結果

user_name sales_date amount rank
Taro 2024-03-01 1500 1
Taro 2024-02-01 1200 2
Taro 2024-01-01 1000 3
Hanako 2024-02-15 2500 1
Hanako 2024-01-15 2000 2
Hanako 2024-03-15 1000 3

使い所④：最高売上だったかを判定したい

select
  user_name,
  sales_date,
  amount,
  case 
    when amount = max(amount) over (partition by user_name) then 'yes'
    else 'no'
  end as is_max
from
  sales;

出力結果

user_name sales_date amount is_max
Taro 2024-01-01 1000 NO
Taro 2024-02-01 1200 NO
Taro 2024-03-01 1500 YES
Hanako 2024-01-15 2000 NO
Hanako 2024-02-15 2500 YES
Hanako 2024-03-15 1000 NO

Discussion