PostgreSQL / 系统课时

使用分区累计与移动计算

使用分区累计与移动计算

本课解决“按门店查看逐日累计、上一行、下一行和移动平均”的问题。完成后,你可以在不合并明细行的前提下,对每个分区执行连续统计。

学习目标

  • 使用窗口分区分别计算累计值
  • 使用 lag 和 lead 读取前后行
  • 使用窗口帧实现最近三行移动平均

窗口计算的三个组成部分

PARTITION BY 决定数据按什么维度分别计算,ORDER BY 决定行的先后顺序,窗口帧决定当前行能够看到哪些行。

累计值通常使用“分区起点到当前行”;最近三行平均值使用“当前行及前两行”。显式写出 ROWS 窗口帧,可以避免重复排序值触发默认 RANGE 语义而扩大计算范围。

完整示例:门店逐日动态统计

下面的 SQL 为两个门店写入每日销售额,并同时计算累计销售额、前一日金额、后一日金额和最近三行平均值。

sql
DROP TABLE IF EXISTS daily_sales;

CREATE TEMP TABLE daily_sales (
    store text NOT NULL,
    sale_date date NOT NULL,
    amount integer NOT NULL,
    PRIMARY KEY (store, sale_date)
);

INSERT INTO daily_sales (store, sale_date, amount) VALUES
('A店', DATE '2026-07-01', 100),
('A店', DATE '2026-07-02', 150),
('A店', DATE '2026-07-03', 80),
('A店', DATE '2026-07-04', 120),
('B店', DATE '2026-07-01', 200),
('B店', DATE '2026-07-02', 100),
('B店', DATE '2026-07-03', 300);

SELECT
    store,
    sale_date,
    amount,
    sum(amount) OVER (
        PARTITION BY store
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_amount,
    lag(amount, 1, 0) OVER (
        PARTITION BY store
        ORDER BY sale_date
    ) AS prev_amount,
    lead(amount, 1, 0) OVER (
        PARTITION BY store
        ORDER BY sale_date
    ) AS next_amount,
    round(
        avg(amount) OVER (
            PARTITION BY store
            ORDER BY sale_date
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS moving_avg_3
FROM daily_sales
ORDER BY store, sale_date;

预期结果

text
store | sale_date  | amount | cumulative_amount | prev_amount | next_amount | moving_avg_3
------+------------+--------+-------------------+-------------+-------------+-------------
A店   | 2026-07-01 |    100 |               100 |           0 |         150 |      100.00
A店   | 2026-07-02 |    150 |               250 |         100 |          80 |      125.00
A店   | 2026-07-03 |     80 |               330 |         150 |         120 |      110.00
A店   | 2026-07-04 |    120 |               450 |          80 |           0 |      116.67
B店   | 2026-07-01 |    200 |               200 |           0 |         100 |      200.00
B店   | 2026-07-02 |    100 |               300 |         200 |         300 |      150.00
B店   | 2026-07-03 |    300 |               600 |         100 |           0 |      200.00

sum 的窗口帧从分区第一行延伸到当前行,因此 A 店和 B 店分别累计。lag 与 lead 的第三个参数设置为 0,所以分区首行没有上一行、末行没有下一行时返回 0;实际业务也可以省略默认值,让结果保留 NULL。

移动窗口的边界含义

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 表示最多取三条记录,而不是固定三个自然日。若日期中间缺少数据,它仍按现有行数计算。要实现严格的时间区间统计,需要改用适合日期范围的窗口设计或先补齐日期序列。

排序字段应能唯一确定行顺序。示例使用 (store, sale_date) 主键保证同一门店每天只有一条记录;若一天可能有多笔数据,应在窗口排序中追加时间或唯一 ID。

累计查询通常会扫描分区内较多行。生产表可建立与筛选、分区和排序条件匹配的索引,例如按门店和日期组织数据,但仍应通过 EXPLAIN ANALYZE 验证实际执行计划。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

使用分区累计与移动计算

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通