如何将物品结余滚动分配至后续无物品的空白日期

业务规则 #

• 单日最多仅可使用1件物品，结余计算公式为total_items - 1，结余无有效期限制
• 结余需要分配到后续所有total_items = 0的日期，同样遵循单日仅可使用1件的规则
• 已按日期维度记录rolling_surplus，需要非递归实现rolling_surplus_spent（即目标表的filled_in字段，标记当日是否使用了过往结余）

原始数据表 #

date       | total_items |  surplus
------------------------------------
2021-01-01 |         0   |       0
2021-01-02 |         2   |       1
2021-01-03 |         2   |       1
2021-01-04 |         0   |       0
2021-01-05 |         1   |       0
2021-01-06 |         0   |       0
2021-01-07 |         0   |       0
2021-01-08 |         3   |       2
2021-01-09 |         0   |       0
2021-01-10 |         0   |       0

目标数据表 #

filled_in字段代表该日是否使用了过往结余

date       | total_items |  surplus | filled_in
------------------------------------------------
2021-01-01 |         0   |       0  |      0
2021-01-02 |         2   |       1  |      0
2021-01-03 |         2   |       1  |      0
2021-01-04 |         0   |       0  |      1
2021-01-05 |         1   |       0  |      0
2021-01-06 |         0   |       0  |      1
2021-01-07 |         0   |       0  |      0
2021-01-08 |         3   |       2  |      0
2021-01-09 |         0   |       0  |      1
2021-01-10 |         0   |       0  |      1

非递归实现方案（以SQL为例，适配所有支持标准窗口函数的数据库） #

核心思路 #

通过两个累计值的匹配判断，完全避免递归逐行计算：

1. 计算从最早日期到当前日期的累计总结余
2. 给所有total_items = 0的缺量日期按时间顺序单独编号，作为累计缺量序号
3. 若某个缺量日期的序号≤对应时间点的累计总结余，说明该日期可以用之前的结余覆盖，filled_in标记为1，否则为0

代码实现 #

WITH step1 AS (
    -- 给所有行按日期排序打编号，同时计算截止到当前行的累计总结余
    SELECT 
        *,
        ROW_NUMBER() OVER(ORDER BY date) AS row_num,
        SUM(surplus) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_surplus
    FROM original_table
),
step2 AS (
    -- 筛选所有缺量日期，按时间顺序给缺量日期单独编号
    SELECT 
        row_num,
        ROW_NUMBER() OVER(ORDER BY date) AS gap_num
    FROM step1
    WHERE total_items = 0
)
-- 匹配判断每个缺量日期是否在结余可覆盖范围内
SELECT 
    s1.date,
    s1.total_items,
    s1.surplus,
    CASE WHEN s2.gap_num IS NOT NULL AND s2.gap_num <= s1.cum_surplus THEN 1 ELSE 0 END AS filled_in
FROM step1 s1
LEFT JOIN step2 s2 ON s1.row_num = s2.row_num
ORDER BY s1.date;

结果验证 #

针对示例数据的计算逻辑完全匹配预期：

1. 截止2021-01-03累计结余为2，前2个缺量日期（2021-01-04、2021-01-06）的序号≤2，标记为1，第三个缺量日期2021-01-07序号3>2，标记为0
2. 2021-01-08新增结余2，累计结余更新为2，后续2个缺量日期（2021-01-09、2021-01-10）序号≤累计可覆盖值，标记为1

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