Sous requête Oracle

Version imprimable

31/10/2011, 14h26
contact32

Sous requête Oracle

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?

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.

31/10/2011, 17h06
contact32

Bonjour,

C'est vraiment excellent :ccool: 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.

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.

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

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)

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.

03/11/2011, 09h49
contact32

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

Bonne journée!