Discussion :

[LEK]

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.

[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) ?

[LEK]

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 :

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

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 :

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

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 :

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

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.

[laurentschneider]

merci d'indiquer la version

pour Oracle10g

Code :

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

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

[LEK]

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 :

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

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 :

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

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

[laurentschneider]

Code :

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

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?

[LEK]

Alors là mille chapeau , 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 :

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

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 :

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

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

[laurentschneider]

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 :

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

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 :

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

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 ;

[laurentschneider]

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

Code :

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

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

[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é.

[laurentschneider]

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