oracle - SQL query, create groups by dates -
this initial table, (the dates in dd/mm/yy format)
id day type_id type num start_date end_date ---- --------- ------- ---- ---- --------- --------- 4241 15/09/15 2 1 66 01/01/00 31/12/99 4241 16/09/15 2 1 66 01/01/00 31/12/99 4241 17/09/15 9 1 59 17/09/15 18/09/15 4241 18/09/15 9 1 59 17/09/15 18/09/15 4241 19/09/15 2 1 66 01/01/00 31/12/99 4241 20/09/15 2 1 66 01/01/00 31/12/99 4241 15/09/15 3 2 63 01/01/00 31/12/99 4241 16/09/15 8 2 159 16/09/15 17/09/15 4241 17/09/15 8 2 159 16/09/15 17/09/15 4241 18/09/15 3 2 63 01/01/00 31/12/99 4241 19/09/15 3 2 63 01/01/00 31/12/99 4241 20/09/15 3 2 63 01/01/00 31/12/99 2134 15/09/15 2 1 66 01/01/00 31/12/99 2134 16/09/15 2 1 66 01/01/00 31/12/99 2134 17/09/15 9 1 59 17/09/15 18/09/15 2134 18/09/15 9 1 59 17/09/15 18/09/15 2134 19/09/15 2 1 66 01/01/00 31/12/99 2134 20/09/15 2 1 66 01/01/00 31/12/99 2134 15/09/15 3 2 63 01/01/00 31/12/99 2134 16/09/15 8 2 159 16/09/15 17/09/15 2134 17/09/15 8 2 159 16/09/15 17/09/15 2134 18/09/15 3 2 63 01/01/00 31/12/99 2134 19/09/15 3 2 63 01/01/00 31/12/99 2134 20/09/15 3 2 63 01/01/00 31/12/99 and i've create groups initial day , end day same id, , type.
i don't want group day, need create group every time type_id changes, based on initial order (id, type, day asc)
this result want achieve:
id day_ini day_end type_id type num start_date end_date ---- --------- --------- ------- ---- ---- --------- --------- 4241 15/09/15 16/09/15 2 1 66 01/01/00 31/12/99 4241 17/09/15 18/09/15 9 1 59 17/09/15 18/09/15 4241 19/09/15 20/09/15 2 1 66 01/01/00 31/12/99 4241 15/09/15 15/09/15 3 2 63 01/01/00 31/12/99 4241 16/09/15 17/09/15 8 2 159 16/09/15 17/09/15 4241 18/09/15 20/09/15 3 2 63 01/01/00 31/12/99 2134 15/09/15 16/09/15 2 1 66 01/01/00 31/12/99 2134 17/09/15 18/09/15 9 1 59 17/09/15 18/09/15 2134 19/09/15 20/09/15 2 1 66 01/01/00 31/12/99 2134 15/09/15 15/09/15 3 2 63 01/01/00 31/12/99 2134 16/09/15 17/09/15 8 2 159 16/09/15 17/09/15 2134 18/09/15 20/09/15 3 2 63 01/01/00 31/12/99 could please give clue how it??, thanks!
oracle 11g r2 schema setup:
create table test ( id, day, type_id, type, num, start_date, end_date ) select 4241, date '2015-09-15', 2, 1, 66, date '2000-01-01', date '1999-12-31' dual union select 4241, date '2015-09-16', 2, 1, 66, date '2000-01-01', date '1999-12-31' dual union select 4241, date '2015-09-17', 9, 1, 59, date '2015-09-17', date '2015-09-18' dual union select 4241, date '2015-09-18', 9, 1, 59, date '2015-09-17', date '2015-09-18' dual union select 4241, date '2015-09-19', 2, 1, 66, date '2000-01-01', date '1999-12-31' dual union select 4241, date '2015-09-20', 2, 1, 66, date '2000-01-01', date '1999-12-31' dual union select 4241, date '2015-09-15', 3, 2, 63, date '2000-01-01', date '1999-12-31' dual union select 4241, date '2015-09-16', 8, 2, 159, date '2015-09-16', date '2015-09-17' dual union select 4241, date '2015-09-17', 8, 2, 159, date '2015-09-16', date '2015-09-17' dual union select 4241, date '2015-09-18', 3, 2, 63, date '2000-01-01', date '1999-12-31' dual union select 4241, date '2015-09-19', 3, 2, 63, date '2000-01-01', date '1999-12-31' dual union select 4241, date '2015-09-20', 3, 2, 63, date '2000-01-01', date '1999-12-31' dual union select 2134, date '2015-09-15', 2, 1, 66, date '2000-01-01', date '1999-12-31' dual union select 2134, date '2015-09-16', 2, 1, 66, date '2000-01-01', date '1999-12-31' dual union select 2134, date '2015-09-17', 9, 1, 59, date '2015-09-17', date '2015-09-18' dual union select 2134, date '2015-09-18', 9, 1, 59, date '2015-09-17', date '2015-09-18' dual union select 2134, date '2015-09-19', 2, 1, 66, date '2000-01-01', date '1999-12-31' dual union select 2134, date '2015-09-20', 2, 1, 66, date '2000-01-01', date '1999-12-31' dual union select 2134, date '2015-09-15', 3, 2, 63, date '2000-01-01', date '1999-12-31' dual union select 2134, date '2015-09-16', 8, 2, 159, date '2015-09-16', date '2015-09-17' dual union select 2134, date '2015-09-17', 8, 2, 159, date '2015-09-16', date '2015-09-17' dual union select 2134, date '2015-09-18', 3, 2, 63, date '2000-01-01', date '1999-12-31' dual union select 2134, date '2015-09-19', 3, 2, 63, date '2000-01-01', date '1999-12-31' dual union select 2134, date '2015-09-20', 3, 2, 63, date '2000-01-01', date '1999-12-31' dual query 1:
with group_changes ( select t.*, case type_id when lag( type_id ) on ( partition id, type order day ) 0 else 1 end has_changed_group test t ), groups ( select id, day, type_id, type, num, start_date, end_date, sum( has_changed_group ) on ( partition id, type order day rows between unbounded preceding , current row ) grp group_changes ) select id, min( day ) day_ini, max( day ) day_end, min( type_id ) type_id, type, min( num ) num, min( start_date ) start_date, min( end_date ) end_date groups group id, type, grp | id | day_ini | day_end | type_id | type | num | start_date | end_date | |------|-----------------------------|-----------------------------|---------|------|-----|-----------------------------|-----------------------------| | 4241 | september, 17 2015 00:00:00 | september, 18 2015 00:00:00 | 9 | 1 | 59 | september, 17 2015 00:00:00 | september, 18 2015 00:00:00 | | 2134 | september, 15 2015 00:00:00 | september, 15 2015 00:00:00 | 3 | 2 | 63 | january, 01 2000 00:00:00 | december, 31 1999 00:00:00 | | 2134 | september, 18 2015 00:00:00 | september, 20 2015 00:00:00 | 3 | 2 | 63 | january, 01 2000 00:00:00 | december, 31 1999 00:00:00 | | 4241 | september, 15 2015 00:00:00 | september, 16 2015 00:00:00 | 2 | 1 | 66 | january, 01 2000 00:00:00 | december, 31 1999 00:00:00 | | 4241 | september, 19 2015 00:00:00 | september, 20 2015 00:00:00 | 2 | 1 | 66 | january, 01 2000 00:00:00 | december, 31 1999 00:00:00 | | 4241 | september, 15 2015 00:00:00 | september, 15 2015 00:00:00 | 3 | 2 | 63 | january, 01 2000 00:00:00 | december, 31 1999 00:00:00 | | 4241 | september, 16 2015 00:00:00 | september, 17 2015 00:00:00 | 8 | 2 | 159 | september, 16 2015 00:00:00 | september, 17 2015 00:00:00 | | 2134 | september, 17 2015 00:00:00 | september, 18 2015 00:00:00 | 9 | 1 | 59 | september, 17 2015 00:00:00 | september, 18 2015 00:00:00 | | 2134 | september, 15 2015 00:00:00 | september, 16 2015 00:00:00 | 2 | 1 | 66 | january, 01 2000 00:00:00 | december, 31 1999 00:00:00 | | 2134 | september, 19 2015 00:00:00 | september, 20 2015 00:00:00 | 2 | 1 | 66 | january, 01 2000 00:00:00 | december, 31 1999 00:00:00 | | 2134 | september, 16 2015 00:00:00 | september, 17 2015 00:00:00 | 8 | 2 | 159 | september, 16 2015 00:00:00 | september, 17 2015 00:00:00 | | 4241 | september, 18 2015 00:00:00 | september, 20 2015 00:00:00 | 3 | 2 | 63 | january, 01 2000 00:00:00 | december, 31 1999 00:00:00 | 
Comments
Post a Comment