Comment grouper par valeur pour identifier les dates min et max, mais dans un ordre spécifique ?

Question

J'ai essayé de trouver quelque chose ici mais je n'ai pas trouvé mon cas d'utilisation. J'espère que vous pourrez m'aider. Tout d'abord ma table qui est disponible :

NOMBRE DE STATIONS

PART_NO

DATE_DU_LIVRE

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

Résultat suivant ce que je veux obtenir :

NOMBRE DE STATIONS

PART_NO

DATE_DU_LIVRE_DE_DÉBUT

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

J'ai essayé de résoudre le problème avec cette requête, mais je n'ai pas obtenu les résultats escomptés.

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

Je dois regrouper par numéro de station et numéro de pièce, mais j'ai besoin de la première et de la dernière date du livre d'un point de vue chronologique. Le changement de PART_NUMBER et/ou STATION_NUMBER est le déclencheur du calcul de la nouvelle ligne.

Answer 1

Votre problème fait partie de la catégorie des problèmes connus sous le nom de "problèmes de lacunes et d'îlots" (si vous souhaitez faire des recherches plus approfondies, recherchez cette expression sur Google).

Dans Oracle 11 et les versions antérieures, vous pouvez utiliser des fonctions analytiques pour obtenir le résultat souhaité. Cette méthode est connue sous le nom de "méthode tabibitosan" ou "méthode des différences fixes".

L'étape clé vient en premier (dans la sous-requête de la rubrique with ci-dessous) : calculer le numéro séquentiel en le regroupant (partitionné) par numéro de station uniquement, et séparément en le partitionnant par numéro de station et de pièce. La différence est constante dans une séquence de lignes consécutives où le numéro de pièce est le même, puis cette différence passe à une valeur différente lorsqu'une nouvelle séquence de ce type commence. Elle est ensuite utilisée pour le regroupement dans la requête externe.

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

Answer 2

Voici ce que dit Oracle 12 MATCH_RECOGNIZE est destiné à :

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
)

Ce qui, pour les données de l'échantillon :

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;

Sorties :

NOMBRE DE STATIONS

PART_NO

DATE_DU_LIVRE_DE_DÉBUT

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 aquí_

Answer 3

• Dans la vue en ligne, les dernières lignes de chaque sous-groupe sont repérées dans la colonne des lacunes.
• Dans la vue en ligne tt, toutes les lignes de la vue en ligne t avec une valeur nulle dans la colonne des lacunes sont remplies à l'aide de la fonction analytique first_value.

• Enfin, je regroupe les lignes de la vue en ligne tt par les colonnes STATION_NUMBER, PART_NO, GRP, puis j'utilise les fonctions d'agrégation min et max pour obtenir le résultat souhaité.

  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 ;

démo