ウィンドウ関数の概要

ウィンドウ関数は、現在の行に関連する一連の行に対して計算を行うことができる機能で、GROUP BYのようにその行を単一の出力に集約することはありません。つまり、元の行レベルの詳細を保持しながら、集計、ランキング、比較を並べて取得できます。一般的なユースケースには、リージョンごとの売上トップ商品のランキング、財務レポートの累計計算、時系列の移動平均の算出などがあります。主な構文は、集約関数と、"ウィンドウ"の行の切り分け方を定義するOVER句を組み合わせたものです。

基本構文とコア関数

ウィンドウ関数の呼び出しは、通常の集約関数のように見えますが、末尾にOVER句が付きます。OVER句の内部では、PARTITION BY（行をウィンドウにグループ化する方法）、ORDER BY（各ウィンドウ内でのソート方法）、およびオプションでROWS BETWEENのようなフレーム句を定義します。最も頻繁に使用されるウィンドウ関数は、ROW_NUMBER、RANK、DENSE_RANK、LAG、LEAD、SUM、AVG、COUNTです。これらはコンテキストに応じて集約関数としてもナビゲーション関数としても機能します。

SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

ROW_NUMBERは各パーティション内で一意の連番を割り当て、パーティションが変わると1から再開します。RANKとDENSE_RANKも同様に動作しますが、同点（タイ）には同じ番号を割り当て、同点後の番号の扱い（ギャップの有無）が異なります。

ランキングとTop-Nクエリ

ランキングは最も実用的なアプリケーションの一つです。部署ごとの給与上位3名の従業員を取得したいとします。ランク列を計算し、それをサブクエリまたは可読性を高めるためにCTEでフィルタリングします。このパターンは相関サブクエリよりもはるかに効率的で、アプリケーションコードでも自然に読めます。

WITH ranked AS (
    SELECT
        employee_id,
        department,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rk
    FROM employees
)
SELECT *
FROM ranked
WHERE rk <= 3
ORDER BY department, rk;

PARTITION BY departmentにより、部署ごとにランキングが再開されるため、一部の給与が同点であっても、各グループで常に正確に3名の勝者が得られることに注目してください。

累計と移動フレーム

累計と移動平均には、OVER句内のフレーム句が必要です。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWは、パーティションの先頭から現在の行まで成長する累計を提供します。ROWS BETWEEN 6 PRECEDING AND CURRENT ROWのような固定ウィンドウに切り替えると、7行の移动平均が生成されます。これは時系列分析で一般的です。

SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales
ORDER BY order_date;

最初の列は単調に増加しますが、移動平均は日々のスパイクを平滑化して underlying trend を明らかにします。フレーム定義はROWS（物理的な行数）またはRANGE（論理的な値ベース）を使用できますが、ほとんどのレポートニーズにはROWSの方が高速で予測可能です。

一般的な落とし穴とパフォーマンスのヒント

よくある間違いは、ウィンドウ関数はWHERE、HAVING、GROUP BYの後に実行されることを忘れることです。そのため、CTEやサブクエリを使用しない限り、同じクエリ内で直接フィルタリングすることはできません。もう一つの落とし穴は、SELECT DISTINCTとウィンドウ関数を混在させたり、プランナーを混乱させる方法で結果セットを集約することです。パフォーマンス向上のためには、PARTITION BYとORDER BYで使用される列に常にインデックスを作成し、値ベースのフレーミングが必要ない場合はRANGEよりも ROWS を優先してください。WHERE句でウィンドウ関数を使用するのは避け、最初にクエリをCTEでラップしてください。

まとめ

PostgreSQLのウィンドウ関数は、行レベルの詳細とグループ化された計算を同時に必要とする場合に最適なツールです。PARTITION BYとORDER BYを含むOVER句により、ウィンドウの定義方法を完全に制御でき、フレーム句はその制御をローリング計算にまで拡張します。ランキング、累計、移動平均をマスターすることで、バックエンド開発者として書くことになる実世界の分析クQLの80%程度をカバーできます。