How to group by value to identify min and max date, but in specific order?

Viewed 240

I tried to find something here but I didn't find my use-case. I hope you can assist me. At first my table which is available:

STATION_NUMBER PART_NO BOOK_DATE
11111 A 2021-08-01 6:00:00
11111 A 2021-08-01 6:05:00
11111 A 2021-08-01 6:07:00
11111 A 2021-08-01 6:08:00
11111 B 2021-08-01 7:10:00
11111 B 2021-08-01 7:13:00
11111 B 2021-08-01 7:15:00
11111 B 2021-08-01 7:25:00
11111 A 2021-08-01 8:10:00
11111 A 2021-08-01 8:12:00
11111 A 2021-08-01 8:16:00
11111 A 2021-08-01 8:19:00
22222 A 2021-08-01 6:00:00
22222 A 2021-08-01 6:05:00
22222 A 2021-08-01 6:07:00
22222 A 2021-08-01 6:08:00
22222 B 2021-08-01 7:10:00
22222 B 2021-08-01 7:13:00
22222 B 2021-08-01 7:15:00
22222 B 2021-08-01 7:25:00
22222 A 2021-08-01 8:10:00
22222 A 2021-08-01 8:12:00
22222 A 2021-08-01 8:16:00
22222 A 2021-08-01 8:19:00

Following result what I want to get:

STATION_NUMBER PART_NO START_BOOK_DATE END_BOOK_DATE
11111 A 2021-08-01 6:00:00 2021-08-01 6:08:00
11111 B 2021-08-01 7:10:00 2021-08-01 7:25:00
11111 A 2021-08-01 8:10:00 2021-08-01 8:19:00
22222 A 2021-08-01 6:00:00 2021-08-01 6:08:00
22222 B 2021-08-01 7:10:00 2021-08-01 7:25:00
22222 A 2021-08-01 8:10:00 2021-08-01 8:19:00

I tried to solve it with this query but I didn't what I expect

SELECT PART_NO,
      STATION_NUMBER,
      GROUP_NUMBER,
      MIN(BOOK_DATE) START_BOOK_DATE,
      MAX(BOOK_DATE) END_BOOK_DATE
FROM(
    SELECT PART_NO,
           STATION_NUMBER,
           BOOK_DATE,
           IS_CHANGED,
           RANK() OVER (ORDER BY PART_NO,IS_CHANGED) GROUP_NUMBER
    FROM(
        SELECT PART_NO,
        STATION_NUMBER,
        BOOK_DATE,
        CASE 
            WHEN NOT LEAD(PART_NO, 1) OVER (ORDER BY BOOK_DATE) = PART_NO
            THEN ROWNUM
            ELSE 0
        END IS_CHANGED
        FROM PROD_DATA
        WHERE STATION_NUMBER in ('11111','22222')
        AND BOOK_DATE BETWEEN TO_TIMESTAMP('01.08.2021 05:00:00', 'DD.MM.YYYY HH24:MI:SS') and TO_TIMESTAMP('01.08.2021 12:00:00', 'DD.MM.YYYY HH24:MI:SS')
        ORDER BY BOOK_DATE
    )ORDER BY BOOK_DATE
) GROUP BY STATION_NUMBER, PART_NO, GROUP_NUMBER

I must group by STATION_NUMBER and PART_NUMBER but I need the first and last BOOK_DATE from chronologic perspective. PART_NUMBER and/or STATION_NUMBER change is the trigger for me to calculate the new line.

3 Answers

Your problem falls in the class of problems known as "gaps and islands problems" (if you want to research further, Google for that phrase).

In Oracle 11 and earlier, you can use analytic functions to get the desired result. The method is known as the "tabibitosan method" or "fixed differences method".

The key step comes first (in the subquery in the with clause below): compute the sequential number as grouped (partitioned) by station number only, and separately as partitioned by station and part number. The difference is constant in a sequence of consecutive rows where the part number is the same, and then that difference jumps to a different value when a new such sequence begins. Then you use that for grouping in the outer query.

with 
  prep as (
    select pd.*,
           row_number() over (partition by station_number order by book_date)
         - row_number() over (partition by station_number, part_no
                                  order by book_date) as grp
    from   prod_date pd
  )
select station_number, part_no, min(book_date) as start_book_date,
       max(book_date) as end_book_date
from   prep
group  by station_number, part_no, grp
order  by station_number, start_book_date
;

STATION_NUMBER PART_NO START_BOOK_DATE     END_BOOK_DATE      
-------------- ------- ------------------- -------------------
         11111 A       2021-08-01 06:00:00 2021-08-01 06:08:00
         11111 B       2021-08-01 07:10:00 2021-08-01 07:25:00
         11111 A       2021-08-01 08:10:00 2021-08-01 08:19:00
         22222 A       2021-08-01 06:00:00 2021-08-01 06:08:00
         22222 B       2021-08-01 07:10:00 2021-08-01 07:25:00
         22222 A       2021-08-01 08:10:00 2021-08-01 08:19:00

From Oracle 12, this is what MATCH_RECOGNIZE is for:

SELECT *
FROM   prod_date
MATCH_RECOGNIZE(
  PARTITION BY station_number
  ORDER     BY book_date
  MEASURES
    FIRST(part_no) AS part_no,
    FIRST(book_date) AS start_book_date,
    LAST(book_date) AS end_book_date
  ONE ROW PER MATCH
  PATTERN (same_part+)
  DEFINE
    same_part AS FIRST(part_no) = part_no
)

Which, for the sample data:

CREATE TABLE prod_date (STATION_NUMBER, PART_NO, BOOK_DATE) AS
SELECT 11111,   'A',    DATE '2021-08-01' + INTERVAL '6:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111,   'A',    DATE '2021-08-01' + INTERVAL '6:05:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111,   'A',    DATE '2021-08-01' + INTERVAL '6:07:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111,   'A',    DATE '2021-08-01' + INTERVAL '6:08:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111,   'B',    DATE '2021-08-01' + INTERVAL '7:10:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111,   'B',    DATE '2021-08-01' + INTERVAL '7:13:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111,   'B',    DATE '2021-08-01' + INTERVAL '7:15:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111,   'B',    DATE '2021-08-01' + INTERVAL '7:25:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111,   'A',    DATE '2021-08-01' + INTERVAL '8:10:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111,   'A',    DATE '2021-08-01' + INTERVAL '8:12:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111,   'A',    DATE '2021-08-01' + INTERVAL '8:16:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111,   'A',    DATE '2021-08-01' + INTERVAL '8:19:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222,   'A',    DATE '2021-08-01' + INTERVAL '6:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222,   'A',    DATE '2021-08-01' + INTERVAL '6:05:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222,   'A',    DATE '2021-08-01' + INTERVAL '6:07:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222,   'A',    DATE '2021-08-01' + INTERVAL '6:08:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222,   'B',    DATE '2021-08-01' + INTERVAL '7:10:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222,   'B',    DATE '2021-08-01' + INTERVAL '7:13:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222,   'B',    DATE '2021-08-01' + INTERVAL '7:15:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222,   'B',    DATE '2021-08-01' + INTERVAL '7:25:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222,   'A',    DATE '2021-08-01' + INTERVAL '8:10:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222,   'A',    DATE '2021-08-01' + INTERVAL '8:12:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222,   'A',    DATE '2021-08-01' + INTERVAL '8:16:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222,   'A',    DATE '2021-08-01' + INTERVAL '8:19:00' HOUR TO SECOND FROM DUAL;

Outputs:

STATION_NUMBER PART_NO START_BOOK_DATE END_BOOK_DATE
11111 A 2021-08-01 06:00:00 2021-08-01 06:08:00
11111 B 2021-08-01 07:10:00 2021-08-01 07:25:00
11111 A 2021-08-01 08:10:00 2021-08-01 08:19:00
22222 A 2021-08-01 06:00:00 2021-08-01 06:08:00
22222 B 2021-08-01 07:10:00 2021-08-01 07:25:00
22222 A 2021-08-01 08:10:00 2021-08-01 08:19:00

db<>fiddle here

  • Within inline view t, the end rows of each subgroup are spotted in the gaps column.
  • Within inline view tt, all the rows from the inline view t with null value in the gaps column are filled using first_value analytic function.
  • Finally, I group the rows from the inline view tt by STATION_NUMBER, PART_NO, GRP columns, and then I use min and max aggregate funcions to get desired output.
select STATION_NUMBER, PART_NO, min(BOOK_DATE) START_BOOK_DATE, max(BOOK_DATE) END_BOOK_DATE
from (
  select STATION_NUMBER, PART_NO, BOOK_DATE, GAPS
      , FIRST_VALUE(GAPS ignore nulls) 
          over( partition by STATION_NUMBER order by BOOK_DATE 
                ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) grp
  from (
    select 
      STATION_NUMBER, PART_NO, BOOK_DATE
      , case 
          when PART_NO != lead(PART_NO, 1, '-'||PART_NO)over(partition by STATION_NUMBER order by BOOK_DATE)
            then row_number()over(partition by STATION_NUMBER order by BOOK_DATE)
          else null
        end gaps
    from PROD_DATA
  )t
)tt
group by STATION_NUMBER, PART_NO, GRP
order by STATION_NUMBER, GRP
;

demo

Related