Select SQL Oracle

**zak-mouk** · 06/02/2018, 20h47

Bonjour à tous,

Je travaille sur une requête SQL pour un KPI à partir d'une table d'historique (ex : HisTable), supposant que cette table contient les données dessous :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
NumSeq || articleID ||  Statut  || Date_Action
______ || _________ || ________ || ___________
   1   || 111       || Submit   ||  20/12/2017
   2   || 111       || Approuve ||  28/12/2017
   3   || 111       || Approuve ||  04/01/2018
   4   || 111       || Accept   ||  10/01/2018
   1   || 222       || Submit   ||  02/01/2018
   2   || 222       || Approuve ||  03/01/2018
   3   || 222       || Accept   ||  20/01/2018
   4   || 222       || Approuve ||  24/01/2018
   1   || 333       || Submit   ||  25/12/2017
   2   || 333       || Approuve ||  07/01/2018
   3   || 333       || Approuve ||  15/01/2018

Il peut avoir d'autres statuts "Approuve" après l'acceptation sur l'article.

L'objectif est d'afficher pour article, la dernière (récente) date d'approbation avant la date d'acceptation s'elle existe, et la date d'acceptation s'il existe sinon un champs null.

Pour faire simple, y'a deux cas à traiter :
- Si le statut "Accept" pour l'article existe : on affiche articleID + date dernière approbation (Accept) avant la date d'acceptation + la date d'acceptation.
- Si le statut "Accept" n'existe pas encore : on afficher articleId + date dernière approbation + NULL (date acceptation = NULL).

Code :

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

1
2
3
4
5
articleID || Date_Action_Appr || Date_Action_Accep
_________ || ________________ || _________________
 111      ||  04/01/2018      ||    10/01/2018 
 222      ||  03/01/2018      ||    20/01/2018
 222      ||  15/01/2018      ||

j'ai réussi à afficher les deux articles accepté et je n'ai pas réussi à afficher le troisième qui n'est pas encore accepté.

Toute aide est précieux et je vous en remercie d'avance.

**McM** · 07/02/2018, 10h54

Bonjour,

Tu as fait une erreur dans le résultat attendu, la dernière ligne c'est l'article 333 et pas 222

Code :

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

1
2
3
4
5
articleID|| Date_Action_Appr || Date_Action_Accep
________ ||_________________|| ______________
111      || 04/01/2018      || 10/01/2018
222      || 03/01/2018      || 20/01/2018
222      || 15/01/2018      ||

Si j'ai bien compris, il faut que l'article ait au moins une ligne 'approuve' ou une ligne 'accept' pour ressortir, et que tu n'as besoin que des données de ces lignes, donc il suffit déjà de limiter à ces 2 états

Ensuite, un simple groupement avec un max suffit (+ decode)

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
WITH T AS
(SELECT 1 numseq,  111 idart, 'Submit' action, TO_DATE('20/12/2017', 'DD/MM/RRRR') dteaction FROM DUAL            
UNION ALL SELECT 2 numseq,  111 idart, 'Approuve' action, TO_DATE('28/12/2017', 'DD/MM/RRRR') dteaction FROM DUAL
UNION ALL SELECT 3 numseq,  111 idart, 'Approuve' action, TO_DATE('04/01/2018', 'DD/MM/RRRR') dteaction FROM DUAL
UNION ALL SELECT 4 numseq,  111 idart, 'Accept' action, TO_DATE('10/01/2018', 'DD/MM/RRRR') dteaction FROM DUAL  
UNION ALL SELECT 1 numseq,  222 idart, 'Submit' action, TO_DATE('02/01/2018', 'DD/MM/RRRR') dteaction FROM DUAL  
UNION ALL SELECT 2 numseq,  222 idart, 'Approuve' action, TO_DATE('03/01/2018', 'DD/MM/RRRR') dteaction FROM DUAL
UNION ALL SELECT 3 numseq,  222 idart, 'Accept' action, TO_DATE('20/01/2018', 'DD/MM/RRRR') dteaction FROM DUAL  
UNION ALL SELECT 4 numseq,  222 idart, 'Approuve' action, TO_DATE('24/01/2018', 'DD/MM/RRRR') dteaction FROM DUAL
UNION ALL SELECT 1 numseq,  333 idart, 'Submit' action, TO_DATE('25/12/2017', 'DD/MM/RRRR') dteaction FROM DUAL  
UNION ALL SELECT 2 numseq,  333 idart, 'Approuve' action, TO_DATE('07/01/2018', 'DD/MM/RRRR') dteaction FROM DUAL
UNION ALL SELECT 3 numseq,  333 idart, 'Approuve' action, TO_DATE('15/01/2018', 'DD/MM/RRRR') dteaction FROM DUAL
)
SELECT idart, MAX(DECODE(action, 'Approuve', dteaction)) last_approb, MAX(DECODE(action, 'Accept', dteaction))  last_accept
FROM T
WHERE action IN ('Approuve', 'Accept')
GROUP BY idart
ORDER BY 1

Code :

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

1
2
3
4
IDART	LAST_APPROB	LAST_ACCEPT
111	04/01/2018	10/01/2018
222	24/01/2018	20/01/2018
333	15/01/2018

**zak-mouk** · 07/02/2018, 11h29

Bonjour McM,

Merci pour ta réponse. Effectivement j'ai fais une erreur, c'est bien 333 et pas 222.

En fait, tous les articles ont le statut 'approuve' contrairement au statut 'accept' (par exemple, des commandes qui sont en cours de traitement sont en attente d'acceptation).

Pour ta requête, au niveau de la première partie t'as utilisé des valeurs en dur et ce n'est pas ce que je voulais. La requête que j'ai fais au début qui permet d'afficher juste les articles acceptés est celle ci :

Code :

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

1
2
3
4
5
select HT1.articleID , HT1.Date_Action as Date_Action_Appr, HT2.Date_Action as Date_Action_Accep from HisTable HT1
join HisTable HT2 on (HT1.articleID = HT2.articleID) 
where HT1.Statut ='Approuve'
and HT2.Statut = 'Accept'
and HT1.NumSeq = HT2.NumSeq - 1

Mais je voudrais bien afficher les articles en attente d'acceptation, pas que ceux qui sont acceptés.

**McM** · 07/02/2018, 11h45

Ma première partie (WITH T..) constitue simplement un jeu de données

Avec ce SELECT tu devrais avoir le résultat réel

Code :

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

1
2
3
4
5
SELECT articleID, MAX(DECODE(Statut , 'Approuve', Date_Action)) Date_Action_Appr, MAX(DECODE(Statut , 'Accept', Date_Action))  Date_Action_Accep
FROM HisTable
WHERE Statut IN ('Approuve', 'Accept')
GROUP BY articleID
ORDER BY articleID

**zak-mouk** · 07/02/2018, 15h06

j'ai testé la requête, c'est presque bon, merci.

Il y'a un petit souci au niveau de l'articleid 222, si on fait

Code :

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

MAX(DECODE(Statut , 'Approuve', Date_Action))

on affiche la date du dernier statut 'Approuve' (numseq = 4) qui vient après l'e statut 'Accept' alors qu'il faut prendre la date du statut 'Approuve' de numseq = 2 (en gros il faut prendre la date du statut 'Approuve' avant l'acceptation == dans le cas au ou l'acceptation est faite).
Le souci est que si on fait

Code :

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

MIN(DECODE(Statut , 'Approuve', Date_Action))

, au niveau de l'articleid 111 on affichera la date de numseq = 2 au lieu de la date du numseq = 3

**Waldar** · 07/02/2018, 15h43

Essayez ainsi :

Code :

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

1
2
3
4
5
6
7
8
9
10
    select t1.articleId
         , max(t1.date_action) as Date_Action_Appr
         , t2.date_action      as Date_Action_Accep 
      from HisTable t1
 left join HisTable t2  on t2.articleId    = t1.articleId
                       and t2.date_action >= t1.date_action
                       and t2.Statut       = 'Accept'
     where t1.Statut = 'Approuve'
  group by t1.articleId
         , t2.date_action;

Edit : il y a encore un petit truc à résoudre, j'y reviendrai.

**McM** · 07/02/2018, 16h40

Oups

Je n'avais pas bien lu la demande, et même mon résultat était différent de celui attendu.. désolé.

Je corrige donc

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
WITH HisTable AS
(					SELECT 1 NumSeq,  111 articleID, 'Submit' Statut,	 	TO_DATE('20/12/2017', 'DD/MM/RRRR') Date_Actio FROM DUAL            
UNION ALL SELECT 2 NumSeq,  111 articleID, 'Approuve' Statut, TO_DATE('28/12/2017', 'DD/MM/RRRR') Date_Actio FROM DUAL
UNION ALL SELECT 3 NumSeq,  111 articleID, 'Approuve' Statut, TO_DATE('04/01/2018', 'DD/MM/RRRR') Date_Actio FROM DUAL
UNION ALL SELECT 4 NumSeq,  111 articleID, 'Accept' Statut, 	TO_DATE('10/01/2018', 'DD/MM/RRRR') Date_Actio FROM DUAL  
UNION ALL SELECT 1 NumSeq,  222 articleID, 'Submit' Statut, 	TO_DATE('02/01/2018', 'DD/MM/RRRR') Date_Actio FROM DUAL  
UNION ALL SELECT 2 NumSeq,  222 articleID, 'Approuve' Statut, TO_DATE('03/01/2018', 'DD/MM/RRRR') Date_Actio FROM DUAL
UNION ALL SELECT 3 NumSeq,  222 articleID, 'Accept' Statut, 	TO_DATE('20/01/2018', 'DD/MM/RRRR') Date_Actio FROM DUAL  
UNION ALL SELECT 4 NumSeq,  222 articleID, 'Approuve' Statut, TO_DATE('24/01/2018', 'DD/MM/RRRR') Date_Actio FROM DUAL
UNION ALL SELECT 1 NumSeq,  333 articleID, 'Submit' Statut, 	TO_DATE('25/12/2017', 'DD/MM/RRRR') Date_Actio FROM DUAL  
UNION ALL SELECT 2 NumSeq,  333 articleID, 'Approuve' Statut, TO_DATE('07/01/2018', 'DD/MM/RRRR') Date_Actio FROM DUAL
UNION ALL SELECT 3 NumSeq,  333 articleID, 'Approuve' Statut, TO_DATE('15/01/2018', 'DD/MM/RRRR') Date_Actio FROM DUAL
)
SELECT articleID, MAX(DECODE(Statut, 'Approuve', Date_Actio)) Date_Action_Appr, MAX(DECODE(Statut, 'Accept', Date_Actio)) Date_Action_Accep
FROM HisTable a
WHERE Statut = 'Accept'
	OR (Statut = 'Approuve' AND NumSeq <= NVL((SELECT MAX(b.NumSeq) FROM HisTable b WHERE b.articleID = a.articleID AND b.Statut = 'Accept'), NumSeq)
  		)
GROUP BY articleID
ORDER BY 1

Ca donne

Code :

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

1
2
3
4
IDART	LAST_APPROB	LAST_ACCEPT
111	04/01/2018	10/01/2018
222	03/01/2018	20/01/2018
333	15/01/2018

**Waldar** · 07/02/2018, 17h44

Comme McM a bien répondu au besoin, je me suis entraîné à sortir une solution avec un OUTER APPLY (12c minimum).

Je ne suis pas complètement satisfait de la requête, je l'espérais plus simple à écrire, mais elle donne le bon résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
     select t1.articleId
          , coalesce(max(t3.date_action), max(case t1.Statut when 'Approuve' then t1.date_action end)) as Date_Action_Appr
          , max(case t1.Statut when 'Accept' then t1.date_action end) as Date_Action_Accep
       from HisTable t1
outer apply (select max(t2.date_action) as date_action
               from HisTable t2
              where t2.Statut       = 'Approuve'
                and t1.Statut       = 'Accept'
                and t2.articleId    = t1.articleId
                and t2.date_action <= t1.date_action) t3
      where t1.Statut in ('Approuve', 'Accept')
   group by t1.articleId
   order by t1.articleId;

**zak-mouk** · 07/02/2018, 20h01

Bonsoir McM et Waldar,

McM: j'ai fais le test sur 3 éléments comme dans cet exemple et ça marche très BIEN, reste à l'adapter

, je te remercie beaucoup

Waldar : Je testerai aussi ta requête demain et je te tiens au courant

, merci beaucoup à toi aussi

**zak-mouk** · 08/02/2018, 09h04

Hello Waldar, je viens de tester ta requête mais il ne marche pas, y'a quelque chose qui manque. Merci en tout cas

.

**Waldar** · 08/02/2018, 11h49

Elle ne fonctionne qu'en 12c, mais c'était plus pour l'exercice de OUTER APPLY.
Utilisez simplement la requête de McM qui fonctionnera sur toutes les versions.

**McM** · 09/02/2018, 12h57

Merci Waldar pour la nouveauté OUTER APPLY que je connais pas.. Je vais regarder ça plus en détail.

**Scriuiw** · 09/02/2018, 14h58

De ce que j'ai compris du OUTER APPLY et CROSS APPLY, ce sont des instructions qui servent avant tout à de la compatibilité avec le monde SQL-SERVER
Peu d'intérêt du coup vis-à-vis des jointures standards

**aieeeuuuuu** · 14/02/2018, 15h34

Bonjour,

Une petite variante à base de fonctions fenêtrées :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
WITH tmp AS (
SELECT *
	   ,	  MAX(CASE WHEN action = 'Approuve' THEN dteaction END) OVER(PARTITION BY idart ORDER BY dteaction RANGE UNBOUNDED PRECEDING) as Date_Action_Appr
	   ,	  MAX(CASE WHEN action = 'Accept' THEN dteaction END) OVER(PARTITION BY idArt) AS Date_Action_Accep
	   ,	  ROW_NUMBER() OVER(PARTITION BY idart ORDER BY CASE WHEN action = 'Accept' THEN 0 ELSE 1 END, dteaction desc) AS Rn
FROM T
)
SELECT idart, Date_Action_Appr, Date_Action_Accep
FROM tmp
WHERE rn = 1

**zak-mouk** · 16/02/2018, 12h12

Bonjour McM,

J'ai une question par rapport à ta requête.
Tout d'abord j'ai adapté la requête à mon cas, j'ai remplacer MAX par MIN dans deux emplacement :

Code :

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

MIN(DECODE(Statut, 'Accept', Date_Actio)) Date_Action_Accep

et

Code :

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

SELECT MIN(b.NumSeq) FROM HisTable b WHERE b.articleID = a.articleID AND b.Statut = 'Accept'

et j'ai rajouté une condition sur la Date_Action_Appr

Code :

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

DECODE(Statut, 'Approuve', Date_Actio) BETWEEN '02/01/2017' AND '08/01/2017'

dans mon résultat j'ai le champs Date_Action_Accep == NULL
je voudrais savoir si c'est normal ou pas ?
Merci d'avance

**McM** · 16/02/2018, 12h20

C'est bizarre de remplacer le MAX par un MIN alors que ta demande d'origine était "L'objectif est d'afficher pour article, la dernière (récente) date d'approbation avant la date d'acceptation si elle existe"
Reposte ta requête que je vérifie, car je ne comprends pas où tu as mis ce DECODE(Statut, 'Approuve', Date_Actio) BETWEEN '02/01/2017' AND '08/01/2017'

**zak-mouk** · 16/02/2018, 13h47

Hello McM,
Je te remercie pour ton retour rapide.
En effet, l'erreur vient de moi, j'aurai du faire la condition WHERE avec "Date_Actio" tout court et pas avec "DECODE(Statut, 'Approuve', Date_Actio)". C'est faisant les tests que j'ai changé les MAX pour faire marcher la condition "DECODE(Statut, 'Approuve', Date_Actio)"

.
Merci encore

**tbc92** · 16/02/2018, 13h57

@Scriuiw

Dans certaines configurations, le Cross Apply permet d'améliorer la performance dans des proportions considérables. Temps d'exécution divisé par 10 dans mon cas.

**SQLpro** · 19/02/2018, 16h47

Envoyé par Scriuiw

De ce que j'ai compris du OUTER APPLY et CROSS APPLY, ce sont des instructions qui servent avant tout à de la compatibilité avec le monde SQL-SERVER
Peu d'intérêt du coup vis-à-vis des jointures standards

Pas du tout....

En fait la norme SQL avait proposé dans sa version 1999 l'opérateur LATERAL pour effectuer un produit cartésien appliqué à une fonction table. On ne peut pas en effet joindre une fonction table par un JOIN car les données de la fonction table ne sont pas statiques mais générées dynamiquement lors de l'arrivée d'un paramètres dans les arguments de la fonction table, argument qui vient le plus souvent d'une colonne d'une table.
Or pour construire un plan d'exécution, le moteur à besoin d'estimer le nombre de lignes et cela n'est pas possible pour une fonction table qui ne contient rien au moment de l'analyse.

Le seul défaut de l'opérateur LATERAL est qu'il ne renvoyait rien si l'argument n'existe pas (la table dont l'argument est extrait n'a pas de ligne) faisant disparaître du coup les données potentielles de la fonction table.

Devant cette lacune de la norme, Microsoft à proposé de remplacer LATERAL par APPLY (il n'ont d'ailleurs jamais utilisé LATERAL à cause de ce défaut), décliné en deux versions :
CROSS APPLY, le produit cartésien
et
OUTER APPLY, le produit cartésien renvoyant quand même une ligne en cas d'absence de référence des arguments.
La particularité de APPLY est que comme il n'existe pas de stats dessous, une cardinalité constante est utilisée en remplacement (par exemple 100 dans PostgreSQL, 1 dans SQL Server) ce qui peut s'avérer payant si l'on est à peu près sûr de son coût (clin d'œil à Pomalaix)...

Pour information, c'est en cours de normalisation, raison pour laquelle Oracle l'a récemment introduit (tout comme les collations qui viennent enfin d'être introduite sous Oracle (trente ans de retard...) en remplacement de l'imbitable et anormatif NLS...).

A +

Select SQL Oracle

SQL Oracle

Discussions similaires

Partager

Partager