如何基于日期范围计算QtyOnHand与IssQty总和的比值

嘿，这个需求我之前做库存报表的时候碰到过，核心就是给每一行的AffectedDate划定一个精准的时间窗口——从该日期往前推12个月的同月到该日期的前一个月，然后把这个窗口内所有RecDate对应的IssQty加起来，最后用当前行的QtyOnHand除以这个总和对吧？

下面我用主流SQL数据库的写法来给你演示实现方案，逻辑通用，不同数据库只需要微调日期函数就行：

核心逻辑拆解 #

• 先给每个AffectedDate算出时间窗口的边界：
  • 起始日期：把AffectedDate往前推12个月（比如2015-02对应的起始就是2014-02）
  • 结束日期：把AffectedDate往前推1个月（比如2015-02对应的结束就是2015-01）
• 计算每个窗口内的IssQty总和
• 最后计算QtyOnHand与总和的比值，记得处理总和为0的情况避免报错

SQL实现示例（以SQL Server为例） #

SELECT
    t.AffectedDate,
    t.QtyOnHand,
    -- 先算出指定时间范围内的IssQty总和
    (SELECT SUM(IssQty)
     FROM YourInventoryTable sub
     WHERE sub.RecDate >= DATEADD(month, -12, t.AffectedDate)
       AND sub.RecDate <= DATEADD(month, -1, t.AffectedDate)) AS TotalIssuedQty,
    -- 计算比值，用CASE处理除零错误
    CASE 
        WHEN (SELECT SUM(IssQty)
              FROM YourInventoryTable sub
              WHERE sub.RecDate >= DATEADD(month, -12, t.AffectedDate)
                AND sub.RecDate <= DATEADD(month, -1, t.AffectedDate)) = 0
        THEN NULL  -- 这里可以换成你需要的默认值，比如0或者N/A
        ELSE t.QtyOnHand / (SELECT SUM(IssQty)
                           FROM YourInventoryTable sub
                           WHERE sub.RecDate >= DATEADD(month, -12, t.AffectedDate)
                             AND sub.RecDate <= DATEADD(month, -1, t.AffectedDate))
    END AS InventoryToIssueRatio
FROM YourInventoryTable t

更高效的窗口函数写法（适用于PostgreSQL、SQL Server 2012+） #

如果数据量比较大，子查询可能有点慢，用窗口函数能提升性能：

SELECT
    AffectedDate,
    QtyOnHand,
    TotalIssuedQty,
    CASE WHEN TotalIssuedQty = 0 THEN NULL ELSE QtyOnHand / TotalIssuedQty END AS InventoryToIssueRatio
FROM (
    SELECT
        t.AffectedDate,
        t.QtyOnHand,
        -- 用窗口函数计算滚动时间窗口内的IssQty总和
        SUM(sub.IssQty) OVER (
            ORDER BY DATEPART(year, t.AffectedDate), DATEPART(month, t.AffectedDate)
            RANGE BETWEEN 12 PRECEDING AND 1 PRECEDING
        ) AS TotalIssuedQty
    FROM YourInventoryTable t
    JOIN YourInventoryTable sub 
        ON sub.RecDate >= DATEADD(month, -12, t.AffectedDate)
        AND sub.RecDate <= DATEADD(month, -1, t.AffectedDate)
) AS CalculationSubquery

几个要注意的点 #

• 除零错误：一定要处理总和为0的情况，不然直接除法会抛出错误，用CASE语句就能轻松解决
• 日期类型：确保AffectedDate和RecDate是标准的日期类型（比如DATE），别用字符串存储日期，否则会出现匹配错误
• 索引优化：如果数据量很大，给RecDate和AffectedDate建个联合索引，能大幅提升查询速度

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