如何用SQL（MS Access/SQLite）计算每日剩余待发包裹量

问题说明 #

现有process_table表，包含day（日期）、arrivals（当日到件量）、max_output_capacity（当日最大处理能力）三列。业务规则为：

• 每日到件需当日处理
• 若到件量超出处理能力，剩余包裹结转至次日处理
• 次日剩余量计算公式：remaining_next_day[ti] = max(0, arrivals - max_output_capacity + remaining_next_day[ti-1])，首天剩余量为max(0, arrivals[0] - max_output_capacity[0])

测试表结构与数据：

CREATE TABLE process_table(day, arrivals, max_output_capacity)
INSERT INTO process_table VALUES ('0', 0, 2), ('1', 2, 3), ('2', 5, 4), ('3', 0, 5), ('4', 0, 5), ('5', 14, 1), ('6', 0, 3)

MS Access 解决方案 #

由于Access的ACE/Jet SQL不支持递归CTE，可通过子查询累加每日净负载实现计算：

SELECT
    pt.day,
    pt.arrivals,
    pt.max_output_capacity,
    IIf(
        (SELECT Sum(p.arrivals - p.max_output_capacity) 
         FROM process_table p 
         WHERE p.day <= pt.day) > 0,
        (SELECT Sum(p.arrivals - p.max_output_capacity) 
         FROM process_table p 
         WHERE p.day <= pt.day),
        0
    ) AS remaining_next_day
FROM process_table pt
ORDER BY pt.day;

逻辑说明：

• 子查询计算从首天到当前天的累计净负载（到件量与处理能力的差值之和）
• 用IIf取累计净负载与0的较大值，得到当日结转至次日的剩余量，完全匹配业务规则

SQLite 解决方案 #

SQLite支持递归CTE，可直接模拟逐天递推的计算逻辑：

WITH RECURSIVE daily_remaining AS (
    -- 递归起始：计算首天剩余量
    SELECT
        day,
        arrivals,
        max_output_capacity,
        MAX(0, arrivals - max_output_capacity) AS remaining_next_day
    FROM process_table
    WHERE day = '0'
    
    UNION ALL
    
    -- 递归递推：基于前一天剩余量计算当日剩余
    SELECT
        pt.day,
        pt.arrivals,
        pt.max_output_capacity,
        MAX(0, pt.arrivals - pt.max_output_capacity + dr.remaining_next_day) AS remaining_next_day
    FROM process_table pt
    JOIN daily_remaining dr ON CAST(pt.day AS INTEGER) = CAST(dr.day AS INTEGER) + 1
)
SELECT * FROM daily_remaining ORDER BY day;

逻辑说明：

• 递归CTE的起始部分计算首天的剩余量
• 递归步骤通过关联前一天的记录，代入业务公式计算当日结转至次日的剩余量，与Excel公式逻辑完全一致

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