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 :

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


Sujet :

Requêtes PostgreSQL

  1. #1
    Futur Membre du Club
    Homme Profil pro
    paysan débrouillard
    Inscrit en
    mars 2015
    Messages
    9
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : paysan débrouillard
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : mars 2015
    Messages : 9
    Points : 7
    Points
    7
    Par défaut Gérer des identifiants : traitement récursif ou pas !
    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.

  2. #2
    Expert confirmé
    Homme Profil pro
    Inscrit en
    mai 2002
    Messages
    3 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : mai 2002
    Messages : 3 173
    Points : 5 337
    Points
    5 337
    Par défaut
    Bonjour,

    A quoi va servir cet identifiant "unique" ?

    Vous cherchez à ressortir quoi ?

  3. #3
    Futur Membre du Club
    Homme Profil pro
    paysan débrouillard
    Inscrit en
    mars 2015
    Messages
    9
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : paysan débrouillard
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : mars 2015
    Messages : 9
    Points : 7
    Points
    7
    Par défaut
    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

  4. #4
    Futur Membre du Club
    Homme Profil pro
    paysan débrouillard
    Inscrit en
    mars 2015
    Messages
    9
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : paysan débrouillard
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : mars 2015
    Messages : 9
    Points : 7
    Points
    7
    Par défaut
    Citation Envoyé par map34 Voir le message
    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...

  5. #5
    Expert confirmé
    Homme Profil pro
    Inscrit en
    mai 2002
    Messages
    3 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : mai 2002
    Messages : 3 173
    Points : 5 337
    Points
    5 337
    Par défaut
    Citation Envoyé par map34 Voir le message
    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 +)

  6. #6
    Futur Membre du Club
    Homme Profil pro
    paysan débrouillard
    Inscrit en
    mars 2015
    Messages
    9
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : paysan débrouillard
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : mars 2015
    Messages : 9
    Points : 7
    Points
    7
    Par défaut
    Citation Envoyé par punkoff Voir le message
    y a-t-il des cycles possibles entre les différents tags ?
    Qu'entendez-vous par "cycle"?

  7. #7
    Expert confirmé
    Homme Profil pro
    Inscrit en
    mai 2002
    Messages
    3 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : mai 2002
    Messages : 3 173
    Points : 5 337
    Points
    5 337
    Par défaut
    ceci :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    A => B
    B => C
    C => A

  8. #8
    Futur Membre du Club
    Homme Profil pro
    paysan débrouillard
    Inscrit en
    mars 2015
    Messages
    9
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : paysan débrouillard
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : mars 2015
    Messages : 9
    Points : 7
    Points
    7
    Par défaut
    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

  9. #9
    Expert confirmé
    Homme Profil pro
    Inscrit en
    mai 2002
    Messages
    3 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : mai 2002
    Messages : 3 173
    Points : 5 337
    Points
    5 337
    Par défaut
    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

  10. #10
    Futur Membre du Club
    Homme Profil pro
    paysan débrouillard
    Inscrit en
    mars 2015
    Messages
    9
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : paysan débrouillard
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : mars 2015
    Messages : 9
    Points : 7
    Points
    7
    Par défaut
    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!

  11. #11
    Expert confirmé
    Homme Profil pro
    Inscrit en
    mai 2002
    Messages
    3 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : mai 2002
    Messages : 3 173
    Points : 5 337
    Points
    5 337
    Par défaut
    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/

  12. #12
    Futur Membre du Club
    Homme Profil pro
    paysan débrouillard
    Inscrit en
    mars 2015
    Messages
    9
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : paysan débrouillard
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : mars 2015
    Messages : 9
    Points : 7
    Points
    7
    Par défaut [RESOLU] [9.3] Gérer des identifiants : traitement récursif ou pas !
    Citation Envoyé par punkoff Voir le message
    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!

  13. #13
    Membre expérimenté
    Avatar de minot83
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    mars 2006
    Messages
    974
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 49
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Administration - Collectivité locale

    Informations forums :
    Inscription : mars 2006
    Messages : 974
    Points : 1 433
    Points
    1 433
    Par défaut
    Citation Envoyé par map34 Voir le message
    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!
    Marseillais
    si le message est pertinent : un petit si votre problème est résolu, n'oubliez pas le Tag Merci

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 1
    Dernier message: 21/05/2014, 11h08
  2. Réponses: 1
    Dernier message: 12/03/2012, 16h52
  3. Gérer des fichiers "à pas fixe" ou "record" avec C#
    Par StringBuilder dans le forum C#
    Réponses: 10
    Dernier message: 01/12/2011, 15h48
  4. Scan récursif des répertoire, ça ne liste pas tout
    Par panthere noire dans le forum C++
    Réponses: 0
    Dernier message: 14/04/2009, 02h58
  5. Réponses: 5
    Dernier message: 14/09/2007, 21h44

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