Lignes vers colonnes [Résolu]

[Chello]

Bonjour à tous,

J'ai un problème simple et classique, mais dont je ne trouve pas la solution.

Il s'agit d'un projet scientifique à vocation statistique et visant une certaine souplesse d'exploitation, ce qui explique que je stocke mes données de manière peu orthodoxe.

Pour faire court, j'ai une table data de type :

Code :

1
2
3
4
5
6
7
8
id_individu | id_variable | valeur
==================================
1           | 1           | "rouge"
1           | 2           | 2
1           | 3           | "beaucoup"
2           | 1           | "bleu"
2           | 2           | 3
2           | 3           | "un peu"

etc. (associées à deux autres tables, l'une pour les individus, l'autre les variables).

et je veux créer une requête me permettant de "recolonniser" (dsl) les données en tableau de type "excel", comme suit :

Code :

1
2
3
4
id_individu | var1    | var2 | var3
==================================
1           | "rouge" | 2    | "beaucoup"
2           | "bleu"  | 3    | "un peu"

J'ai commencé par utiliser des sous requêtes :

Code :

1
2
3
4
5
SELECT id_individu, 
(SELECT value FROM `data` WHERE id_var = '1') AS var1,
(SELECT value FROM `data` WHERE id_var = '2') AS var2,
(SELECT value FROM `data` WHERE id_var = '3') AS var3
FROM `data`

Ça marche. Sauf qu'avec 300 variables et 700 individus pour plus de 50000 lignes, la bécane s'étouffe à chaque coup (déjà au-delà de 10, c'est dur...). Il me faut donc une autre méthode.

J'ai songé aux RIGHT OUTER JOIN, et à utiliser des conditions (IF (id_var = '1', value, ''), mais ça répond pas au problème.

Je cherche encore mais si l'un d'entre vous connaît la solution ou est prêt à donner une piste, je suis preneur...

Merci d'avance et bonne journée,

==> Je précise : je suis sous MySQL 5.0.

[CinePhil]

Ce que tu cherches à faire est de la cosmétique et n'est pas le boulot du SGBD mais celui du programme qui présente les données à l'utilisateur. Tu as d'ailleurs découvert toi-même les limites de ce bricolage qui s'avère irréaliste au delà de quelques valeurs !

[punkoff]

Bonjour,

sous MySql vous avez une fonction group_concat qui correspond à peu prés à votre demande.

sinon ce que vous essayez de faire n'étant pas vraiment orthodoxe c'est normale que mysql ait du mal.

Ceci devrait être géré par un programme qui génère votre rapport.

[Chello]

Merci pour vos réponses éclairs.

Effectivement, j'aurais dû préciser que la programmation (php) était la voie initiale et privilégiée pour résoudre à ce problème.
Problème : là aussi, ça patine et j'ai des soucis de mémoire : le script bouffe 135 Mo et je n'ai pas encore mis la main sur le bon paramètre pour élargir la mémoire (alors que le memory_limit est de 256 Mo dans php.ini).

Par conséquent, comme on me demande à titre urgent les données provisoires sous format csv, j'essaie de passer par une requête manuelle exécutée sous phpMyAdmin.

J'ai aussi songé à GROUP_CONCAT, mais comme toutes les variables ne sont pas renseignées (et dans le désordre), je risque d'avoir de sérieux décalages.

edit : Woops, il bouffe 134217728 bytes, i.e. 128 Mo. Pourtant, j'ai modifié le plafond...

[Snipah]

Bonjour,

Essayez avec un CASE :

Code :

1
2
3
4
5
6
7
SELECT 
       id_individu, 
       max (CASE WHEN id_variable = 1 THEN valeur ELSE NULL END) AS var1,
       max (CASE WHEN id_variable = 2 THEN valeur ELSE NULL END) AS var2,
       max (CASE WHEN id_variable = 3 THEN valeur ELSE NULL END) AS var3
FROM DATA
GROUP BY id_individu

[CinePhil]

Envoyé par Chello

Effectivement, j'aurais dû préciser que la programmation (php) était la voie initiale et privilégiée pour résoudre à ce problème.
Problème : là aussi, ça patine et j'ai des soucis de mémoire : le script bouffe 135 Mo et je n'ai pas encore mis la main sur le bon paramètre pour élargir la mémoire (alors que le memory_limit est de 256 Mo dans php.ini).

Quel est l'algorithme de ce script ?

Par conséquent, comme on me demande à titre urgent les données provisoires sous format csv, j'essaie de passer par une requête manuelle exécutée sous phpMyAdmin.

Ben tu expliqueras au demandeur que ce n'est pas comme ça qu'on travaille avec un SGBD et qu'en plus MySQL n'est pas constitué pour travailler ainsi, notamment parce qu'il ne connaît pas les requêtes récursives.

J'ai aussi songé à GROUP_CONCAT, mais comme toutes les variables ne sont pas renseignées (et dans le désordre), je risque d'avoir de sérieux décalages.

De toutes façon, GROUP_CONCAT te mettrait toutes les valeurs en chaîne dans une seule colonne : " 'rouge', 2, 'beaucoup' "

[Chello]

@CinePhil
Le script stocke dans un tableau toutes les données sous forme d'objets (objet de classe variable, objet de classe individu, objet de classe data). C'est lui qui bouffe tout.

Par ailleurs, ayant conçu le système, je suis engagé sur le résultat. Ça fonctionnait bien avec quelques centaines d'individu sur 50 variables. C'est mon premier "gros" projet. J'optimise au fur et à mesure.

@Snipah
Je viens d'essayer CASE à l'arrache. Ça pourrait peut-être fonctionner. Je creuse ça en début d'apm. Merci !

[punkoff]

Si vous ramenez toute votre BDD en mémoire via des objets c'est sur que ça va prendre des ressources.

Là vous essayez de faire un csv (ou xls), pourquoi ne pas le générer directement avec la sortie de votre requête sql ?
Si elle est ordonnée ca devrait être assez simple de traiter ligne par ligne le résultat.

[CinePhil]

Si c'est seulement l'id_individu qui vous intéresse, ramenez toute votre table data ordonnée par individu et par variable dans un tableau PHP puis gérez les ruptures de personnes et de variables dans la présentation des données. C'est assez classique à faire en PHP et avec 50 000 lignes, ça ne devrait pas poser de gros problèmes.

Si vous voulez le nom des individus et le nom des variables, voire d'autres infos complémentaires, faites une jointure de votre table data avec les tables individus et variables.

Code :

1
2
3
4
5
6
7
SELECT i.nom_individu AS individu,
    v.nom_variable AS variable,
    d.valeur
FROM DATA d
INNER JOIN individu i ON i.id_individu = d.id_individu
INNER JOIN variable v ON v.id_variable = d.id_variable
ORDER BY i.nom_individu, v.nom_variable

Vous pouvez d'abord extraire tous les noms de variables pour construire votre tableau de présentation de données. En supposant que la table variable existe et que chaque variable a un nom unique :

Code :

1
2
3
SELECT nom_variable
FROM variable
ORDER BY nom_variable

Avec ça, vous pouvez déjà construire l'entête du tableau de présentation des données. 300 variables, je n'aimerais pas être à la place de celui qui lira les données à l'écran !

Ensuite, dans une boucle parcourant le tableau issu de la première requête, vous gérer les changement d'individus pour construire les lignes et, à l'intérieur de chaque ligne, vous comparez la liste des variables de l'individu à la liste de toutes les variables pour gérer l'affichage de la valeur ou rien si la variable n'a pas de valeur pour l'individu considéré.

Mais tout ceci est plutôt à voir dans le forum PHP.

[Chello]

Bon, l'opérateur CASE ne convient pas. Comme je commence tout juste les opérateurs SQL, je crois comprendre que c'est logique : après le premier cas positif, il sort de la boucle. Par conséquent, j'ai toujours une colonne renseignée, mais les deux autres (sur un test avec trois colonnes) sont systématiquement nulles.

Voici ce que j'ai essayé :

Code :

1
2
3
4
5
6
SELECT id_individu, 
       (CASE WHEN DATA.id_var = '1042' THEN value ELSE NULL END) AS var1,
       (CASE WHEN DATA.id_var = '1043' THEN value ELSE NULL END) AS var2,
       (CASE WHEN DATA.id_var = '1045' THEN value ELSE NULL END) AS var3
FROM `data`
GROUP BY id_individu

J'ai basculé sur IF() :

Code :

1
2
3
4
5
6
SELECT id_individu 
       IF(DATA.id_var = '1042', value, NULL) AS var1,
       IF(DATA.id_var = '1043', value, NULL) AS var2,
       IF(DATA.id_var = '1044', value, NULL) AS var3
FROM `data`
GROUP BY id_individu

Résultat rigoureusement équivalent. Par contre, l'éditeur me fait ressortir les IF en rouge. Pourtant, je crois pas qu'il y ait d'erreur.

Le problème, c'est GROUP BY. Il ne remplace pas les champs NULL par les champs non NULL ; il ne fait que conserver la première ligne... C'est bête, ça...

@CinePhil
Effectivement, je crois que ça va se terminer comme ça, avec un traitement php d'une requête. Je perds pas mal de fonctions dans l'affaire (contrôle et versions multiples des mêmes données), mais j'essaierai de rectifier avec le temps.

[Snipah]

Parce que vous avez enlevé les max, regardez à nouveau mon exemple, vous devriez obtenir ce que vous voulez.

Edit : d'ailleurs un autre SGBD que MySQL vous aurait interdit de faire une telle requête. Du coup il ne conserve qu'une ligne "au hasard", donc forcément pour chaque id_individu une seul variable est renseignée.

[Chello]

HouUUUUuu pinaise !

Merci Snipah !

J'avais bien mis le max() au début, mais il se plaignait de ne pas trouver la fonction. J'avais donc laissé tomber et retirer la fonction, ce qui semblait encore... fonctionner.
Avec MAX en majuscule, ça marche !

Et je comprends maintenant l'utilité de MAX() : si supérieur ou différent de NULL, alors je garde. Sinon, je jette.

Hurrah !! Parce qu'autrement, même en passant par un simple tableau php pour traiter une requête SQL "en ligne", toute ma mémoire y passe... !

[Snipah]

Et je comprends maintenant l'utilité de MAX() : si supérieur ou différent de NULL, alors je garde. Sinon, je jette.

Pas vraiment, c'est une fonction d'agrégation qui conserve la valeur maximum de l'expression placée entre parenthèse pour le groupement demandé.

Si vous avez un peu de temps pour vous renseigner, vous pouvez lire par exemple le cours de sqlpro : http://sqlpro.developpez.com/cours/sqlaz/ensembles/

[Chello]

Je découvre en effet pas mal de choses. C'est bon pour la suite.

Merci encore !