I wanna get query result Group By and Orderd by Date

Question

Can I get result like below with Oracle Query? Group by Type but seperated by date order. Date can't be overlaped.

[RAW DATA]

TYPE    FR_DATE             TO_DATE             INCOME
A       04/14 01:04:15      04/14   22:10:15    100 
A       04/15 03:02:22      04/16   18:10:44    200
B       04/17 05:13:22      04/17   20:11:44    300
B       04/18 01:24:22      04/20   23:13:44    100
A       04/22 05:11:22      04/22   19:25:44    400
A       04/23 02:12:22      04/24   17:43:44    200
B       04/25 02:19:22      04/28   18:32:44    500

[RESULT]

TYPE    FR_DATE             TO_DATE             INCOME_SUM
A       04/14 01:04:15      04/16 18:10:44      300 
B       04/17 05:13:22      04/20 23:13:44      400 
A       04/22 05:11:22      04/24 17:43:44      600
B       04/25 02:19:22      04/28 18:32:44      500

score 2 · Answer 1 · answered Apr 14 '23 at 08:05

One option is to look at the problem as if it were gaps and islands, i.e. put certain rows into groups (islands) and then extract data you need.

Sample data:

SQL> with test (type, fr_date, to_date, income) as
  2    (select 'A', date '2023-04-14', date '2024-04-14', 100 from dual union all
  3     select 'A', date '2023-04-15', date '2024-04-16', 200 from dual union all
  4     select 'B', date '2023-04-17', date '2024-04-17', 300 from dual union all
  5     select 'B', date '2023-04-18', date '2024-04-20', 100 from dual union all
  6     select 'A', date '2023-04-21', date '2024-04-21', 400 from dual union all
  7     select 'A', date '2023-04-22', date '2024-04-24', 200 from dual union all
  8     select 'B', date '2023-04-25', date '2024-04-28', 500 from dual
  9    ),

Query begins here:

 10  temp as
 11    (select type, fr_date, to_date, income,
 12       to_number(to_char(fr_date, 'yyyymmdd')) - row_number() over (partition by type order by fr_date) grp
 13     from test
 14    )
 15  select type, min(fr_date) fr_date, max(to_date) to_date, sum(income) income_sum
 16  from temp
 17  group by type, grp
 18  order by fr_date;

TYPE FR_DATE    TO_DATE    INCOME_SUM
---- ---------- ---------- ----------
A    04/14/2023 04/16/2024        300
B    04/17/2023 04/20/2024        400
A    04/21/2023 04/24/2024        600
B    04/25/2023 04/28/2024        500

SQL>

score 2 · Accepted Answer · answered Apr 14 '23 at 08:58

Another gaps-and-islands approach is to use the match_recognize clause, available since 12c, to perform pattern matching:

select type,
  min(fr_date) as fr_date,
  max(to_date) as to_date,
  sum(income) as income_sum
from your_table
match_recognize (
  order by fr_date
  measures match_number() as mnum
  all rows per match
  pattern (start_type same_type*)
  define same_type as (type = prev(type))
)
group by type, mnum
order by fr_date

TYPE	FR_DATE	TO_DATE	INCOME_SUM
A	2023-04-14 01:04:15	2023-04-16 18:10:44	300
B	2023-04-17 05:13:22	2023-04-20 23:13:44	400
A	2023-04-22 05:11:22	2023-04-24 17:43:44	600
B	2023-04-25 02:19:22	2023-04-28 18:32:44	500

fiddle

I wanna get query result Group By and Orderd by Date

2 Answers2