SQL窗口函数实战：如何精准计算指定行前后的加权平均
在数据分析中，滑动窗口内的加权平均是个高频需求，但SQL的窗口函数语法细节繁多，一不留神就会踩坑。今天，我们就来拆解几个关键技巧，帮你把“前后几行”的加权平均算得又快又准。
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING 怎么写才生效
首先得明确一个前提：ROWS BETWEEN ... 这个范围定义，必须和 ORDER BY 绑定使用。道理很简单，如果窗口里的行序是乱的，“前两行”和“后一行”就失去了意义。SQL标准对此有严格要求，像PostgreSQL会直接报错，而MySQL 8.0+虽然可能执行，但会给出警告，结果并不可靠。
具体操作时，有几点经验值得分享：
- 排序列要尽量唯一。最好用时间戳、自增ID这类高唯一性的字段。否则，如果排序字段值大量重复，数据库在不同执行时可能给出不同的行顺序，导致窗口划界飘忽不定。
- 为逻辑顺序加一道保险。如果业务上需要按“用户操作序列”这类逻辑排序，但字段（如操作时间）可能存在重复，稳妥的做法是在
ORDER BY后面追加一个唯一列。比如：ORDER BY event_time, event_id，用event_id来兜底。 - 对了，如果你的数据库还是MySQL 5.7，那这套语法直接就用不了，它会报
ERROR 1064。动手前先用SELECT VERSION()确认下版本。
加权平均怎么套进滑动窗口里
这里有个常见的误解：直接用A VG()。但A VG()是等权平均，要算加权平均，得手动把公式SUM(weight * value) / SUM(weight)整个塞进窗口函数里。
新手常犯两个错误：一是把SUM()写在窗口函数外面，导致聚合范围不对；二是不小心让权重列也参与了OVER()里的排序，可能引发意料之外的类型转换。
正确的打开方式是这样的：
- 确保权重和数值两列都是数值类型，并且提前处理好
NULL值——NULL会让整个计算中断。可以用COALESCE(weight, 0)在计算时替换，或者在WHERE子句里提前过滤掉。 - 来看一个完整的例子，假设我们要计算每家门店前后共4行（前2、自身、后1）的销售额加权评级：
SELECT
store_id,
rating,
sales,
SUM(rating * sales) OVER (
ORDER BY store_id
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING
) / NULLIF(SUM(sales) OVER (
ORDER BY store_id
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING
), 0) AS weighted_a vg_rating
FROM stores;
注意这里的NULLIF(..., 0)，它比写CASE WHEN ... THEN ... END更简洁，专门用来防止分母为零的情况。
ROWS 和 RANGE 的关键区别在哪
这两个关键字决定了窗口的划界方式，区别很大：ROWS按物理行数计数，而RANGE按排序值的逻辑范围匹配。对于我们要算的加权滑动平均，ROWS通常是唯一可靠的选择。
怎么选？看场景：
- 如果你想算“过去7天的销量加权平均”，因为日期可能不连续，就必须用
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW。 - 但如果你想算“最近3笔订单的金额加权平均”，就必须用
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW。如果用RANGE，同一天发生的多笔订单会被全部纳入窗口，就破坏了“3笔”这个固定数量的语义。 - 另外，数据库支持度也不同。PostgreSQL对
RANGE支持很全，包括日期间隔；而MySQL 8.0的RANGE只支持数值范围（比如RANGE BETWEEN 10 PRECEDING），不支持直接的日期偏移。
性能和 NULL 值怎么悄悄拖慢查询
滑动窗口计算本身就不太友好索引，ROWS范围越大，每一行需要扫描的邻近行就越多。再加上加权平均要算两次SUM()，计算量直接翻倍。更隐蔽的杀手是NULL值——多数数据库引擎不会自动跳过NULL行，而是会让包含NULL的整个窗口聚合结果变成NULL，除非你显式处理。
优化上可以这么做：
- 前置过滤。在
WHERE子句里提前排除无效数据，远比在窗口函数内部用COALESCE处理要高效。 - 考虑替代方案。如果窗口范围固定且很小（比如就前后一两行），有时用自连接（Self-Join）来模拟，反而比窗口函数更快，尤其是在SQLite或旧版MySQL这类对窗口函数优化不足的数据库中。
- 善用执行计划。在PostgreSQL里，跑一下
EXPLAIN ANALYZE，关注WindowAgg节点下的Rows Removed by Filter，这是探查NULL值影响最直接的线索。
说到底，加权滑动平均真正的难点，往往不在语法，而在于业务逻辑的厘清。比如，如果你的权重是“累计销量”，那这个权重本身会随着窗口移动而变化吗？如果会，就不能简单地把销量列放在窗口内SUM，而需要先算出全局的累计值，再通过JOIN关联进来。这个细节很少被提及，但一旦忽略，查询结果可能完全错误，排查起来更是耗时费力。
