Generate date range data group based on data by dates-CodePudding

I have a list of date's data in daily basis below:

| Daytime   | Item | Category| Value |
| --------  |------|-------  |-------|
| 01.01.2022|A     |1        |500    |
| 02.01.2022|A     |1        |500    |
| 03.01.2022|A     |1        |80000  |
| 04.01.2022|A     |1        |500    |
| 05.01.2022|A     |1        |500    |
| 01.01.2022|A     |2        |600    |
| 02.01.2022|A     |2        |600    |
| 03.01.2022|A     |2        |600    |
| 04.01.2022|A     |2        |600    |
| 05.01.2022|A     |2        |600    |
| 01.01.2022|C     |1        |600    |
| 02.01.2022|C     |1        |600    |
| 03.01.2022|C     |1        |600    |
| 04.01.2022|C     |1        |600    |
| 05.01.2022|C     |1        |600    |

How can i transform the data into this form?

| FromDate  | ToDate    | Item |Category| Value |
| --------- |---------  |------|------  |-------|
| 01.01.2022| 02.01.2022|A     |1       |500    |
| 03.01.2022| 03.01.2022|A     |1       |80000  |
| 04.01.2022| 05.01.2022|A     |1       |500    |
| 01.01.2022| 05.01.2022|A     |2       |600    |
| 01.01.2022| 05.01.2022|C     |1       |600    |

I want to group the value (by item and category too) only if they are same for consecutive dates, please help, thank you!

Date format in DD.MM.YYYY and daytime's datatype is Date. Following script for questions:

(SELECT to_date('01/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 1 Category, 500 Value FROM dual UNION ALL
SELECT to_date('02/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 1 Category, 500 Value FROM dual UNION ALL
SELECT to_date('03/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 1 Category, 80000 Value FROM dual UNION ALL
SELECT to_date('04/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 1 Category, 500 Value FROM dual UNION ALL
SELECT to_date('05/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 1 Category, 500 Value FROM dual UNION ALL
SELECT to_date('01/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 2 Category, 600 Value FROM dual UNION ALL
SELECT to_date('02/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 2 Category, 600 Value FROM dual UNION ALL
SELECT to_date('03/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 2 Category, 600 Value FROM dual UNION ALL
SELECT to_date('04/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 2 Category, 600 Value FROM dual UNION ALL
SELECT to_date('05/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 2 Category, 600 Value FROM dual UNION ALL
SELECT to_date('01/01/2022', 'dd/mm/yyyy') daytime, 'C' Item, 1 Category, 600 Value FROM dual UNION ALL
SELECT to_date('02/01/2022', 'dd/mm/yyyy') daytime, 'C' Item, 1 Category, 600 Value FROM dual UNION ALL
SELECT to_date('03/01/2022', 'dd/mm/yyyy') daytime, 'C' Item, 1 Category, 600 Value FROM dual UNION ALL
SELECT to_date('04/01/2022', 'dd/mm/yyyy') daytime, 'C' Item, 1 Category, 600 Value FROM dual UNION ALL
SELECT to_date('05/01/2022', 'dd/mm/yyyy') daytime, 'C' Item, 1 Category, 600 Value FROM dual)

CodePudding user response：

You can use common table expression (cte) technique for that purpose.

with YourSample ( Daytime, Item, Category, Value) as (
select to_date('01.01.2022', 'DD.MM.YYYY'), 'A', 1, 500    from dual union all
select to_date('02.01.2022', 'DD.MM.YYYY'), 'A', 1, 500    from dual union all
select to_date('03.01.2022', 'DD.MM.YYYY'), 'A', 1, 80000  from dual union all
select to_date('04.01.2022', 'DD.MM.YYYY'), 'A', 1, 500    from dual union all
select to_date('05.01.2022', 'DD.MM.YYYY'), 'A', 1, 500    from dual union all
select to_date('01.01.2022', 'DD.MM.YYYY'), 'A', 2, 600    from dual union all
select to_date('02.01.2022', 'DD.MM.YYYY'), 'A', 2, 600    from dual union all
select to_date('03.01.2022', 'DD.MM.YYYY'), 'A', 2, 600    from dual union all
select to_date('04.01.2022', 'DD.MM.YYYY'), 'A', 2, 600    from dual union all
select to_date('05.01.2022', 'DD.MM.YYYY'), 'A', 2, 600    from dual union all
select to_date('01.01.2022', 'DD.MM.YYYY'), 'C', 1, 600    from dual union all
select to_date('02.01.2022', 'DD.MM.YYYY'), 'C', 1, 600    from dual union all
select to_date('03.01.2022', 'DD.MM.YYYY'), 'C', 1, 600    from dual union all
select to_date('04.01.2022', 'DD.MM.YYYY'), 'C', 1, 600    from dual union all
select to_date('05.01.2022', 'DD.MM.YYYY'), 'C', 1, 600    from dual
)

, YourSampleRanked (Daytime, Item, Category, Value, rnb) as (
select Daytime, Item, Category, Value
    , row_number()over(PARTITION BY ITEM, CATEGORY ORDER BY DAYTIME) rnb 
from YourSample
)
, cte (Daytime, Item, Category, Value, rnb, grp) as (
select Daytime, Item, Category, Value, rnb, 1 grp
from YourSampleRanked
where rnb = 1
union all
select t.Daytime, t.Item, t.Category, t.Value, t.rnb
          , decode( t.Value, c.Value, c.grp, c.grp   1 ) grp
from YourSampleRanked t
join cte c 
  on ( c.Category = t.Category and c.Item = t.Item and t.rnb = c.rnb   1 )
)
select min(DAYTIME) FromDate, max(DAYTIME) ToDate, ITEM, CATEGORY, min(Value) Value
from cte
GROUP BY GRP, ITEM, CATEGORY
order by ITEM, CATEGORY, FromDate
;

demo on fiddle<>db

You can also use the MATCH_RECOGNIZE clause for the same purpose if you are running Oracle 12c and later.

select FromDate, toDate, ITEM, CATEGORY, VALUE
from YourSample
MATCH_RECOGNIZE (
     PARTITION BY ITEM, CATEGORY
     ORDER BY DAYTIME
     MEASURES first(STRT.VALUE) as VALUE,
              first(STRT.DAYTIME) as FromDate,
              nvl(last(SAME.DAYTIME), first(STRT.DAYTIME)) as toDate
     ONE ROW PER MATCH
     PATTERN (STRT Same*)
     DEFINE
        Same AS VALUE = PREV(VALUE)
     ) MR
ORDER BY  ITEM, CATEGORY, FromDate, toDate
;

demo2 on fiddle

CodePudding user response：

From Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row processing:

SELECT *
FROM   table_name
MATCH_RECOGNIZE (
  PARTITION BY item, category
  ORDER     BY daytime
  MEASURES
    FIRST(daytime) AS from_date,
    LAST(daytime)  AS to_date,
    FIRST(value)   AS value
  ONE ROW PER MATCH
  PATTERN (same_value )
  DEFINE
    same_value AS FIRST(value) = value
)

Which, for the sample data:

CREATE TABLE table_name (daytime, item, category, value) AS
SELECT DATE '2022-01-01', 'A', 1,   500 FROM DUAL UNION ALL
SELECT DATE '2022-01-02', 'A', 1,   500 FROM DUAL UNION ALL
SELECT DATE '2022-01-03', 'A', 1, 80000 FROM DUAL UNION ALL
SELECT DATE '2022-01-04', 'A', 1,   500 FROM DUAL UNION ALL
SELECT DATE '2022-01-05', 'A', 1,   500 FROM DUAL UNION ALL
SELECT DATE '2022-01-01', 'A', 2,   600 FROM DUAL UNION ALL
SELECT DATE '2022-01-02', 'A', 2,   600 FROM DUAL UNION ALL
SELECT DATE '2022-01-03', 'A', 2,   600 FROM DUAL UNION ALL
SELECT DATE '2022-01-04', 'A', 2,   600 FROM DUAL UNION ALL
SELECT DATE '2022-01-05', 'A', 2,   600 FROM DUAL UNION ALL
SELECT DATE '2022-01-01', 'C', 1,   600 FROM DUAL UNION ALL
SELECT DATE '2022-01-02', 'C', 1,   600 FROM DUAL UNION ALL
SELECT DATE '2022-01-03', 'C', 1,   600 FROM DUAL UNION ALL
SELECT DATE '2022-01-04', 'C', 1,   600 FROM DUAL UNION ALL
SELECT DATE '2022-01-05', 'C', 1,   600 FROM DUAL

Outputs:

ITEM CATEGORY FROM_DATE TO_DATE VALUE

A 1 2022-01-01 00:00:00 2022-01-02 00:00:00 500

A 1 2022-01-03 00:00:00 2022-01-03 00:00:00 80000

A 1 2022-01-04 00:00:00 2022-01-05 00:00:00 500

A 2 2022-01-01 00:00:00 2022-01-05 00:00:00 600

C 1 2022-01-01 00:00:00 2022-01-05 00:00:00 600

ITEM	CATEGORY	FROM_DATE	TO_DATE	VALUE
A	1	2022-01-01 00:00:00	2022-01-02 00:00:00	500
A	1	2022-01-03 00:00:00	2022-01-03 00:00:00	80000
A	1	2022-01-04 00:00:00	2022-01-05 00:00:00	500
A	2	2022-01-01 00:00:00	2022-01-05 00:00:00	600
C	1	2022-01-01 00:00:00	2022-01-05 00:00:00	600

db<>fiddle here

CodePudding user response：

This is a job for a GROUP BY using TRUNC(daytime, 'MM'). TRUNC(), when used with dates, truncates them to the beginning of a calendar / clock period.

SELECT TRUNC(Daytime, 'MM') FromDate,
       ADD_MONTHS(TRUNC(Daytime, 'MM'), 1) ToDate,
       Item, Category,
       SUM(Value) Value
  FROM my_table
 GROUP BY TRUNC(Daytime, 'MM'), Item, Category

Or alternatively you can avoid those arcane Oracle date format specifiers like 'MM' and go with LAST_DAY().

SELECT ADD_MONTHS(LAST_DAY(Daytime)   1, -1) FromDate,
       LAST_DAY(Daytime)   1 ToDate,
       Item, Category,
       SUM(Value) Value
  FROM my_table
 GROUP BY LAST_DAY(Daytime), Item, Category