Sous requête Oracle [Résolu]

[contact32]

Bonjour,

J'ai un requete à faire, mais je bloque... Je suis assez léger en sql.

Donc, j'ai le tableau suivant:
Pers couleur date_debut date_fin
A bleu 18/11/2010 19/02/2011
A vert 20/02/2011 05/05/2011
A vert 06/05/2011 31/12/2099
B vert 18/11/2010 05/02/2011
B bleu 06/02/2011 09/04/2011
B vert 10/04/2011 31/12/2099
C vert 18/11/2010 25/03/2011
C bleu 26/03/2011 31/12/2099

Je ne dois garder que les occurrences pour chaque personne ayant pour derniere couleur vert (ici A et B).
ET uniquement la ligne ayant la couleur verte et qui a été la première occurrence d'une suite de vert (ici la deuxième ligne de A et la dernière de B).

Je n'arrive pas construire ma requête.

Quelqu'un a une idée?

[skuatamad]

Que donne cette requête non testée ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
  WITH t AS (
SELECT pers, couleur, date_debut, date_fin,
       lag(couleur)  over (partition BY pers ORDER BY date_debut) AS prev_couleur,
       lead(couleur) over (partition BY pers ORDER BY date_debut) AS next_couleur,
       case when couleur <> nvl(lag(couleur)  over (partition BY pers ORDER BY date_debut),'ZZZ')
             AND couleur =  nvl(lead(couleur) over (partition BY pers ORDER BY date_debut),couleur)
            then 1
        end AS debut_liste
  FROM latable
)
SELECT pers, couleur, date_debut, date_fin
  FROM t
 WHERE debut_liste = 1
   AND couleur = 'vert'
   AND pers IN (SELECT pers
                  FROM t
                 WHERE couleur = 'vert'
                   AND next_couleur IS NULL)

Ca ne tient pas compte d'un éventuel cas où il y aurait plusieurs liste de vert pour une personne.
Si ce cas est envisageable il faudrait expliquer quelle liste doit être retenue.

[contact32]

Bonjour,

C'est vraiment excellent même si j'ai du mal à comprendre l'ensemble de la requête

Merci pour ton aide.

J'attends de voir si j'ai besoin d'un petit complément avant de mettre résolu.

[skuatamad]

Pour mieux comprendre la requête regarde LAG et LEAD
Puis execute le WITH seul pour visualiser les données :

Code :

1
2
3
4
5
6
7
8
9
10
11
WITH t AS (
SELECT pers, couleur, date_debut, date_fin,
       lag(couleur)  over (partition BY pers ORDER BY date_debut) AS prev_couleur,
       lead(couleur) over (partition BY pers ORDER BY date_debut) AS next_couleur,
       case when couleur <> nvl(lag(couleur)  over (partition BY pers ORDER BY date_debut),'ZZZ')
             AND couleur =  nvl(lead(couleur) over (partition BY pers ORDER BY date_debut),couleur)
            then 1
        end AS debut_liste
  FROM latable
)
SELECT * FROM

Le reste n'est qu'un filtre assez simple.

[contact32]

Bonjour,

J'ai bien regardé, ce sont des fonctions analytiques. C'est vraiment performant!

Pour répondre à la remarque de ton premier message, j'aimerais pouvoir indiquer que la suite qu'il faut utiliser est la dernière (celle où la dernière ligne de la suite a pour date de fin 31/12/2099).
J'ai essayé le code ci-dessous mais cela ne marche pas (il ne me renvoie que les lignes dont la date de fin est le 31/12/2099):

Code :

1
2
3
4
5
6
7
8
9
SELECT pers, couleur , DATE_DEBU, DATE_FIN
  FROM t
 WHERE debut_liste = 1
   AND couleur = 'vert'
   AND NUMR_PERS IN (SELECT NUMR_PERS
                  FROM t
                 WHERE couleur = 'vert'
                   AND next_couleur IS NULL
                   AND  DATE_FIN='31/12/2099')

[skuatamad]

En incrémentant debut_liste et en prenant la valeur max ça devrait fonctionner :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
  WITH t AS (
SELECT pers, couleur, date_debut, date_fin,
       lag(couleur)  over (partition BY pers ORDER BY date_debut) AS prev_couleur,
       lead(couleur) over (partition BY pers ORDER BY date_debut) AS next_couleur,
       case when couleur <> nvl(lag(couleur)  over (partition BY pers ORDER BY date_debut),'ZZZ')
             AND couleur =  nvl(lead(couleur) over (partition BY pers ORDER BY date_debut),couleur)
            then row_number() over (partition BY pers ORDER BY date_debut)
        end AS debut_liste
  FROM latable
)
SELECT pers, couleur, date_debut, date_fin
  FROM t
 WHERE debut_liste = (SELECT max(t2.debut_liste) FROM t t2 WHERE t2.pers = t.pers AND t2.couleur = 'vert')
   AND couleur = 'vert'
   AND pers IN (SELECT pers
                  FROM t
                 WHERE couleur = 'vert'
                   AND next_couleur IS NULL)

[skuatamad]

Sans sous-requête supplémentaire, ça pourrait donner ça :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
  WITH t AS (
SELECT pers, couleur, date_debut, date_fin,
       lag(couleur)  over (partition BY pers ORDER BY date_debut) AS prev_couleur,
       lead(couleur) over (partition BY pers ORDER BY date_debut) AS next_couleur,
       case when couleur <> nvl(lag(couleur)  over (partition BY pers ORDER BY date_debut),'ZZZ')
             AND couleur =  nvl(lead(couleur) over (partition BY pers ORDER BY date_debut),couleur)
            then row_number() over (partition BY pers ORDER BY date_debut)
        end AS debut_liste
  FROM latable
)
SELECT pers, 
       max(couleur) keep (dense_rank first ORDER BY debut_liste DESC), 
       max(date_debut) keep (dense_rank first ORDER BY debut_liste DESC), 
       max(date_fin) keep (dense_rank first ORDER BY debut_liste DESC)
  FROM t
 WHERE couleur = 'vert'
   AND pers IN (SELECT pers
                  FROM t
                 WHERE couleur = 'vert'
                   AND next_couleur IS NULL)
 GROUP BY pers

Je ne sais pas ce qui serait le plus performant.

[edit]
Je viens de faire un test et la requête avec MAX GROUP BY devrait en fait s'écrire :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
  WITH t AS (
SELECT pers, couleur, date_debut, date_fin,
       lag(couleur)  over (partition BY pers ORDER BY date_debut) AS prev_couleur,
       lead(couleur) over (partition BY pers ORDER BY date_debut) AS next_couleur,
       case when couleur <> nvl(lag(couleur)  over (partition BY pers ORDER BY date_debut),'ZZZ')
             AND couleur =  nvl(lead(couleur) over (partition BY pers ORDER BY date_debut),couleur)
            then row_number() over (partition BY pers ORDER BY date_debut)
        end AS debut_liste
  FROM latable
)
SELECT pers, 
       max(couleur) keep (dense_rank first ORDER BY debut_liste DESC NULLS LAST), 
       max(date_debut) keep (dense_rank first ORDER BY debut_liste DESC NULLS LAST), 
       max(date_fin) keep (dense_rank first ORDER BY debut_liste DESC NULLS LAST)
  FROM t
 WHERE couleur = 'vert'
   AND pers IN (SELECT pers
                  FROM t
                 WHERE couleur = 'vert'
                   AND next_couleur IS NULL)
 GROUP BY pers

L'autre requête semble fonctionner.

[contact32]

Merci beaucoup, la seconde requête marche très bien.

Bonne journée!