Tableaux croisés MySQL

**PhilLU** · 28/01/2018, 15h55

Bonjour,
je souhaite récupérer des données de manière croisée.
Je sèche sur une solution efficace, ma table contient 50000 mesures :-(
Attention, le nombre de probes n'est pas fixe!!!

la table d'entrée est de ce type:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
+---------------------+-----------------+-------------------+
|      DateTime       |     Probe       |   Temperature     +
+---------------------|-----------------+-------------------+
|     24/01/2018      |       1         |      21.5         +
+---------------------|-----------------+-------------------+
|     24/01/2018      |       2         |      22.5         +
+---------------------|-----------------+-------------------+
|     24/01/2018      |       3         |      23.5         +
+---------------------|-----------------+-------------------+
|     25/01/2018      |       1         |      18.5         +
+---------------------|-----------------+-------------------+
|     25/01/2018      |       2         |      19.5         +
+---------------------|-----------------+-------------------+
|     25/01/2018      |       3         |      20.5         +
+---------------------|-----------------+-------------------+
|     26/01/2018      |       1         |      15.5         +
+---------------------|-----------------+-------------------+
|     26/01/2018      |       3         |      15.5         +
+---------------------|-----------------+-------------------+
|     27/01/2018      |       1         |      17.5         +
+---------------------|-----------------+-------------------+
|     27/01/2018      |       2         |      18.5         +
+---------------------|-----------------+-------------------+

la table en sortie devrait être de ce type:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
+---------------------+-----------------+-------------------+-------------------+
|      DateTime       |     Probe 1     |      Probe 2      +      Probe 3      +
+---------------------+-----------------+-------------------+-------------------+
|     24/01/2018      |      21.5       |      22.5         |      23.5         +
+---------------------+-----------------+-------------------+-------------------+
|     25/01/2018      |      18.5       |      19.5         |      20.5         +
+---------------------+-----------------+-------------------+-------------------+
|     26/01/2018      |      15.5       |                   |      15.5         +
+---------------------+-----------------+-------------------+-------------------+
|     27/01/2018      |      17.5       |      18.5         |                   +
+---------------------+-----------------+-------------------+-------------------+

Merci pour vos idées

PhilLU

**escartefigue** · 28/01/2018, 16h38

Bonjour,

Voici une solution possible sous réserve qu'il y ait au maximum 3 mesures (probe) par date comme semble le montrer votre exemple
J'ai utilisé des jointures outer pour le cas où il peut y avoir moins de 3 mesures pour une date. Si c'est toujours 3, vous pouvez utiliser des jointures inner

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
select t1.madate
     , t1.probe as probe1
     , t2.probe as probe2
     , t3.probe as probe3
from matable t1
left join matable t2
  on t2.madate=t1.madate
 and t2.probe >t1.probe
left join matable t3
  on t3.madate=t1.madate
 and t3.probe >t2.probe
order by t1.madate

**pier.antoine** · 28/01/2018, 17h15

Bonjour à toutes et à tous

Sauf erreur de ma part, j'avais cru comprendre que ce genre de présentation était plus du ressort de l'affichage, donc php ou autre langage.

Même si on peut y parvenir avec Mysql (merci Escartefigue), cela est-il possible (facilement) avec plus de colonnes?

Pierre

**escartefigue** · 28/01/2018, 18h46

Tout à fait d'accord sur l'aspect cosmétique, à prendre en compte coté traitement

MySQL n'accepte pas la fonction PIVOT, donc non pas de solution élégante si le nombre de colonne à afficher est important

**PhilLU** · 29/01/2018, 00h37

Bonsoir,
J'ai trouvé ce code:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN clName = ''',
      clName,
      ''' THEN score else ''-'' END) AS `',
      clName, '`'
    )
  ) INTO @sql
FROM clients;
 
SET @sql 
  = CONCAT('SELECT s.playdate, ', @sql, ' 
            from clients c
            inner join scores s
              on c.clid = s.clid
            group by s.playdate');
 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

A cette adresse
https://stackoverflow.com/questions/...-view-in-mysql

mais je n'y comprends pas grand chose...

**Artemus24** · 29/01/2018, 14h29

Salut PhilLU.

Envoyé par PhilLU

Attention, le nombre de probes n'est pas fixe!!!

Ca dépend de la présentation que vous désirez obtenir.
Si vous désirez mettre un résultat dans une colonne, il nous faut impérativement avoir un nombre de probes fixes !

Si vous désirez regrouper tous vos probes dans la même colonne, et traiter cela par la suite en php, le nombre de probes variables ne posent aucun problème.

La solution proposée par Escartefigue consiste à créer une jointure par valeur de votre colonne "probes".
L'idée est là, sauf que la solution proposée n fonctionne pas.

Je tiens à souligner qu'il faut préciser "left outer join" dans la jointure car si un probes n'existe pas, la ligne n'apparait pas.

Dans mon exemple suivant, la première requête est celle d'Escartefigue, et comme on peut le voir, elle ne fonctionne pas.
Des répétitions de lignes qui n'ont pas lieu d'être.
Choix de la mauvaise colonne (probe) au lieu de température.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`           integer  unsigned  not null auto_increment primary key,
  `date`         date               not null,
  `probe`        smallint unsigned  not null,
  `temperature`  decimal(5,2)       not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`date`,`probe`,`temperature`) values
  ('2018-01-24', 1, 21.5),
  ('2018-01-24', 2, 22.5),
  ('2018-01-24', 3, 23.5),
  ('2018-01-25', 1, 18.5),
  ('2018-01-25', 2, 19.5),
  ('2018-01-25', 3, 20.5),
  ('2018-01-26', 1, 15.5),
  ('2018-01-26', 3, 15.5),
  ('2018-01-27', 1, 17.5),
  ('2018-01-27', 2, 18.5)
--------------
 
--------------
select * from `test`
--------------
 
+----+------------+-------+-------------+
| id | date       | probe | temperature |
+----+------------+-------+-------------+
|  1 | 2018-01-24 |     1 |       21.50 |
|  2 | 2018-01-24 |     2 |       22.50 |
|  3 | 2018-01-24 |     3 |       23.50 |
|  4 | 2018-01-25 |     1 |       18.50 |
|  5 | 2018-01-25 |     2 |       19.50 |
|  6 | 2018-01-25 |     3 |       20.50 |
|  7 | 2018-01-26 |     1 |       15.50 |
|  8 | 2018-01-26 |     3 |       15.50 |
|  9 | 2018-01-27 |     1 |       17.50 |
| 10 | 2018-01-27 |     2 |       18.50 |
+----+------------+-------+-------------+
--------------
select           t1.date,
                 t1.probe  as Probe1,
                 t2.probe  as Probe2,
                 t3.probe  as Probe3
 
           from  test as t1
 
left outer join  test as t2
             on  t2.date = t1.date
            and  t2.probe    > t1.probe
 
left outer join  test as t3
             on  t3.date = t1.date
            and  t3.probe    > t2.probe
 
       order by  t1.date
--------------
 
+------------+--------+--------+--------+
| date       | Probe1 | Probe2 | Probe3 |
+------------+--------+--------+--------+
| 2018-01-24 |      3 |   NULL |   NULL |
| 2018-01-24 |      1 |      2 |      3 |
| 2018-01-24 |      1 |      3 |   NULL |
| 2018-01-24 |      2 |      3 |   NULL |
| 2018-01-25 |      3 |   NULL |   NULL |
| 2018-01-25 |      1 |      2 |      3 |
| 2018-01-25 |      1 |      3 |   NULL |
| 2018-01-25 |      2 |      3 |   NULL |
| 2018-01-26 |      1 |      3 |   NULL |
| 2018-01-26 |      3 |   NULL |   NULL |
| 2018-01-27 |      1 |      2 |   NULL |
| 2018-01-27 |      2 |   NULL |   NULL |
+------------+--------+--------+--------+
--------------
select distinct  t1.date,
                 t2.temperature as 'Probe 1',
                 t3.temperature as 'Probe 2',
                 t4.temperature as 'Probe 3'
 
           from  test as t1
 
left outer join  test as t2
             on  t2.date = t1.date
            and  t2.probe    = 1
 
left outer join  test as t3
             on  t3.date = t1.date
            and  t3.probe    = 2
 
left outer join  test as t4
             on  t4.date = t1.date
            and  t4.probe    = 3
 
       order by  t1.date
--------------
 
+------------+---------+---------+---------+
| date       | Probe 1 | Probe 2 | Probe 3 |
+------------+---------+---------+---------+
| 2018-01-24 |   21.50 |   22.50 |   23.50 |
| 2018-01-25 |   18.50 |   19.50 |   20.50 |
| 2018-01-26 |   15.50 |    NULL |   15.50 |
| 2018-01-27 |   17.50 |   18.50 |    NULL |
+------------+---------+---------+---------+
--------------
select    date,
          group_concat(temperature order by probe separator " - ") as temperature
 
    from  test
 
group by  date
order by  date
--------------
 
+------------+-----------------------+
| date       | temperature           |
+------------+-----------------------+
| 2018-01-24 | 21.50 - 22.50 - 23.50 |
| 2018-01-25 | 18.50 - 19.50 - 20.50 |
| 2018-01-26 | 15.50 - 15.50         |
| 2018-01-27 | 17.50 - 18.50         |
+------------+-----------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

La deuxième requête est la correction de la solution proposée par Escartefigue.
Elle permet d'avoir dans la bonne colonne, la température qui est associé au bon probe.

La troisième solution consiste à faire un regroupement des températures dans une seule colonne.
Comme on peut le constater, la requête est plus légère, mais elle a l'inconvénient de ne pas faire apparaître la température à la bonne position, si un probe n'existe pas.

Envoyé par PhilLU

mais je n'y comprends pas grand chose...

Quelle est la question ?

Envoyé par Pier.Antoine

Sauf erreur de ma part, j'avais cru comprendre que ce genre de présentation était plus du ressort de l'affichage, donc php ou autre langage.

Pas exactement car ici, il s'agit plus d'une extraction des données dans un ordre précis, plutôt qu'une réorganisation pour présentation.
A vrai dire, on peut très bien gérer cela en php au lieu de le faire sous MySql, chaque ligne de la table est en fait une cellule d'un tableau.

Envoyé par Pier.Antoine

Même si on peut y parvenir avec Mysql (merci Escartefigue), cela est-il possible (facilement) avec plus de colonnes?

Cela dépend de ce que l'on cherche à faire et sous quelle condition.
Dans mon message précédent, j'utilise la fonction "group_concat()" afin de regrouper dans une seule colonne, la concaténation des températures selon un ordre précis.
si tous les probes sont présents, cette solution rappelle celle d'un tableau.
Sauf que dans l'exemple donnée par PhilLU, il y a des trous, et cela provoque un décalage positionnel des températures.
Sans gérer la valeur des probes, je ne connais aucune solution qui permet de gérer positionnellement les températures.

@+

**aieeeuuuuu** · 29/01/2018, 15h41

Bonjour,

il serait en effet plus judicieux de traiter ce problème du coté applicatif.
Si vous ne pouvez pas, plutôt que des multiples jointures, un regroupement sera probablement plus efficace :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SELECT 
    date
    , MAX(CASE WHEN Probe = 1 THEN temperature END) As Probe1
    , MAX(CASE WHEN Probe = 2 THEN temperature END) As Probe2
    , MAX(CASE WHEN Probe = 3 THEN temperature END) As Probe3
FROM LaTable
GROUP BY date

**Artemus24** · 29/01/2018, 16h14

Salut aieeeuuuuu.

Votre solution ne fonctionne pas (sous MySql 8.0) à cause d'un problème d'agrégation sur la colonne "probe".

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ERROR 1055 (42000) at line 115: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'base.test.probe' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Il faut le faire en deux phases :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
select    date,
          max(prob1) as prob1,
          max(prob2) as prob2,
          max(prob3) as prob3
 
    from  (  select    date,
                       case probe when 1 then temperature else '' end as prob1,
                       case probe when 2 then temperature else '' end as prob2,
                       case probe when 3 then temperature else '' end as prob3
 
                 from  test
          ) as x
 
group by  date
order by  date
--------------
 
+------------+-------+-------+-------+
| date       | prob1 | prob2 | prob3 |
+------------+-------+-------+-------+
| 2018-01-24 | 21.50 | 22.50 | 23.50 |
| 2018-01-25 | 18.50 | 19.50 | 20.50 |
| 2018-01-26 | 15.50 |       | 15.50 |
| 2018-01-27 | 17.50 | 18.50 |       |
+------------+-------+-------+-------+

@+

**PhilLU** · 29/01/2018, 17h09

Envoyé par Artemus24

La deuxième requête est la correction de la solution proposée par Escartefigue.

Merci Artemus24 pour ta réponse détaillée :-)
Si je crée la deuxième requète avec 25 sondes, mais qu'elles n'existent pas toutes, comment tester cette condition pour ne sortir que celles qui existent?
(Par exemple les 10 premières)

Envoyé par Artemus24

Quelle est la question ?

Aussi, dans le code mentionné:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN clName = ''',
      clName,
      ''' THEN score else ''-'' END) AS `',
      clName, '`'
    )
  ) INTO @sql
FROM clients;
 
SET @sql 
  = CONCAT('SELECT s.playdate, ', @sql, ' 
            from clients c
            inner join scores s
              on c.clid = s.clid
            group by s.playdate');
 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

J'ai testé mais ne renvoie rien???
La db est bien créée pourtant...

Merci pour vos conseils

**Artemus24** · 30/01/2018, 18h20

Salut PhilLU.

Envoyé par PhilLU

Si je crée la deuxième requête avec 25 sondes, mais qu'elles n'existent pas toutes, comment tester cette condition pour ne sortir que celles qui existent?

Je ne comprends pas trop le sens de votre question, mais je suppose que cela est en relation avec la question suivante.

Envoyé par PhilLU

J'ai testé mais ne renvoie rien???

C'est la mise en forme de votre requête qui ne fonctionne pas.
Voici ce que je propose en reprenant mon dernier exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `client`
--------------
 
--------------
CREATE TABLE `client`
( `id`           integer  unsigned  not null auto_increment primary key,
  `clName`       varchar(255)       not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `client` (`clName`) values
  ('name1'),('name2')
--------------
 
--------------
select * from `client`
--------------
 
+----+--------+
| id | clName |
+----+--------+
|  1 | name1  |
|  2 | name2  |
+----+--------+
--------------
DROP TABLE IF EXISTS `score`
--------------
 
--------------
CREATE TABLE `score`
( `id`           integer  unsigned  not null auto_increment primary key,
  `score`        decimal(5,2)       not null,
  `playdate`     date               not null,
  `clid`         integer unsigned   not null,
  CONSTRAINT `FK_CLIENT` FOREIGN KEY (`clid`) REFERENCES `client` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `score` (`score`,`playdate`,`clid`) values
  (12.50, '2018-01-20', 1),
  ( 7.50, '2018-01-20', 2),
  (10.00, '2018-01-21', 1),
  ( 3.50, '2018-01-21', 2),
  ( 2.00, '2018-01-22', 1)
--------------
 
--------------
select * from `score`
--------------
 
+----+-------+------------+------+
| id | score | playdate   | clid |
+----+-------+------------+------+
|  1 | 12.50 | 2018-01-20 |    1 |
|  2 |  7.50 | 2018-01-20 |    2 |
|  3 | 10.00 | 2018-01-21 |    1 |
|  4 |  3.50 | 2018-01-21 |    2 |
|  5 |  2.00 | 2018-01-22 |    1 |
+----+-------+------------+------+
--------------
set @req1 = (SELECT GROUP_CONCAT(CONCAT('SUM(',clname,') AS ',clname)) FROM client)
--------------
 
--------------
select @req1
--------------
 
+-----------------------------------------+
| @req1                                   |
+-----------------------------------------+
| SUM(name1) AS name1,SUM(name2) AS name2 |
+-----------------------------------------+
--------------
set @req2 = (SELECT GROUP_CONCAT(CONCAT('CASE clid WHEN ',id,' THEN score else ''-'' END AS ''',clName, '''')) FROM client)
--------------
 
--------------
select @req2
--------------
 
+---------------------------------------------------------------------------------------------------------+
| @req2                                                                                                   |
+---------------------------------------------------------------------------------------------------------+
| CASE clid WHEN 1 THEN score else '-' END AS 'name1',CASE clid WHEN 2 THEN score else '-' END AS 'name2' |
+---------------------------------------------------------------------------------------------------------+
--------------
set @req3 = CONCAT('SELECT playdate,',@req1,' from ( select playdate,', @req2,' from score) AS x group by playdate')
--------------
 
--------------
select @req3
--------------
 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @req3                                                                                                                                                                                                                     |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT playdate,SUM(name1) AS name1,SUM(name2) AS name2 from ( select playdate,CASE clid WHEN 1 THEN score else '-' END AS 'name1',CASE clid WHEN 2 THEN score else '-' END AS 'name2' from score) AS x group by playdate |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
--------------
commit
--------------
 
--------------
PREPARE stmt FROM @req3
--------------
 
--------------
EXECUTE stmt
--------------
 
+------------+-------+-------+
| playdate   | name1 | name2 |
+------------+-------+-------+
| 2018-01-20 |  12.5 |   7.5 |
| 2018-01-21 |    10 |   3.5 |
| 2018-01-22 |     2 |     0 |
+------------+-------+-------+
--------------
DEALLOCATE PREPARE stmt
--------------
 
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

Tableaux croisés MySQL

Requêtes MySQL

Discussions similaires

Partager

Partager