PostgreSQLウィンドウ関数入門！OVER句の使い方を初心者向けに徹底解説

1. PostgreSQLのウィンドウ関数とは？

1. PostgreSQLのウィンドウ関数とは？

PostgreSQL（ポストグレスキューエル）というデータベース管理システムには、非常に強力な機能が備わっています。その中でも「ウィンドウ関数」は、データの分析やレポート作成において欠かせない存在です。

通常、SQLで合計や平均を計算するときは「GROUP BY（グループ・バイ）」という命令を使います。しかし、これを使うと行が一つにまとまってしまい、個別のデータ（例えば誰が何点だったかという詳細）が消えてしまいます。

ウィンドウ関数を使えば、「個別の行を残したまま、その横に集計結果を表示する」ことが可能です。まるで窓（ウィンドウ）から特定の範囲を覗き見ながら計算するような動きをするため、この名前がつきました。

2. ウィンドウ関数の基本「OVER句」の仕組み

2. ウィンドウ関数の基本「OVER句」の仕組み

ウィンドウ関数を使うために、必ずセットで覚える必要があるのが「OVER（オーバー）句」です。これは「どの範囲を対象に計算するか」を指定する命令です。

初心者の方向けに、最も簡単な例として「全体の合計を全行に表示する」方法を見てみましょう。まずは、スーパーの売り上げデータが入ったテーブル（表）を用意しました。

id | category | product | price
---+----------+---------+-------
1  | 野菜     | キャベツ| 150
2  | 野菜     | トマト  | 200
3  | 果物     | リンゴ  | 300
4  | 果物     | バナナ  | 100
5  | 肉       | 豚肉    | 500

このデータを使って、各商品の横に「全商品の合計金額」を表示させてみましょう。以下のSQLを実行します。

SELECT 
    product, 
    price, 
    SUM(price) OVER() as total_price
FROM sales;

実行結果は以下のようになります。

product  | price | total_price
---------+-------+-------------
キャベツ | 150   | 1250
トマト   | 200   | 1250
リンゴ   | 300   | 1250
バナナ   | 100   | 1250
豚肉     | 500   | 1250

OVER句の中に何も書かないと、表全体を一つの「窓」として捉えます。その結果、すべての行に合計値である1250が表示されました。これがウィンドウ関数の基本的な動きです。

3. PARTITION BYでグループ分けして計算する

3. PARTITION BYでグループ分けして計算する

次に、もっと実用的な使い方を紹介します。「カテゴリーごとに合計を出したい」という場面です。ここで使うのが「PARTITION BY（パーティション・バイ）」です。パーティションとは「仕切り」という意味で、データをグループごとに区切る役割をします。

例えば、野菜の行には野菜の合計を、果物の行には果物の合計を表示させたい場合に便利です。以下のSQLを見てみましょう。

SELECT 
    category, 
    product, 
    price, 
    SUM(price) OVER(PARTITION BY category) as category_total
FROM sales;

実行結果は以下のようになります。

category | product  | price | category_total
---------+----------+-------+----------------
野菜     | キャベツ | 150   | 350
野菜     | トマト   | 200   | 350
果物     | リンゴ   | 300   | 400
果物     | バナナ   | 100   | 400
肉       | 豚肉     | 500   | 500

「野菜」のグループでは150円と200円を足した350円が、「果物」のグループでは300円と100円を足した400円が表示されていますね。このように、表をバラバラに分解することなく、特定の範囲ごとに計算ができるのが大きなメリットです。

4. ORDER BYで順位や累計を出す

4. ORDER BYで順位や累計を出す

ウィンドウ関数のもう一つの強力な武器が「ORDER BY（オーダー・バイ）」です。これは「並び順」を指定するものですが、ウィンドウ関数の中で使うと「上から順番に足していく（累計）」や「ランキング（順位）」を作ることができます。

ここでは、売り上げが高い順にランキングを付けてみましょう。使うのは「RANK()（ランク）」という関数です。

SELECT 
    product, 
    price, 
    RANK() OVER(ORDER BY price DESC) as sales_rank
FROM sales;

※DESC（デスク）とは「大きい順（降順）」という意味です。

実行結果は以下のようになります。

product  | price | sales_rank
---------+-------+------------
豚肉     | 500   | 1
リンゴ   | 300   | 2
トマト   | 200   | 3
キャベツ | 150   | 4
バナナ   | 100   | 5

価格が高い順に、1位から5位まで綺麗に番号がつきました。もし同じ価格の商品があった場合、RANK関数は同じ順位を割り当ててくれます。このように、複雑な計算も1行の命令で完結するのがウィンドウ関数の魅力です。

5. JSONBやCTEとの組み合わせで広がる可能性

5. JSONBやCTEとの組み合わせで広がる可能性

PostgreSQLには、他にも高度な機能があります。例えば「JSONB（ジェイソン・ビー）」は、複雑な構造のデータをそのまま保存できる形式です。最近のWebアプリでは非常によく使われます。

また、「CTE（シー・ティー・イー）」は、複雑なSQLを一時的な表として名前を付けて保存し、再利用しやすくする仕組みです。これらをウィンドウ関数と組み合わせることで、プロのデータエンジニアが行うような高度な分析が可能になります。

未経験の方にとっては、まずは「ウィンドウ関数を使えば、行をまとめずに集計や順位付けができる！」という点だけを覚えておけば十分です。これがわかると、Excelで何時間もかけて行っていた作業が、SQLの命令ひとつで一瞬で終わるようになります。

データベースの世界は奥が深いですが、一つひとつの命令は、私たちが普段「表」を見て考えていることをコンピュータに伝えているだけです。焦らずに、少しずつ慣れていきましょう。