Discussion :

[emmachane]

Bonjour,

Je cherche à traiter un cas que je trouve compliqué.
J'ai en entrée :

Code :

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

1
2
3
4
5
6
7
col1 col2
AA BB
BB CC
CC DD
EE FF
GG HH
HH ZZ

En sortie, je souhaite obtenir ceci :

Code :

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

1
2
3
4
col1 col2
AA DD
EE FF
GG ZZ

En gros quand la valeur de la seconde colonne se retrouve sur une autre ligne et à la première colonne, je prends les deux extrémités.
Avez-vous une idée svp ?

Merci pour votre aide.

[StringBuilder]

On peut probablement faire autrement, voici en SQL standard ce qu'on peut faire :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
with hier (col1, col2, niv)
as
(
        -- On cherche les points de départ (col1 inexistant en col2)
	select t1.col1, t1.col2, 1 from test t1 where not exists (select 1/0 from test t2 where t2.col2 = t1.col1)
	union
        -- On complète la hiérarchie en incrémentant le niveau
	select h.col1, t.col2, h.niv + 1 from test t inner join hier h on h.col2 = t.col1
)
-- Et on ne conserve que la valeur de col2 pour le plus grand niveau
select distinct col1, first_value(col2) over (partition by col1 order by niv desc) from hier;

Testé avec SQLite, marchera aussi avec SQL Server.

[Waldar]

union all entre la racine et la récursion - je suis même étonné que ça fonctionne avec union.

[emmachane]

Bonsoir et merci pour vos réponses.

Dans le

Code :

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

select 1/0 from

Je vois une division par zéro

[vanagreg]

Bonjour,

Ce n'est pas de la récursivité. Ce que tu cherches à faire s'appelle "start-of-group" en anglais, je ne sais pas s'il existe un nom en français. Voici la méthode à utiliser (j'ai simuler ta table par le bloc "t" ci-dessous):

Code :

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

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
27
28
with t(col1, col2) 
as (select 'AA', 'BB' from dual union all 
select 'BB', 'CC' from dual union all 
select 'CC', 'DD' from dual union all 
select 'EE', 'FF' from dual union all 
select 'GG', 'HH' from dual union all 
select 'HH', 'ZZ' from dual 
), 
ruptures as ( 
select col1, 
       col2, 
        case when lag(col2, 1, 0) over (order by col1) <> col1 then 1 end flag_rupt
from t), 
groupes as ( 
select col1, 
       col2, 
       flag_rupt, 
       sum(flag_rupt) over (order by col1) num_groupe 
from ruptures) 
select min(col1), max(col2) 
from groupes  
group by num_groupe;
 
 
MIN(COL1)	MAX(COL2)
AA	DD
EE	FF
GG	ZZ

[Waldar]

Je pense que ça dépend, ici les données sont triées mais si ce n'est pas le cas la récursivité est une solution.

Si au lieu de :

GG HH
HH ZZ

Il y a :

GG NN
NN HH
HH ZZ

La solution récursive fonctionne et mais pas la "start-of-group".

[emmachane]

Effectivement, dans la réalité, les données ne sont pas triées et le trie qu'on voit dans l'exemple cité est une pure coïncidence.
Merci pour vos réponses. Je vais tester ça et je reviens vers vous.

[vanagreg]

Oui, j'ai oublié de préciser qu'il fallait une colonne pour le tri, j'ai choisi col1 mais il faudrait connaitre la vraie structure des données.

[escartefigue]

Je vois une division par zéro

Bonsoir

Aucune importance : (NOT)EXISTS ne transporte aucune colonne mais seulement un booléen en fonction du succès ou non de la requête.
Du coup
SELECT 1.
SELECT 'martine à la plage'.
SELECT *.
ou encore
SELECT 1/0.

renvoient le même résultat

Sauf que...
SELECT 1/0 peut produire des warning avec certains SGBD (c'est le cas pour DB2 for Z/OS), warnings pas forcément appréciés de l'exploitation qui veille au grain du coup

à noter également
SELECT NULL n'est pas accepté par tous les SGBD

[StringBuilder]

union all entre la racine et la récursion - je suis même étonné que ça fonctionne avec union.

Oups, oui en effet.

Pour SQLite ça lui fait ni chaud ni froid

En effet, avec SQL Server ça produit une erreur (avec la correction explicitement indiquée dans le message d'erreur d'ailleurs).

[SQLpro]

Avec ce jeu d'essais :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE T_LISTE_CHAINEE_LCN
(LCN_GAUCHE    VARCHAR(16) NOT NULL PRIMARY KEY,
 LCN_DROITE    VARCHAR(16) NOT NULL UNIQUE,
 CHECK (LCN_GAUCHE <> LCN_DROITE));
 GO
 
 INSERT INTO T_LISTE_CHAINEE_LCN VALUES
('AA', 'BB'),
('BB', 'CC'),
('CC', 'DD'),
('EE', 'FF'),
('GG', 'HH'),
('HH', 'ZZ');

Les requêtes suivantes :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WITH liste 
as
(-- On cherche les points de départ (LCN_GAUCHE inexistant en LCN_DROITE)
SELECT t1.LCN_GAUCHE, t1.LCN_DROITE, 1 AS niv
FROM   T_LISTE_CHAINEE_LCN AS t1 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   T_LISTE_CHAINEE_LCN AS t2 
                   WHERE  t2.LCN_DROITE = t1.LCN_GAUCHE)
UNION ALL
-- On complète la hiérarchie en incrémentant le niveau
SELECT h.LCN_GAUCHE, t.LCN_DROITE, h.niv + 1 
FROM   T_LISTE_CHAINEE_LCN AS t 
       inner join liste AS h ON h.LCN_DROITE = t.LCN_GAUCHE
)
-- Et on ne conserve que la valeur de LCN_DROITE pour le plus grand niveau
SELECT LCN_GAUCHE, FIRST_VALUE(LCN_DROITE) OVER (partition by LCN_GAUCHE order by niv desc) AS LCN_DROITE 
from   liste;

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
WITH liste 
as
(-- On cherche les points de départ (LCN_GAUCHE inexistant en LCN_DROITE)
SELECT t1.LCN_GAUCHE, t1.LCN_DROITE, 1 AS niv
FROM   T_LISTE_CHAINEE_LCN AS t1 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   T_LISTE_CHAINEE_LCN AS t2 
                   WHERE  t2.LCN_DROITE = t1.LCN_GAUCHE)
UNION ALL
-- On complète la hiérarchie en incrémentant le niveau
SELECT h.LCN_GAUCHE, t.LCN_DROITE, h.niv + 1 
FROM   T_LISTE_CHAINEE_LCN AS t 
       inner join liste AS h ON h.LCN_DROITE = t.LCN_GAUCHE
),
liste_rang AS
(
-- calcule de rang sur sur l'écart de niveau
SELECT l1.*, RANK() OVER(PARTITION BY LCN_GAUCHE ORDER BY niv DESC) AS RANG
FROM   liste AS l1
) 
SELECT LCN_GAUCHE, LCN_DROITE
FROM   liste_rang 
WHERE  RANG = 1;

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- par recherche de rupture et calcul de groupes
with t (col1, col2) 
as (select * FROM T_LISTE_CHAINEE_LCN
), 
ruptures as ( 
select col1, 
       col2, 
        case when lag(col2, 1, 0) over (order by col1) <> col1 then 1 end flag_rupt
from t), 
groupes as ( 
select col1, 
       col2, 
       flag_rupt, 
       sum(flag_rupt) over (order by col1) num_groupe 
from ruptures) 
select min(col1), max(col2) 
from groupes  
group by num_groupe;

Montre que la solution par rupture et groupe est la plus optimisée (SQL Server).
Alors que c'est l'inverse dans ce autre exemple :
https://www.developpez.net/forums/d2.../#post11677829

A +