基于SQL实现忠诚度积分追踪：计算会员当前有效积分

针对你的积分交易表，要实现先进先出（FIFO）扣除未过期积分并计算当前会员有效总积分，可通过以下SQL方案解决，核心思路是拆分信用/扣除记录并利用窗口函数累计匹配扣除额度：

解决方案SQL（以MySQL为例，其他方言可调整日期函数） #

WITH credit_cte AS (
    -- 筛选未过期的积分入账记录，按会员+交易日期排序，计算累计入账积分
    SELECT 
        member_id,
        txn_date,
        points AS credit_points,
        exp_date,
        SUM(points) OVER (PARTITION BY member_id ORDER BY txn_date) AS cumulative_credit
    FROM point_txn
    WHERE status = 'credit' 
      AND STR_TO_DATE(exp_date, '%Y-%m-%d') >= CURRENT_DATE -- 仅保留未过期积分
),
debit_cte AS (
    -- 处理扣除记录，转换为正数并计算累计扣除总额
    SELECT 
        member_id,
        ABS(points) AS debit_points,
        SUM(ABS(points)) OVER (PARTITION BY member_id ORDER BY txn_date) AS cumulative_debit
    FROM point_txn
    WHERE status = 'debit'
),
-- 计算每个入账积分的剩余可用额度
remaining_credit AS (
    SELECT 
        c.member_id,
        -- 计算当前入账记录可被扣除的最大额度：累计入账 - 上一笔累计扣除（如果有）
        GREATEST(
            0,
            c.credit_points - 
            GREATEST(
                0,
                COALESCE((SELECT MAX(cumulative_debit) FROM debit_cte d WHERE d.member_id = c.member_id AND d.cumulative_debit <= c.cumulative_credit - c.credit_points), 0) -
                COALESCE((SELECT MAX(cumulative_debit) FROM debit_cte d WHERE d.member_id = c.member_id AND d.cumulative_debit <= c.cumulative_credit), 0)
            )
        ) AS remaining_points
    FROM credit_cte c
)
-- 汇总每个会员的有效剩余积分
SELECT 
    member_id,
    SUM(remaining_points) AS total_valid_points
FROM remaining_credit
GROUP BY member_id
UNION ALL
-- 处理没有有效积分的会员（如果需要）
SELECT 
    DISTINCT member_id,
    0 AS total_valid_points
FROM point_txn
WHERE member_id NOT IN (SELECT member_id FROM remaining_credit);

逻辑说明 #

1. 信用记录筛选：先过滤出当前日期未过期的入账积分，按会员和交易时间排序，计算累计入账额度，确保FIFO的顺序。
2. 扣除记录转换：把负数的扣除积分转为正数，计算累计扣除总额，同样按时间排序保证扣除顺序。
3. 匹配扣除额度：对每一笔未过期的入账积分，计算它需要承担的扣除额度——即累计扣除中落在该笔入账区间内的部分，用GREATEST确保不会出现负数剩余。
4. 汇总结果：将每笔入账的剩余积分相加，得到会员当前有效总积分，同时补充没有有效积分的会员记录。

适配调整 #

• 如果使用SQL Server，将STR_TO_DATE替换为CONVERT(DATE, exp_date)，CURRENT_DATE替换为GETDATE()。
• 若需要指定特定日期而非当前日期，可将CURRENT_DATE替换为固定日期字符串（如'2023-10-01'）并转为日期类型。

示例数据验证 #

以当前日期为2023-03-01为例，你的示例会员003的有效积分计算：

• 未过期的入账记录：2020-07-05的50分、2020-08-01的100分
• 累计扣除总额：15+5+20+25+15=80分
• 按FIFO扣除：先扣50分（全部用完），再扣30分（从100分中扣），剩余70分
• 最终结果：003的有效积分为70

内容的提问来源于stack exchange，提问作者Dr. Octopus