Posted inデータベース

基本的なクエリを超えて：実務のデータ分析でSQLウィンドウ関数を使いこなす

Database tutorial - IT technology blog
Database tutorial - IT technology blog

パフォーマンスの壁：ウィンドウ関数以前の世界

2019年当時、私は自重でクラッシュしそうな日次の売上ダッシュボードを作成していました。要件はシンプルで、日次の売上、その月の累計、 schoolそして前日との比較を表示することでした。当初、私はこのロジックをアプリケーション層で処理していました。Pythonスクリプトで500行を読み込み、シンプルなループを使用しました。データセットが10万行に達し、ダッシュボードの読み込みに15秒かかるようになるまでは、完璧に動作していました。

最初、私は自己結合（self-join）やサブクエリを使って、このロジックをSQLに移そうとしました。テーブルを自分自身に結合して累計を計算しようとしたことがある人なら、そのフラストレーションがわかるでしょう。クエリはメンテナンスの悪夢でした。エンジンが1行の出力ごとに500万行の同じテーブルを繰り返しスキャンするため、パフォーマンスは急落しました。その瞬間、私はSQLを単なるデータバケツとして扱うのをやめ、ウィンドウ関数を使い始める必要があると悟ったのです。

MySQL 8.0以降、PostgreSQL、SQL Serverなどのモダンなデータベースでは、これらの関数は現代のスタックにおいて絶対的な必需品として提供されています。ウィンドウ関数を使用すると、現在の行に関連する特定の行セットにわたって計算を実行できます。決定的な違いは、標準的な GROUP BY 句のようにデータを1行の集計結果に圧縮することなく、計算を実行できる点です。

秘訣：OVERとPARTITION BY

OVER() 句は、すべてのウィンドウ関数の核となる存在です。これはデータベースエンジンに対して、「この計算を特定のデータウィンドウに対して実行せよ」と指示します。その句の中で、PARTITION BY と ORDER BY を使用して境界を定義します。

なぜGROUP BYだけでは不十分なのか

エンジニアがよくつまずくポイントがここです。GROUP BY を使用すると、基本的にはデータを押しつぶしてしまいます。すべてがグループごとの1つの合計に集約されるため、個々の行の粒度が失われます。ウィンドウ関数は異なります。各行は一意のアイデンティティを保持したまま、関数が計算された値（部門平均やランクなど）を元のデータのすぐ隣に追加します。

データの境界を定義する

  • PARTITION BY: これは局所的なフィルタのように機能します。結果セットを個別のバケット（区切り）に分割し、関数はバケットごとにリセットされます。
  • ORDER BY: これは各バケット内での行の順序を決定します。累積合計や、時系列順がすべてである時系列分析において非常に重要です。
-- 標準的な構文のテンプレート
SELECT 
    column_name,
    FUNCTION() OVER (PARTITION BY group_col ORDER BY sort_col) as result_alias
FROM table_name;

実践ロジック：現実の課題を解決する

employee_name（従業員名）、department（部門）、sale_amount（売上額）、sale_date（売上日）を含む、実践的な sales テーブルを見てみましょう。ここではPostgreSQLの構文を使用しますが、これらの例はほとんどのモダンなSQLダイアレクトで共通です。

1. リーダーボード（順位付け）の課題を解決する

リーダーボードは典型的なユースケースです。適切な順位付け関数を選択するかどうかは、同点をどのように扱うかによって決まります。アリスとボブという2人の従業員が、どちらも正確に5,000ドルのソフトウェアを販売したと仮定しましょう。

  • ROW_NUMBER(): 同点に関わらず、一意のランク（1と2）を割り当てます。
  • RANK(): 両方に1位を割り当てますが、次の人は3位になります。
  • DENSE_RANK(): 両方に1位を割り当て、次の人は2位になります。
SELECT 
    employee_name, 
    department, 
    sale_amount,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY sale_amount DESC) as dept_rank
FROM sales;

部門ごとにパーティションを区切ることで、複数のクエリを発行することなく、「営業」「マーケティング」「エンジニアリング」の個別のリーダーボードを同時に生成できます。

2. 効率的な累積合計（累計）

これは、私が2019年のレポートプロジェクトで処理時間を最も節約できた部分です。累計とは、行を下に移動するにつれて構築される加算のことです。OVER の中に ORDER BY を追加することで、SQLはパーティションの最初の行から現在の行までの合計を自動的に計算します。

SELECT 
    sale_date, 
    sale_amount,
    SUM(sale_amount) OVER (ORDER BY sale_date) as cumulative_revenue
FROM sales;

3. LAGとLEADでトレンドを検知する

システム監視や収益のモニタリングでは速度が重要です。LAG() は前の行からデータを取得し、LEAD() は先の行を参照します。最近、私はこれを使用して、現在のミリ秒単位のレスポンスタイムを60秒前に記録されたメトリクスと比較することで、サーバーのレイテンシスパイクを検知しました。

ビジネスの成長において、これは前日比の変化を計算する最もクリーンな方法です：

SELECT 
    sale_date, 
    sale_amount,
    LAG(sale_amount) OVER (ORDER BY sale_date) as yesterday_revenue,
    sale_amount - LAG(sale_amount) OVER (ORDER BY sale_date) as daily_delta
FROM sales;

LAG を使用することは、t1.date = t2.date - 1 による自己結合よりもはるかに効率的です。自己結合は、休日や週末で記録にギャップが生じると失敗することが多いためです。

4. 移動平均によるデータの平滑化

ノイズは分析の敵です。1つの高額取引が、1週間のパフォーマンスに対する認識を歪めてしまうことがあります。私は通常、これらの外れ値を平滑化するために7日間の移動平均を適用します。これには ROWS BETWEEN を使用した「ウィンドウフレーム」が必要です。

SELECT 
    sale_date, 
    sale_amount,
    AVG(sale_amount) OVER (
        ORDER BY sale_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as weekly_rolling_avg
FROM sales;

このロジックは、現在の行と、その直前の6行を参照するようにデータベースに指示します。これを標準的なSQLで実現しようとすると複雑なサブクエリが必要になりますが、ここでは可読性の高い1行で済みます。

隠れたコスト

これらのツールは強力ですが、無料ではありません。特に ORDER BY 句によって大規模なディスクソートが強制される場合、数千万行のテーブルにウィンドウ関数を適用する際には注意が必要であることを学びました。パーティション列にインデックスが貼られていない場合、CPU使用率が急上昇する可能性があります。

これを軽減するために、私は通常、まず共通テーブル式（CTE）を使用してデータセットを可能な限りフィルタリングします。ウィンドウ関数を適用する前に範囲を直近30日間に絞り込むことで、メモリ使用量を低く抑え、レポートの結果が数分ではなく数ミリ秒で返ってくるようにします。

ループ処理からの脱却

データベースの方が高速に処理できるのであれば、アプリケーションコードで重い処理を行うべきではありません。複雑なロジックをデータベース層に移すことで、APIのレスポンスが向上し、バックエンドコードが大幅にクリーンになります。ウィンドウ関数は、この移行を可能にする架け橋です。

まずは、よく知っているデータセットでシンプルな DENSE_RANK()

Share:
Tags: