SQL 窗口函数计算累计值

功能说明

示例假定 payments 表包含 id、account_id、paid_at 与 amount_cents，并用同名临时表写入六条付款记录。daily CTE 先按账户和日期合并同日多笔付款，随后三个窗口函数分别计算：按日期累计的 running_total_cents、包含当前行在内最近三行的 moving_avg_cents，以及当天金额占该账户总额的百分比。输出按账户和日期排序。

关键代码

每个窗口都用 PARTITION BY account_id 限定账户范围，再用 ORDER BY paid_at 定义顺序。累计值显式写出 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，避免依赖默认帧。移动平均使用 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW，因此它基于最近三个有付款的日期，而不是最近三个自然日。占比的分母是不带 ORDER BY 的分区总和，会覆盖该账户全部日期。100.0 强制使用 numeric 除法。

使用注意

ROWS 与 RANGE 语义不同：有并列日期时 RANGE 会把同一排序值的行一起纳入帧。示例先聚合到日粒度，若同一账户同一天仍有多行则不会进入 daily。金额必须用整数或 numeric，不能使用浮点。窗口函数在 WHERE 与 GROUP BY 之后执行，不能在同一层 WHERE 中引用 running_total_cents，需要再包一层 CTE。数据量大时应确保 (account_id, paid_at) 上有索引。

验证方式

运行查询后，账户 10 的三天累计应为 1200、2000、3500，移动平均依次为 1200、1000、1166.67，占比为 34.29、22.86、42.86。账户 20 的两天合并后累计为 500 与 1500，占比为 33.33 与 66.67。可把 ROWS 改为 RANGE 并插入同日多笔记录，比较两种帧的差异。

代码示例

-- Schema assumptions (PostgreSQL 15+):
--   payments(id bigint PRIMARY KEY, account_id bigint NOT NULL,
--            paid_at date NOT NULL, amount_cents integer NOT NULL CHECK (amount_cents > 0))
-- The TEMP table mirrors that shape so the query runs in a scratch database.

CREATE TEMP TABLE payments (
    id           bigint PRIMARY KEY,
    account_id   bigint NOT NULL,
    paid_at      date NOT NULL,
    amount_cents integer NOT NULL CHECK (amount_cents > 0)
);

INSERT INTO payments (id, account_id, paid_at, amount_cents) VALUES
    (1, 10, DATE '2026-10-01', 1200),
    (2, 10, DATE '2026-10-02', 800),
    (3, 10, DATE '2026-10-04', 1500),
    (4, 20, DATE '2026-10-01', 500),
    (5, 20, DATE '2026-10-03', 700),
    (6, 20, DATE '2026-10-03', 300);

WITH daily AS (
    SELECT account_id, paid_at, SUM(amount_cents) AS day_cents
    FROM payments
    GROUP BY account_id, paid_at
)
SELECT
    account_id,
    paid_at,
    day_cents,
    SUM(day_cents) OVER (
        PARTITION BY account_id
        ORDER BY paid_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total_cents,
    ROUND(AVG(day_cents) OVER (
        PARTITION BY account_id
        ORDER BY paid_at
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_cents,
    ROUND(100.0 * day_cents / SUM(day_cents) OVER (PARTITION BY account_id), 2) AS pct_of_account
FROM daily
ORDER BY account_id, paid_at;