Calcul du temps de changement de référence

**guillaume-info** · 17/03/2023, 17h12

Bonjour,

J'ai besoin d'aide sur une requête SQL que je dois réaliser.

J'ai des données de production dans une table.
Je dois extraire les temps de changement de référence.
L'idée est de trouver les changements sur la colonne "Reference", et calculer la différence entre la date de la ref précédente et la ref suivante.

Je voudrais le faire en SQL plutôt que par programme car le nombre de lignes est important selon la période choisie.

J'ai des compétences limitées en SQL. Je vous remercie par avance de bien vouloir m'aider.

Lien db-fiddle : https://www.db-fiddle.com/f/vA44ZtDk1VtWffz3w33L25/0

Exemple :
La table de production :

| Date | NumLigne | Reference | NumIncremental |
| ------------------- | -------- | --------- | -------------- |
| 2023-03-17 08:52:10 | 3 | REF_A | 8001234 |
| 2023-03-17 08:52:45 | 3 | REF_A | 8001235 |
| 2023-03-17 08:53:16 | 3 | REF_A | 8001236 |
| 2023-03-17 08:53:50 | 3 | REF_A | 8001237 |
| 2023-03-17 08:58:20 | 3 | REF_B | 2001238 |
| 2023-03-17 08:58:55 | 3 | REF_B | 2001239 |
| 2023-03-17 08:59:10 | 3 | REF_B | 2001240 |
| 2023-03-17 08:59:45 | 3 | REF_B | 2001241 |
| 2023-03-17 09:02:30 | 3 | REF_B | 2001242 |
| 2023-03-17 09:03:10 | 3 | REF_B | 2001243 |
| 2023-03-17 09:10:02 | 3 | REF_C | 3001244 |
| 2023-03-17 09:10:52 | 3 | REF_C | 3001245 |
| 2023-03-17 09:11:30 | 3 | REF_C | 3001246 |
| 2023-03-17 09:12:30 | 3 | REF_A | 8001247 |
| 2023-03-17 09:13:10 | 3 | REF_A | 8001248 |
| 2023-03-17 09:13:50 | 3 | REF_A | 8001249 |
| 2023-03-17 09:14:30 | 3 | REF_A | 8001250 |
| 2023-03-17 09:28:30 | 3 | REF_B | 2001251 |
| 2023-03-17 09:29:10 | 3 | REF_B | 2001252 |

Le résultat voulu :
| Date changement | Reference avant| Reference après | Durée|
| ----------------------- | ----------- | ---------- | -------------- |
| 2023-03-17 08:58:20 | REF_A | REF_B | 4:30
| 2023-03-17 09:10:02 | REF_B | REF_C | 6:52
| 2023-03-17 09:12:30 | REF_C | REF_A | 1:00
| 2023-03-17 09:28:30 | REF_A | REF_B | 14:00

**Waldar** · 17/03/2023, 19h00

Comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
  select DateChgt, OldRef, NewRef, Duree
    from (SELECT cast(@ReferencePre as char) as OldRef
               , (@ReferencePre <> Reference) AS statusChanged
               , @ReferencePre := Reference as NewRef
               , SEC_TO_TIME(TIMESTAMPDIFF(SECOND, @DatePre, Date)) as Duree
               , @DatePre := Date as DateChgt
            FROM production 
               , (SELECT @ReferencePre:=NULL, @DatePre:=null) AS d
         ) t
   where statusChanged
order by DateChgt;
 
| DateChgt            | OldRef | NewRef | Duree    |
| ------------------- | ------ | ------ | -------- |
| 2023-03-17 08:58:20 | REF_A  | REF_B  | 00:04:30 |
| 2023-03-17 09:10:02 | REF_B  | REF_C  | 00:06:52 |
| 2023-03-17 09:12:30 | REF_C  | REF_A  | 00:01:00 |
| 2023-03-17 09:28:30 | REF_A  | REF_B  | 00:14:00 |

https://www.db-fiddle.com/f/vA44ZtDk1VtWffz3w33L25/2

**guillaume-info** · 18/03/2023, 08h41

Merci, c'est top ce qu'on peut faire quand on maitrise.

Ça va beaucoup m'aider.

Bonne journée.

**Séb.** · 18/03/2023, 16h42

Ton sqlfiddle est sous MySQL 5, au cas où tu aurais en fait MySQL 8, avec une CTE et la fonction fenêtrée LAG() :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SET @@sql_mode = 'ANSI,TRADITIONAL';
 
WITH report AS (
	SELECT ALL
		CASE WHEN LAG("Reference") OVER (ORDER BY "Date" ASC) <> "Reference" THEN
			"Date"
		END AS "Date changement",
		CASE WHEN LAG("Reference") OVER (ORDER BY "Date" ASC) <> "Reference" THEN
			LAG("Reference") OVER (ORDER BY "Date" ASC)
		END AS "Référence avant",
		"Reference" AS "Référence après",
		CASE WHEN LAG("Reference") OVER (ORDER BY "Date" ASC) <> "Reference" THEN
			SEC_TO_TIME(TIMESTAMPDIFF(SECOND, LAG("Date") OVER (ORDER BY "Date" ASC), "Date"))
		END AS "Durée changement"
	FROM production 
)
SELECT ALL *
FROM report
WHERE "Date changement" IS NOT NULL
;

**guillaume-info** · 20/03/2023, 08h00

Oui ca fonctionne également.

Je vais pouvoir valider ça cette semaine avec la base de production Oracle. Je verrai quelle version fonctionne le mieux.

**Waldar** · 20/03/2023, 16h01

Vous êtes sur Oracle DB ou MySQL ?
Votre fiddle étant sur MySQL 5 je suis parti sur cette version, mais ça ne marchera pas pour Oracle DB.

**guillaume-info** · 20/03/2023, 19h43

Oui je suis sous Oracle. J'ai oublié de le préciser. Je pensais que la différence était minime.
Sur db-fiddle je n'avais pas ce choix.

**guillaume-info** · 20/03/2023, 20h19

J'ai essayé sur LiveSQL.Oracle.

J'arrive à avoir quelque chose d'à peu près équivalent avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
WITH report AS (
SELECT ALL
		CASE WHEN LAG(Reference) OVER (ORDER BY DateHeure ASC) <> Reference THEN
			LAG(DateHeure) OVER (ORDER BY DateHeure ASC)
		END AS Date_Avant,
    	CASE WHEN LAG(Reference) OVER (ORDER BY DateHeure ASC) <> Reference THEN
			DateHeure
		END AS Date_Apres,
		CASE WHEN LAG(Reference) OVER (ORDER BY DateHeure ASC) <> Reference THEN
			LAG(Reference) OVER (ORDER BY DateHeure ASC)
		END AS Reference_Avant,
    	CASE WHEN LAG(Reference) OVER (ORDER BY DateHeure ASC) <> Reference THEN
			Reference
		END AS Reference_Apres
	FROM production 
)
SELECT Date_Avant, Date_Apres, Reference_Avant, Reference_Apres, (Date_apres-Date_Avant)*1440 as Duree
FROM report
WHERE Date_Avant IS NOT NULL
;

**Waldar** · 21/03/2023, 13h30

Envoyé par guillaume-info

Oui je suis sous Oracle. J'ai oublié de le préciser. Je pensais que la différence était minime.

Oui rien à voir ! Ce n'est pas bien grave j'aurai appris quelque chose sur MySQL 5.

Sur Oracle vous pouvez utiliser MATCH_RECOGNIZE :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select DateChgt, OldRef, NewRef, Duree
  from production
       match_recognize(partition by NumLigne
                           order by dt
                           measures dt                                    as DateChgt
                                  , prev(Reference)                       as OldRef
                                  , Reference                             as NewRef
                                  , numtodsinterval(dt - prev(dt), 'day') as Duree
                            one row per match
                            pattern ( x+ )
                             define x as  Reference <> prev(Reference)
                      );
 
DateChgt             OldRef  NewRef  Duree
-------------------  ------  ------  -----------------------------
2023-03-17 08:58:20  REF_A   REF_B   +000000000 00:04:30.000000000
2023-03-17 09:10:02  REF_B   REF_C   +000000000 00:06:52.000000000
2023-03-17 09:12:30  REF_C   REF_A   +000000000 00:01:00.000000000
2023-03-17 09:28:30  REF_A   REF_B   +000000000 00:14:00.000000000

**guillaume-info** · 21/03/2023, 19h49

Merci, je vais pouvoir essayer ça demain ou jeudi.

Calcul du temps de changement de référence

Langage SQL

Discussions similaires

Partager

Partager