如何使用SQL窗口函数以start_date为基准实现带条件销售额求和

你当前的查询逻辑存在两个叠加的偏差，才会输出不符合预期的结果：

1. 用了随event_date动态滑动的时间窗口：你定义的RANGE BETWEEN INTERVAL 7 DAYS PRECEDING AND CURRENT ROW，窗口边界会跟着每一行的event_date移动，只要某条记录的event_date落在当前行event_date往前7天的范围内，就会被纳入求和范围，完全没有考虑「必须在start_date往前7天的静态边界内」的要求。
   比如id=1在2020-09-05的那行，按滑动窗口规则，窗口覆盖范围是2020-08-29到2020-09-05，本该排除的2020-08-30（27.9）、2020-09-01（15）两条记录，就因为在这个滑动范围内，被加进了累计值，才算出67.9的错误结果。
2. 边界判断的位置不对：你把CASE判断放在了窗口函数外层，只校验了「当前行是否在统计范围内」，但完全没有对窗口内部的其他记录做边界过滤——哪怕其他记录离start_date已经超过7天，只要在滑动窗口的覆盖范围内，就会被计入总和。

需求对应的累计逻辑是静态边界过滤 + 逐行累计，不需要用随event_date滑动的时间窗口：

1. 先划定固定的统计边界：对每个id对应的start_date，只有event_date >= start_date - 7天的销售记录才是有效统计值，早于这个边界的记录，累计时统一按0计算
2. 按id、start_date做分区，在分区内按event_date从早到晚排序，对刚才计算的有效销售额做从头至当前行的逐行累计就行

SELECT
  id,
  start_date,
  event_date,
  sales,
  SUM(
    -- 先对窗口内每一行做静态边界判断，早于边界的记录销售额按0计
    CASE
      WHEN event_date >= DATE_SUB(start_date, INTERVAL 7 DAY) THEN sales
      ELSE 0
    END
  ) OVER (
    -- 按id和对应的start_date分区，避免跨start_date统计
    PARTITION BY id, start_date
    ORDER BY event_date
    -- 从分区第一行开始，逐行累计到当前行即可
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS sales_7_days
FROM sample_df
ORDER BY id, start_date, event_date

对照示例数据逐行计算，输出结果完全符合预期：

• id=1（start_date=2020-09-09，静态边界为2020-09-02）：
  • 2020-08-30的event_date早于边界，有效销售额为0，累计值0
  • 2020-09-01的event_date早于边界，有效销售额为0，累计值0
  • 2020-09-05的event_date在边界内，有效销售额25，累计值25
  • 2020-09-06的event_date在边界内，有效销售额20.75，累计值40.75
• id=2（start_date=2020-09-09，静态边界为2020-09-02）：
  • 2020-01-30的event_date早于边界，有效销售额为0，累计值0
  • 2020-09-03的event_date在边界内，有效销售额12，累计值12

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