Sql oracle中多行的跨日期范围的交集

发布于06月02日

Oracle中各行日期范围的交集.

我有一个表，其中包含以下记录

Item_no	item_type	active_from	active_to	rule_id
10001	SAR	2020-01-01	2023-01-01	rule1
10001	SAR.	2024-01-01	9999-12-31	rule1
10001	SAR	2020-05-01	2021-06-01	rule2
10001	SAR	2021-01-01	2021-02-01	rule2

我们需要查找规则ID之间的公共日期

输出将为

Item_no	item_type	active_from	active_to
10001	SAR	2020-05-01	2021-06-01

我试着按级别连接来生成日期，然后取交叉点，但由于9999-12-31，它运行了很长时间

推荐答案

在Oracle 12中，您可以使用UNPIVOT个日期，然后使用分析函数和MATCH_RECOGNIZE逐行处理结果集，以找到两个规则都处于活动状态的连续行:

SELECT *
FROM   (
  SELECT item_no,
         item_type,
         rule_id,
         dt,
         SUM(CASE rule_id WHEN 'rule1' THEN active END) OVER (
           PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
         ) AS rule1,
         SUM(CASE rule_id WHEN 'rule2' THEN active END) OVER (
           PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
         ) AS rule2
  FROM   table_name
         UNPIVOT (
           dt FOR active IN ( active_from AS 1, active_to AS -1 )
         )
)
MATCH_RECOGNIZE(
  PARTITION BY item_no, item_type
  ORDER BY dt, rule1 DESC, rule2 DESC
  MEASURES
    FIRST(dt) AS active_from,
    NEXT(dt) AS active_to
  PATTERN ( active_rules+ )
  DEFINE active_rules AS rule1 > 0 AND rule2 > 0
)

其中，对于样本数据:

CREATE TABLE table_name (Item_no, item_type, active_from, active_to, rule_id) AS
SELECT 10001, 'SAR', DATE '2020-01-01', DATE '2023-01-01', 'rule1' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2024-01-01', DATE '9999-12-31', 'rule1' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2020-05-01', DATE '2021-06-01', 'rule2' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2021-01-01', DATE '2021-02-01', 'rule2' FROM DUAL;

输出:

ITEM_NO	ITEM_TYPE	ACTIVE_FROM	ACTIVE_TO
10001	SAR	2020-05-01 00:00:00	2021-06-01 00:00:00

以及:

CREATE TABLE table_name (Item_no, item_type, active_from, active_to, rule_id) AS
SELECT 10001, 'SPR', DATE '2023-01-01', DATE '2023-01-31', 'rule1' FROM DUAL UNION ALL
SELECT 10001, 'SPR', DATE '2023-01-31', DATE '2023-02-27', 'rule2' FROM DUAL;

输出为: