SUM / AVG（ウィンドウ）

累計（先頭から現在行まで）です。

SUM(列名) OVER (
    [PARTITION BY グループ列]
    ORDER BY 並び順列
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

直近N行の移動平均です。

AVG(列名) OVER (
    ORDER BY 並び順列
    ROWS BETWEEN N PRECEDING AND CURRENT ROW
)

grand_total（フレーム句なし・PARTITION BY なし）です。

SUM(列名) OVER ()

フレーム句	概要
UNBOUNDED PRECEDING	ウィンドウ（またはパーティション）の先頭を意味します。
CURRENT ROW	現在の行を意味します。
UNBOUNDED FOLLOWING	ウィンドウ（またはパーティション）の末尾を意味します。
N PRECEDING	現在行のN行前を意味します。
N FOLLOWING	現在行のN行後を意味します。

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

order_date順のrunning_totalとgrand_totalに対する構成比を計算します。

sample_sum_avg_window.sql

SELECT
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    SUM(amount) OVER () AS grand_total,
    ROUND(
        amount * 100.0 / SUM(amount) OVER (), 1
    ) AS pct_of_total
FROM daily_sales
ORDER BY order_date;

+------------+----------+----------+----------+----------+
| order_date     | amount | running_total | grand_total | pct_of_total |
+------------+----------+----------+----------+----------+
| 2024-01-01 |   120000 |   120000 |   533000 |     22.5 |
| 2024-01-02 |    85000 |   205000 |   533000 |     15.9 |
| 2024-01-03 |   230000 |   435000 |   533000 |     43.2 |
| 2024-01-04 |    98000 |   533000 |   533000 |     18.4 |
+------------+----------+----------+----------+----------+
4 rows in set

直近3日間の移動平均を計算します。

sample_sum_avg_window.sql

SELECT
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3d
FROM daily_sales
ORDER BY order_date;

+------------+----------+-------------+
| order_date     | amount | moving_avg_3d |
+------------+----------+-------------+
| 2024-01-01 |   120000 |  120000.000 |
| 2024-01-02 |    85000 |  102500.000 |
| 2024-01-03 |   230000 |  145000.000 |
| 2024-01-04 |    98000 |  137666.667 |
+------------+----------+-------------+
4 rows in set

直近2日間の移動合計を計算します。

sample_sum_avg_window.sql

SELECT
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS moving_sum_2d
FROM daily_sales
ORDER BY order_date;

+------------+----------+-------------+
| order_date     | amount | moving_sum_2d |
+------------+----------+-------------+
| 2024-01-01 |   120000 |      120000 |
| 2024-01-02 |    85000 |      205000 |
| 2024-01-03 |   230000 |      315000 |
| 2024-01-04 |    98000 |      328000 |
+------------+----------+-------------+
4 rows in set

ウィンドウ関数としての『SUM()』『AVG()』とフレーム句は MySQL（8.0以降）・PostgreSQL・Oracle・SQLite（3.25以降）で共通して使用できます。

SELECT
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM daily_sales
ORDER BY order_date;

ウィンドウ関数として使うSUM・AVGは、行数を減らさずに集計値を各行に付与できます。フレーム句でどの行を処理対象にするかを指定することが重要です。

ORDER BY を指定してフレーム句を省略すると、デフォルトのフレームは RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW になります。同じORDER BY値を持つ行はすべて「現在行」とみなされ、意図しない結果になることがあります。明示的に ROWS BETWEEN を指定する習慣をつけましょう。迷った場合は『ROWS』を使うのが安全です。

ウィンドウ関数の基本構文は『OVER / PARTITION BY』を、前後の行参照は『LAG / LEAD』を参照してください。