如何将R dplyr实现3行滚动平均的代码转换为Netezza SQL

• 待处理数据集为my_data，包含id、var_1、var_2三个字段，样本结构参考如下R生成示例：

my_data = data.frame(id = c(1,1,1,1,2,2,2,3,4,4,5,5,5,5,5), 
                     var_1 = sample(c(0,1), 15, replace = TRUE), 
                     var_2 = sample(c(0,1), 15, replace = TRUE))

head(my_data)
# 输出
  id var_1 var_2
1  1     0     0
2  1     0     0
3  1     0     1
4  1     1     0
5  2     1     1
6  2     1     1

计算规则 #

原有R语言dplyr实现的计算逻辑如下：

• v_1字段：对var_1做滚动平均，取当前行、前1行、前2行（最多3行）的值计算均值；若前序行数不足（如分组内第1、2行），按实际存在的行数计算均值
• v_2字段：对var_2执行和v_1完全一致的滚动平均计算
  具体计算示例：
• 分组内第1行：v_1 = 第1行var_1值
• 分组内第2行：v_1 = (第1行var_1 + 第2行var_1)/2
• 分组内第3行：v_1 = (第1行var_1 + 第2行var_1 + 第3行var_1)/3
• 分组内第4行及以后：v_1 = 最近3行var_1值之和/3
  原有R实现代码及运行结果：

library(dplyr)

final = my_data %>% 
    mutate(
        row = seq_along(id),
        v_1 = (var_1 + lag(var_1, default = 0) + lag(var_1, 2, default = 0))/pmin(row, 3),
        v_2 = (var_2 + lag(var_2, default = 0) + lag(var_2, 2, default = 0))/pmin(row, 3)
    ) 

head(final)
# 输出
  id var_1 var_2 row       v_1       v_2
1  1     0     0   1 0.0000000 0.0000000
2  1     0     0   2 0.0000000 0.0000000
3  1     0     1   3 0.0000000 0.3333333
4  1     1     0   4 0.3333333 0.3333333
5  2     1     1   5 0.6666667 0.6666667
6  2     1     1   6 1.0000000 0.6666667

尝试的两种Netezza SQL写法均存在逻辑错误：

• 第一种写法错误：排序字段用了var_1/var_2本身，是按指标值排序而非数据原有行序排序，会打乱行的先后顺序，计算结果完全不符合预期；虽然窗口范围rows between 2 preceding and current row是对的，但排序基准错误
• 第二种写法错误：除排序字段错误外，rows unbounded preceding是取分组内从第一行到当前行的所有值计算累计平均，不是最近3行的滚动窗口，逻辑完全不匹配

Netezza原生支持窗口函数的行范围定义，实现时先给每个id分组内的行生成连续序号作为排序基准，再定义3行滚动窗口计算均值即可。

注意：关系型数据库表没有天然行序，如果表中有明确的排序字段（如记录生成时间、自增主键），请将下方代码中生成行号的ORDER BY (SELECT NULL)替换为对应字段，保证行序和R中计算时的顺序完全一致。

WITH ordered_data AS (
    SELECT 
        id,
        var_1,
        var_2,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY (SELECT NULL)) AS rn
    FROM my_data
)
SELECT 
    id,
    var_1,
    var_2,
    AVG(var_1) OVER(
        PARTITION BY id 
        ORDER BY rn 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS v_1,
    AVG(var_2) OVER(
        PARTITION BY id 
        ORDER BY rn 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS v_2
FROM ordered_data
LIMIT 100;

该写法逻辑和R实现完全对齐：窗口固定取当前行及之前最多2行，行数不足3行时AVG函数会自动按实际存在的行数计算均值，不需要额外做除法修正。

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