Oracle使用分析函数实现按日期间隔分组汇总Qty（无需循环）

核心方案基于LEAD()分析函数实现，无循环、无递归，写法简洁且执行效率高。

基础信息 #

测试表结构与样例数据 #

• Tab1（区间定义表）：包含Item（物料编码）、Dates（区间节点日期）两个字段，样例数据如下：

  Item	Dates
  I1	06-30-2022
  I1	07-02-2022
  I1	07-05-2022

• Tab2（数量明细表）：包含Item（物料编码）、Qty（统计数量）、ItemAvailDate（数量可用日期）三个字段，样例数据如下：

  Item	Qty	ItemAvailDate
  I1	10	06-30-2022
  I1	20	07-01-2022
  I1	40	07-02-2022
  I1	30	07-03-2022
  I1	40	07-04-2022
  I1	50	07-05-2022

计算规则 #

按Item分区维度，对Tab1的每个日期节点：

1. 取同物料下按日期升序排列的相邻下一个日期作为区间结束点，统计区间为 [当前日期, 下一个日期)（左闭右开，不包含结束日期当日数据）
2. 若当前日期是同物料下最后一个节点，区间为 [当前日期, 当前日期+1)，即仅统计当日数据
3. 汇总Tab2中可用日期落在对应区间内的数量总和，期望输出结果：

   Item	Dates	Total(Qty)
   I1	06-30-2022	30
   I1	07-02-2022	110
   I1	07-05-2022	50

实现代码 #

WITH tab1_with_interval AS (
    SELECT
        Item,
        Dates AS range_start,
        -- 用LEAD分析函数直接取同Item下相邻的下一个日期，最后一行默认取当前日期+1适配左闭右开规则
        LEAD(Dates, 1, Dates + 1) OVER (PARTITION BY Item ORDER BY Dates) AS range_end
    FROM Tab1
)
SELECT
    t1.Item,
    t1.range_start AS Dates,
    SUM(t2.Qty) AS "Total(Qty)"
FROM tab1_with_interval t1
LEFT JOIN Tab2 t2
    ON t1.Item = t2.Item
    AND t2.ItemAvailDate >= t1.range_start
    AND t2.ItemAvailDate < t1.range_end
GROUP BY t1.Item, t1.range_start
ORDER BY t1.Item, t1.range_start;

逻辑说明 #

• LEAD(字段, 偏移量, 默认值) OVER(PARTITION BY 分区字段 ORDER BY 排序字段)是Oracle标准分析函数，这里设置偏移量为1，直接在单次扫描中拿到每个日期对应的下一个相邻节点日期，最后一行无后续节点时默认用当前日期+1作为区间结束点，不需要额外写CASE分支判断
• 生成的区间同物料下连续无重叠、无空隙，关联明细时不会出现重复统计、漏统计问题
• 全语句未使用任何循环、递归逻辑，多物料、大批量数据场景下可直接运行，执行效率远高于循环、多层自连接类写法

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