Explication comportement fonctions analytiques

Version imprimable

Bonsoir,
je m'essaye aux fonctions analytiques mais je rencontre quelques difficultés.
Pour mes tests j'ai le modèle suivant décrivant différents modèles (ID_MODELE) de véhicules/machines dont plusieurs prototypes (NUM_PROTOTYPE) sont mis à l'essai dans le temps
(à la date DATE_MEC).

Code:

1
2
3
4
5
6
7
 
ID_MODELE	 |NUM_PROTOTYPE |	DATE_MEC
1		 |1                       |16/09/2012
1		 |2                       |01/01/2012
2		 |3                       |01/01/2013
2		 |4                       |01/09/2012
2		 |5                       |01/08/2012

Pour chaque modèle, en fonction de la date courante,
je voudrais afficher le dernier numéro de prototype mis en circulation et sa date de mise en circulation ainsi
que le prochain à mettre en circulation et sa date de mise en circulation.

Ce qui devrait me donner le résultat suivant :

Code:

1
2
3
4
5
 
id_modele   |Numproto_prec       |dte_mec_prec	 |Numproto_next         |dte_mec_next
-------------------------------------------------------------------------
1           | 1                   |16/09/2012    |                     |
2           | 4                   |01/09/2012    |3                    |01/01/2013

J'ai donc écrit ma requête ainsi, mais je n'ai pas le résultat escompté :

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
with T as
(
    select 1 id_modele, 1 num_prototype, trunc(to_date('16/09/2012','DD/MM/YYYY')) DATE_MEC from dual union
    select 1 id_modele, 2 num_prototype, trunc(to_date('01/01/2012','DD/MM/YYYY')) DATE_MEC from dual union
    select 2 id_modele, 3 num_prototype, trunc(to_date('01/01/2013','DD/MM/YYYY')) DATE_MEC from dual union
    select 2 id_modele, 4 num_prototype, trunc(to_date('01/09/2012','DD/MM/YYYY')) DATE_MEC from dual union
    select 2 id_modele, 5 num_prototype, trunc(to_date('01/08/2012','DD/MM/YYYY')) DATE_MEC from dual 
)
SELECT  distinct 
               id_modele, 
             case when (date_mec <= TRUNC (SYSDATE)) then 
             last_value(num_prototype ) OVER (PARTITION BY id_modele ORDER BY date_mec asc, num_prototype desc )
             else null end
              AS Numprototype_prec
             , 
             case when (TRUNC (date_mec) <= TRUNC (SYSDATE)) then 
             last_value(TRUNC(date_mec)) OVER (PARTITION BY  id_modele ORDER BY date_mec desc)
             else null end  AS date_mec_prec,
             case when (TRUNC (date_mec) > TRUNC (SYSDATE)) then
              first_value(num_prototype ) OVER (PARTITION BY  id_modele ORDER BY date_mec asc, num_prototype desc ) 
              else null end  AS Numprotonext
             , 
             case when (TRUNC (date_mec) > TRUNC (SYSDATE)) then
             first_value(TRUNC(date_mec)) OVER (PARTITION BY  id_modele ORDER BY date_mec )
         else null end as date_mec_next
          FROM T;

Mais je n'arrive pas au résultat voulu. J'ai réussi à écrire la requête en traitant la recherche du prototype passé et du prototype à venir via deux requêtes séparées mais je n'arrive pas à en faire une seule... Un oeil avisé pourrais-t-il me mettre sur la bonne voie ?

Merci pour toute aide,
Lek.

Tu peux tenter comme ça, mais je n'ai pas fait de tests complémentaires sur d'autres valeurs, je ne suis pas sûr que ce soit assez blindé...

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
  WITH T AS (
SELECT 1 id_modele, 1 num_prototype, trunc(to_date('16/09/2012','DD/MM/YYYY')) DATE_MEC FROM dual union
SELECT 1 id_modele, 2 num_prototype, trunc(to_date('01/01/2012','DD/MM/YYYY')) DATE_MEC FROM dual union
SELECT 2 id_modele, 3 num_prototype, trunc(to_date('01/01/2013','DD/MM/YYYY')) DATE_MEC FROM dual union
SELECT 2 id_modele, 4 num_prototype, trunc(to_date('01/09/2012','DD/MM/YYYY')) DATE_MEC FROM dual union
SELECT 2 id_modele, 5 num_prototype, trunc(to_date('01/08/2012','DD/MM/YYYY')) DATE_MEC FROM dual 
),
       t_next as (
select id_modele, num_prototype, date_mec,
       lead(num_prototype) over (partition by id_modele order by date_mec) as next_proto,
       lead(date_mec) over (partition by id_modele order by date_mec) as next_date_mec
  from t
)
select id_modele, 
       max(num_prototype) keep (dense_rank first order by case when date_mec <= trunc(sysdate) 
                                                               then date_mec 
                                                           end desc NULLS LAST) as Numproto_prec,
       max(case when date_mec <= trunc(sysdate) then date_mec end) as dte_mec_prec,
       min(next_proto) keep (dense_rank first order by case when coalesce(next_date_mec, to_date('31/12/9999','dd/mm/yyyy')) >= trunc(sysdate) 
                                                            then coalesce(next_date_mec, to_date('31/12/9999','dd/mm/yyyy'))
                                                        end NULLS LAST) as Numproto_next,
       min(case when next_date_mec >= trunc(sysdate) then next_date_mec end) as dte_mec_next
  from t_next
 group by id_modele

Sinon tu dois pouvoir juste joindre les 2 requêtes que tu as créé.

17/09/2012, 20h47
LEK

Merci pour ton aide.
J'ai fais des tests avec différents jeux de données et ça à l'air de coller.
Je me replonge dans les tutos car je pense que je n'ai pas complètement saisi le fonctionnement : j'aurais jurer que dans ce cas à je devais passer par les fonctions last_value/first_value.
18/09/2012, 09h31
skuatamad

C'est pas vraiment un problème de premier/dernier mais plutôt un problème de suivant donc LEAD.
Après j'utilise aussi FIRST mais dans sa version agrégée.
19/09/2012, 22h45
LEK

Ok j'ai compris : merci!