Zenn
🪟

【備忘録】ウィンドウ関数の活用例

2024/05/03に公開
MySQL
SQL
tech

仕事でBIツール MetaBaseを使用し、
非エンジニア向けにダッシュボードやエクセルのエクスポート機能を提供しています。

最近、以下のような要望が続いたため、ウィンドウ関数の活用例を自分用にまとめました。

  • ある時点での最新のデータを1件表示してほしい
  • 折れ線グラフの変動が激しいので移動平均線が欲しい

なお、本記事ではMySQL 8系でのウィンドウ関数の利用を前提としています。

ウィンドウ関数とは

ウィンドウは、選択されたレコードの集合に対して、
順序付けやフレーム定義が行なわれたうえでのデータセット
を指します。

ウィンドウ関数は次の3つの要素で構成されます。

①：PARTITION BY句によるレコード集合のカット
②：ORDER BY句によるレコードの順序付け
③：フレーム句によるカレントレコードを中心としたサブセットの定義

例

SELECT
  sample_date AS  cur_date,
  MIN(sample_date)
  OVER(
    PARTITION BY server_id
    ORDER BY sample_date ASC 
    ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
  ) AS latest_date
FROM LoadSample;

（参考書籍「2 必ずわかるウィンドウ関数 フレーム句を使って違う行を自分の行に持ってくる」章のSQL文にPARTITION BY句を追加）

server_id(サーバー)ごとの評価点数の過去1週間の移動平均を取得するクエリです。
PARTITION BY句でserver_idごとにデータを分割（①）し、
ORDER BY句でsample_dateで昇順に並べ替え（②）、
ROWS BETWEEN句で範囲を指定（③）しています。

主なウィンドウ関数

関数名 説明
ROW_NUMBER パーティション内の現在の行数
RANK パーティション内の現在の行のランク (ギャップあり)
DENSE_RANK パーティション内の現在の行のランク (ギャップなし)
FIRST_VALUE ウィンドウフレームの最初の行からの引数の値
LAST_VALUE ウィンドウフレームの最後の行からの引数の値
NTH_VALUE ウィンドウフレームの N 番目の行からの引数の値
LEAD パーティション内の現在の行の先頭行からの引数の値
LAG パーティション内の現在行より遅れている行の引数の値

この他にもSUMやAVGなどの集計関数をウィンドウ関数として利用することもできます。

フレーム句の主なオプション

オプション名 説明
ROWS 移動単位を行で設定する
RANGE 移動単位を列の値で設定する。基準となる列はORDERBY句で指定された列
nPRECEDING nだけ前へ（小さいほう）へ移動する。nは正の整数
nFOLLOWING nだけ後へ（大きいほう）へ移動する。nは正の整数
UNBOUNDEDPRECEDING 無制限にさかのぼるほうへ移動する
UNBOUNDEDFOLLOWING 無制限に下るほうへ移動する
CURRENTROW 現在行

（参考書籍「2 必ずわかるウィンドウ関数 フレーム句を使って違う行を自分の行に持ってくる」章より引用）

具体的な利用ケース

飲食店の評価データを例に、ウィンドウ関数を利用したクエリを紹介します。
データ内容はChatGPTが作成した架空のデータです。

ER図

restaurant_mastersテーブル

  • name: 飲食店名

restaurant_masters

restaurant_ratingsテーブル

  • restaurant_master_id: 飲食店マスタID
  • rating_date: 評価日
  • rating_score: 評価点数

restaurant_ratings

restaurant_eventsテーブル

  • restaurant_master_id: 飲食店マスタID
  • content:ニュースや口コミの内容
  • event_date:発生日

restaurant_rates

1. ある出来事があった直後のデータを取得する

ニュースや口コミの内容が更新された直前と直後の評価データを取得するクエリです。

SELECT
    name,
    before_score,
    content,
    after_score
FROM
 (
    select
    rm.id as master_id,
    rm.name,
    rr.rating_score as before_score,
    re.content,
    SUM(rating_score) over (
      PARTITION BY rr.restaurant_master_id
      order by rating_date asc
      range between interval 1 day following and interval 1 day following
    ) as after_score,
    event_date,
    rr.rating_date
    from restaurant_ratings rr
    join restaurant_masters rm
    on rr.restaurant_master_id = rm.id
    join restaurant_events re
    on re.restaurant_master_id = rm.id
  ) as events
where rating_date = event_date 
order by master_id;

range between interval 1 day following and interval 1 day followingで、
出来事の直後のデータを取得しています。

restaurant_event_study

2. 移動平均を作成する

飲食店ごとの評価点数の過去1週間の移動平均を取得するクエリです。

SELECT
  restaurant_master_id,
  rating_date,
  rating_score,
  AVG(rating_score) OVER (
    PARTITION BY restaurant_master_id
    ORDER BY rating_date
    RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM
  restaurant_ratings;

moving_avg

3. ランキングを取得する

飲食店ごとの評価点数の順位を取得するクエリです。

SELECT
  name,
  rating_date,
  rating_score,
  RANK() OVER (
    PARTITION BY restaurant_master_id
    ORDER BY rating_score DESC
  ) AS each_restaurant_rating_rank
FROM
  restaurant_ratings re
join restaurant_masters rm
on re.restaurant_master_id = rm.id
order by restaurant_master_id,rating_date asc

restaurant_rank

参考書籍・URL

GitHubで編集を提案

Discussion