Ordonner suivant une condition des groupes de lignes

**lookmehard** · 13/07/2011, 11h18

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 :

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

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 :

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

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** · 13/07/2011, 12h07

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
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 :

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

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** · 13/07/2011, 13h48

bonjour,

Avec de la recursion ca semble ok :

Code :

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

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 :

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

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

**Waldar** · 13/07/2011, 14h56

Plus simple je pense :

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
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 :

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 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** · 13/07/2011, 15h12

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 :

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
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** · 13/07/2011, 15h15

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.

**Yanika_bzh** · 13/07/2011, 13h58

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** · 13/07/2011, 14h48

Merci pour vos réponses, j'ai choisi d'opter pour la récursivité 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
 
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

**punkoff** · 13/07/2011, 15h43

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** · 13/07/2011, 16h11

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 :

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
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

Ordonner suivant une condition des groupes de lignes

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager