3天窗口滚动求和异常：同日期行重复计算及数据缺失

原始交易数据 #

原SQL查询 #

SELECT 
  user_id, 
  transaction_date, 
  SUM(amount) OVER(
    PARTITION BY user_id 
    ORDER BY transaction_date 
    RANGE BETWEEN interval '2 days' PRECEDING AND CURRENT ROW 
  ) AS rolling_earnings_3d 
FROM user_transactions;

错误输出 #

期望输出 #

问题分析与解决方案 #

问题原因 #

1. 同日期重复行：原查询直接对单条交易计算滚动和，同一用户同一天的多笔交易都会返回相同的滚动结果，导致输出重复行。
2. 用户826数据缺失：原查询未返回用户826的01/04交易行，核心原因是未先聚合每日金额，窗口统计逻辑未覆盖完整数据；另外期望输出中01/07的交易行需确保原始数据包含该记录。

解决步骤 #

1. 聚合每日金额：按user_id和transaction_date分组，合并同一天的多笔交易为一行，消除重复输出的根源。
2. 基于聚合数据计算滚动和：对每日聚合后的数据应用窗口函数，确保每个日期只返回一行结果，同时正确计算前2天到当前日期的金额总和。

修正后的SQL #

WITH daily_transactions AS (
  SELECT 
    user_id,
    transaction_date,
    SUM(amount) AS daily_amount
  FROM user_transactions
  GROUP BY user_id, transaction_date
)
SELECT 
  user_id,
  transaction_date,
  SUM(daily_amount) OVER(
    PARTITION BY user_id
    ORDER BY transaction_date
    RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW
  ) AS rolling_earnings_3d
FROM daily_transactions
ORDER BY user_id, transaction_date;

说明 #

• daily_transactions CTE先完成每日金额聚合，避免同日期多行重复。
• 窗口函数基于聚合后的数据计算，确保每个日期仅返回一个滚动和结果。
• 用户826的01/04交易行，聚合后滚动和会包含01/02的金额，若期望输出为530.24需检查原始数据是否存在其他补充交易；01/07的100.00交易需确保该记录存在于user_transactions表中。

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