前言
基于会员积分的获取和过期策略，本文提出一种迭代的算法，无需复杂的事务机制和冗余的表结构支持，即可高效准确的计算出用户的过期积分。
需求：
用户通过签到、活动、下单等渠道获取积分，积分获得一年后过期(可配置)，每月 1 日计算并扣除一批过期的积分。用户如果有消费，则优先使用快过期的积分。
方案设计：
业务表结构
积分交易记录：业务表，参与用户事务，记录用户每一笔积分交易，包括消费和获得，消费的记录用负数表示。交易记录根据用户求和的结果就是用户的积分余额。
我们先简单设计表结构如下：
CREATE TABLE dwd_user_score_log(
id bigint auto_increament comment "主键",
user_id bigint comment "用户 id",
score bigint comment "交易额",
expired datetime comment "过期时间",
created datetime comment "创建时间"
) comment "积分交易记录";算法构思
这个算法的难点在于消费优先使用快过期的积分，这似乎需要复杂的业务逻辑，而不适合 SQL 来处理。我们使用案例分析一下，假设你一年前获得了 100 积分，这笔积分会在本月（T月） 末过期，如果这笔积分没有被消费，则在下个月（T+1月）的 1 日凌晨扣除。
场景一：你这一年都没有消费，T月你只消费了 60，T+1 月的 1 日你会被扣除 40 分。
场景二：你在T月消费了 160，你在 T+1 月又有一笔 100 积分到期，T+1月的 1 日你不会被扣除，但是 T+2 月的 1 日你会被扣除 40 分。
场景三：你在T月消费了一笔 60 和一笔40，你在 T+1 月不需要被扣分。
从上面几个场景，我们可以有一个直观的结论：
- 需要为每笔收入订单和支出订单做关联关系，并且这个关联关系是多对多的。
- 每一笔收入记录会有三种状态，完全未消费，部分被消费，全部被消费。
- 消费的时候在所有未到期且未全部被消费的收入记录中凑这个需要被消费的数额，且需要按时间顺序去查找。
- 计算过期就是找到所有已经达到过期时间，并且没有被完全消费的收入记录。
OMG，是不是非常晕。而且如果不修改业务逻辑，只通过 SQL 来分析这些记录而得到过期积分，完全就是 impossible mission。即使用业务逻辑去处理，如何用一条 SQL 查到总和刚好大于等于被消费金额的收入记录，就已经是一个挑战。
算法改进
我们回顾上面的场景分析，无论哪种场景，在扣除过期积分之后，是不是达成了过期积分的收支平衡。换句话说，我累计有100 积分要过期，如果我累计消费没有达到 100，那就需要通过扣除操作达到支出 100。如果累计消费超过 100，那这个积分一定是被消费掉的，也就不需要再做过期扣除了。
我们用公式来表示一下这条定律：|总消费|>=|总过期|，消费用负数表示，这条定律也可以写成总消费+总过期<=0。而过期扣除的积分就是：因为总过期增加了，为了维护这条定律的成立而做的补偿消费。
基于这个定律，我们新增一个表
CREATE TABLE dws_month_user_score(
user_id bigint comment "用户 id",
expired_score bigint comment "累计过期的积分，正数",
cosume_score bigint comment "累计消费的积分，负数",
need_expired_score bigint comment "当期需要被扣除的积分，正数",
next_expired_score bigint comment "下一期即将被扣除的积分"
bizmonth string comment "业务月份（T月）"
);表中的need_expired_score=MAX(expired_score+cosume_score, 0),即如果满足总消费+总过期<=0则扣除 0 积分，否则扣到满足定律的数额。
next_expired_score的算法与need_expired_score相同，只不过需要在过期里面再加上 T月结束后新增的过期数。
这个表是可以递推计算的，累计过期与累计消费不用每次找到所有的历史记录来求和，我们只要把上一期（T-1月）的记录加当期的增量即可求得当期的累计，因此这个算法的计算性能是更好的。
下面附上完整 SQL 以供参考。${bizmonth}是业务月，通常是执行任务时候的前一个月。
INSERT OVERWRITE TABLE dws_month_user_score PARTITION(dt = '${bizmonth}')
SELECT user_id,
expired_score_total AS expired_score,
consume_score_total AS cosume_score,
ARRAY_MAX(ARRAY(expired_score_total + consume_score_total, 0)) AS need_expired_score,
ARRAY_MAX(ARRAY(ARRAY_MIN(ARRAY(expired_score_total + consume_score_total, 0)) + next_expire, 0)) AS next_expired_score,
'${bizmonth}' AS `bizmonth`,
UNIX_TIMESTAMP(DATEADD(TO_DATE('${bizmonth}', 'yyyymm'), 2, 'mm')) AS `next_expired_time` -- 下个月零点时间戳
FROM (
SELECT A1.user_id,
NVL(A2.`expired_score`, 0) + NVL(A4.expire, 0) AS `expired_score_total`,
NVL(A2.`cosume_score`, 0) + NVL(A3.consume, 0) AS `consume_score_total`,
A5.next_expire
FROM (
SELECT user_id
FROM ods_user
WHERE dt = '${bizdate}'
) A1
LEFT JOIN (
--上月数据（截止上月总共消费与总共过期，用于叠加）
SELECT *
FROM dws_month_user_score
WHERE dt = '${bizmonth_pre}' -- bizmonth-1
) A2
ON A1.user_id = A2.user_id
LEFT JOIN (
--上个月（bizmonth）消费
SELECT user_id,
SUM(IF(score <= 0 AND TO_CHAR(created_datetime, 'yyyymm') = '${bizmonth}', score, 0)) AS consume
FROM dwd_task_score_log
WHERE TO_CHAR(TO_DATE(dt, 'yyyymmdd'), 'yyyymm') = '${bizmonth}'
GROUP BY user_id
) A3
ON A1.user_id = A3.user_id
LEFT JOIN (
--本月过期多少（一年前获得的糖豆）
SELECT user_id,
SUM(IF(score > 0 AND TO_CHAR(expired_datetime, 'yyyymm') = '${bizmonth_next}', score, 0)) AS expire
FROM dwd_task_score_log
WHERE TO_CHAR(TO_DATE(dt, 'yyyymmdd'), 'yyyymm') = '${lastyear_bizmonth}' -- bizmonth-12
GROUP BY user_id
) A4
ON A1.user_id = A4.user_id
LEFT JOIN (
--下月过期多少（下月一年前获得的糖豆）
SELECT user_id,
SUM(IF(score > 0 AND TO_CHAR(expired_datetime, 'yyyymm') = '${bizmonth_next_next}', score, 0)) AS next_expire
FROM dwd_task_score_log
WHERE TO_CHAR(TO_DATE(dt, 'yyyymmdd'), 'yyyymm') = '${lastyear_bizmonth_next}' -- bizmonth-12+1
GROUP BY user_id
) A5
ON A1.user_id = A5.user_id
);