基于SQL partition with running totals计算月度累计消费赠礼品卡数量

核心计算规则 #

• 按用户独立计算，不同用户消费数据互不交叉
• 当月可计算总金额 = 上月结转余额 + 当月消费金额
• 当月可获得礼品卡数量 = 当月可计算总金额向下整除2500
• 当月结转至下月的余额 = 当月可计算总金额对2500取余

实现思路 #

因为结转余额是逐月累加且要扣除已兑换礼品卡对应的金额，属于有状态的迭代计算，我们采用递归CTE+窗口排序的方案实现，兼容支持窗口函数的主流SQL版本（MySQL 8.0+、PostgreSQL、SQL Server等）。
假设你的业务表名为customer_purchase，包含字段FiscalYear、FiscalMonth、Customer、AmountPurchased。

完整SQL代码 #

WITH ranked_purchases AS (
    -- 对每个用户的消费记录按时间排序，生成行号方便递归遍历
    SELECT 
        Customer,
        FiscalYear,
        FiscalMonth,
        AmountPurchased,
        ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY FiscalYear, FiscalMonth) AS rn
    FROM customer_purchase
),
recursive_calculation AS (
    -- 递归初始节点：计算每个用户第一个月的礼品卡和结转余额
    SELECT 
        Customer,
        FiscalYear,
        FiscalMonth,
        AmountPurchased,
        rn,
        FLOOR(AmountPurchased / 2500) AS gift_card_count,
        MOD(AmountPurchased, 2500) AS carry_over_balance
    FROM ranked_purchases
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归迭代：后续月份用上月结转余额计算
    SELECT 
        rp.Customer,
        rp.FiscalYear,
        rp.FiscalMonth,
        rp.AmountPurchased,
        rp.rn,
        FLOOR((rc.carry_over_balance + rp.AmountPurchased) / 2500) AS gift_card_count,
        MOD((rc.carry_over_balance + rp.AmountPurchased), 2500) AS carry_over_balance
    FROM ranked_purchases rp
    INNER JOIN recursive_calculation rc 
        ON rp.Customer = rc.Customer 
        AND rp.rn = rc.rn + 1
)
-- 输出最终结果
SELECT 
    Customer,
    FiscalYear,
    FiscalMonth,
    AmountPurchased,
    gift_card_count AS monthly_gift_card_count,
    carry_over_balance AS next_month_carry_over
FROM recursive_calculation
ORDER BY Customer, FiscalYear, FiscalMonth;

验证示例 #

以你给出的测试数据为例：
某用户1月消费200、2月消费600、3月消费1900，计算结果如下：

完全符合预期逻辑。

适配注意事项 #

• MySQL环境下整数除法可以用DIV关键字替代FLOOR(xxx/2500)，取余用%替代MOD即可
• 如果存在退款导致当月消费为负的场景，代码逻辑同样兼容，无需额外调整
• 排序时必须先按FiscalYear再按FiscalMonth排序，避免跨财年的月份顺序错误
• 如果单个用户消费记录超过100条，需要调整数据库递归CTE的最大深度限制，避免执行报错

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