Forums PostgreSQL.fr

arn

Membre

Boucle sur un champ de table

Bonjour à tous,

Je voudrais créer une fonction pour attribuer de manière itérative des valeurs à des tronçons  en fonction de certains critères. Je m'explique : j'ai une table qui comporte notamment ces champs :
nb_deb   integer
nb_fin integer
compt numeric (la valeur à attribuer)

Le but est qu'un tronçon sans valeur, si nb_deb ou nb_fin est égal à 2, récupère la valeur du tronçon qui lui est contigu. Comme il s'agit d'une valeur située dans la même table, j'ai pensé à un self join, plus ou moins dans ces termes :
if nb_deb = 2 and nb_fin = 2 then compt :=  compt from ma_table t1
full join ma_tablet2 on t1.nb_debut = t2.nb_fin ;

elsif nb_deb <> 2 and nb_fin = 2 then compt :=  compt from ma_table t1
full join ma_table t2 on t1.nb_fin = t2.nb_debut ;

elsif nb_deb = 2 and nb_fin <> 2 then compt  :=  compt  from ma_table t1
full join ma_table t2 on t1.nb_debut = t2.nb_fin ;

Comme à chaque fois un tronçon est susceptible de récupérer une donnée, le tronçon sans valeur qui lui est contigu pourra récupérer cette valeur au tour suivant, d'où l'idée d'intégrer cette formule dans une boucle. Je pense à un expression du type : while compt is null mais je n'arrive pas à bien positionner mes variables et mes déclarations.

Pourriez-vous me donner des conseils ? D'avance merci.

alassanediakite

Membre

Re : Boucle sur un champ de table

Salut
Pour une compréhension facile du problème, le mieux est de proposer un échantillon de données et la sortie voulue (le résultat sous forme de tableau).
@+

arn

Membre

Re : Boucle sur un champ de table

Ma table (ttable) ressemble à ceci :

id      nb_deb        nb_fin        debut      fin      compt
1          1              2            2        4        10
2          2              2            4        6         0
3          2              4            6        8        12

Je voudrais arriver à ceci :

id      nb_deb        nb_fin        debut      fin      compt
1          1              2            2        4        10
2          2              2            4        6        11
3          2              4            6        8        12

Si nb_deb = nb_fin, à chaque tour la valeur 0 prend la moyenne des valeurs compt des tronçons qui lui sont contigus, déterminés ainsi par les valeurs de debut et fin. Dans ce cas le debut de l'id n°2 est égal à la fin de l'id n°1 et la fin de l'id n°2 est égale à au début de l'id n°3. Dans les autres cas on prend la valeur du tronçon contigu (jonction par nb_deb ou nb_fin qui vaut 2).

J'ai pensé à ce code :

declare
i record ;
ttable alias for $1 ;

begin

	for i in select * from ttable where compt = 0
	loop
	update ttable
	set compt = 
	avg(m1 + m2) as 
	(select t1.compt as m1, t3.compt as m2 
  from ttable t1, ttable t2, ttable t3 where t1.fin = t2.debut and where t2.fin = t3.debut) 
	where nb_deb = 2 and nb_fin = 2 ;
	
	update ttable
	set compt = t2.compt
	from ttable t1, ttable t2
	where t1.fin = t2.debut and t1.nb_deb <> 2 and t1.nb_fin = 2 ;
	
	update ttable
	set compt = t2.compt
	from ttable t1, ttable t2
	where t1.debut = t2.fin and t1.nb_deb = 2 and t1.nb_fin <> 2 ;
	
	end loop ;

alassanediakite

Membre

Re : Boucle sur un champ de table

Salut
A présent j'ai encore des incompréhensions. Alors questions...
1->seules les lignes contenant 0 dans "compt" doivent être mise à jour? si oui, comment ces lignes se retrouvent avec 0? par insertion? mise à jour? ou import de données? ou est-ce des données ad-hoc à corriger?
2->pour la mise à jour on utilisera uniquement la ligne avant et la ligne suivante? si oui, comment identifie-t-on ces deux lignes sans ambiguïté?
3->est-il possible d'avoir une explication du contenu de la table (la signification des différents champs)?
Par ailleurs, à priori on a pas besoin de boucle pour mettre à jour les données d'une table. SQL fonctionne sur des ensembles.
@+

rjuju

Administrateur

Re : Boucle sur un champ de table

Je suis d'accord avec les questions et conclusions ci-dessus.

Un exemple de requête qui permet de calculer le compteur, avec le peu que j'ai compris des règles, vous devriez pouvoir vous en inspirer pour corriger les points indéfinis ou mal compris.

SELECT id, nb_deb, nb_fin, debut, fin,
CASE WHEN nb_deb = nb_fin THEN
    CASE WHEN nb_deb = lag(nb_fin) OVER (ORDER BY id)
        AND nb_fin = lead(nb_deb) OVER (ORDER BY id)
        THEN (lead(compt) OVER (ORDER BY id) + lag(compt) OVER (ORDER BY id)) / 2
    ELSE
        compt
    END
ELSE
    compt
END AS compt
from ttable ;

---

Julien.
https://rjuju.github.io/

arn

Membre

Re : Boucle sur un champ de table

Bonjour,
Merci pour vos réponses.

1) Oui, seules les lignes à 0 doivent être mises à jour. Elles correspondent en fait à une absence de données, ou le 0 permettra des calculs.
2) Oui, pour la maj ce sont les tronçons contigus qui seront utilisés, clairement identifiés par les champs "debut" et "fin" qui correspondent aux coordonnées des extrémités des tronçons.
3) "debut" et "fin" sont donc les coordonnées des extrémités des tronçons, "id" est bien sûr l'identifiant du tronçon, "nb_deb" est le nombre d’occurrences, dans la table, d'une coordonnée "debut", "nb_fin" est l'équivalent pour le champ "fin", "compt" est une valeur numérique de comptages

La boucle me paraît nécessaire car la "répartition" des valeurs "compt" n'est pas uniforme sur l'ensemble du réseau de tronçons. On peut ainsi avoir une série de 10 tronçons où seul le 1er possède une valeur. Si on fait une maj, le tronçon immédiatement contigu pourra récupérer une valeur mais pas les suivants, d'où l'idée de boucler la formule. Différencier la maj selon les champs nb_deb et nb_fin permet de s'assurer que les tronçons sur lesquels ont applique la formule correspondent à un ensemble cohérent. Concrètement, un nb_deb ou nb_fin à 3 signifie que nous sommes en présence d'une intersection de trois tronçons. Un nb_deb ET un nb_fin à 2 signifie qu'il s'agit d'un tronçon situé entre deux autres d'une même route. On peut donc lui attribuer une valeur moyenne des deux autres tronçons.
J'espère que mes explications sont plus claires.

J'ai pensé à cette formule :

for i in select from ttable where compt = 0
	loop
	update ttable as d
  set compt = (p.compt + x.compt) / 2
  from ttable as p, ttable as x
  where
      p.fin = d.debut and
      d.fin = x.debut and 
      d.nb_deb = 2 and d.nb_fin = 2 ;
	
	update ttable
	set compt = t2.compt
	from ttable as t1, ttable as t2
	where t1.fin = t2.debut and t1.nb_deb <> 2 and t1.nb_fin = 2 ;
	
	update ttable
	set compt = t2.compt
	from ttable as t1, ttable as t2
	where t1.debut = t2.fin and t1.nb_deb = 2 and t1.nb_fin <> 2' ;
	
	end loop ;

La fonction tourne depuis vendredi, ma table contenant plusieurs dizaines de milliers de tronçons....