Redshift中基于交易时间分配折扣（单客上限$20）的SQL查询需求

没问题，我来帮你搞定这个Redshift里的客户折扣计算需求！下面是完整的查询语句，还有详细的逻辑说明：

需求回顾 #

• 基于cust_tran_info表，包含字段：Time_of_transaction、Customer_id、Order_id、Order_Value
• 每笔订单的Eligible_discount为Order_Value的10%
• 单个客户的累计折扣总额上限为20美元，需按交易时间顺序分配折扣，最终计算出每笔订单的Applicable_Discount

完整Redshift查询语句 #

WITH ranked_transactions AS (
    SELECT 
        Time_of_transaction,
        Customer_id,
        Order_id,
        Order_Value,
        0.1 * Order_Value AS Eligible_discount,
        -- 计算到当前订单为止的累计可享折扣（按客户分组、交易时间升序）
        SUM(0.1 * Order_Value) OVER (
            PARTITION BY Customer_id 
            ORDER BY Time_of_transaction ASC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_eligible_discount,
        -- 计算到上一笔订单为止的累计可享折扣，用于判断剩余额度
        SUM(0.1 * Order_Value) OVER (
            PARTITION BY Customer_id 
            ORDER BY Time_of_transaction ASC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS cumulative_eligible_discount_prev
    FROM cust_tran_info
)
SELECT 
    Time_of_transaction,
    Customer_id,
    Order_id,
    Order_Value,
    Eligible_discount,
    CASE
        -- 累计折扣未达上限，直接用当前可享折扣
        WHEN cumulative_eligible_discount <= 20 THEN Eligible_discount
        -- 上一笔累计已超上限，当前订单无折扣
        WHEN cumulative_eligible_discount_prev >= 20 THEN 0
        -- 用剩余额度作为当前订单的适用折扣
        ELSE 20 - COALESCE(cumulative_eligible_discount_prev, 0)
    END AS Applicable_Discount
FROM ranked_transactions
ORDER BY Customer_id, Time_of_transaction;

关键逻辑解释 #

1. CTE窗口计算：

   • 用SUM() OVER()窗口函数按客户分组、交易时间排序，分别计算到当前订单和上一笔订单的累计可享折扣，这样能清晰跟踪每个客户的折扣使用进度
   • ROWS BETWEEN子句明确了窗口的范围，确保累计计算的准确性

2. CASE条件判断：

   • 覆盖了三种场景：累计未达上限、累计已超上限（当前订单无折扣）、刚好在当前订单突破上限（取剩余额度）
   • COALESCE处理了客户第一笔订单的特殊情况（此时cumulative_eligible_discount_prev为NULL，默认取0）

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