如何高效实现按product_id分组的7天滚动营业额求和计算

1 方案2的修改方法 #

方案2错误原因 #

• 仅生成了全局日历，没有为每个product_id生成对应的连续日期序列，无营业额的日期不会出现在对应产品的窗口分区中，使用ROWS BETWEEN 6 PRECEDING时会把时间间隔超过7天的历史行计入统计
• 窗口函数使用物理行偏移的ROWS模式，而非按日期逻辑匹配的RANGE模式，无法正确匹配近7天的时间范围

修改后代码 #

WITH 
-- 取业务数据覆盖的全量日期范围，可按需调整起止时间
all_days AS (
  SELECT DATE_TRUNC('day', d)::DATE AS dt
  FROM GENERATE_SERIES(
    (SELECT MIN(dt) FROM turnover_per_day),
    (SELECT MAX(dt) FROM turnover_per_day),
    '1 day'::INTERVAL
  ) d
),
-- 取去重的产品维度表
all_products AS (
  SELECT DISTINCT product_id, product_name FROM turnover_per_day
),
-- 生成每个产品的全量日期序列
product_full_days AS (
  SELECT a.dt, p.product_id, p.product_name
  FROM all_days a
  CROSS JOIN all_products p
),
-- 补全每日营业额，无营业额的日期填充0
filled_turnover AS (
  SELECT 
    pfd.dt,
    pfd.product_id,
    pfd.product_name,
    COALESCE(t.turnover, 0) AS turnover
  FROM product_full_days pfd
  LEFT JOIN turnover_per_day t
    ON pfd.product_id = t.product_id AND pfd.dt = t.dt
)
SELECT 
  product_id,
  product_name,
  turnover,
  dt,
  SUM(turnover) OVER(
    PARTITION BY product_id 
    ORDER BY dt 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS turnover_7day
FROM filled_turnover
-- 仅保留原业务表存在的记录，和原方案输出格式一致
WHERE EXISTS (
  SELECT 1 FROM turnover_per_day t 
  WHERE t.product_id = filled_turnover.product_id AND t.dt = filled_turnover.dt
)
ORDER BY product_id, dt;

运行后输出结果和方案1完全一致。

2 更低开销的实现方式 #

不需要生成日历表，直接使用Snowflake原生支持的日期范围窗口函数即可，仅需一次全表扫描，计算复杂度为O(n)，完全适配百万级产品数年数据的计算场景，代码如下：

SELECT
  product_id,
  product_name,
  turnover,
  dt,
  SUM(turnover) OVER(
    PARTITION BY product_id
    ORDER BY dt
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS turnover_7day
FROM turnover_per_day
ORDER BY product_id, dt;

方案优势 #

• 避免了产品与日期笛卡尔积产生的海量中间数据，内存和IO开销极低
• 无需逐行关联子查询，消除了O(n²)的计算开销
• 为Snowflake原生优化的窗口计算逻辑，执行效率远高于前两种方案

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