构建库存迭代日历表求助：SQL实现每日库存状态统计

核心思路 #

放弃逐行循环的WHILE写法，改用日历表+窗口函数的集合式处理逻辑，利用数据库的批量运算能力提升效率，同时解决日期缺失、空值等问题。

具体实现步骤 #

1. 生成目标日期范围的连续日历表 #

先生成2023-01-01至2023-01-10的完整日期序列，确保无日期缺失。不同数据库写法如下：

MySQL/MariaDB 写法

WITH calendar AS (
    SELECT DATE('2023-01-01') AS calendar_day
    UNION ALL
    SELECT DATE_ADD(calendar_day, INTERVAL 1 DAY)
    FROM calendar
    WHERE calendar_day < DATE('2023-01-10')
)
SELECT * FROM calendar;

SQL Server 写法

WITH calendar AS (
    SELECT CAST('2023-01-01' AS DATE) AS calendar_day
    UNION ALL
    SELECT DATEADD(DAY, 1, calendar_day)
    FROM calendar
    WHERE calendar_day < CAST('2023-01-10' AS DATE)
)
SELECT * FROM calendar OPTION (MAXRECURSION 0);

PostgreSQL 写法

WITH calendar AS (
    SELECT generate_series(
        '2023-01-01'::DATE,
        '2023-01-10'::DATE,
        '1 day'::INTERVAL
    ) AS calendar_day
)
SELECT calendar_day::DATE FROM calendar;

2. 转换出入库记录为每日库存变动量 #

将原始出入库记录转换为每日的库存增减值：入库操作加对应数量，出库操作减对应数量；同一产品同一日期的多次操作先汇总当日总变动。

假设原始表名为inventory_operations，结构为product, operation_type, operation_date, quantity：

WITH daily_changes AS (
    SELECT
        product,
        operation_date AS calendar_day,
        SUM(CASE 
            WHEN operation_type = 'IN' THEN quantity
            WHEN operation_type = 'OUT' THEN -quantity
            ELSE 0
        END) AS daily_change
    FROM inventory_operations
    WHERE product = 'test_product'
      AND operation_date BETWEEN '2023-01-01' AND '2023-01-10'
    GROUP BY product, operation_date
)
SELECT * FROM daily_changes;

3. 关联日历表并计算累计库存 #

将日历表与每日变动表关联，用窗口函数从初始库存（0）开始计算累计值，得到每日库存状态：

-- 完整SQL示例（以MySQL为例，其他数据库调整日历表部分即可）
WITH calendar AS (
    SELECT DATE('2023-01-01') AS calendar_day
    UNION ALL
    SELECT DATE_ADD(calendar_day, INTERVAL 1 DAY)
    FROM calendar
    WHERE calendar_day < DATE('2023-01-10')
),
daily_changes AS (
    SELECT
        product,
        operation_date AS calendar_day,
        SUM(CASE 
            WHEN operation_type = 'IN' THEN quantity
            WHEN operation_type = 'OUT' THEN -quantity
            ELSE 0
        END) AS daily_change
    FROM inventory_operations
    WHERE product = 'test_product'
      AND operation_date BETWEEN '2023-01-01' AND '2023-01-10'
    GROUP BY product, operation_date
)
SELECT
    c.calendar_day,
    'test_product' AS product,
    COALESCE(SUM(d.daily_change) OVER (ORDER BY c.calendar_day), 0) AS current_inventory
FROM calendar c
LEFT JOIN daily_changes d 
    ON c.calendar_day = d.calendar_day 
    AND d.product = 'test_product'
ORDER BY c.calendar_day;

方案优势 #

• 效率飞跃：集合式运算由数据库优化器批量处理，远快于逐行循环的WHILE逻辑，9000条数据场景下性能差距显著。
• 解决空值/日期缺失：日历表保证日期连续，LEFT JOIN确保每个日期都有记录，COALESCE自动处理无变动日期的空值，直接继承前一日累计库存。
• 可扩展性强：若需处理多产品，只需移除product = 'test_product'过滤，窗口函数改为按产品分区：OVER (PARTITION BY product ORDER BY c.calendar_day)。

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