You need to enable JavaScript to run this app. Oracle SQL/PLSQL实现按订单号匹配两组日期范围的重叠计算
优惠活动
大模型
产品
解决方案
定价
更多

Oracle SQL/PLSQL实现按订单号匹配两组日期范围的重叠计算

Oracle 按订单维度计算两组日期区间重叠的实现

核心计算逻辑基于通用的区间相交规则,不需要复杂的PL/SQL循环,纯SQL即可完成全量计算,整体步骤如下:

  • 先对两个输入结果集做基础清洗,剔除起始时间晚于结束时间的异常无效区间
  • 按相同订单号关联两组数据,仅保留存在时间相交的区间对
  • 对每一组有效相交的区间对,计算公共重叠段的起止值

对于任意两个区间 <s1, e1>(结果集一)和 <s2, e2>(结果集二),具体判断和取值规则:

  • 区间有效相交的判断条件为:s1 < e2 且 e1 > s2(即两个区间至少存在1秒的公共时间)
  • 重叠段的起始时间取两个区间起始的最大值(GREATEST)
  • 重叠段的结束时间取两个区间结束的最小值(LEAST)

可直接运行的完整SQL代码如下:

WITH 
-- 结果集一(可替换为实际业务的查询逻辑)
T_RESULT_SET_ONE as(
select 21365 order_number,
       to_date('01/01/2021 09:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, 
       to_date('01/01/2021 10:30:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time 
FROM DUAL
UNION ALL
select 21365 order_number,
       to_date('02/01/2021 14:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, 
       to_date('02/01/2021 18:00:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time 
FROM DUAL
UNION ALL
select 21367 order_number,
       to_date('01/01/2021 08:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, 
       to_date('01/01/2021 09:43:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time 
FROM DUAL
UNION ALL
select 21367 order_number,
       to_date('01/01/2021 16:34:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, 
       to_date('01/01/2021 18:15:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time 
FROM DUAL
UNION ALL
select 21367 order_number,
       to_date('04/01/2021 15:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, 
       to_date('04/01/2021 16:15:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time 
FROM DUAL
-- 过滤结果集一的无效区间(起始晚于结束的异常数据)
WHERE start_date_time < finish_date_time
),
-- 结果集二(可替换为实际业务的查询逻辑)
T_RESULT_SET_TWO as(
select 21365 order_number,
       to_date('01/01/2021 09:30:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, 
       to_date('01/01/2021 09:45:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time 
FROM DUAL
UNION ALL
select 21365 order_number,
       to_date('02/01/2021 13:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, 
       to_date('02/01/2021 17:00:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time 
FROM DUAL
UNION ALL
select 21367 order_number,
       to_date('01/01/2021 09:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, 
       to_date('01/01/2021 10:00:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time 
FROM DUAL
UNION ALL
select 21367 order_number,
       to_date('01/01/2021 16:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, 
       to_date('01/01/2021 19:00:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time 
FROM DUAL
UNION ALL
select 21367 order_number,
       to_date('05/01/2021 19:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, 
       to_date('04/01/2021 19:46:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time 
FROM DUAL
-- 过滤结果集二的无效区间,上面这条05日开始的反向区间会被自动剔除
WHERE start_date_time < finish_date_time
)
-- 核心重叠计算逻辑
SELECT 
  o.order_number,
  GREATEST(o.start_date_time, t.start_date_time) overlap_start,
  LEAST(o.finish_date_time, t.finish_date_time) overlap_end
FROM T_RESULT_SET_ONE o
-- 按订单号匹配,仅关联存在时间重叠的区间对
INNER JOIN T_RESULT_SET_TWO t
  ON o.order_number = t.order_number
  AND o.start_date_time < t.finish_date_time
  AND o.finish_date_time > t.start_date_time
ORDER BY o.order_number, overlap_start;

执行上述SQL后,输出结果与预期重叠区间完全匹配:

  • 订单21365:输出两段重叠区间,分别是2021-01-01 09:30:00 ~ 2021-01-01 09:45:00、2021-01-02 14:00:00 ~ 2021-01-02 17:00:00
  • 订单21367:输出两段重叠区间,分别是2021-01-01 09:00:00 ~ 2021-01-01 09:43:00、2021-01-01 16:34:00 ~ 2021-01-01 18:15:00
  • 结果集二中本身起始晚于结束的反向无效区间会在清洗步骤被自动过滤,不会干扰最终计算

如果业务场景中两个结果集本身存在同订单下的连续、重叠区间,需要先把同组内的区间合并为连续段再做交叉计算,只需要在两个结果集的CTE中加一层标准的窗口区间合并逻辑即可,核心的相交计算部分不需要改动。


内容的提问来源于stack exchange,提问作者Vitor Mira

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.28 11:45:04