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 :

Joindre deux tables en supprimant des lignes en trop


Sujet :

Requêtes PostgreSQL

  1. #1
    Membre à l'essai
    Joindre deux tables en supprimant des lignes en trop
    Bonjour à tous.

    Je travaille sur une table contenant: nom, prenom, date_naissance, sexe (environ 600 000 lignes). Appelons la A

    J'essaie de la joindre avec une table qui contient un autre ensemble de noms, prenoms d'une autre table (environ 5000 lignes). Appelons la B

    Mon objectif est de joindre les deux, pour réussir à sortir le nombre de femme et d'homme de la table B.

    Problème: il faut que je supprime les lignes doublons dans la table A (il y a de nombreux prénoms qui apparaissent un grand nombre de fois)
    sinon, le JOIN me renvoi un nombre de prénoms qui correspond à la table A et non à la table B.

    Je ne sais pas comment supprimer les lignes qui correspondent aux prénoms en doubles. J'ai essayé plusieurs solutions dont un SELECT DISTINCT, mais rien de pertinent.

    J'espère que mon problème est suffisamment clair.

  2. #2
    Modérateur

    Citation Envoyé par Balibar Voir le message
    J'espère que mon problème est suffisamment clair.
    Pas tout à fait...

    Un petit exemple avec la structure des tables, la requête exécutée et un jeu de données en précisant le réultat attendu et le résultat obtenu ne serait pas de trop.
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  3. #3
    Membre à l'essai
    Voici la structure des tables avec quelques données. J'espère que ça servira:

    Première table. C'est la liste des prenoms de l'INSEE (prenominsee) d'environ 600 000 personnes

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    sexe | prenom | date_naissance | nombre 
    ------+--------+----------------+--------
        1 | A      |           1980 |      3
        1 | A      |           1998 |      3
        1 | A      |                |     22
        1 | AADAM  |           2009 |      4
        1 | AADAM  |           2014 |      3
        1 | AADAM  |           2016 |      4
        1 | AADAM  |           2017 |      4
        1 | AADAM  |                |      9
        1 | AADEL  |           1976 |      5
        1 | AADEL  |           1978 |      3


    Seconde table (utilisateur): C'est la liste des utilisateurs de la base de données participative dont je me sers (j'ai modifié les noms pour l'exemple), qui comporte environ 5000 personnes enregistrées.

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    id_utilisateur |nom     |  prenom  
    ----------------+------------------------+----------
                273 | GIUSEPPE      | ALAIN
               2738 | PATRIN           | GUY
               3382 | Pattari             | YANN
               3836 | verleiyne          | C.
               4004 | R                     | LILIANE
               4315 | DUVALON        | G.
               5074 | Magino             | YVES
               5196 | PINAILLE         | BERTRAND
                 58 | NANSTOIS        | THIERRY
                 61 | RIOLO               | THIERRY


    Bon, mon but final est d'obtenir le nombre d'homme et de femmes dans ma table "utilisateur".
    Comme vous le voyez, le sexe n'est pas présent dans la table "utilisateur" mais il est spécifié dans la table "prenominsee".

    Je voulais donc faire une jointure entre les deux tables, par le champ "prénom" de manière à obtenir le sexe correspondant à chaque prénom de la table "utilisateur".

    Mon essai de requête:

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    SELECT u.prenom, COUNT(u.prenom), p.sexe
    FROM utilisateur u
    INNER JOIN prenominsee p ON u.prenom = p.prenom
    GROUP BY u.prenom, p.sexe
    ORDER BY u.prenom;


    Cette requete me sort les prénoms de la table utilisateur, le sexe de chaque prénom, mais là ou je bloque c'est que pour chaque prénom, le COUNT() me sort le nombre d'occurence de ce prenom dans la table "prenominsee",
    alors que je souhaite le nombre d’occurrences dans la table "utilisateur"
    Note: Je sais que la requête est incomplète pour obtenir le nombre d'hommes et femmes. Je procède par étapes et j'aimerais d'abord sortir le bon nombre d’occurrences de chaque prénom avant d'aller plus loin.

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    prenom      | count | sexe 
    -----------------+-------+------
     ABRAHAM         |   115 |    1
     ADELINE         |   708 |    2
     ADIL            |    48 |    1
     ADRIEN          |    30 |    2
     ADRIEN          |  1180 |    1
     AGLAE           |    70 |    2
     AGNES           |    63 |    2
     ALAIN           |  6136 |    1
     ALAIN           |   260 |    2


    Je pense qu'il faut que je trouve le moyen de sélectionner les prénoms uniques avec dans "prenominsee" avec la clause DISTINCT. Mais je n'arrive pas à trouver comment l'insérer pour que tout fonctionne...

  4. #4
    Membre expérimenté
    Citation Envoyé par Balibar Voir le message
    COUNT() me sort le nombre d'occurence de ce prenom dans la table "prenominsee"
    ..que s'il n'existe qu'un seul prénom correspondant dans la table "utilisateur".
    Le "travail" normal d'une jointure est de faire l'association de toutes les lignes correspondantes de chaque table.

    Pour votre problème la difficulté est qu'il existe des prénoms unisexe, Dominique par exemple.
    Du coup vous aurez 2* plus de lignes comptées (le même nombre en femme qu'en homme)

    La déduction du sexe par rapport au prénom nécessitera de passer par un coefficient de répartition.

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    CREATE TABLE SCORE_PRENOM_SEXE
    ( Prenom varcahr(25) not null primary key
    , Pct_homme decimal (5,4)
    , Pct_femme decimal (5,4)
    );


    Et l'alimenter avec le résultat de :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    with 
    Nb_Prenom_sexe as
    ( select prenom
    , case sexe when 1 then sum(nombre) else 0 end  as Nb_homme
    , case sexe when 2 then sum(nombre) else 0 end  as Nb_femme
    from prenominsee
    group by prenom
    )
    SELECT prenom
     , 1.000*Nb_homme/(Nb_homme+Nb_femme) as Pct_homme
     , 1.000*Nb_femme/(Nb_homme+Nb_femme) as Pct_femme
    from Nb_Prenom_sexe NPS


    A partir de là on peut déterminer la proportion globale par sexe en fonction des coefficients.

    Nb : êtes vous sûr que tous les prénoms sont effectivement répertoriés dans la table "prenominsee" ? (faute de frappe, loupé, ...)
    Le savoir est une nourriture qui exige des efforts.

  5. #5
    Expert éminent sénior
    Bonsoir,

    Cette modélisation aberrante produira un résultat très approximatif !

    Le code sexe devrait être un attribut de la table des personnes, le prénom seul ne garantit en rien le genre d'une personne :
    - il existe des prénoms mixtes (claude, dominique, jackie, morgan...)
    - il existe des prénoms dont l'orthographe des versions masculine et féminine sont très proches, une erreur de saisie et pan !
    - depuis quelques années, les prénoms sont à peu près libres et à l'initiative des parents, aucun dictionnaire ne saurait donc les recenser.

    Donc, si vous en avez la possibilité, revoyez d'urgence ce modèle de données, à défaut, alertez sur l'imprécision des résultats fournis

  6. #6
    Membre expérimenté
    Citation Envoyé par escartefigue Voir le message
    Cette modélisation aberrante produira un résultat très approximatif !
    Oh que oui.
    Mais quand tu dois faire le taf et que tu n'as pas la donnée source comment faire mieux ?

    Les approches statistiques permettent de faire du big data, les prévisions météo, mais, toujours pas de gagner au loto .
    Comme quoi produire quelque chose d'incertain peut être recevable.

    Une autre approche serait de retenir dans la table insee que les prénom qui sont sexué à 100% (Luc, Janine, ...) et de ne retenir qu'eux.
    Ca élimine les "claude" (unisexe) et les "francoise" (faute de frappe)
    Si l’échantillon retenu reste représentatif, alors, la stat peut être jugée comme recevable.

    A voir en fonction du besoin.
    Le savoir est une nourriture qui exige des efforts.