OVER / PARTITION BY

ウィンドウ関数の基本構文です。

ウィンドウ関数() OVER (
    [PARTITION BY グループ列]
    [ORDER BY 並び順列 ASC|DESC]
    [フレーム句]
)

フレーム句: 現在行を基準にした処理対象の行の範囲を指定します。

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 先頭から現在行まで
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW          -- 2行前から現在行まで
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING  -- 現在行から末尾まで

構文	概要
OVER()	全行を1つのウィンドウとして処理します。GROUP BY なしで全体集計値を各行に付与できます。
PARTITION BY 列	指定列の値でウィンドウをグループ分けします。GROUP BY と異なり行数は減りません。
ORDER BY 列	ウィンドウ内の並び順を指定します。累計など順序に意味がある集計に必要です。
ROWS BETWEEN ... AND ...	処理対象の行の範囲（フレーム）を現在行を基準に指定します。フレーム句は集計の対象範囲を指定する機能で、基本的な使い方では省略しても構いません。

以下の『employees』テーブルを例に説明します。

部署ごとの平均給与を全行に付与します（行数は減らない）。

sample_over_partition_by.sql

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees
ORDER BY department, salary DESC;

+------------+------------+--------+-----------------+
| name       | department | salary | dept_avg_salary |
+------------+------------+--------+-----------------+
| 常守朱     | 監視官     | 620000 |    505000.0000  |
| 宜野座伸元 | 監視官     | 390000 |    505000.0000  |
| 狡噛慎也   | 執行官     | 480000 |    400000.0000  |
| 征陸智己   | 執行官     | 320000 |    400000.0000  |
+------------+------------+--------+-----------------+
4 rows in set

給与の高い順に並べて累計給与を計算します（フレーム句）。

sample_over_partition_by.sql

SELECT
    name,
    salary,
    SUM(salary) OVER (
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM employees
ORDER BY salary DESC;

+------------+--------+---------------+
| name       | salary | running_total |
+------------+--------+---------------+
| 常守朱     | 620000 |        620000 |
| 狡噛慎也   | 480000 |       1100000 |
| 宜野座伸元 | 390000 |       1490000 |
| 征陸智己   | 320000 |       1810000 |
+------------+--------+---------------+
4 rows in set

部署ごとの最大・最小給与を全行に付与します。

sample_over_partition_by.sql

SELECT
    name,
    department,
    salary,
    MAX(salary) OVER (PARTITION BY department) AS dept_max_salary,
    MIN(salary) OVER (PARTITION BY department) AS dept_min_salary
FROM employees;

+------------+------------+--------+-----------------+-----------------+
| name       | department | salary | dept_max_salary | dept_min_salary |
+------------+------------+--------+-----------------+-----------------+
| 狡噛慎也   | 執行官     | 480000 |          480000 |          320000 |
| 常守朱     | 監視官     | 620000 |          620000 |          390000 |
| 宜野座伸元 | 監視官     | 390000 |          620000 |          390000 |
| 征陸智己   | 執行官     | 320000 |          480000 |          320000 |
+------------+------------+--------+-----------------+-----------------+
4 rows in set

この構文は MySQL（8.0以降）・PostgreSQL・Oracle・SQLite（3.25以降）で共通して使用できます。

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees
ORDER BY department, salary DESC;

ウィンドウ関数の最大の特徴は、GROUP BY と異なり行数を減らさずに集計値を各行に付与できる点です。これにより「各行に部署の合計を並べて比較する」といった処理がシンプルに書けます。

フレーム句を省略して ORDER BY だけを指定すると、デフォルトのフレームは「先頭から現在行まで（RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）」になります。累計計算では ROWS を明示的に指定する方が意図通りの結果になります。

ウィンドウ関数を使った順位付けは『ROW_NUMBER / RANK / DENSE_RANK』を、累計・移動平均は『SUM / AVG（ウィンドウ）』を参照してください。