Oracle中多行之间的日期范围交集

huangapple
go评论96阅读模式
英文:

intersection across date ranges from multiple rows in oracle

问题

在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

我尝试使用connect by level生成日期，然后取交集，但由于9999-12-31，运行时间很长。

英文:

Intersection of date ranges across rows in oracle.

I have a table which contains following records

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

We need to find common dates between rule ids

Output will be

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

I tried with connect by level to generate dates and then take intersection, but it is running for long time due to 9999-12-31

答案1

得分: 1

从Oracle 12开始，您可以使用UNPIVOT将日期转换，然后使用分析函数和MATCH_RECOGNIZE逐行处理结果集，以找到同时满足两个规则的连续行：

  1. SELECT *
  2. FROM   (
  3.   SELECT item_no,
  4.          item_type,
  5.          rule_id,
  6.          dt,
  7.          SUM(CASE rule_id WHEN 'rule1' THEN active END) OVER (
  8.            PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
  9.          ) AS rule1,
  10.          SUM(CASE rule_id WHEN 'rule2' THEN active END) OVER (
  11.            PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
  12.          ) AS rule2
  13.   FROM   table_name
  14.          UNPIVOT (
  15.            dt FOR active IN ( active_from AS 1, active_to AS -1 )
  16.          )
  17. )
  18. MATCH_RECOGNIZE(
  19.   PARTITION BY item_no, item_type
  20.   ORDER BY dt, rule1 DESC, rule2 DESC
  21.   MEASURES
  22.     FIRST(dt) AS active_from,
  23.     NEXT(dt) AS active_to
  24.   PATTERN ( active_rules+ )
  25.   DEFINE active_rules AS rule1 > 0 AND rule2 > 0
  26. )

对于样本数据：

  1. CREATE TABLE table_name (Item_no, item_type, active_from, active_to, rule_id) AS
  2. SELECT 10001, 'SAR', DATE '2020-01-01', DATE '2023-01-01', 'rule1' FROM DUAL UNION ALL
  3. SELECT 10001, 'SAR', DATE '2024-01-01', DATE '9999-12-31', 'rule1' FROM DUAL UNION ALL
  4. SELECT 10001, 'SAR', DATE '2020-05-01', DATE '2021-06-01', 'rule2' FROM DUAL UNION ALL
  5. 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

对于：

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

输出结果为：

ITEM_NO ITEM_TYPE ACTIVE_FROM ACTIVE_TO
10001 SPR 2023-01-31 00:00:00 2023-01-31 00:00:00

fiddle

英文:

From Oracle 12, you can UNPIVOT the dates and then use analytic functions and MATCH_RECOGNIZE to process the result set row-by-row to find the consecutive rows where both rules are active:

  1. SELECT *
  2. FROM   (
  3.   SELECT item_no,
  4.          item_type,
  5.          rule_id,
  6.          dt,
  7.          SUM(CASE rule_id WHEN 'rule1' THEN active END) OVER (
  8.            PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
  9.          ) AS rule1,
  10.          SUM(CASE rule_id WHEN 'rule2' THEN active END) OVER (
  11.            PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
  12.          ) AS rule2
  13.   FROM   table_name
  14.          UNPIVOT (
  15.            dt FOR active IN ( active_from AS 1, active_to AS -1 )
  16.          )
  17. )
  18. MATCH_RECOGNIZE(
  19.   PARTITION BY item_no, item_type
  20.   ORDER BY dt, rule1 DESC, rule2 DESC
  21.   MEASURES
  22.     FIRST(dt) AS active_from,
  23.     NEXT(dt) AS active_to
  24.   PATTERN ( active_rules+ )
  25.   DEFINE active_rules AS rule1 > 0 AND rule2 > 0
  26. )

Which, for the sample data:

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

Outputs:

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

and for:

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

The output is:

ITEM_NO ITEM_TYPE ACTIVE_FROM ACTIVE_TO
10001 SPR 2023-01-31 00:00:00 2023-01-31 00:00:00

fiddle

答案2

得分: 0

当寻找相同物品的重叠日期范围，但使用不同规则时，请使用以下代码：

  1. select
  2.   a.item_no,
  3.   a.item_type,
  4.   greatest(a.active_from, b.active_from) as active_from,
  5.   least(a.active_to, b.active_to) as active_to
  6. from mytable a
  7. join mytable b on b.item_no      =   a.item_no
  8.               and b.item_type    =   a.item_type
  9.               and b.rule         <>  a.rule
  10.               and b.active_from  <=  a.active_to
  11.               and b.active_to    >=  a.active_from;

这是用于查找重叠日期范围的SQL查询。

英文:

You are looking for overlapping date ranges for the same item, but a different rule.

When do two date ranges A and B overlap? Answer: when the start(B) <= end(A) AND end(B) >= start(A). With this knowledge we can join. The overlapping range is just the greater start date until the lesser end date.

  1. select
  2.   a.item_no,
  3.   a.item_type,
  4.   greatest(a.active_from, b.active_from) as active_from,
  5.   least(a.active_to, b.active_to) as active_to
  6. from mytable a
  7. join mytable b on b.item_no      =   a.item_no
  8.               and b.item_type    =   a.item_type
  9.               and b.rule         &lt;&gt;  a.rule
  10.               and b.active_from  &lt;=  a.active_to
  11.               and b.active_to    &gt;=  a.active_from;

huangapple
  • 本文由 发表于 2023年6月2日 04:13:31
  • 转载请务必保留本文链接：https://go.coder-hub.com/76385399.html
  • oracle
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定