关于rows between 7 preceding and 1 preceding的功能及结果验证问询

一、窗口子句rows between 7 preceding and 1 preceding的作用 #

这个子句用于定义窗口函数的计算行范围，结合你给出的SQL语句sum(VALUE) over(partition by (COUNTRY_CODE, ID) order by Date rows between 7 preceding AND 1 preceding)，具体逻辑如下：

• partition by (COUNTRY_CODE, ID)：将数据按国家编码和标识ID分组，每组内独立计算聚合值
• order by Date：每组内的行按日期升序排列
• rows between 7 preceding and 1 preceding：指定聚合计算的行范围是当前行之前的第7行到当前行的前一行（不包含当前行）：
  • 如果当前行是组内第1行，无任何前序行，聚合结果为null
  • 如果当前行前序行不足7行，就取所有存在的前序行（比如组内第3行，前序有2行，则取这2行参与计算）
  • 最多取当前行之前的7行数据进行求和

二、目标结果验证 #

你的目标表结果无法通过当前SQL语句得到，具体分析如下：

源表排序后数据（按Date升序） #

当前SQL语句的计算结果 #

按rows between 7 preceding and 1 preceding计算每行的7D_Spend：

1. 2018-07-30：无前行 → null（和目标表一致）
2. 2018-11-05：前1行是2018-07-30 → sum=6037.2（目标表为null，不一致）
3. 2018-11-12：前2行是2018-07-30、2018-11-05 → sum=6037.2+971.02=7008.22（目标表为971.02，不一致）
4. 后续行的前序行更多，求和值远大于目标表的null，均不一致

目标表结果对应的正确逻辑 #

目标表的7D_Spend是按**日期范围（当前日期的前7天到前1天）**计算的，而非行数量。要得到该结果，需要使用基于日期范围的窗口子句（以PostgreSQL为例）：

sum(VALUE) over(
    partition by (COUNTRY_CODE, ID) 
    order by Date 
    range between interval '7 days' preceding and interval '1 day' preceding
) as 7D_Spend

该语句会筛选出当前日期往前推7天到前1天内的行进行求和，和你的目标表结果完全匹配：

• 2018-11-12的前7天是2018-11-05，仅2018-11-05的行在范围内，sum=971.02
• 其他行的日期区间内无有效数据，结果为null

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