IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Requêtes PostgreSQL Discussion :

Addition au fil de l'eau


Sujet :

Requêtes PostgreSQL

  1. #1
    Nouveau membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    mars 2017
    Messages
    66
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Service public

    Informations forums :
    Inscription : mars 2017
    Messages : 66
    Points : 36
    Points
    36
    Par défaut Addition au fil de l'eau
    Bonjour,

    Après beaucoup de temps passer à m'arracher les cheveux et souhaitant conserver ceux restants, je recherche une âme charitable qui me tirera de cette situation .

    Je dispose de valeur sur un réseau (hydrographique) que je cherche à additionner dans le sens du courant.
    Chaque segment possède un champs identifiant id_nd_ini (identifiant l'amont du segment) et id_nd_fin (la fin)

    id_bdcarth id_nd_ini id_nd_fin v19 valeur attendue
    1 10 11 1 1
    2 11 12 0 1
    3 12 13 1 2

    J'ai actuellement cette requête :

    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
    WITH RECURSIVE walk_network (v, i, ini,fin) AS
     (    
    	 select  	
    	 0, 	   
    	 n.id_bdcarth,
    	 n.id_nd_ini,
    	 n.id_nd_fin,
    	 array[n.id_bdcarth] as ids 	  
    	 from last_input_somme n  		
    	 LEFT JOIN last_input_somme b  		
    	 ON n.id_nd_ini = b.id_nd_fin 		
    	 WHERE b.id_nd_ini is null 		
    		 UNION ALL 			 
    			SELECT 				
    			n.v19+v, 				
    			n.id_bdcarth,
    			n.id_nd_ini, 
    			n.id_nd_fin ,		
    			ids || n.id_bdcarth
    			FROM last_input_somme n, walk_network w 				
    			WHERE w.fin = n.id_nd_ini and not (n.id_bdcarth = any(ids)) ) 	
     
     
    			select  i,  v from walk_network
    la table last_input_somme contient 37 entités et je me retrouve avec un select qui me retourne 98 lignes et de surcroit même si il y'a bien une addition , certaines valeurs sont omises
    Images attachées Images attachées  
    Fichiers attachés Fichiers attachés

  2. #2
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 952
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 952
    Points : 49 777
    Points
    49 777
    Billets dans le blog
    1
    Par défaut
    Sans votre jeu d'essais sous forme CREATE TABLE... INSERT ... ça va être difficile de vous répondre. Mais à vu de nez il manque un filtre dans la requête finale pour ne prendre en compte que les totaux finaux et non pas les résultats intermédiaires...

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  3. #3
    Membre expérimenté
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    mai 2003
    Messages
    852
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Pyrénées Atlantiques (Aquitaine)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : Alimentation

    Informations forums :
    Inscription : mai 2003
    Messages : 852
    Points : 1 596
    Points
    1 596
    Par défaut
    Bonjour,

    Tu devrais regarder les fonctions de fenêtrage, je pense que ça peut répondre à ton besoin (Fonctions de fenêtrage), notamment la fonction lag().
    Philippe,


    N'hésitez à lever le pouce si mon aide vous a été utile.

  4. #4
    Nouveau membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    mars 2017
    Messages
    66
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Service public

    Informations forums :
    Inscription : mars 2017
    Messages : 66
    Points : 36
    Points
    36
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    Sans votre jeu d'essais sous forme CREATE TABLE... INSERT ... ça va être difficile de vous répondre. Mais à vu de nez il manque un filtre dans la requête finale pour ne prendre en compte que les totaux finaux et non pas les résultats intermédiaires...

    A +
    J'ai ajouté le fichier sql en PJ .

    Dans ma requête finale j'avais tenté de faire un select i, sum(distinct v) ... mais les valeurs n'étaient pas du tous cohérentes (2+3 = 8).

  5. #5
    Nouveau membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    mars 2017
    Messages
    66
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Service public

    Informations forums :
    Inscription : mars 2017
    Messages : 66
    Points : 36
    Points
    36
    Par défaut
    Citation Envoyé par philouZ Voir le message
    Bonjour,

    Tu devrais regarder les fonctions de fenêtrage, je pense que ça peut répondre à ton besoin (Fonctions de fenêtrage), notamment la fonction lag().
    Je connaissais pas l'existence de ces fonctions ca semblerait être une option

  6. #6
    Nouveau membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    mars 2017
    Messages
    66
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Service public

    Informations forums :
    Inscription : mars 2017
    Messages : 66
    Points : 36
    Points
    36
    Par défaut
    Citation Envoyé par philouZ Voir le message
    Bonjour,

    Tu devrais regarder les fonctions de fenêtrage, je pense que ça peut répondre à ton besoin (Fonctions de fenêtrage), notamment la fonction lag().
    En fait je ne suis pas certain que ca me soit utile car lag permet d'effectuer des opérations sur des tuples qui sont à une distance définit ce qui n'est pas le cas chez moi. Si j'ordonne mes tables selon le champ "id_nd_fin" je vais me retrouvé avec les segment les plus en amonts (qui ne sont pas forcément connectés les uns aux autres)

  7. #7
    Membre expérimenté
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    mai 2003
    Messages
    852
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Pyrénées Atlantiques (Aquitaine)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : Alimentation

    Informations forums :
    Inscription : mai 2003
    Messages : 852
    Points : 1 596
    Points
    1 596
    Par défaut
    Bonjour,

    Alors basé sur ton jeu de donnée et pour un tronçon voici ce que ça donne:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    with recursive x(id_bdcarth,id_nd_ini,id_nd_fin,v19) as (
    	select id_bdcarth,id_nd_ini,id_nd_fin,v19 from last_input_somme
    	where id_bdcarth = 973195760
    	union all
    	select b.id_bdcarth,b.id_nd_ini,b.id_nd_fin,b.v19 
    	from x,last_input_somme b 
    	where x.id_nd_fin = b.id_nd_ini
    )
    select * from x
    Ensuite si tu veux la somme il te suffit de faire
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    with recursive x(id_bdcarth,id_nd_ini,id_nd_fin,v19) as (
    	select id_bdcarth,id_nd_ini,id_nd_fin,v19 from last_input_somme
    	where id_bdcarth = 973195760
    	union all
    	select b.id_bdcarth,b.id_nd_ini,b.id_nd_fin,b.v19 
    	from x,last_input_somme b 
    	where x.id_nd_fin = b.id_nd_ini
    )
    select sum(v19) from x
    Exemple sur fiddle : https://www.db-fiddle.com/f/nsKWKVsdEnGFiukELGT8Zc/0

    Ce code fonctionne pour un tronçon identifié au départ. Est-ce que c'est ce que tu recherches ?
    Philippe,


    N'hésitez à lever le pouce si mon aide vous a été utile.

  8. #8
    Nouveau membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    mars 2017
    Messages
    66
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Service public

    Informations forums :
    Inscription : mars 2017
    Messages : 66
    Points : 36
    Points
    36
    Par défaut
    Merci d'avoir pris le temps de plancher sur cette question ,

    Effectivement c'est à peu près cette démarche avec une nuance c'est que je ne pars pas d'un point en amont vers un point en aval mais de tous les points en amont vers tous les exutoires et que le jeu de données sur lequel je travail comporte 150 000 segments .
    Dans la requête j'ai également un champs qui aggrège les identifiants des segments et qui test que l'id n'a pas déjà été calculé (éviter que des boucles topologiques potentielles entrainent des boucles infinies)
    J'ai eu l'occasion de parler avec un quelqu'un habitué à travailler sur les réseau hydro. Il me conseille de décomposer le traitement et de faire tourner ça sur python.

    Donc j'hésite désormais entre deux méthodes:
    - Utiliser votre méthode en la faisant boucler sur les segments les plus en aval d'un bassin versant jusqu'à à arriver à un exutoire principal défini arbitrairement.
    - découper le réseau hydro par zone hydrographique (la plus petite entité de bassin versant) , réaliser des tables de chainages amont aval entre ces zones (je crains malheureusement que le process soit déjà trop lourd à cette échelle)

  9. #9
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 952
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 952
    Points : 49 777
    Points
    49 777
    Billets dans le blog
    1
    Par défaut
    Pour information, la dernière (ou prochaine...) version de PG intégrera (ou intègre déjà) le parcours de graphe en largeur ou profondeur de préférence, à votre choix, ce qui permet généralement d'améliorer certaines requêtes récursives dont la topologie est connue.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

Discussions similaires

  1. [AJAX] Affichage d'une variable au fils de l'eau (flux PHP)
    Par Jonathan.b dans le forum Général JavaScript
    Réponses: 1
    Dernier message: 27/10/2007, 14h25
  2. Lecture du COM2 au fil de l'eau
    Par 202UH dans le forum C
    Réponses: 110
    Dernier message: 29/11/2006, 14h47
  3. messages à l'utilisateur au fil de l'eau
    Par thmane dans le forum Général JavaScript
    Réponses: 2
    Dernier message: 04/10/2006, 12h51
  4. [VB.Net] Impression fil de l'eau
    Par Silvinho42 dans le forum Windows Forms
    Réponses: 3
    Dernier message: 18/10/2005, 11h43
  5. [IO] downloader au fil de l'eau
    Par Ekros dans le forum Servlets/JSP
    Réponses: 3
    Dernier message: 09/06/2005, 10h04

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo