SQL窗口函数报错：如何按零件编号统计指定日期区间的订单总量？

需要编写SQL查询，按零件编号统计**订单日期至回溯日期（订单日期减去Lead Time）**之间的订单数量总和（对应示例中的「Total Orders」列）。当前使用窗口函数时，因在between子句中引用Lead Time列值导致报错，现有查询语句如下：

select 
    "Part Number",
    "Snapshot Date",
    sum("Lead Time"),
    sum("Order Qty") over (partition by "Part Number" order by 
    "Snapshot Date" desc rows between "Lead Time" preceding and current 
    row) as "Total Demand"
  from table1

源数据结构 #

示例需求表格 #

解决方法 #

窗口函数的rows between子句仅支持固定数值偏移，无法直接引用列值，因此需要换用日期范围过滤的方式实现需求，以下提供两种可行方案：

方案1：关联子查询（通用所有SQL方言） #

通过自连接匹配同零件、日期在回溯周期内的行，再聚合求和：

SELECT
    t1."Part Number" AS "Part Num",
    t1."Snapshot Date" AS "Order Date",
    -- 注意：日期加减语法需根据数据库调整，以下为PostgreSQL示例
    t1."Snapshot Date" - INTERVAL '1 day' * t1."Lead Time" AS "Lookback Date",
    t1."Lead Time",
    t1."Order Qty",
    SUM(t2."Order Qty") AS "Total Orders"
FROM table1 t1
JOIN table1 t2
    ON t2."Part Number" = t1."Part Number"
    AND t2."Snapshot Date" BETWEEN (t1."Snapshot Date" - INTERVAL '1 day' * t1."Lead Time") AND t1."Snapshot Date"
GROUP BY
    t1."Part Number",
    t1."Snapshot Date",
    t1."Lead Time",
    t1."Order Qty"
ORDER BY
    t1."Part Number",
    t1."Snapshot Date" DESC;

方案2：范围窗口函数（仅支持部分SQL方言） #

如果使用的数据库（如PostgreSQL、BigQuery）支持基于日期范围的RANGE窗口，可以直接用窗口函数实现：

SELECT
    "Part Number" AS "Part Num",
    "Snapshot Date" AS "Order Date",
    "Snapshot Date" - INTERVAL '1 day' * "Lead Time" AS "Lookback Date",
    "Lead Time",
    "Order Qty",
    SUM("Order Qty") OVER (
        PARTITION BY "Part Number"
        ORDER BY "Snapshot Date"
        -- 基于日期范围的窗口，语法需匹配数据库
        RANGE BETWEEN INTERVAL '1 day' * "Lead Time" PRECEDING AND CURRENT ROW
    ) AS "Total Orders"
FROM table1
ORDER BY
    "Part Number",
    "Snapshot Date" DESC;

注意事项 #

• 日期加减语法因数据库而异：
  • MySQL：DATE_SUB(t1."Snapshot Date", INTERVAL t1."Lead Time" DAY)
  • SQL Server：DATEADD(day, -t1."Lead Time", t1."Snapshot Date")
  • Oracle：t1."Snapshot Date" - t1."Lead Time"
• 确保Snapshot Date字段为日期类型，而非字符串，否则需先转换格式。

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