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  -- 現在行から末尾まで

-- 部署ごとの平均給与を全行に付与します（行数は減らない）。
SELECT
    社員名,
    部署名,
    給与,
    AVG(給与) OVER (PARTITION BY 部署名) AS 部署平均給与
FROM 社員
ORDER BY 部署名, 給与 DESC;

-- 注文日順の累計売上を計算します（フレーム句）。
SELECT
    注文日,
    売上金額,
    SUM(売上金額) OVER (
        ORDER BY 注文日
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS 累計売上
FROM 注文
ORDER BY 注文日;

-- 部署ごと・給与順で最大・最小給与を全行に付与します。
SELECT
    社員名,
    部署名,
    給与,
    MAX(給与) OVER (PARTITION BY 部署名) AS 部署最高給与,
    MIN(給与) OVER (PARTITION BY 部署名) AS 部署最低給与
FROM 社員;

-- 部署平均給与を各行に付与した結果。
社員名     | 部署名   | 給与    | 部署平均給与
-----------+----------+---------+-------------
鈴木 花子  | 技術部   | 620000  | 505000
佐藤 三郎  | 技術部   | 390000  | 505000
山田 太郎  | 営業部   | 480000  | 400000
田中 恵    | 営業部   | 320000  | 400000

-- 累計売上。
注文日      | 売上金額 | 累計売上
------------+----------+---------
2024-01-05  | 120000   | 120000
2024-01-12  | 85000    | 205000
2024-01-20  | 230000   | 435000

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

-- 部署ごとの平均給与を各行に付与します（主要データベース共通）。
SELECT
    社員名,
    部署名,
    給与,
    AVG(給与) OVER (PARTITION BY 部署名) AS 部署平均給与
FROM 社員
ORDER BY 部署名, 給与 DESC;

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

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

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