如何用SQL计算商品实际库存天数（含累计求和）

假设你的库存变动表名为inventory_changes，包含核心字段：

• kArticle：商品ID
• tChangeTime：库存变动发生时间
• tAvailable：变动后的可用库存数量

以下是完整的SQL查询，可实现合并连续在库时段、处理边界问题并统计累计在库天数：

WITH inventory_groups AS (
    -- 标记连续的库存状态分组
    SELECT
        kArticle,
        tChangeTime,
        tAvailable,
        -- 库存状态切换时生成新分组ID（0→>0 或 >0→0）
        SUM(CASE WHEN prev_available IS NULL OR ((prev_available > 0) != (tAvailable > 0)) THEN 1 ELSE 0 END) 
            OVER (PARTITION BY kArticle ORDER BY tChangeTime) AS group_id
    FROM (
        -- 获取前一行库存数量，用于判断状态变化
        SELECT
            kArticle,
            tChangeTime,
            tAvailable,
            LAG(tAvailable) OVER (PARTITION BY kArticle ORDER BY tChangeTime) AS prev_available
        FROM inventory_changes
        WHERE kArticle = 250 -- 指定目标商品ID
    ) AS prev_data
),
available_periods AS (
    -- 提取每个连续在库时段的起止时间及天数
    SELECT
        kArticle,
        MIN(tChangeTime) AS period_start,
        -- 处理末行库存未耗尽的情况：用当前时间作为结束时间
        CASE 
            WHEN MAX(tAvailable) > 0 AND MAX(tChangeTime) = (SELECT MAX(tChangeTime) FROM inventory_groups WHERE kArticle = 250)
            THEN SYSDATETIME()
            ELSE MAX(tChangeTime)
        END AS period_end,
        -- 计算时段天数（精确到小数）
        DATEDIFF(SECOND, MIN(tChangeTime), 
            CASE 
                WHEN MAX(tAvailable) > 0 AND MAX(tChangeTime) = (SELECT MAX(tChangeTime) FROM inventory_groups WHERE kArticle = 250)
                THEN SYSDATETIME()
                ELSE MAX(tChangeTime)
            END) / 86400.0 AS gap_in_days
    FROM inventory_groups
    WHERE tAvailable > 0 -- 仅统计库存可用的时段
    GROUP BY kArticle, group_id
)
-- 汇总累计总在库天数
SELECT
    kArticle,
    SUM(gap_in_days) AS total_available_days
FROM available_periods
GROUP BY kArticle;

关键逻辑说明： #

1. 连续时段分组：通过LAG()窗口函数获取前一行库存状态，当状态发生切换时生成新分组ID，将连续同状态记录归为一组。
2. 边界问题处理：
   • 首行库存为0：通过WHERE tAvailable > 0直接过滤掉不可用库存的分组，自动规避错误。
   • 末行库存未耗尽：判断当前分组是否为商品最后一条变动记录且库存>0，若是则用SYSDATETIME()作为时段结束时间。
3. 天数计算与求和：用秒数差除以86400（一天的秒数）得到精确天数，最后通过SUM()汇总所有在库时段的总天数。

内容的提问来源于stack exchange，提问作者Sascha R.