SQL使用ROWS BETWEEN子句分组计算滚动均值的方案正确性验证

你的实现逻辑不对，有两个核心问题，跑出来的结果不符合需求：

第一个问题：窗口排序规则完全错了 #

你给窗口函数写的排序规则是ORDER BY var_1 DESC，相当于把同一个id下的行按var_1的值倒序排，所有值为1的行堆在前面，值为0的堆在后面，直接打乱了观测的原始先后顺序。你要算的是「最近N条观测」的均值，这种排法算出来的滚动值根本和观测顺序无关，完全是错的。
要注意SQL表本身是无序的，要定义「最近」必须有明确的先后标识字段，比如你示例里最左侧1到20的行序号、观测入库的时间戳这类字段，窗口排序必须用这类字段，才能保证取到的是按时间/录入顺序的最近N条。

第二个问题：全组均值的实现太冗余 #

你要的var_1_lag_all是同id下所有var_1的平均值，根本不需要单独建中间表分组聚合再左连，窗口函数直接就支持：只要分区后不写自定义行范围，默认统计范围就是同分区的所有行，单条SQL就能算完，省掉建中间表、多表关联的额外开销。 #

修正后可直接运行的代码 #

假设你表里标识观测先后顺序的字段叫obs_id（就是你示例最左列1-20的序号，替换成你表里实际的自增ID/时间戳字段即可），不需要建多个中间表，单条SQL就能出最终结果：

CREATE TABLE FINAL AS
SELECT *,
    AVG(var_1) OVER (PARTITION BY id ORDER BY obs_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS var_1_lag_2,
    AVG(var_1) OVER (PARTITION BY id ORDER BY obs_id ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS var_1_lag_3,
    AVG(var_1) OVER (PARTITION BY id ORDER BY obs_id ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS var_1_lag_4,
    AVG(var_1) OVER (PARTITION BY id) AS var_1_lag_all
FROM my_data;

补充说明：如果你表里确实没有能标识观测先后顺序的字段，数据库本身无法判定哪条属于「最近」的记录，滚动均值的计算结果是随机不稳定的，没有实际业务意义。
如果硬要说你原来的三步写法哪里能得到正确值：假设你把窗口的ORDER BY字段换成正确的顺序字段，那最后左连得到的var_1_lag_all数值是对的，但属于冗余实现，效率比直接用窗口函数低。

内容的提问来源于stack exchange，提问作者stats_noob