行を潰さず集計する - ウィンドウ関数入門 (OVER / PARTITION BY)

GROUP BY と違い行数を潰さず集計する。OVER と PARTITION BY の基本、移動平均の例

概念図

構文

sql

集約関数(...) OVER ( [PARTITION BY ...] [ORDER BY ...] [フレーム] )

サンプル

ユーザーごとに注文日順で累計を計算（行は潰さず 1 注文 1 行のまま）

sql

SELECT
  user_id,
  order_date,
  amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS running_total
FROM orders;

ウィンドウ関数とは

ウィンドウ関数は OVER(...) を付けて呼び出す集計関数で、結果の行数を減らさずに集計値を各行に添えて返します。GROUP BY が「集約してまとめる」のに対し、ウィンドウ関数は「集約値を隣に並べる」のが特徴です。

用途の例: 累計、移動平均、グループ内順位、前行との差分、グループ内の割合。

PARTITION BY と ORDER BY

PARTITION BY: ウィンドウを切り分ける単位。省略すると全行が 1 ウィンドウ
ORDER BY: ウィンドウ内の順序。累計やランキングでは必須

イメージ: PARTITION BY user_id は「ユーザーごとに行をグループ分け」、ORDER BY order_date は「各グループ内を日付順に並べる」動作です。

集約関数をウィンドウとして使う

SUM / AVG / COUNT など通常の集約関数は、OVER を付けるだけでウィンドウ関数として動きます。移動平均は AVG(...) OVER (ORDER BY ... ROWS BETWEEN ... ) で書けます。

sql

-- 直近 7 日間の移動平均
SELECT
  sales_date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY sales_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;

GROUP BY との使い分け

「部署ごとの平均給与だけが欲しい」→ GROUP BY
「各社員の行に、その部署の平均給与 を並べて 表示したい」→ ウィンドウ関数

同じクエリで両方を混ぜることもできます。GROUP BY で集約した結果に対し、グループ間のランキングを付ける、など。

RDBMS 対応状況と注意点

PostgreSQL / SQL Server / Oracle / MySQL 8.0+: 全面対応
SQLite: 3.25 (2018) 以降で対応。古いモバイル環境では使えないことがある
MySQL 5.7 以前: ウィンドウ関数なし。変数で擬似的に書くしかなかった

WHERE 句では ウィンドウ関数を直接使えない（実行順序が SELECT より前）ため、絞り込みたいときは CTE / サブクエリでラップします。