#1 Fri 23 May 2014 16:24
- Géronimo
- Participant occasionnel
- Date d'inscription: 21 May 2014
- Messages: 14
[9.3] nommer les récursions d'une requête récursive
Bonjour à tous,
Voici ma requête :
Code:
WITH RECURSIVE en_aval(profondeur, code_zone, id_nd_ini, id_nd_fin, geom) AS ( SELECT 1, code_zone, id_nd_ini, id_nd_fin, geom FROM essai_pl UNION ALL SELECT ea.profondeur + 1, e.code_zone, e.id_nd_ini, e.id_nd_fin, e.geom FROM en_aval AS ea, essai_pl AS e WHERE e.id_nd_fin = ea.id_nd_ini ) SELECT row_number()OVER()::integer AS gid, * FROM en_aval
Dans la colonne "profondeur" de cette requête, il m'est indiquer un numéro pour chaque récursion (1 pour la première, 2 pour la seconde, 3 pour la troisième,...). J'aimerais remplacer ce numéro par l'identifiant de la zone où est pratiquée chaque récursion (code_zone). Il s'agit d'une chaîne de caractère sous la forme : lettre numéro numéro numéro.
Je vous remercie pour vos futures réponses.
Géronimo
Hors ligne
#2 Fri 23 May 2014 17:20
- Géronimo
- Participant occasionnel
- Date d'inscription: 21 May 2014
- Messages: 14
Re: [9.3] nommer les récursions d'une requête récursive
Et voilà la réponse :
Code:
WITH RECURSIVE en_aval(profondeur, code_zone, id_nd_ini, id_nd_fin, geom) AS ( SELECT MAX(code_zone) OVER () AS profondeur, code_zone, id_nd_ini, id_nd_fin, geom FROM essai_pl UNION ALL SELECT MAX(e.code_zone) OVER () AS profondeur, e.code_zone, e.id_nd_ini, e.id_nd_fin, e.geom FROM en_aval AS ea INNER JOIN essai_pl AS e ON e.id_nd_fin = ea.id_nd_ini) SELECT ROW_NUMBER() OVER()::integer AS gid, profondeur, code_zone, id_nd_ini, id_nd_fin, geom FROM en_aval;
Bon week-end !
Hors ligne
#3 Mon 26 May 2014 19:20
- Géronimo
- Participant occasionnel
- Date d'inscription: 21 May 2014
- Messages: 14
Re: [9.3] nommer les récursions d'une requête récursive
Bonjour !
Je me permets de poster à nouveau sur ce sujet, car en fait la fonction que j'ai écrite ne permet pas de nommer la récursivité d'une requête récursive comme il faut. Elle se contente de prendre la valeur maximum des code_zone et la met comme nom.
Voilà la table sur laquelle je travaille. Elle s'appelle init recurs_ord_2 :
Code:
gid ; rnk_desc ; profondeur ; code_hydro ; c_hyd_cdo, code_zone, lettre_bv, toponyme1, fpkh, tpkh, id_nd_ini, id_nd_fin 1;12;1;"V7300000";"V---0000";"V730";"V";"fleuve le rhône";947663;1000000;619003051;619005470 2;11;1;"V7210000";"V---0000";"V721";"V";"fleuve le rhône";944880;947663;619002712;619003051 3;10;1;"V7200000";"V---0000";"V720";"V";"fleuve le rhône";931497;944880;619001184;619002712 4;9;1;"V7000000";"V---0000";"V700";"V";"fleuve le rhône";916913;931497;614010566;619001184 5;8;1;"X3500000";"X---0000";"X350";"X";"rivière la durance";975886;1000000;619000786;614010566 6;7;1;"X3480400";"X34-0400";"X348";"X";"rivière le coulon";975108;1000000;619000784;619000786 7;6;1;"X3460400";"X34-0400";"X346";"X";"rivière le coulon";963787;975108;619000560;619000784 8;5;1;"X3450400";"X34-0400";"X345";"X";"rivière le calavon";955523;963787;615009286;619000560 9;4;1;"X3430400";"X34-0400";"X343";"X";"rivière le calavon";938396;955523;615009662;615009286 10;3;1;"X3420400";"X34-0400";"X342";"X";"rivière le calavon";935022;938396;615009013;615009662 11;2;1;"X3410400";"X34-0400";"X341";"X";"rivière le calavon";922089;935022;614009387;615009013 12;1;1;"X3400400";"X34-0400";"X340";"X";"rivière le calavon";913111;922089;614008612;614009387
Ce que je veux faire, c'est une récursivité par l'égalité id_nd_fin=id_nd_ini.
Cette table représente les tronçons hydrographiques de cours d'eau qui se suivent. Pour chacun des tronçons, de part et d'autres, il y a un identifiant : id_nd_ini pour l'extrémité la plus en amont et id_nd_fin pour l'extrémité la plus en aval. On comprend donc bien que, l'id_nd_fin d'un tronçon est égal à l'id_nd_ini du tronçon situé en aval.
Je veux donc que, pour chaque tronçon, le logiciel m'indique la liste des tronçons qui lui sont en amont.
Cela j'y arrive.
Ce que je veux, en plus, c'est que sur une des colonnes de la table qui m'est renvoyée, pour chaque liste, il soit indiqué le la nomenclature du tronçon dont est déployé toute la listes des tronçons qui lui sont en amont (l'identifiant code_zone).
Et ça je n'arrive pas à le faire...
J'arrive à ce qu'il me donne la récursivité pour chaque tronçon sans rangement par code_zone avec le code suivant :
Code:
WITH recursive rec (gid, profondeur, code_zone_max, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_link) AS ( SELECT gid, profondeur, code_zone, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_desc FROM init_recurs_ord_2 union ALL SELECT b.gid, a.profondeur, a.code_zone_max, b.code_zone, b.lettre_bv, b.id_nd_ini, b.id_nd_fin, b.rnk_desc FROM rec a INNER JOIN init_recurs_ord_2 b ON b.id_nd_fin = a.id_nd_ini ) SELECT * FROM rec
La table qui m'est retournée est :
Code:
gid, profondeur, code_zone_max, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_link 12;1;"V730";"V730";"V";619003051;619005470;1 11;1;"V721";"V721";"V";619002712;619003051;2 10;1;"V720";"V720";"V";619001184;619002712;3 9;1;"V700";"V700";"V";614010566;619001184;4 8;1;"X350";"X350";"X";619000786;614010566;5 7;1;"X348";"X348";"X";619000784;619000786;6 6;1;"X346";"X346";"X";619000560;619000784;7 5;1;"X345";"X345";"X";615009286;619000560;8 4;1;"X343";"X343";"X";615009662;615009286;9 3;1;"X342";"X342";"X";615009013;615009662;10 2;1;"X341";"X341";"X";614009387;615009013;11 1;1;"X340";"X340";"X";614008612;614009387;12 11;1;"V730";"V721";"V";619002712;619003051;2 10;1;"V721";"V720";"V";619001184;619002712;3 9;1;"V720";"V700";"V";614010566;619001184;4 8;1;"V700";"X350";"X";619000786;614010566;5 7;1;"X350";"X348";"X";619000784;619000786;6 6;1;"X348";"X346";"X";619000560;619000784;7 5;1;"X346";"X345";"X";615009286;619000560;8 4;1;"X345";"X343";"X";615009662;615009286;9 3;1;"X343";"X342";"X";615009013;615009662;10 2;1;"X342";"X341";"X";614009387;615009013;11 1;1;"X341";"X340";"X";614008612;614009387;12 etc... 3;1;"V730";"X342";"X";615009013;615009662;10 2;1;"V721";"X341";"X";614009387;615009013;11 1;1;"V720";"X340";"X";614008612;614009387;12 2;1;"V730";"X341";"X";614009387;615009013;11 1;1;"V721";"X340";"X";614008612;614009387;12 1;1;"V730";"X340";"X";614008612;614009387;12 78 lignes en tout.
J'arrive à avoir un rangement en fonction de code_zone, mais seulement sur une récursivité, avec cette fonction :
Code:
WITH recursive rec (gid, profondeur, code_zone_max, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_link) AS ( SELECT gid, profondeur, code_zone, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_desc FROM init_recurs_ord_2 WHERE rnk_desc=1 union ALL SELECT b.gid, a.profondeur, a.code_zone_max, b.code_zone, b.lettre_bv, b.id_nd_ini, b.id_nd_fin, b.rnk_desc FROM rec a INNER JOIN init_recurs_ord_2 b ON b.id_nd_fin = a.id_nd_ini AND a.rnk_link + 1 = b.rnk_desc) SELECT * FROM rec
Qui me donne cette table :
Code:
gid, profondeur, code_zone_max, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_link 12;1;"V730";"V730";"V";619003051;619005470;1 11;1;"V730";"V721";"V";619002712;619003051;2 10;1;"V730";"V720";"V";619001184;619002712;3 9;1;"V730";"V700";"V";614010566;619001184;4 8;1;"V730";"X350";"X";619000786;614010566;5 7;1;"V730";"X348";"X";619000784;619000786;6 6;1;"V730";"X346";"X";619000560;619000784;7 5;1;"V730";"X345";"X";615009286;619000560;8 4;1;"V730";"X343";"X";615009662;615009286;9 3;1;"V730";"X342";"X";615009013;615009662;10 2;1;"V730";"X341";"X";614009387;615009013;11 1;1;"V730";"X340";"X";614008612;614009387;12 12 lignes
J'aimerais savoir si vous pouvez m'indiquer quel code utiliser pour avoir l'ensemble des récursivités (78 lignes comme sur la 1ère table) avec le rangement par code_zone (comme sur la deuxième table).
Je suis désolé pour le long poste, mais ça me semble indispensable pour tout décrire.
Je vous remercie d'avance pour vos futures réponses,
Bonne soirée.
Hors ligne
#4 Tue 27 May 2014 09:36
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1544
Re: [9.3] nommer les récursions d'une requête récursive
Bonjour,
Je ne suis pas sur de bien comprendre ce que vous voulez faire avec les codes zones, mais ne pouvez-vous pas refaire une jointure sur la table apres la requete recursive pour récupérer les codes zones que vous voulez ?
Nicolas
Hors ligne
#5 Tue 27 May 2014 09:48
- Géronimo
- Participant occasionnel
- Date d'inscription: 21 May 2014
- Messages: 14
Re: [9.3] nommer les récursions d'une requête récursive
Désolé,
Je n'avais pas écrit correctement la 1ère fonction et la table qui en découlait hier soir. Surement la fatigue...
Donc, voilà la fonction :
Code:
WITH recursive rec (gid, profondeur, code_zone_max, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_link) AS ( SELECT gid, 1, code_zone, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_desc FROM init_recurs_ord_2 union ALL SELECT b.gid, a.profondeur +1, code_zone_max, b.code_zone, b.lettre_bv, b.id_nd_ini, b.id_nd_fin, b.rnk_desc FROM rec a INNER JOIN init_recurs_ord_2 b ON b.id_nd_fin = a.id_nd_ini) SELECT * FROM rec
Et voilà la table qui en découle :
Code:
12;1;"V730";"V730";"V";619003051;619005470;1 11;1;"V721";"V721";"V";619002712;619003051;2 10;1;"V720";"V720";"V";619001184;619002712;3 9;1;"V700";"V700";"V";614010566;619001184;4 8;1;"X350";"X350";"X";619000786;614010566;5 7;1;"X348";"X348";"X";619000784;619000786;6 6;1;"X346";"X346";"X";619000560;619000784;7 5;1;"X345";"X345";"X";615009286;619000560;8 4;1;"X343";"X343";"X";615009662;615009286;9 3;1;"X342";"X342";"X";615009013;615009662;10 2;1;"X341";"X341";"X";614009387;615009013;11 1;1;"X340";"X340";"X";614008612;614009387;12 11;2;"V730";"V721";"V";619002712;619003051;2 10;2;"V721";"V720";"V";619001184;619002712;3 9;2;"V720";"V700";"V";614010566;619001184;4 8;2;"V700";"X350";"X";619000786;614010566;5 7;2;"X350";"X348";"X";619000784;619000786;6 6;2;"X348";"X346";"X";619000560;619000784;7 5;2;"X346";"X345";"X";615009286;619000560;8 4;2;"X345";"X343";"X";615009662;615009286;9 3;2;"X343";"X342";"X";615009013;615009662;10 2;2;"X342";"X341";"X";614009387;615009013;11 1;2;"X341";"X340";"X";614008612;614009387;12 10;3;"V730";"V720";"V";619001184;619002712;3 9;3;"V721";"V700";"V";614010566;619001184;4 8;3;"V720";"X350";"X";619000786;614010566;5 7;3;"V700";"X348";"X";619000784;619000786;6 etc... 3;10;"V730";"X342";"X";615009013;615009662;10 2;10;"V721";"X341";"X";614009387;615009013;11 1;10;"V720";"X340";"X";614008612;614009387;12 2;11;"V730";"X341";"X";614009387;615009013;11 1;11;"V721";"X340";"X";614008612;614009387;12 1;12;"V730";"X340";"X";614008612;614009387;12
On voit bien que ma deuxième colonne, la colonne profondeur a un numéro différent pour chaque récursivité. Je voudrais que, sur la troisième colonne (code_zone_max), apparaisse le nom du tronçon (code_zone) sur lequel s'effectue chaque récursivité.
Ainsi, à côté du 1 de la colonne profondeur pour la 1ère récursivité, il s'afficherait V730 sur les 12 lignes.
A côté du 2 de la colonne profondeur pour la 2e récursivité, il s'afficherait V720 sur 11 lignes.
...
A côté du 10 de la colonne profondeur pour la 10e récursivité, il s'afficherait X342 sur 3 lignes.
A côté du 11 de la colonne profondeur pour la 11e récursivité, il s'afficherait X341 sur 2 lignes.
A côté du 12 de la colonne profondeur pour la 12e récursivité, il s'afficherait X340 sur 1 lignes.
Comment pensez-vous faire une jointure pour récupérer les codes_zones que je veux ?
Hors ligne
#6 Wed 28 May 2014 10:56
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1544
Re: [9.3] nommer les récursions d'une requête récursive
Bonjour,
Merci pour ces précisions.
Dans la premiere requete (partie non recursive: SELECT ... FROM init_recurs_ord_2), il y a bien 12 tronçons différents, chacun associé à son propre code_zone ?
Du coup, pourquoi est-ce le code V730 que vous souhaitez voir apparaitre à la premiere itération ?
N'est-ce pas un ORDER BY code_zone que vous voulez dans la requete finale, histoire de voir tous les tronçons connectés ayant pour code_zone V730 ?
Nicolas
Hors ligne
#7 Wed 28 May 2014 11:00
- Géronimo
- Participant occasionnel
- Date d'inscription: 21 May 2014
- Messages: 14
Re: [9.3] nommer les récursions d'une requête récursive
Bijour !!!
Voilà le code qui m'a permis de résoudre mon problème :
Code:
WITH recursive rec (gid, profondeur, code_zone_max, code_zone, id_nd_ini, id_nd_fin, geom, chemin) AS ( SELECT gid, 1, code_zone, code_zone, id_nd_ini, id_nd_fin, geom, code_zone::text FROM init_recurs_ord_7 union ALL SELECT b.gid, a.profondeur +1, a.code_zone_max, b.code_zone, b.id_nd_ini, b.id_nd_fin, b.geom, a.chemin || ' > ' || b.code_zone FROM rec a INNER JOIN init_recurs_ord_7 b ON a.id_nd_fin = b.id_nd_ini) SELECT * FROM rec ORDER BY 1, profondeur DESC
Hors ligne