Database:
Oracle Database 12c Release 12.2.0.1.0
Following is my test case script:
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;Question:
I want to modify the above Select SQL which returns all 7 rows from test table, selects all the columns plus two more columns.
First additional column (min_sdate) will return 10/17/2012 for rows 1,2 and 2/16/2016 for rows 3,4 and 3/16/2017 for rows 5,6,7.
Second additional column (max_tdate) will return 2/16/2016 for rows 1,2 and 3/16/2017 for rows 3,4 and 12/1/2999 for rows 5,6,7.
Basically, I'm trying to group by prnt_id column but instead of two groups (prnt_id: 2 and 3), I want three groups (prnt_id: 2,3,2), and then for those three groups get the min(sdate) and max(tdate).
I was thinking I could use analytical function min() and max() with window clause to achieve this, but not sure how to frame the SQL.
Any or all help will be appreciated. Thanks!
I was thinking I could use analytical function min() and max() with window clause to achieve thisIndeed you can. But there's another way:
Pattern matching!
With this you use match_recognize to:
- Partition by ID
- Sort by date
- Find any row (init; undefined means "always true")
- Then keep adding rows to the group if they have the same PRNT_ID value as the previous ( prnt_id = prev ( prnt_id ) and pattern same_val*)
- Return all the rows
- Add the min start dt and final max end date to the results in the measures clause
Which gives:
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