こんにちは、かつコーチです。
「各カテゴリの売上ランキングを、元の行を保ったまま表示したい」と思ってGROUP BYを使ったら、個別の行が消えて集計値しか出せなかった、という経験はありませんか。
そんなときに使うのがウィンドウ関数です。
GROUP BYのように行を集約せず、各行を保ったまま「その行が属するグループ内での順位や合計」を計算できます。
ウィンドウ関数とは？
用語の定義：ウィンドウ関数
ウィンドウ関数とは、OVER句で指定した範囲（ウィンドウ）ごとに集計や順位付けを行いながら、集計後も元の行数を維持したまま結果を返す関数です。
GROUP BYが「複数行を1行にまとめる」のに対し、ウィンドウ関数は「行はそのままに、隣の列として集計結果を追加する」イメージです。
SELECT
product_id,
category_id,
sales_amount,
SUM(sales_amount) OVER (PARTITION BY category_id) AS category_total
FROM product_sales;
OVER (PARTITION BY category_id)が「カテゴリごとに集計する」という指示で、product_id単位の行を保ったままcategory_total列が追加されます。
なぜウィンドウ関数が必要なのか
GROUP BYだけで「各商品の売上」と「カテゴリ内の合計」を同時に見ようとすると、サブクエリで自己結合する必要があり、クエリが冗長になります。
-- GROUP BYとサブクエリで同じことをやろうとすると結合が必要
SELECT
p.product_id,
p.category_id,
p.sales_amount,
c.category_total
FROM product_sales p
JOIN (
SELECT category_id, SUM(sales_amount) AS category_total
FROM product_sales
GROUP BY category_id
) c ON c.category_id = p.category_id;
ウィンドウ関数を使えば、この結合が不要になり1回のスキャンで済みます。
基本の書き方
手順1：ランキングを付ける（RANK / DENSE_RANK / ROW_NUMBER）
売上金額でカテゴリごとの順位を付ける例です。
SELECT
product_id,
category_id,
sales_amount,
RANK() OVER (PARTITION BY category_id ORDER BY sales_amount DESC) AS rank_in_category
FROM product_sales;
RANK()は同じ値なら同じ順位を付け、次の順位を飛ばします（1位が2件なら次は3位）。
DENSE_RANK()は順位を飛ばさず連番にし、ROW_NUMBER()は同じ値でも必ず一意の連番を振ります。
3つの違いを表で整理します。
| 関数 | 同点の扱い | 次の順位 |
|---|---|---|
ROW_NUMBER() | 常に一意の連番 | 1, 2, 3, 4 |
RANK() | 同点は同順位 | 1, 1, 3, 4（2が欠番） |
DENSE_RANK() | 同点は同順位 | 1, 1, 2, 3（欠番なし） |
「カテゴリ内で売上1位の商品だけ抽出したい」ならRANK() = 1、「重複なく上位N件を取りたい」ならROW_NUMBER() <= Nを使うのが定石です。
手順2：移動平均・累計を計算する（フレーム句）
ORDER BYと組み合わせて、直近3件の移動平均を出す例です。
SELECT
order_date,
daily_sales,
AVG(daily_sales) OVER (
ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3days
FROM daily_sales_summary
ORDER BY order_date;
ROWS BETWEEN 2 PRECEDING AND CURRENT ROWが「自分の行を含めて直前2行」を対象にするフレーム指定です。
累計売上を出したい場合は、フレームをUNBOUNDED PRECEDINGにします。
SELECT
order_date,
daily_sales,
SUM(daily_sales) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM daily_sales_summary
ORDER BY order_date;
つまずきやすい設定・注意点
ORDER BYをウィンドウ関数のOVER句に書くと、デフォルトのフレームはRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWになります。
つまりORDER BYだけ書いてROWS BETWEENを省略すると、自動的に「先頭から現在行まで」の累計になる点は覚えておきましょう。
単純に「カテゴリ全体の合計」を出したいだけならORDER BYは付けず、PARTITION BYだけにするのがシンプルです。
よくあるつまずきポイント・エラー対処
Before/After：WHERE句でウィンドウ関数を絞り込もうとしてエラーになる
ウィンドウ関数の結果はSELECT句で計算されるため、同じSELECT文のWHERE句では参照できません。
-- ❌ Before：WHERE句でウィンドウ関数の結果を直接使おうとしてエラー
SELECT
product_id,
category_id,
RANK() OVER (PARTITION BY category_id ORDER BY sales_amount DESC) AS rank_in_category
FROM product_sales
WHERE rank_in_category <= 3;
-- ERROR: column "rank_in_category" does not exist
私が初めてウィンドウ関数を使ったときも、まさにこのエラーで「計算した列なのに参照できない」と混乱しました。
原因は、SQLの実行順序ではWHERE句がSELECT句より先に評価されるためです。
-- ✅ After：サブクエリかCTEでいったん結果を確定させてから絞り込む
WITH ranked_sales AS (
SELECT
product_id,
category_id,
sales_amount,
RANK() OVER (PARTITION BY category_id ORDER BY sales_amount DESC) AS rank_in_category
FROM product_sales
)
SELECT product_id, category_id, sales_amount
FROM ranked_sales
WHERE rank_in_category <= 3;
前回紹介したCTEでウィンドウ関数の結果を確定させてからWHEREで絞り込む、という組み合わせは実務で頻出のパターンです。
応用・一歩先の使い方
LAG / LEADで前後の行と比較する
LAG()は1行前、LEAD()は1行後の値を取得できます。
前日比の売上を計算する例です。
SELECT
order_date,
daily_sales,
daily_sales - LAG(daily_sales) OVER (ORDER BY order_date) AS diff_from_prev_day
FROM daily_sales_summary
ORDER BY order_date;
在庫アラートの前回チェック日との比較や、ユーザーの前回ログインとの間隔算出など、時系列データの差分計算に幅広く使えます。
第2引数でずらす行数、第3引数でデータが無いときのデフォルト値も指定できます。
SELECT
order_date,
daily_sales,
LAG(daily_sales, 7, 0) OVER (ORDER BY order_date) AS sales_7days_ago
FROM daily_sales_summary
ORDER BY order_date;
まとめ
この記事のポイント
- ウィンドウ関数は
OVER句で範囲を指定し、行を保ったまま集計・順位付けができる RANK/DENSE_RANK/ROW_NUMBERは同点の扱いが異なるため用途で使い分ける- フレーム句（
ROWS BETWEEN）で移動平均や累計を計算できる - ウィンドウ関数の結果は同じ
SELECT文のWHERE句で使えないため、CTEでラップする
次に読むべき記事
集計とランキングの次は、登録と更新を1つの文で済ませるUPSERTを押さえると、実装の幅がさらに広がります。
- CTE（WITH句）の基本：クエリを読みやすく整理する
- UPSERT（ON CONFLICT）とRETURNINGで往復を減らす（近日公開）
タグ: PostgreSQL, 中級者向け, クエリ