求Oracle SQL合并2天内相邻发货数据的查询语句

问题说明 #

现有一张包含订单数量（Qty）和发货日期（shipment_date）的表，原始数据如下：

Qty shipment_date
10  9/20
8   9/19
3   9/18
5   9/16
2   9/13

需合并发货日期间隔在2天以内的行数据，合并规则为从最晚日期开始，将该日期前2天内的订单归为一组，剩余订单重复此逻辑，期望结果如下：

Qty shipment_date
2   9/13
8   9/18
18  9/20

解决方案 #

以下是实现该需求的Oracle SQL查询语句，假设表名为orders：

WITH ordered_orders AS (
    -- 将日期转为date类型，并按日期倒序排序
    SELECT 
        qty,
        TO_DATE(shipment_date, 'MM/DD') AS ship_date,
        ROW_NUMBER() OVER (ORDER BY TO_DATE(shipment_date, 'MM/DD') DESC) AS rn
    FROM orders
),
recursive_groups AS (
    -- 递归分组：从最晚日期开始，将间隔2天内的订单归为同一组
    SELECT 
        qty,
        ship_date,
        rn,
        ship_date AS group_end_date,
        1 AS group_id
    FROM ordered_orders
    WHERE rn = 1
    UNION ALL
    SELECT 
        o.qty,
        o.ship_date,
        o.rn,
        -- 当前日期与组尾日期间隔≤2天则沿用原组尾日期，否则以当前日期为新组尾
        CASE WHEN o.ship_date >= rg.group_end_date - 2 THEN rg.group_end_date ELSE o.ship_date END,
        -- 间隔超过2天则创建新组
        CASE WHEN o.ship_date >= rg.group_end_date - 2 THEN rg.group_id ELSE rg.group_id + 1 END
    FROM recursive_groups rg
    JOIN ordered_orders o ON o.rn = rg.rn + 1
)
-- 按组汇总数量，格式化日期输出
SELECT 
    SUM(qty) AS Qty,
    TO_CHAR(group_end_date, 'MM/DD') AS shipment_date
FROM recursive_groups
GROUP BY group_id, group_end_date
ORDER BY group_end_date;

逻辑说明 #

1. ordered_orders CTE：将字符串类型的发货日期转换为DATE类型，并按日期倒序生成行号，方便后续递归处理。
2. recursive_groups CTE：通过递归方式从最晚日期开始分组，若当前订单日期与当前组的最晚日期间隔不超过2天，则归入同一组；否则创建新组。
3. 最终查询：按组汇总订单数量，将组的最晚日期格式化为指定字符串后输出，并按日期升序排列结果。

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