Oracle SQL -使用MATCH_RECOGNIZE返回日期范围

fykwrbwg 于 2023-01-30 发布在 Oracle

关注(0)|答案(2)|浏览(118)

我需要有关我正在尝试完成的一项任务的帮助。我需要将数据联接到尽可能小的日期范围中，并检索一个 * id * 下的对象（在列"* name "中）的MIN（ P_MIN ）和SUM（ P_MAX *）。

|ID |NAME    |DATE_FROM |DATE_TO   |P_MAX|P_MIN|
|---|--------|----------|----------|-----|-----|
|1  |OBJECT 1|10/11/2021|10/10/2022|150  |20   |
|1  |OBJECT 1|10/10/2022|02/02/2023|200  |40   |
|1  |OBJECT 1|02/02/2023|18/06/2027|100  |70   |
|1  |OBJECT 2|10/11/2021|01/05/2022|300  |60   |
|1  |OBJECT 2|01/05/2022|01/12/2022|50   |40   |
|1  |OBJECT 2|01/12/2022|18/06/2027|350  |40   |

对于上述内容，我希望获得

|ID |DATE_FROM |DATE_TO   |SUM_P_MAX|P_MIN|
|---|----------|----------|---------|-----|
|1  |10/11/2021|01/05/2022|150+300  |20   |
|1  |01/05/2022|10/10/2022|50+150   |20   |
|1  |10/10/2022|01/12/2022|200+50   |40   |
|1  |01/12/2022|02/02/2023|350+200  |40   |
|1  |02/02/2023|18/06/2027|100+350  |40   |

"提示"

MIN（* date_from ）和MAX（ date_to *）对于每个对象（列'name'）始终相同。
MAX（date_to）可以为NULL（表示对象持续到"无穷大"）。
对于每个对象，* date_from * 始终与前一个 * date_to * 相同。
一个ID下可能有两个以上的对象
因此，对于MIN（* date_from ），我需要找到MIN（ date_to ），然后移动到下一行（因此，找到下一个MIN（date_from/to）），以此类推。问题可能是有两个MIN（ date_from ）和MAX（ date_to *）

我尝试使用MATCH_RECOGNIZE解决此问题，但无法获得预期结果。我已使用MATCH_RECOGNIZE解决此问题，但可能有更好的解决方法？
有人能帮忙吗？
数据：

CREATE TABLE my_table (id         number
                         ,name       varchar2(100)
                         ,date_from  date
                         ,date_to    date
                         ,p_max      number
                         ,p_min      number);   
                         
 INSERT INTO my_table VALUES (1, 'OBJECT 1', TO_DATE('10/11/2021', 'DD/MM/YYYY'), TO_DATE('10/10/2022', 'DD/MM/YYYY'), 150, 20);
 INSERT INTO my_table VALUES (1, 'OBJECT 1', TO_DATE('10/10/2022', 'DD/MM/YYYY'), TO_DATE('02/02/2023', 'DD/MM/YYYY'), 200, 40);
 INSERT INTO my_table VALUES (1, 'OBJECT 1', TO_DATE('02/02/2023', 'DD/MM/YYYY'), TO_DATE('18/06/2027', 'DD/MM/YYYY'), 100, 70);
 INSERT INTO my_table VALUES (1, 'OBJECT 2', TO_DATE('10/11/2021', 'DD/MM/YYYY'), TO_DATE('01/05/2022', 'DD/MM/YYYY'), 300, 60);
 INSERT INTO my_table VALUES (1, 'OBJECT 2', TO_DATE('01/05/2022', 'DD/MM/YYYY'), TO_DATE('01/12/2022', 'DD/MM/YYYY'),  50, 40);
 INSERT INTO my_table VALUES (1, 'OBJECT 2', TO_DATE('01/12/2022', 'DD/MM/YYYY'), TO_DATE('18/06/2027', 'DD/MM/YYYY'), 350, 40);

oracle

来源：https://stackoverflow.com/questions/75247019/oracle-sql-return-date-range-using-match-recognize

2条答案

按热度按时间

hmae6n7t1#

待在更多数据上测试：

with alldates(id, dat) as (
    select id, date_from from my_table
    union 
    select id, date_to from my_table
)
, allintervals(id, date_from, date_to) as (
    select * from (
        select id, dat as date_from, lead(dat) over (partition by id order by dat) as date_to from alldates
    )
    where date_to is not null
)
select inter.id, inter.date_from, inter.date_to, sum(p_max) as sum_pmax, min(p_min) as min_pmin
from allintervals inter
join my_table t on inter.id = t.id and (
    inter.date_from between t.date_from and t.date_to-1
    or 
    inter.date_from between t.date_from and t.date_to-1
    )
group by inter.id, inter.date_from, inter.date_to
order by id, inter.date_from
;

1   10/11/21    01/05/22    450 20
1   01/05/22    10/10/22    200 20
1   10/10/22    01/12/22    250 40
1   01/12/22    02/02/23    550 40
1   02/02/23    18/06/27    450 40

赞(0）回复(0）举报 2023-01-30

niwlg2el2#

您可以使用model子句引用其他行的值并计算此类合计。
此解决方案背后的思想是为每个间隔计算新的结束日期（只要每个间隔没有间隔，新的结束日期就是下一个开始日期）。然后计算此间隔与所有原始间隔的交集的合计。

select distinct
  date_from,
  to_ as date_to,
  sum_pmax,
  min_pmin
from my_table
model
  partition by (id)
  dimension by (
    date_from, date_to
  )
  measures (
    p_min, p_max,
    /*New result values*/
    0 as min_pmin, 0 as sum_pmax,
     /*New value of date_to*/
    date_from as to_,
    /*Auxiliary date_from to avoid cycle reference*/
    date_from as dummy_nocycle
  )
  rules update (
    /*Each new interval starts an new value of date_from,
    so it will be reused. The end of each interval is
    the next date_from*/

    /*Calculate new date_to as the nearest date_from
    of subsequent interval. Here we use a copy of date_from
    as a measure to avoid cyclic reference and be able to access it*/
    to_[any, any] = coalesce(min(dummy_nocycle)[date_from > cv(date_from), date_to > cv(date_from)], cv(date_to)),
    /*Then aggregate measures: calculate total for all intervals that intersect
    with the current one (with new date_to)*/
    sum_pmax[any, any] = sum(p_max)[date_from < to_[cv(), cv()], date_to > cv(date_from)],
    min_pmin[any, any] = min(p_min)[date_from < to_[cv(), cv()], date_to > cv(date_from)]
  )
order by 1, 2

| 起始日期|日期_至|总和_P最大值|最小值_PMIN|
| - ------|- ------|- ------|- ------|
| 二○二一年十一月十日|2022年5月1日|四百五十|二十个|
| 2022年5月1日|2022年10月10日|二百|二十个|
| 2022年10月10日|二○二二年十二月一日|二百五十|四十|
| 二○二二年十二月一日|二○二三年二月二日|五百五十|四十|
| 二○二三年二月二日|2027年6月18日|四百五十|四十|

赞(0）回复(0）举报 2023-01-30

我来回答

Oracle SQL -使用MATCH_RECOGNIZE返回日期范围

2条答案

相关问题

热门标签

最新问答