Блог

Я пытался найти что-то здесь, но не нашел своего варианта использования. Я надеюсь, что вы сможете мне помочь. Сначала мой столик, который доступен:

Следующий результат, который я хочу получить:

Я попытался решить эту проблему с помощью этого запроса, но я сделал не то, что ожидал

 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
 

Я должен сгруппироваться по НОМЕРУ СТАНЦИИ и НОМЕРУ ДЕТАЛИ, но мне нужна дата первой и последней КНИГИ с хронологической точки зрения. Изменение номера ДЕТАЛИ и/или НОМЕРА СТАНЦИИ является для меня триггером для расчета новой строки.

Ваша проблема относится к классу проблем, известных как «проблемы пробелов и островов» (если вы хотите продолжить исследование, найдите эту фразу в Google).

В Oracle 11 и более ранних версиях вы можете использовать аналитические функции для получения желаемого результата. Этот метод известен как «метод табибитозана» или «метод фиксированных различий».

Сначала выполняется ключевой шаг (в with подзапросе в приведенном ниже предложении): вычислите порядковый номер, сгруппированный (разделенный) только по номеру станции, и отдельно, разделенный по станции и номеру детали. Разница постоянна в последовательности последовательных строк, где номер детали одинаков, а затем эта разница переходит в другое значение, когда начинается новая такая последовательность. Затем вы используете это для группировки во внешнем запросе.

 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
 

Начиная с Oracle 12, это то, что MATCH_RECOGNIZE предназначено для:

 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
)
 

Который, для выборочных данных:

 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;
 

Выходы:

НОМЕР СТАНЦИИ	ЧАСТЬ_НО	START_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

бд<>скрипка <>здесь

• В встроенном представлении t конечные строки каждой подгруппы отображаются в столбце пробелы.
• В встроенном представлении tt все строки из встроенного представления t с нулевым значением в столбце пробелы заполняются с помощью аналитической функции first_value.
• Наконец, я группирую строки из встроенного представления tt по столбцам STATION_NUMBER, PART_NO, GRP, а затем использую функции агрегирования min и max для получения желаемого результата.

 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
;
 

ДЕМОНСТРАЦИЯ