如何在Oracle SQL中使用窗口函数实现复杂的物料平衡表计算

窗口函数必须配合PARTITION BY才能正确分组累计；需按material_id和时间字段分区，用date或year_month排序，同一日期多记录须先聚合，且必须用ROWS而非RANGE以确保物理顺序累计。

窗口函数必须配合 PARTITION BY 才能正确分组累计

物料平衡表的核心是按物料、时间维度做流入/流出的滚动累计，但很多人直接套用 SUM() 窗口函数却得到全表总和——因为漏写了 PARTITION BY material_id, year_month。不加分区，SUM(quantity) OVER (ORDER BY date) 会把所有物料混在一起累加，结果完全失真。

实操建议：

• 先确认业务分组粒度：通常是 material_id + 时间字段（如 year_month 或 date），这两个字段必须同时出现在 PARTITION BY
• 时间排序字段必须有确定顺序：避免用 TO_CHAR(date, 'YYYYMM') 后再排序，应直接用 date 或带索引的 year_month 字段
• 若存在同一物料同一天多条记录，需提前聚合（GROUP BY material_id, date），否则窗口累计会重复计算

用 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 而非 RANGE

物料平衡要求“截至当前行”的累计值，比如第3天的库存 = 初始库存 + 第1天流入 − 第1天流出 + 第2天流入 − ……。用 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 在日期相同时可能跨行误累计（Oracle 默认按值而非物理顺序处理 RANGE）。

实操建议：

• 一律使用 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，确保严格按 ORDER BY 的物理顺序累计
• 如果 ORDER BY 包含多个字段（如 date, transaction_type），需保证 transaction_type 有确定排序（例如 'IN' ），否则相同日期下出入库顺序不确定会导致结果波动
• 测试时在子查询中加 ROW_NUMBER() OVER (PARTITION BY material_id ORDER BY date, transaction_type) 辅助验证执行顺序

初始库存不能硬编码进窗口函数，得用 LEFT JOIN 预加载

窗口函数只能基于当前查询结果集运算，无法“凭空”加上期初库存。常见错误是写成 SUM(net_change) OVER (...) + :opening_stock，但这会让每行都加同一个期初值，而实际不同物料期初库存不同。

实操建议：

• 单独建一张 material_opening 表，含 material_id 和 as_of_date（如 '202401'）、opening_qty
• 主查询用 LEFT JOIN material_opening ON t.material_id = o.material_id AND t.year_month = o.as_of_date + 1（注意月份+1逻辑）
• 最终库存公式写成：COALESCE(o.opening_qty, 0) + SUM(net_change) OVER (PARTITION BY t.material_id ORDER BY t.date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

性能陷阱：ORDER BY 字段没索引，窗口函数变全表扫描

当物料数据量大（百万级），且 ORDER BY date 字段无索引时，Oracle 会强制对每个 PARTITION BY 组做内部排序，CPU 和 TEMP 空间飙升，查询从秒级变分钟级。

实操建议：

• 必须为 (material_id, date) 建复合索引，顺序不能颠倒——material_id 是分区键，date 是排序键
• 避免在 ORDER BY 中用函数：如 TRUNC(date) 或 TO_CHAR(date, 'YYYY-MM')，会导致索引失效
• 用 EXPLAIN PLAN 检查执行计划，确认 WINDOW SORT 操作前有 INDEX RANGE SCAN，而非 FULL TABLE SCAN

真正卡住的往往不是函数怎么写，而是分区键和排序键有没有落在索引最左前缀上，以及期初库存能不能一次性关联进来——这两点没对，窗口函数再标准也出不了可用的平衡表。