Multiples hiérarchies dans la même table

[Phil]

Merci à Lionel pour sa question :

Bonjour,
J’aurais besoin d’un coup de main pour faire une requête hiérarchique un poil complexe.
J'ai tous les clans de géants dans une table. Un géant peut appartenir à plusieurs clans, un lien hiérarchique peut être en place ou non. C'est donné par la colonne ACTIF, si une hiérarchie s'interrompt du haut en bas, il faut interrompre la chaîne et ne pas traiter les bouts de hiérarchie orphelins (il ne faut partir que du top chef donc) :
Avec :

Code : Tout sélectionner

   IDGE 	ACTIF 		IDCH 		IDCL
	1	TRUE		2		1
	2	TRUE		3		1
	3	TRUE		null		1
	1	TRUE		4		2
	3	TRUE		1		2
	4	TRUE		null		2
	3	FALSE		5		3
	5	TRUE		null		3
	

Cela doit donner :

Code : Tout sélectionner

Hiérarchie	Clan	
1/2/3		1
3/1/4		2
5		3

Si vous avez une idée cela m’intéresse, merci.
Sinon, je le fais par programmation.

Réponse :

Cela semble tout à fait possible, il faut juste que la condition de jointure père-fils de la CTE hiérarchique se fasse par clan.
Exemple :

Code : Tout sélectionner

select version();
                                                    version
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.5 (Debian 11.5-1+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

create table geants(idge int, actif boolean, idch int, idcl int);

insert into geants(idge, actif, idch, idcl) values
(1,true,2,1),
(2,true,3,1),
(3,true,null,1),
(1,true,4,2),
(3,true,1,2),
(4,true,null,2),
(3,false,5,3),
(5,true,null,3);

WITH RECURSIVE hierarchie_clans(idge, idges, idcl, niveau) 
AS 
( SELECT idge, idge::text as idges, idcl, 1 FROM geants where idch is null and actif
UNION ALL 
SELECT g.idge, g.idge || '/' ||  h.idges as idges, g.idcl, h.niveau+1 FROM hierarchie_clans AS h join geants AS g on (h.idge = g.idch and h.idcl = g.idcl and actif))
select distinct idcl as clan, first_value(idges) over (partition by idcl order by niveau desc) as hierarchie from hierarchie_clans  order by idcl ;

 clan | hierarchie
------+------------
    1 | 1/2/3
    2 | 3/1/4
    3 | 5
    

[Phil]

Merci à Lionel pour son complément :

"C'est OK mais si je veux remplacer les "/" par des "-" et inverser l'ordre, c'est à dire partir du chef ?"

Réponse :

Code : Tout sélectionner

select version();
                                                    version
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.5 (Debian 11.5-1+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

WITH RECURSIVE hierarchie_clans(idge, idges, idcl, niveau) 
AS 
( SELECT idge, idge::text as idges, idcl, 1 FROM geants where idch is null and actif
UNION ALL 
SELECT g.idge,  h.idges || '-' ||  g.idge as idges, g.idcl, h.niveau+1 FROM hierarchie_clans AS h join geants AS g on (h.idge = g.idch and h.idcl = g.idcl and actif))
select distinct idcl clan, first_value(idges) over (partition by idcl order by niveau desc) hierarchie from hierarchie_clans  order by idcl ;

 clan | hierarchie
------+------------
    1 | 3-2-1
    2 | 4-1-3
    3 | 5