Redshift SQL按时间戳取用户最近3个order_id的滚动求和实现咨询

Redshift 订单维度滚动求和解决方案 #

你原有窗口函数不满足需求的核心原因是ROWS 3 PRECEDING是行级滑动规则，只会计算当前行及前2行的数值，无法覆盖同个订单ID对应的所有行。以下是两种可行实现方案：

方案1：CTE + 自JOIN（兼容性最强，逻辑易校验） #

WITH order_rank_mapping AS (
    -- 为同用户下的所有订单按时间升序生成唯一序号，同一order_id序号完全一致
    SELECT 
        *,
        DENSE_RANK() OVER(PARTITION BY customer_id ORDER BY timestamp ASC, order_id ASC) AS order_rn
    FROM orders
)
SELECT 
    t1.timestamp,
    t1.customer_id,
    t1.order_id,
    t1.category,
    t1.brand,
    t1.quantity,
    SUM(t2.quantity) AS rolling_sum
FROM order_rank_mapping t1
-- 关联同用户下，属于当前订单及前2个订单的所有行
LEFT JOIN order_rank_mapping t2
    ON t1.customer_id = t2.customer_id
    AND t2.order_rn BETWEEN t1.order_rn - 2 AND t1.order_rn
GROUP BY 
    t1.timestamp,
    t1.customer_id,
    t1.order_id,
    t1.category,
    t1.brand,
    t1.quantity,
    t1.order_rn
ORDER BY t1.customer_id, t1.order_rn;

方案2：窗口函数RANGE范围扫描（Redshift原生支持，性能更优） #

如果你的Redshift集群版本支持窗口函数的数值范围扫描，可以用更简洁的写法，避免自JOIN的性能消耗：

WITH order_rank_mapping AS (
    SELECT 
        *,
        DENSE_RANK() OVER(PARTITION BY customer_id ORDER BY timestamp ASC, order_id ASC) AS order_rn
    FROM orders
)
SELECT 
    timestamp,
    customer_id,
    order_id,
    category,
    brand,
    quantity,
    SUM(quantity) OVER(
        PARTITION BY customer_id 
        ORDER BY order_rn ASC 
        RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM order_rank_mapping;

关键逻辑说明 #

• 使用DENSE_RANK而非ROW_NUMBER生成序号，保证同一个order_id对应的所有行拿到的序号完全相同，避免同一订单的不同行被拆分到不同的统计窗口
• 排序规则追加order_id ASC是为了处理同一用户同一时间生成多笔订单的极端场景，保证序号排序稳定
• 两种方案都支持用户任意数量订单的场景，即使单用户订单量超过100也能正常计算

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