Gérer des identifiants : traitement récursif ou pas !

**map34** · 18/03/2015, 09h08

Bonsoir!

Je n'arrive pas à faire ce dont j'ai besoin... (et je débute avec PostgreSQL, et le SQL d'une manière générale...).

Voici la situation :
- j'ai une table qui stocke des couples d'identifiants (tags) synonymes
- chaque nouveau couple de tags renseigné est affublé d'un identifiant propre à ce couple
- un seul tag peut avoir de nombreux synonymes sur plusieurs lignes
- à chaque nouveau couple de tags il est possible que j'associe des groupes de tags synonymes jusque-là dissociés (car il manquait un couple de tags observés pour faire le lien)

- J'ai besoin de générer un identifiant unique de l'objet qui porte ces nombreux tags

Par Exemple :
La table de synonymes, dont les enregistrements sont ajoutés par lot lors d'importations, est structurée de cette manière :
id | tag1 | tag2
1 | G0001 | G0001
2 | P0856 | P0856
3 | G4596 | G4596
4 | A0023 | A0023
5 | Z9999 | Z9999
6 | P0856 | G4596

Les 5 premiers tuples identifient des tags uniques.
Le tuple 6 associe les tuples 2 et 3... donc une petite requête met a jour en :
id | tag1 | tag2
1 | G0001 | G0001
2 | P0856 | P0856
3 | G4596 | G4596
4 | A0023 | A0023
5 | Z9999 | Z9999
2 | P0856 | G4596

Mais comment faire pour associer dans le même temps le tag G4596?
id | tag1 | tag2
1 | G0001 | G0001
2 | P0856 | P0856
2 | G4596 | G4596
4 | A0023 | A0023
5 | Z9999 | Z9999
2 | P0856 | G4596

Et lorsque ce sont des cascades beaucoup plus longues qui s'opèrent... là c'est la galère!
par ex en ajoutant encore :
id | tag1 | tag2
7 | A0023 | G0001
8 | G4596 | G0001

Je suppose qu'il me faut passer par des fonctions... que pour le coup je ne maîtrise pas!
Pour info ma table fait plus de 70000 lignes... donc des cascades sur plusieurs niveaux, il y en a!

Merci beaucoup à ceux qui pourraient me filer un coup de pouce!

Pat.

**punkoff** · 18/03/2015, 10h25

Bonjour,

A quoi va servir cet identifiant "unique" ?

Vous cherchez à ressortir quoi ?

**map34** · 18/03/2015, 11h39

Bon allez, je rentre dans le détail :

Les "tags" sont différentes marques (transpondeurs, balises, bagues, etc), toutes uniques, posées sur de grands animaux. Ces marques ont une durée de vie limitée et a chaque capture d'un animal, lorsqu'une marque est perdue (ou pour maintenance sur les balises actives), elle est remplacée par une autre. Chaque individu doit avoir au moins deux marques afin de garantir une traçabilité sur le long terme en cas de perte ou de difficulté de détection.
Il arrive que des transpondeurs (petites balises passives sous le peau) "migrent" et deviennent difficilement détectable. Du coup un transpondeur peut "disparaître" et "réapparaître" plus tard... Cela génère des difficultés surtout sur des animaux que l'on pensait "nouveaux" et dont nous nous apercevons quelques saisons plus tard qu'ils ont déjà été identifiés donc avec déjà un lot d'informations attitrées.

Des suivis scientifiques sont réalisés (biométrie, comportement, etc) lors de chaque contact avec les individus. En fonction du contact (capture ou observation), des marques différentes sont lues (transpondeurs nécessitent la capture, alors que les bagues sont lisibles a distance). Les informations collectées sont associées uniquement aux marques (tags) lisibles et lues, et je veux pouvoir compiler toutes les données d'un individu.

Donc l'identifiant que je cherche a obtenir est celui de l'individu associé à chaque tag.

Je ne sais pas si je répond à la question...?

Patrick

**map34** · 18/03/2015, 11h42

Envoyé par map34

Donc l'identifiant que je cherche a obtenir est celui de l'individu associé à chaque tag.

Je n'ai pas besoin d'un identifiant fixe auquel je doive pouvoir me référer... s'il est re-généré a chaque fois, c'est pas grave...

**punkoff** · 18/03/2015, 12h05

Envoyé par map34

Je n'ai pas besoin d'un identifiant fixe auquel je doive pouvoir me référer... s'il est re-généré a chaque fois, c'est pas grave...

ok.

Sinon pour le côté fonctionnel c'est sympa la description mais le 2eme poste est plus interessant techniquement.

Bref, vous avez une hiérarchie, comment détermine-t-on qu'un tuple est un père, une feuille, y a-t-il des cycles possibles entre les différents tags ?

La récursion peut répondre à votre problématique. (je ferai une proposition de requete cette aprem +)

**map34** · 18/03/2015, 12h53

Envoyé par punkoff

y a-t-il des cycles possibles entre les différents tags ?

Qu'entendez-vous par "cycle"?

**punkoff** · 18/03/2015, 13h05

ceci :

Code :

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

1
2
3
4
 
A => B
B => C
C => A

**map34** · 18/03/2015, 13h16

oui!
mais il n'est pas dérangeant de considérer :
A => B
B => C
A => C (au lieu de C => A)
dans ce cas nous n'avons pas de "cycle"

La difficulté avec l'arborescence est de savoir qui est parent ou enfant...

Merci,

Patrick.

PS : Je serais absent cet après-midi... de retour vers 19:00

**punkoff** · 18/03/2015, 13h25

Bah pour la récurssion c'est embetant, cest un coup a ce que la requete ne finisse jamais.

Bref, je vous ai mis un garde fou, je ne suis pas sur a 100% de la syntaxe de récurssion sous postgresql mais l'idée est là :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
with recursive tmp (id, tag1, tag2, id_pere, niveau) as (
select id, tag1, tag2, id, 1
from ma_table a
where tag1 = tag2 and not exists (select 1 from ma_table b where a.tag1 = b.tag2 and a.id <> b.id)
union all
select tmp.id, tmp.tag1, tmp.tag2, a.id_pere, a.niveau + 1
from tmp a
inner join ma_table on a.tag2 = ma_table.tag1 and a.id <> ma_table.id 
where a.niveau < 100)
select * from tmp

**map34** · 18/03/2015, 14h28

Pas encore parti.. j'ai regardé et retranscrit (ma table process.tagref, identifiant = idtgref, tag1=tag, tag2=syn) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
with recursive tmp (id, tag1, tag2, id_pere, niveau) as (
		select idtagref, tag, syn, idtagref, 1
		from process.tagref a
		where tag = syn and not exists (select 1 from process.tagref b where a.tag = b.syn and a.idtagref <> b.idtagref)
	union all
		select tmp.id, tmp.tag1, tmp.tag2, tmp.id_pere, tmp.niveau + 1
		from tmp
		inner join process.tagref on tmp.tag2 = tagref.tag and tmp.id <> tagref.idtagref
		where tmp.niveau < 20
	)
select * from tmp ORDER BY id, id_pere;

Dans le second select, la référence a l'alias "a" était ambigu, je ne sais pas si j'ai correctement corrigé...
J'ai limité le "garde fou" a 10 car là il partait dans des délires infinis...
Au final le résultat me donne un id systématiquement = à id_pere et tag1 systématiquement = à tag2, juste niveau qui change...)

Je ne suis pas familier de WITH RECURSIVE... et je ne capte pas bien la manip'...

Merci!

**punkoff** · 19/03/2015, 09h34

la 2eme sous requete y a un probleme :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
with recursive tmp (id, tag1, tag2, id_pere, niveau) as (
		select idtagref, tag, syn, idtagref, 1
		from process.tagref a
		where tag = syn and not exists (select 1 from process.tagref b where a.tag = b.syn and a.idtagref <> b.idtagref)
	union all
		select tagref.idtagref, tagref.tag, tagref.syn, tmp.id_pere, tmp.niveau + 1
		from tmp
		inner join process.tagref on tmp.tag2 = tagref.tag and tmp.id <> tagref.idtagref
		where tmp.niveau < 20
	)

ca devrait etre ok comme ca.

Pour tester et comprendre, indiquez un tag dans la 1ere requete à la place du not exists.

Pour l'explication lisez cet article : http://sqlpro.developpez.com/cours/s...te-recursives/

**map34** · 19/03/2015, 21h38

Envoyé par punkoff

ca devrait etre ok comme ca.

Maravelous !

Merci beaucoup.

Je viens de triturer tout ça dans tous les sens... je commence à capter.

Patrick.

PS : la requête SQL récursive s’exécute en 4931ms dans postgres contre 26h32 avec des boucles imbriquées en VBA dans M$Access

même pas mal!