根據日期資料生成日期范圍資料組-有解無憂

我每天都有以下日期資料串列：

| 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    |

我怎樣才能將資料轉換成這種形式？

| 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    |

我只想對連續日期相同的值（也按專案和類別）進行分組，請幫助，謝謝！

DD.MM.YYYY 中的日期格式和白天的資料型別是日期。以下問題腳本：

(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)

uj5u.com熱心網友回復：

為此，您可以使用公用表運算式 (cte)技術。

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
;

小提琴<>db上的演示

如果您正在運行 Oracle 12c 及更高版本，您也可以將MATCH_RECOGNIZE子句用于相同目的。

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

uj5u.com熱心網友回復：

從 Oracle 12 開始，您可以使用MATCH_RECOGNIZE來執行逐行處理：

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
)

其中，對于樣本資料：

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

輸出：

物品類別從日期迄今為止價值

一個 1 2022-01-01 00:00:00 2022-01-02 00:00:00 500

一個 1 2022-01-03 00:00:00 2022-01-03 00:00:00 80000

一個 1 2022-01-04 00:00:00 2022-01-05 00:00:00 500

一個 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

物品	類別	從日期	迄今為止	價值
一個	1	2022-01-01 00:00:00	2022-01-02 00:00:00	500
一個	1	2022-01-03 00:00:00	2022-01-03 00:00:00	80000
一個	1	2022-01-04 00:00:00	2022-01-05 00:00:00	500
一個	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<>在這里擺弄

uj5u.com熱心網友回復：

這是一項GROUP BY使用的作業TRUNC(daytime, 'MM')。TRUNC()與日期一起使用時，會將它們截斷到日歷/時鐘周期的開頭。

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

或者，您也可以避免使用那些晦澀難懂的 Oracle 日期格式說明符，例如'MM'使用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

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/408372.html

標籤：

上一篇：使用更多條件PL/SQL從另一個表更新資料

下一篇：即使滿足條件，存盤程序也不更新列