Transposition de colonnes variables [Résolu]

[Irken]

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!

[Waldar]

Regardez ce sujet :
http://www.developpez.net/forums/d12...ganiser-table/

[Irken]

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.

[CinePhil]

Il te faudrait 3 colonnes !
id / var / valeur

Sinon, comment vas-tu retrouver la valeur de la var5 de l'id 12 ?

[skuatamad]

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) !

[Irken]

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

[CinePhil]

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'

[Irken]

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 ?

[CinePhil]

Envoyé par Irken

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 :

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

[skuatamad]

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.

[Rei Ichido]

Envoyé par skuatamad

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.

[skuatamad]

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

[Rei Ichido]

Envoyé par skuatamad

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.

[Irken]

En tout cas merci à tous !