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.00sum 的窗口帧从分区第一行延伸到当前行,因此 A 店和 B 店分别累计。lag 与 lead 的第三个参数设置为 0,所以分区首行没有上一行、末行没有下一行时返回 0;实际业务也可以省略默认值,让结果保留 NULL。
移动窗口的边界含义
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 表示最多取三条记录,而不是固定三个自然日。若日期中间缺少数据,它仍按现有行数计算。要实现严格的时间区间统计,需要改用适合日期范围的窗口设计或先补齐日期序列。
排序字段应能唯一确定行顺序。示例使用 (store, sale_date) 主键保证同一门店每天只有一条记录;若一天可能有多笔数据,应在窗口排序中追加时间或唯一 ID。
累计查询通常会扫描分区内较多行。生产表可建立与筛选、分区和排序条件匹配的索引,例如按门店和日期组织数据,但仍应通过 EXPLAIN ANALYZE 验证实际执行计划。
按自己的节奏,确认真正理解。