Ordonner suivant une condition des groupes de lignes [Résolu]

[lookmehard]

Bonjour à tous,
n'ayant toujours pas trouvé la solution à mon problème, j'ai décidé de vous poser la question.

J'effectue un reporting sur une base de donnée d'incidents, et j'aimerais organiser les actions d'un incident(traitement, escalade, redirection,....) par ordre de groupe de prise en charge, je m'explique :


Je peux classer ces actions par ordre chronologique (ORDRE_ACTION), chaque action a un identifiant (ACTION_ID)

Code :

1
2
3
4
5
6
7
8
9
    ORDRE_ACTION              GROUPE_ID               INCIDENT_ID   
        8                       29                       22423
        7                       29                       22423    
        6                       48                       22423
        5                      NULL                     22423
        4                       50                       22423
        3                       50                       22423
        2                       50                       22423
        1                       30                       22423

La où je bloque, c'est pour obtenir les périodes de prise en charge des différents groupe. J'aimerais obtenir ceci :

Code :

1
2
3
4
5
6
7
8
9
10
11

 ORDRE_PEC      ORDRE_ACTION              GROUPE_ID               INCIDENT_ID   
   5                8                           29                 22423
   5                7                           29                 22423    
   4                6                           48                 22423
   3                5                          NULL                22423
   2                4                           50                 22423
   2                3                           50                 22423
   2                2                           50                 22423
   1                1                           30                 22423

J'ai tout essayé, même les fonctions analytiques mais rien à faire,
Je travaille sous sql server ou oracle, merci d'avance pour vos réponses, cdt.

[Rams7s]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
WITH TMP AS
 (SELECT 1 AS ORDRE, 30 AS GROUPE FROM DUAL
  UNION
  SELECT 2 AS ORDRE, 50 AS GROUPE FROM DUAL
  UNION
  SELECT 3 AS ORDRE, 50 AS GROUPE FROM DUAL
  UNION
  SELECT 4 AS ORDRE, 50 AS GROUPE FROM DUAL
  UNION
  SELECT 5 AS ORDRE, NULL AS GROUPE FROM DUAL
  UNION
  SELECT 6 AS ORDRE, 48 AS GROUPE FROM DUAL
  UNION
  SELECT 7 AS ORDRE, 29 AS GROUPE FROM DUAL
  UNION
  SELECT 8 AS ORDRE, 29 AS GROUPE FROM DUAL)
SELECT T2.ORDRE_PREC, T1.ORDRE, T1.GROUPE
  FROM TMP T1,
       (SELECT GROUPE, ROWNUM AS ORDRE_PREC
          FROM (SELECT GROUPE, MIN(ORDRE) AS ORDRE_MIN
                  FROM TMP
                 GROUP BY GROUPE
                 ORDER BY ORDRE_MIN)) T2
 WHERE T1.GROUPE(+) = T2.GROUPE
 ORDER BY T2.ORDRE_PREC DESC, ORDRE DESC;

Mais ca ne va pas tout a fait.
Il y a un peu trop de vide, genre a la ligne 6. Par contre je me dit que peut-etre que quelqu'un d'autre reparer ce probleme de join avec un null.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
ORDRE_PREC      ORDRE     GROUPE
---------- ---------- ----------
         5          8         29
         5          7         29
         4          6         48
         3
         2          4         50
         2          3         50
         2          2         50
         1          1         30
 
8 rows selected.

[punkoff]

bonjour,

Avec de la recursion ca semble ok :

Code :

1
2
3
4
5
6
7
8
9
10
11
 
WITH tmp (incident_id, groupe_id, ordre_action, ordre_pec) AS (
SELECT  incident_id, groupe_id, ordre_action, 1 AS ordre_pec
FROM inc
WHERE ordre_action = 1
union ALL
SELECT  b.incident_id, b.groupe_id, b.ordre_action, case when a.groupe_id = b.groupe_id then a.ordre_pec else a.ordre_pec + 1 end
FROM tmp a
INNER JOIN  inc b ON b.ordre_action = a.ordre_action + 1)
 
SELECT * FROM tmp;

Résultat :

Code :

1
2
3
4
5
6
7
8
9
10
11
 
INCIDENT_ID GROUPE_ID ORDRE_ACTION ORDRE_PEC
-------------------------------------------------------
22423          30             1                    1
22423          50             2                    2
22423          50             3                    2
22423          50             4                    2
22423                          5                    3
22423          48             6                    4
22423          29             7                    5
22423          29             8                    5

[Yanika_bzh]

J'aurai tenté de passer par un count et une clause having pour ma part ...
Mais ca n'est qu'une idée
Bon courage

[lookmehard]

Merci pour vos réponses, j'ai choisi d'opter pour la récursivité avec

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
WITH tmp (incident_id, group_id, ordre_action, ordre_pec)  
AS (
SELECT  REQUEST_ID, group_id, ordre_action, 1 AS ordre_pec
FROM V_ACTION_ORDONNEE
WHERE ordre_action = 1                                                  
 
union ALL
 
 
 
SELECT  b.request_id, b.group_id, b.ordre_action, case when a.group_id = b.group_id then a.ordre_pec else a.ordre_pec + 1 end
FROM tmp a
INNER JOIN  V_ACTION_ORDONNEE b ON b.ordre_action = a.ordre_action +1 AND b.REQUEST_ID = a.incident_id)
 
SELECT * FROM tmp

En ajoutant à la jointure la dépendance des actions à l'incident : b.REQUEST_ID = a.incident_id

Cela fonctionne bien pour un ou deux incident mais pour l'ensemble de la table : 53000 actions provenant de 15000 incidents il n'arrive pas à terminer sa requête.

Le count m'a l'air compromis car j'ai besoin des valeurs de ligne précédent celle en cours de calcul.

La récursivité est peut être plus performante chez oracle, je vais tester.

Merci pour votre aide

[Waldar]

Plus simple je pense :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
WITH TMP AS
(
SELECT 1 AS ORDRE, 30 AS GROUPE FROM DUAL UNION ALL
SELECT 2         , 50           FROM DUAL UNION ALL
SELECT 3         , 50           FROM DUAL UNION ALL
SELECT 4         , 50           FROM DUAL UNION ALL
SELECT 5         , NULL         FROM DUAL UNION ALL
SELECT 6         , 48           FROM DUAL UNION ALL
SELECT 7         , 29           FROM DUAL UNION ALL
SELECT 8         , 29           FROM DUAL
)
  ,  SR AS
(
  SELECT GROUPE, row_number() over(ORDER BY min(ordre)) AS ordre_pec
    FROM TMP
GROUP BY groupe
)
  SELECT SR.ordre_pec, TMP.ORDRE, TMP.GROUPE
    FROM TMP
         INNER JOIN SR
           ON SR.groupe = TMP.groupe
           OR (SR.groupe IS NULL AND TMP.groupe IS NULL)
ORDER BY TMP.ORDRE DESC;
 
 ORDRE_PEC      ORDRE     GROUPE
---------- ---------- ----------
         5          8         29
         5          7         29
         4          6         48
         3          5           
         2          4         50
         2          3         50
         2          2         50
         1          1         30

Ou encore :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
WITH TMP AS
(
SELECT 1 AS ORDRE, 30 AS GROUPE FROM DUAL UNION ALL
SELECT 2         , 50           FROM DUAL UNION ALL
SELECT 3         , 50           FROM DUAL UNION ALL
SELECT 4         , 50           FROM DUAL UNION ALL
SELECT 5         , NULL         FROM DUAL UNION ALL
SELECT 6         , 48           FROM DUAL UNION ALL
SELECT 7         , 29           FROM DUAL UNION ALL
SELECT 8         , 29           FROM DUAL
)
  ,  SR AS
(
SELECT ORDRE, GROUPE
     , min(ordre) over(partition BY groupe ORDER BY ordre ASC) AS rn
  FROM TMP
)
  SELECT dense_rank() over(ORDER BY rn ASC) AS ordre_pec
       , ORDRE, GROUPE
    FROM SR
ORDER BY ORDRE DESC;

[lookmehard]

Merci Waldar pour ta réponse, le soucis c'est que je ne connais pas le nombre d'actions par incident à l'avance, en effet, Ordre_action commencera toujours à 1 mais selon les incidents(incident_id) il peut terminer à 18 ou 2.

Voyez le comme un arbre au final avec

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
incident_id1 -----------------ordre_action :1     ;;;  groupe_id :1  ;;;   1
                |
                 -----------------ordre_action :2     ;;;  groupe_id :1  ;;;   1
                |
                 -----------------ordre_action :3     ;;;  groupe_id :2  ;;;   2
                |
                 -----------------ordre_action :4     ;;;  groupe_id :3  ;;;   3
                |
                 -----------------ordre_action :5     ;;;  groupe_id :3  ;;;   3
                |
                 -----------------ordre_action :6     ;;;  groupe_id :3  ;;;   3
 
 
incident_id2 -----------------ordre_action :1     ;;;  groupe_id :9 ;;;   1
                |
                 -----------------ordre_action :2     ;;;  groupe_id :9 ;;;   1
                |
                 -----------------ordre_action :3     ;;;  groupe_id :4 ;;;   2
                |
                 -----------------ordre_action :4     ;;;  groupe_id :4 ;;;   2
                |
                 -----------------ordre_action :5     ;;;  groupe_id :4 ;;;   2
                |
                 -----------------ordre_action :6     ;;;  groupe_id :1 ;;;   3
                |
                 -----------------ordre_action :7     ;;;  groupe_id :1 ;;;   3              
                |
                 -----------------ordre_action :8     ;;;  groupe_id :1 ;;;   3

En espérant avoir été clair, merci

[Waldar]

Envoyé par lookmehard

Merci Waldar pour ta réponse, le soucis c'est que je ne connais pas le nombre d'actions par incident à l'avance, en effet, Ordre_action commencera toujours à 1 mais selon les incidents(incident_id) il peut terminer à 18 ou 2.

Ok, mais je ne vois pas où vous voulez en venir, rien n'est codé en dur dans les deux requêtes que je vous ai proposées.
Sauf le jeu de test pour reproduire votre table de départ, mais ce n'est qu'un jeu de test, pour vous la requête commence au-dessous.
D'ailleurs il faudra rajouter les incident_id dans les group by / partition by.

[Rams7s]

Dans le deuxieme exemple de waldar, pourquoi le order by ordre ASC a la ligne 15?

[punkoff]

Envoyé par lookmehard

Merci pour vos réponses, j'ai choisi d'opter pour la récursivité avec

En ajoutant à la jointure la dépendance des actions à l'incident : b.REQUEST_ID = a.incident_id

Cela fonctionne bien pour un ou deux incident mais pour l'ensemble de la table : 53000 actions provenant de 15000 incidents il n'arrive pas à terminer sa requête.

Le count m'a l'air compromis car j'ai besoin des valeurs de ligne précédent celle en cours de calcul.

La récursivité est peut être plus performante chez oracle, je vais tester.

Merci pour votre aide

Êtes vous sure d’avoir bien maitriser la récursivité quand vous faites votre test sur tous les incidents ..?

J'ai chargé une table avec 15000 incident diff * 9 actions

Récursion ou la 2eme solution de Waldar mettent à peu pres le même temps : entre 1min15 et 1min30 (c'est une fourchette sur plusieurs essais des solutions)

J'ai essayé avec des index, et je n'ai pas eu de gain perceptible.

Bref à vous de tester sur vos environnement pour utiliser la meilleur des 3.

[lookmehard]

Désolé waldar, je n'avais pas fait attention.

J'ai essayé les 2 solutions de waldar, Elle fonctionne mais pas completement :la prise en charge n'est pas respectée si un groupe apparaît 2 fois mais non successivement (ce qui est possible) :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
WITH TMP AS
(
SELECT 1 AS ORDRE, 30 AS GROUPE FROM DUAL UNION ALL
SELECT 2         , 50           FROM DUAL UNION ALL
SELECT 3         , 50           FROM DUAL UNION ALL
SELECT 4         , 50           FROM DUAL UNION ALL
SELECT 5         , NULL         FROM DUAL UNION ALL
SELECT 6         , 48           FROM DUAL UNION ALL
SELECT 7         , 29           FROM DUAL UNION ALL
SELECT 8         , 29           FROM DUAL UNION ALL
SELECT 9         , 48           FROM DUAL UNION ALL
SELECT 10        , 48           FROM DUAL UNION ALL
SELECT 11        , 29           FROM DUAL
)
  ,  SR AS
(
SELECT ORDRE, GROUPE
     , min(ordre) over(partition BY groupe ORDER BY ordre ASC) AS rn
  FROM TMP
)
  SELECT dense_rank() over(ORDER BY rn ASC) AS ordre_pec
       , ORDRE, GROUPE
    FROM SR
ORDER BY ORDRE DESC;

Merci pour vos réponses je retourne du coté de la récursion

[McM]

En testant la valeur précédente en fct analytique, afin de mettre un indicateur +1, puis en faisant la somme je pense que ça marche

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
WITH TMP AS
(
SELECT 1 AS ORDRE, 30 AS GROUPE FROM DUAL UNION ALL
SELECT 2         , 50           FROM DUAL UNION ALL
SELECT 3         , 50           FROM DUAL UNION ALL
SELECT 4         , 50           FROM DUAL UNION ALL
SELECT 5         , NULL         FROM DUAL UNION ALL
SELECT 6         , 48           FROM DUAL UNION ALL
SELECT 7         , 29           FROM DUAL UNION ALL
SELECT 8         , 29           FROM DUAL UNION ALL
SELECT 9         , 48           FROM DUAL UNION ALL
SELECT 10        , 48           FROM DUAL UNION ALL
SELECT 11        , 29           FROM DUAL
)
SELECT ordre, groupe, sum(p) over(ORDER BY ordre DESC) AS pec
FROM (SELECT ordre, groupe, 
        decode(lag(groupe, 1) over(ORDER BY ordre DESC), groupe, 0,1) AS p
    FROM TMP
   ) ORDER BY ordre DESC;
 
ORDRE	GROUPE	PEC
11	29	1
10	48	2
9	48	2
8	29	3
7	29	3
6	48	4
5	""	5
4	50	6
3	50	6
2	50	6
1	30	7

Edit : En fait le PEC est inversé par rapport à l'exemple du premier post, suffit donc d'enlever le DESC des ORDER BY des fonctions analytiques

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT ordre, groupe, sum(p) over(ORDER BY ordre) AS pec
FROM (SELECT ordre, groupe, 
        decode(lag(groupe, 1) over(ORDER BY ordre), groupe, 0,1) AS p
    FROM TMP
   ) ORDER BY ordre DESC;
 
ORDRE	GROUPE	PEC
11	29	7
10	48	6
9	48	6
8	29	5
7	29	5
6	48	4
5	""	3
4	50	2
3	50	2
2	50	2
1	30	1

[McM]

Attention au groupe NULL s'il intervient en premier !
Le lag(x,1) renvoie null si la ligne précédente n'existe pas.
Il faut :
Soit faire un NVL sur le groupe avec une valeur impossible
Soit tester la première ligne différemment (rajouter un tests sur le row_number) pour toujours mettre p=1

voici le code sans NVL

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
WITH TMP AS
(
SELECT 0 AS ORDRE, NULL AS GROUPE FROM DUAL UNION ALL
SELECT 1, 30 FROM DUAL UNION ALL
SELECT 2, 50 FROM DUAL UNION ALL
SELECT 3, 50 FROM DUAL UNION ALL
SELECT 4, 50 FROM DUAL UNION ALL
SELECT 5, NULL FROM DUAL UNION ALL
SELECT 6, 48  FROM DUAL UNION ALL
SELECT 7, 29  FROM DUAL UNION ALL
SELECT 8, 29  FROM DUAL UNION ALL
SELECT 9, 48  FROM DUAL UNION ALL
SELECT 10, 48 FROM DUAL UNION ALL
SELECT 11, 29 FROM DUAL )
SELECT ordre, groupe, sum(p) over(ORDER BY ordre) AS pec
FROM (
SELECT ordre, groupe, 
        case when row_number() over (ORDER BY ordre) = 1 then 1
        else decode(lag(groupe, 1) over(ORDER BY ordre), groupe, 0,1)
        end AS p
    FROM TMP
) ORDER BY ordre DESC;
 
ORDRE GROUPE PEC
11	29	8
10	48	7
9	48	7
8	29	6
7	29	6
6	48	5
5		4
4	50	3
3	50	3
2	50	3
1	30	2
0		1

[lookmehard]

Désolé de ne pas avoir répondu plus tôt j'étais à un festival ce week end,

Merci beaucoup!! ça fonctionne, je le passe le post en résolu bonne journée.