Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 14 sur 14
  1. #1
    Invité régulier
    Inscrit en
    avril 2007
    Messages
    26
    Détails du profil
    Informations forums :
    Inscription : avril 2007
    Messages : 26
    Points : 6
    Points
    6

    Par défaut Transposition de colonnes variables

    Bonjour à tous,
    J'ai pas mal cherché pour trouver une réponse à cette question, mais je n'ai pas pu trouver. Voici mon problème : je dispose d'une table qui se présente un peu sous cette forme :
    id var1 var2 var3 var4 var5 ... varn
    1 1 10 8
    2 5
    3 2 4 9 3 5
    4 3 1 2
    ...

    Afin de réduire la taille de la table, j'aimerais la transformer pour qu'elle ait cette forme :
    id var
    1 1
    1 10
    1 8
    2 5
    3 2
    3 4
    3 9
    3 3
    3 5
    4 3
    4 1
    4 2

    Mon SGBD est MySQL.

    Avez-vous une idée ?
    Merci!

  2. #2
    Modérateur

    Homme Profil pro Fabien
    Ingénieur d'études en décisionnel
    Inscrit en
    septembre 2008
    Messages
    6 808
    Détails du profil
    Informations personnelles :
    Nom : Homme Fabien
    Âge : 36
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Ingénieur d'études en décisionnel
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : septembre 2008
    Messages : 6 808
    Points : 13 461
    Points
    13 461

  3. #3
    Invité régulier
    Inscrit en
    avril 2007
    Messages
    26
    Détails du profil
    Informations forums :
    Inscription : avril 2007
    Messages : 26
    Points : 6
    Points
    6

    Par défaut

    Merci!
    Je vais essayer la méthode des UNION ALL, mais j'ai peur d'en avoir pour des semaines, ma table faisant plusieurs dizaines de Go.

  4. #4
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 747
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 50
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 13 747
    Points : 22 930
    Points
    22 930

    Par défaut

    Il te faudrait 3 colonnes !
    id / var / valeur

    Sinon, comment vas-tu retrouver la valeur de la var5 de l'id 12 ?
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  5. #5
    Expert Confirmé
    Inscrit en
    août 2008
    Messages
    2 126
    Détails du profil
    Informations forums :
    Inscription : août 2008
    Messages : 2 126
    Points : 3 706
    Points
    3 706

    Par défaut

    Quel est l'intérêt ? Tel que présenté, je ne vois aucune simplification, si c'est pour plus de flexibilité il faut quand même faire attention...

    Ce type de modélistaion s'appelle l'EAV (entity attribute value), très peut adapté au SGBDR (R pour relationnelle).

    En effet si la demande est de récupérer les lignes pour var1=1 ET var2=10 il faudra faire une autojointure, et donc plus il y a de colonnes (dans le précédent modèle) interrogé, plus il y aura d'autojointure... C'est très vite peu performant !

    Notamment s'il y a des besoins de statistiques (aggrégation), ce modèle est totalement inexploitable... (autojointure et/ou pivot) !

  6. #6
    Invité régulier
    Inscrit en
    avril 2007
    Messages
    26
    Détails du profil
    Informations forums :
    Inscription : avril 2007
    Messages : 26
    Points : 6
    Points
    6

    Par défaut

    Merci pour vos réponses, je m'aperçois que je vous ai trop simplifié mon exemple pour qu'il soit compréhensible.
    Voilà, j'ai une table avec des centaines de colonnes et de dizaines de milliers de ligne.
    Parmi ces colonnes, certaines sont uniques, d'autres sont répétées :

    id_individu nom prenom age boisson1 boisson2 boisson3 boisson4 boissonn
    1 dupond jean 30 coca
    2 durand kévin 15 eau orangina coca
    3 dupont arthur 28 perrier vin orangina eau

    Intuitivement, on se dit que mes requêtes vont être complexe si je veux recueillir tous les individus qui prennent au moins du coca
    Code :
    SELECT * FROM table1 WHERE boisson1="coca" OR boisson2="coca" OR boisson3="coca" OR boisson4="coca" OR ... boissonn="coca"
    Je me suis dit que pour simplifier tout ça, je ferais mieux de créer une table 2 de la forme :
    id_individu boisson
    1 coca
    2 eau
    2 orangina
    2 coca
    3 perrier
    3 vin
    3 orangina
    3 eau

    Du coup, la requête est plus simple. Si je veux connaître l'âge moyen de ceux qui boivent du coca, il me suffit de réaliser une jointure entre la table1 et la table2

  7. #7
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 747
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 50
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 13 747
    Points : 22 930
    Points
    22 930

    Par défaut

    Tu peux faire encore mieux en modélisant correctement les données.

    Règle de gestion :
    Une personne peut boire plusieurs boissons et une boisson peut être bue par plusieurs personnes.

    MCD :
    personne -0,n----boire----0,n- boisson

    Tables :
    te_personne_prs (prs_id, prs_nom...)
    te_boisson_bsn (bsn_id, bsn_nom...)
    tj_prs_boire_bsn_pbb (pbb_id_personne, pbb_id_boisson...)

    Quelles sont toutes les personnes qui boivent du cidre ?
    Code :
    1
    2
    3
    4
    5
    SELECT p.prs_id, p.prs_nom
    FROM te_personne_prs p
    INNER JOIN tj_prs_boire_bsn_pbb j ON j.pbb_id_personne = p.prs_id
    	INNER JOIN te_boisson_bsn b ON b.bsn_id = j.pbb_id_boisson
    WHERE b.bsn_nom = 'cidre'
    Quelles sont toutes les boissons bues par Irken ?
    Code :
    1
    2
    3
    4
    5
    SELECT b.bsn_id, b.bsn_nom
    FROM te_boisson_bsn b
    INNER JOIN tj_prs_boire_bsn_pbb j ON j.pbb_id_boisson = b.bsn_id
    	INNER JOIN te_personne_prs p ON p.prs_id = j.pbb_id_personne
    WHERE p.prs_nom = 'Irken'
    C'est pas plus compliqué que ça le SQL !

    EDIT :
    Avec ta dernière demande :
    Si je veux connaître l'âge moyen de ceux qui boivent du coca
    Code :
    1
    2
    3
    4
    5
    SELECT AVG(p.prs_age) AS moyenne_age
    FROM te_personne_prs p
    INNER JOIN tj_prs_boire_bsn_pbb j ON j.pbb_id_personne = p.prs_id
    	INNER JOIN te_boisson_bsn b ON b.bsn_id = j.pbb_id_boisson
    WHERE b.bsn_nom = 'coca'
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  8. #8
    Invité régulier
    Inscrit en
    avril 2007
    Messages
    26
    Détails du profil
    Informations forums :
    Inscription : avril 2007
    Messages : 26
    Points : 6
    Points
    6

    Par défaut

    Ok,
    mais en fait, comment je passe de ma grosse table à ces 3 petites tables ?
    Et puis en réfléchissant d'après le post de skuatamad, je m'aperçois que je vais devoir faire des autojointures si je veux connaître tous les individus qui prennent par exemple à la fois du cidre ET du coca, ce qui risque de pas être très efficient.

    Si je comprends bien, j'ai 3 possibilités :
    - Garder une seule grosse table et faire des requêtes super longues
    - Opter pour un modèle relationnel
    - Opter pour un modèle en étoile

    Comme vous l'aurez compris, je ne suis pas un spécialiste des bases de données, donc laquelle me préconiseriez vous, et surtout si la solution retenue est la 2ème ou la 3ème, comment faire pour passer de ma grosse table à ce nouveau modèle ?

  9. #9
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 747
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 50
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 13 747
    Points : 22 930
    Points
    22 930

    Par défaut

    Citation Envoyé par Irken Voir le message
    Ok,
    mais en fait, comment je passe de ma grosse table à ces 3 petites tables ?
    Ça, il va y avoir un peu de boulot mais ça doit pouvoir s'automatiser dans une procédure SQL ou en PHP si c'est vraiment trop long de copier coller la requête en changeant juste le nom de la colonne.

    Avec la structure que j'ai donnée tout à l'heure :
    Citation Envoyé par CinéPhil
    Tables :
    te_personne_prs (prs_id, prs_nom...)
    te_boisson_bsn (bsn_id, bsn_nom...)
    tj_prs_boire_bsn_pbb (pbb_id_personne, pbb_id_boisson...)
    Insertion des individus de la table actuelle dans la nouvelle table des personnes :
    Code :
    1
    2
    3
    INSERT INTO te_personne_prs (prs_nom, prs_prenom, prs_age)
    SELECT DISTINCT nom, prenom, age
    FROM table_actuelle;
    Insertion des boissons dans la nouvelle table des boissons :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    INSERT INTO te_boisson_bsn (bsn_nom)
    SELECT DISTINCT boisson1
    FROM table_actuelle
    WHERE boisson1 IS NOT NULL
    UNION
    SELECT DISTINCT boisson2
    FROM table_actuelle
    WHERE boisson2 IS NOT NULL
    -- etc.
    Insertion dans la table de jointure :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    INSERT INTO tj_prs_boire_bsn_pbb (pbb_id_personne, pbb_id_boisson)
    SELECT p.prs_id, b.bsn_id
    FROM table_actuelle a
    INNER JOIN te_personne_prs p
    	ON p.prs_nom = a.nom
    	AND p.prs_prenom = a.prs_prenom
    	AND p.prs_age = a.age
    INNER JOIN te_boisson_bsn b
    	ON b.bsn_nom = a.boisson1
    Il faudra répéter la dernière requête en changeant juste le nom de la colonne de boisson dans la dernière jointure, pour toutes les colonnes de boisson.

    Et puis en réfléchissant d'après le post de skuatamad, je m'aperçois que je vais devoir faire des autojointures si je veux connaître tous les individus qui prennent par exemple à la fois du cidre ET du coca, ce qui risque de pas être très efficient.
    Non.
    Code :
    1
    2
    3
    4
    5
    6
    7
    SELECT p.prs_id, p.prs_nom, p.prs_prenom, p.prs_age
    FROM te_personne_prs p
    INNER JOIN tj_prs_boire_bsn_pbb j ON j.pbb_id_personne = p.prs_id
    	INNER JOIN te_boisson_bsn b ON b.bsn_id = j.pbb_id_boisson
    WHERE b.bsn_nom IN ('cidre', 'coca')
    GROUP BY p.prs_id, p.prs_nom, p.prs_prenom, p.prs_age
    HAVING COUNT(j.pbb_id_boisson) = 2
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  10. #10
    Expert Confirmé
    Inscrit en
    août 2008
    Messages
    2 126
    Détails du profil
    Informations forums :
    Inscription : août 2008
    Messages : 2 126
    Points : 3 706
    Points
    3 706

    Par défaut

    id_individu nom prenom age boisson1 boisson2 boisson3 boisson4 boissonn
    Ok cette table n'est pas normalisée, il faut effectivement faire des modifications.

    Un modèle comme id / var / valeur c'est de l'EAV, très flexible mais souvent une mauvaise idée.

    Le modèle proposé par cinephil n'est pas de l'EAV.
    Il utilise une table associative entre les boissons et les individus, mais effectivement les ET sont plus complexes à obtenir à partir de lignes que de colonnes.

  11. #11
    Membre Expert
    Inscrit en
    août 2009
    Messages
    1 041
    Détails du profil
    Informations forums :
    Inscription : août 2009
    Messages : 1 041
    Points : 1 601
    Points
    1 601

    Par défaut

    Citation Envoyé par skuatamad Voir le message
    Ok cette table n'est pas normalisée, il faut effectivement faire des modifications.

    Un modèle comme id / var / valeur c'est de l'EAV, très flexible mais souvent une mauvaise idée.
    Pour peu qu'on partitionne par var, ça revient à avoir N tables différentes d'association, mais en plus simple à gérer.

  12. #12
    Expert Confirmé
    Inscrit en
    août 2008
    Messages
    2 126
    Détails du profil
    Informations forums :
    Inscription : août 2008
    Messages : 2 126
    Points : 3 706
    Points
    3 706

    Par défaut

    Il y a quand même répétition de la valeur. De plus l'objectif de l'EAV c'est de pouvoir ajouter des VAR sans faire de DDL.
    Je ne suis pas expert en partitionnement, mais je pense que ce partitionnement dynamique est impossible...

    Si l'EAV était vraiment la panacée, la modélisation d'un SGBDR n'existerait même pas. Poussé à l'extreme, toutes les applis peuvent être gérées avec seulement 4 tables... sauf que ça ne fonctionne pas.
    http://tkyte.blogspot.fr/2009/01/thi...-to-watch.html

  13. #13
    Membre Expert
    Inscrit en
    août 2009
    Messages
    1 041
    Détails du profil
    Informations forums :
    Inscription : août 2009
    Messages : 1 041
    Points : 1 601
    Points
    1 601

    Par défaut

    Citation Envoyé par skuatamad Voir le message
    Il y a quand même répétition de la valeur. De plus l'objectif de l'EAV c'est de pouvoir ajouter des VAR sans faire de DDL.
    Je ne suis pas expert en partitionnement, mais je pense que ce partitionnement dynamique est impossible...
    Créer une partition peut être light. Et surtout, le modèle EAV peut être intéressant du point de vue applicatif pour les mises à jour rapides, en développement agile avec beaucoup de développeurs par exemple.
    Attention, que les choses soient claires, je ne défend pas de façon acharnée le modèle EAV (et dans le cas général, au contraire), je dis simplement qu'il peut être pratique d'utilisation dans certains cas sans pour autant tuer complètement les performances.

  14. #14
    Invité régulier
    Inscrit en
    avril 2007
    Messages
    26
    Détails du profil
    Informations forums :
    Inscription : avril 2007
    Messages : 26
    Points : 6
    Points
    6

    Par défaut

    En tout cas merci à tous !

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

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •