SQL 如何计算累计值和移动平均？窗口聚合怎么使用？

简化版

累计值和移动平均通常用窗口聚合函数实现。累计求和可以写 SUM(amount) OVER (PARTITION BY user_id ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)；移动平均可以写最近 N 行或最近 N 天窗口。关键是理解 PARTITION BY 分组、ORDER BY 排序和窗口 frame 范围。

详细版

普通 GROUP BY 会把多行合并成一行，而窗口聚合会在保留明细行的同时计算统计值。累计销售额、余额变化、最近 7 天平均值、排名前后的上下文指标，都适合用窗口函数。

面试中最容易混淆的是 ROWS 和 RANGE。ROWS 按物理行数定义窗口，RANGE 按排序值范围定义窗口；当排序字段有重复值时，二者结果可能不同。为了结果稳定，实际项目中常用明确的 ROWS frame，并补充唯一排序字段。

完整版教学

一、为什么 GROUP BY 不够

GROUP BY 会把一个用户多天数据聚成一行。

但累计值要求每天仍然保留一行。

每一行还要知道从开始到当前的累计结果。

这正是窗口函数的适用场景。

窗口函数能在明细行旁边追加统计列。

二、累计求和示例

SELECT
  user_id,
  dt,
  amount,
  SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY dt
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_amount
FROM daily_sales;

PARTITION BY user_id 表示每个用户单独累计。

ORDER BY dt 定义累计顺序。

frame 定义从分区第一行到当前行。

三、移动平均示例

SELECT
  dt,
  amount,
  AVG(amount) OVER (
    ORDER BY dt
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7_rows
FROM daily_sales;

这里表示最近 7 行平均值。

如果要最近 7 天，需根据数据库支持使用日期范围或先补齐日历表。

四、ROWS 和 RANGE

frame 类型	含义	风险
`ROWS`	按行数计算窗口	排序相同仍按行处理
`RANGE`	按排序值范围计算窗口	相同排序值可能一起进入窗口
默认 frame	数据库有默认规则	容易产生非预期
显式 frame	明确窗口边界	推荐用于面试和项目

五、排序稳定性

如果同一天有多条记录，只按 dt 排序不稳定。

可以加上订单 id 或创建时间。

例如 ORDER BY dt, order_id。

累计类指标必须保证顺序可重复，否则不同执行可能结果不同。

窗口函数不是只写 OVER，排序字段和 frame 才决定结果语义。

六、空日期和补齐问题

移动 7 天平均不等于最近 7 行平均。

如果某些日期没有数据，最近 7 行可能跨越很长时间。

日报表通常要先和日期维表 LEFT JOIN，补齐缺失日期。

然后再对连续日期做窗口计算。

七、误区和追问

误区：窗口聚合会减少行数。 窗口函数保留明细行，GROUP BY 才会合并行。
误区：最近 7 行就是最近 7 天。 缺失日期或一天多行时，两者不同。
误区：不写 frame 也一定是累计到当前行。 默认规则因数据库和排序情况不同而容易误解。
追问：累计余额怎么计算？ 按账户分区、按流水时间排序，对变动金额做累计和。
追问：移动窗口很慢怎么办？ 关注分区大小、排序索引、预聚合和物化结果。
追问：排序字段重复怎么办？ 增加唯一排序字段，保证窗口顺序稳定。

八、面试表达方式

先说窗口函数保留明细行。

再解释 PARTITION BY、ORDER BY 和 frame。

最后补充 ROWS/RANGE、日期补齐和排序稳定性。

简化版

详细版