WINDOW関数の「重複」を解消する

少し遅いですが、皆様あけましておめでとうございます🎍

新年一発目の記事にしては少々ニッチな内容かもしれませんが、今年もたくさんの有益な情報を発信できるように頑張っていきたいと思います！

今回は、最初は戸惑いますが、慣れると非常に便利な SQL のWINDOW関数に関する記事です。

このWINDOW関数ですが、データの集計や順位付けにおいて非常に便利な機能です。

しかし、一つのクエリで「累計」「前回の値」「ランキング」など複数の視点で分析を行おうとすると、同じような OVER 句を何度も書くことになってしまいます。

例えば、SUM(...) OVER (...)、LAG(...) OVER (...)、RANK(...) OVER (...) と繰り返すうちに、SQLはどんどん長く、可読性が落ちてしまいます。
仕様変更で集計単位が変わったとき、複数箇所の PARTITION BY を修正して回り、一つ直し忘れてバグを生む……なんてこともあり得ます。

今回は、そんな悩みをスマートに解決するSQLの機能「 WINDOW 句」を紹介します。

こちら、実は標準SQLで定義されている構文であり、使いこなせるとクエリの保守性がグッと上がります。

よくある「もっとスマートに書けるクエリ」の例

まずは、よくある「少しメンテナンスが大変になりがちなクエリ」の例を見てみましょう。
ECサイトのユーザー行動ログ（purchase_logs テーブル）を想定します。

データは以下のようなシンプルな構造です。

user_id	purchased_at	amount
U001	2024-01-01 10:00:00	1000
U001	2024-01-05 15:30:00	2500
U002	2024-01-02 11:00:00	5000

このデータから、ユーザーごとに以下の指標を出したいとします。

1. 累計購入額
2. 前回の購入日
3. 購入回数の連番

Before: OVER 句の記述が重複している状態

SELECT
  user_id,
  purchased_at,
  amount,
  -- 1. 累計購入額
  SUM(amount)
    OVER (
      PARTITION BY user_id
      ORDER BY purchased_at
    ) AS cumulative_amount,
  -- 2. 前回の購入日
  LAG(purchased_at)
    OVER (
      PARTITION BY user_id
      ORDER BY purchased_at
    ) AS prev_purchased_at,
  -- 3. 購入回数
  ROW_NUMBER()
    OVER (
      PARTITION BY user_id
      ORDER BY purchased_at
    ) AS purchase_seq
FROM
  purchase_logs;

いかがでしょうか。
PARTITION BY user_id ORDER BY purchased_at という全く同じ定義が3回も登場しています。

このくらいだとまだマシですが、もう少し定義が複雑になると非常に可読性の悪いクエリになってしまいます。

また、冒頭で触れたように「集計軸の変更」が発生した場合、3箇所すべてを正確に修正する必要があります。これはバグの温床であり、プログラミングの鉄則である「同じ記述を繰り返さない（DRY: Don't Repeat Yourself）」に反しています(AIコードレビューで知った言葉を早速使っています🙄)。

WINDOW句を使った「スマートなクエリ」

ここで登場するのが WINDOW 句です。
これは、プログラミング言語で言うところの「変数定義」や「定数定義」に似ています。共通のウィンドウ定義に名前をつけて、再利用できるようにするものです。

After: 定義を一箇所に集約

SELECT
  user_id,
  purchased_at,
  amount,
  -- 定義したウィンドウ 'w' を参照するだけ
  SUM(amount) OVER w AS cumulative_amount,
  LAG(purchased_at) OVER w AS prev_purchased_at,
  ROW_NUMBER() OVER w AS purchase_seq
FROM
  purchase_logs
-- ここでウィンドウを定義
WINDOW
  w AS (
    PARTITION BY user_id
    ORDER BY purchased_at
  );

劇的に見通しが良くなりましたね！

WINDOW w AS (...) で定義を作成し、SELECT句の中では OVER w と書くだけ。
これなら「集計ロジック」だけに集中して読むことができますし、ウィンドウ定義を変更したい場合も、修正箇所は一箇所で済みます。

複数のウィンドウ定義も可能

さらに、ひとつのクエリ内で複数のウィンドウ定義を使い分けることも可能です。例えば、「ユーザーごとの集計」と「全体での集計」を同時に行いたい場合などです。

SELECT
  user_id,
  amount,
  -- ユーザーごとの順位
  RANK() OVER w_by_user AS rank_in_user,
  -- 全体での順位
  RANK() OVER w_global AS rank_global
FROM
  purchase_logs
WINDOW
  w_by_user AS (PARTITION BY user_id ORDER BY amount DESC),
  w_global AS (ORDER BY amount DESC);

このようにカンマ区切りで複数のウィンドウを定義できます。
それぞれに w_by_user や w_global といった意味のある名前をつけることで、複雑になりがちなクエリも驚くほど読みやすくなります。

ちなみに、この WINDOW 句は SQL:2003 で定義された標準SQLの一部です。BigQueryやPostgreSQL、MySQLなど、多くのデータベースでも同じように利用できるので、覚えておくと汎用的なスキルになります。

まとめ

今回は、SQLにおけるWINDOW句についての記事でした。

これを利用することで、WINDOW関数を利用するときの集計軸を共通化することができます。

SQL は他のプログラミング言語と比較すると構造化がしにくい言語と思われがちですが、CTE であったり、今回のWINDOW句を活用することで、高い保守性と可読性を維持することができます。

今後、生成AIがさらに発達していく中で、どれだけ人間が言語を記述するかは分かりませんが、引き出しが多いことで損は無いかと思いますので、新たなことを学ぶ姿勢は変えずに行きたいですね。