Oracle 将相同的值分组在不同的组中

数据库:
Oracle数据库12c版本12.2.0.1.0

以下是我的测试用例脚本:

create table test
(
       id             number(1),
       sdate          date,
       tdate          date,
       prnt_id        number(1)
);

insert into test (id, sdate, tdate, prnt_id) values (1, to_date('10/17/2012','mm/dd/yyyy'), to_date('10/16/2014','mm/dd/yyyy'), 2);
insert into test (id, sdate, tdate, prnt_id) values (1, to_date('10/16/2014','mm/dd/yyyy'), to_date('2/16/2016','mm/dd/yyyy'), 2);
insert into test (id, sdate, tdate, prnt_id) values (1, to_date('2/16/2016','mm/dd/yyyy'), to_date('9/30/2016','mm/dd/yyyy'), 3);
insert into test (id, sdate, tdate, prnt_id) values (1, to_date('9/30/2016','mm/dd/yyyy'), to_date('3/16/2017','mm/dd/yyyy'), 3);
insert into test (id, sdate, tdate, prnt_id) values (1, to_date('3/16/2017','mm/dd/yyyy'), to_date('1/16/2019','mm/dd/yyyy'), 2);
insert into test (id, sdate, tdate, prnt_id) values (1, to_date('1/16/2019','mm/dd/yyyy'), to_date('10/16/2019','mm/dd/yyyy'), 2);
insert into test (id, sdate, tdate, prnt_id) values (1, to_date('10/16/2019','mm/dd/yyyy'), to_date('12/1/2999','mm/dd/yyyy'), 2);

commit;

select * from test order by sdate;

问题:

我想修改上面的Select SQL，它从测试表中返回所有7行，选择所有列加上另外两列。
第一个附加列 (min_sdate) 将返回行1,2的10/17/2012，并返回行3,4的2/16/2016，并返回行5,6、7的3/16/2017。
第二个附加列 (max_tdate) 将为行1,2返回2/16/2016，为行3,4返回3/16/2017，为行5,6、7返回12/1/2999。
基本上，我试图分组prnt_id列，但不是两个组 (prnt_id: 2和3)，我想要三个组 (prnt_id: 2,3，2)，然后对于这三个组得到最小 (sdate) 和最大 (tdate)。
我当时想我可以使用分析函数min() 和max() 与window子句来实现这一点，但不确定如何构建SQL。

任何或所有帮助将不胜感激。谢谢!

I was thinking I could use analytical function min() and max() with window clause to achieve this

确实可以。但是还有另一种方法:

模式匹配!

有了这个，你使用match_regnize来:

-按ID划分
-按日期排序
-查找任意行 (init; undefined表示 “始终为真”)
-如果行的PRNT_ID值与前一个相同 (prnt_id = prev (prnt_id) 和模式same_val *)，则继续向组中添加行
-返回所有行
-将最小开始dt和最终最大结束日期添加到 “度量” 子句中的结果中

这给出了:

alter session set nls_date_format = 'DD MON YYYY';

select id, sdate, tdate, prnt_id, mn, mx
from   test match_recognize (
  partition by id
  order by sdate
  measures 
    min ( sdate ) as mn,
    final max ( tdate ) as mx
  all rows per match
  pattern ( init same_val* )
  define 
    same_val as prnt_id = prev ( prnt_id )
);

ID    SDATE          TDATE          PRNT_ID    MN             MX              
    1 17 OCT 2012    16 OCT 2014             2 17 OCT 2012    16 FEB 2016    
    1 16 OCT 2014    16 FEB 2016             2 17 OCT 2012    16 FEB 2016    
    1 16 FEB 2016    30 SEP 2016             3 16 FEB 2016    16 MAR 2017    
    1 30 SEP 2016    16 MAR 2017             3 16 FEB 2016    16 MAR 2017    
    1 16 MAR 2017    16 JAN 2019             2 16 MAR 2017    01 DEC 2999    
    1 16 JAN 2019    16 OCT 2019             2 16 MAR 2017    01 DEC 2999    
    1 16 OCT 2019    01 DEC 2999             2 16 MAR 2017    01 DEC 2999