utilisation des fonctions analytiques

Version imprimable

02/04/2007, 11h13
LEK

utilisation des fonctions analytiques

Bonjour, je rencontre un problème que je pense pouvoir être résolu avec les fonctions analytiques (lag,lead?) mais je ne sais pas comment m'y prendre :
J'ai une table A avec les champs :
id_groupe number,
id_elem number

je voudrais avoir une sortie de ce type :
id_groupe | id_elem_deb | id_eleme_fin
1 1 10
1 20 25

Quelqu'un pourrait m'aider à recréer ce type d'intervalle ?

Merci d'avance.
02/04/2007, 12h05
Yorglaa

salut,
quelles sont les règles à respecter pour définir tes colonnes début et fin ?

celle des lignes juste précédentes et suivantes ? min/ max ? autre ?

quid de la première ligne (qui n'aura pas de précédent) et de la dernière (qui n'aura pas de suivant) ?

Dans mon exemple la table A est remplie avec les plages suivantes :
id_groupe : 1 est relié aux éléments ayant les identifiants allant de 1 à 10
et de 20 à 25
id_groupe : 2 est relié aux éléments ayant les identifiants allant de 11 à 19

Ce qui donne les insert suivants :
Code:

1 2 3 4 insert into A (id_groupe, id_member) values(1,1) insert into A (id_groupe, id_member) values(1,2) .. insert into A (id_groupe, id_member) values(1,10)
Puis pour le groupe 2 :
Code:

1 2 3 insert into A (id_groupe, id_member) values(2,11) ... insert into A (id_groupe, id_member) values(2,19)
et enfin la dernière plage du groupe 1 :
Code:

1 2 3 4 5 6 7 8 insert into A (id_groupe, id_member) values(1,20) insert into A (id_groupe, id_member) values(1,21) insert into A (id_groupe, id_member) values(1,22) insert into A (id_groupe, id_member) values(1,23) insert into A (id_groupe, id_member) values(1,24) insert into A (id_groupe, id_member) values(1,25)
Les valeurs deb et fin sont donc les bornes inf et sup des intervalles d'identifiants reliés à un groupe.

merci d'indiquer la version

pour Oracle10g

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
select id_groupe,b,e from (
  select id_groupe,last_value(b ignore nulls) over (order by id_member)b,e from (
    select id_groupe,id_member,
      decode(lag(id_groupe) over (order by id_member),id_groupe,null,id_member) b,
      decode(lead(id_groupe) over (order by id_member),id_groupe,null,id_member) e
   from a))
where e is not null;
 
ID_GROUPE B                                        E                                       
--------- ---------------------------------------- ----------------------------------------
        1 1                                        10                                      
        2 11                                       19                                      
        1 20                                       25

Pardon : la version d'oracle que j'utilise est ora 10.2.
Merci pour la requête cela répond presque à mon besoin : en effet au départ je souhaitais afficher les intervalles rattachés à un seul groupe.
Entre deux intervalles d'un seul groupe, je peux soit avoir des plages vides, soit des plages rattachées à un autre groupe.
En utilisant la requête présente cela revient donc à faire :
Code:

1 2 3 4 5 6 7 8 9 SELECT id_groupe,b,e FROM ( SELECT id_groupe,last_value(b IGNORE nulls) over (ORDER BY id_member)b,e FROM ( SELECT id_groupe,id_member, decode(lag(id_groupe) over (ORDER BY id_member),id_groupe,NULL,id_member) b, decode(lead(id_groupe) over (ORDER BY id_member),id_groupe,NULL,id_member) e FROM a)) WHERE e IS NOT NULL AND id_groupe = 1;
Mais est ce que cela ne pourrait pas être plus optimal car ici les sous requêtes imbriquées paracourt toute la table a et renvoie tous les intervalles par groupe puis la clause where est appliquée pour ne récupérer que les enregistrement du groupe 1. Je possède une table ayant 5 millions de lignes, indexée sur les champs id_groupe et id_member(PK) , et les temps de réponses avec la requêtes ci-dessus sont de l'ordre de 5 minutes.

La requête fournie me permet quand même d'avoir un temps d'exécution assez rapide comparée à la requête que j'utilisait initialement :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 select a_deb.id_member "A", min(a_fin.id_member) "B" from a a_deb, a a_fin where a_deb.id_groupe = 1 and a_fin.id_groupe = 1 and a_fin.id_member >= a_deb.id_member and not exists (select 'x' from A a_before where a_before.id_groupe = 1 and a_before.id_member = a_deb.id_member - 1) and not exists (select 'x' from A a_after where a_after.id_groupe = 1 and a_after.id_member = a_fin.id_member + 1) group by a_deb.id_member HAVING min(a_fin.id_member) - a_deb.id_member >= 1 ;
qui me donnait un temps de réponse de l'ordre de 7/8 minutes...

Code:

1
2
3
4
5
6
7
8
9
10
11
 
select * from (
 select
last_value(b ignore nulls) over (order by id_member)b,e from (
select id_member,
 decode(lag(id_member) over (order by id_member),id_member-1,null,id_member) b,
 decode(lead(id_member) over (order by id_member),id_member+1,null,id_member) e
from a where id_groupe=1
 )
)
where e is not null;

ça joue?

03/04/2007, 11h45
LEK
Alors là mille chapeau :D , c'est vraiment la requête par excellence que je cherchais. J'avais effectué des requêtes alambiquées comme tu as vu et commençais presque à me demander si je ne ferais pas mieux de faire un curseur et noter le intervalles aux fur et à mesure de leur apparitions...
Avec ta nouvelle requête je suis descendu à un temps de réponse de 0.48 seconde (lorsque je la joue la première fois) !!!
Enfin juste une dernière confirmation avant que je ne puisse clore le sujet, si je ne veux voire apparaitre que les intervalles qui comprennent plus d'un élément sans dénaturés les perf, je rajoute simplement la clause suivante en fin, non? :
Code:

1 2 and e-b>1
Inversement pour n'afficher que les intervalles d'un élement en optimisant au max , dois je ajouter la clause suivante ou trouver quelque chose de plus raffiné du type élement précédent et suivant null :

Code:

and e-b=1

En tous les cas merci encore, je suis vraiment impressioné de voir comment le SQL répond lorsqu'il est bien utilisé.;)

un élément, c'est b=e non?
de 10 à 11, il y a bien 2 éléments non?

ok

exactement un élément

Code:

1
2
3
4
5
6
7
8
 
SELECT b,e FROM (
SELECT
 decode(lag(id_member) over (ORDER BY id_member),id_member-1,NULL,id_member) b,
 decode(lead(id_member) over (ORDER BY id_member),id_member+1,NULL,id_member) e
FROM a WHERE id_groupe=1
 )
WHERE b=e;

plus d'un élément

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT * FROM (
 SELECT
last_value(b IGNORE nulls) over (ORDER BY id_member)b,e FROM (
SELECT id_member,
 decode(lag(id_member) over (ORDER BY id_member),id_member-1,NULL,id_member) b,
 decode(lead(id_member) over (ORDER BY id_member),id_member+1,NULL,id_member) e
FROM a WHERE id_groupe=1
 )
where b is null or b!=e or e is null
)
WHERE e IS NOT NULL ;

si tu es vraiment intéressé aux intervales ou b=e-1, tu peux tenter

Code:

1
2
3
4
5
6
7
8
9
 
SELECT x1 b, id_member e FROM (
SELECT id_member,
 lag(id_member) over (ORDER BY id_member) x1,
 lag(id_member,2) over (ORDER BY id_member) x2,
 lead(id_member) over (ORDER BY id_member) x3
FROM a WHERE id_groupe=1
) where id_member=x1+1 and lnnvl(id_member=x2+2) and lnnvl(id_member=x3-1)
;

03/04/2007, 13h25
LEK

Ok avec les deux requêtes je traites tous les cas de mon applis : je devais afficher dans un rapport les élements par groupes et les éléments isolés.
Merci du temps que tu m'as accordé.
03/04/2007, 13h27
laurentschneider

je t'en prie. il est sans doute possible de pouvoir faire mieux 8-)