Tableaux croisés MySQL Delphi

Version imprimable

Bonjour,
je souhaite via Delphi 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:

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:

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 :ccool:
PhilLU

28/01/2018, 10h00
SergioMaster

Bonjour,
Il vaudrait mieux poser la question dans le forum SQL et fouiller d'abord dans celui-ci car la question est assez souvent posée. Voir des mots clés comme PIVOT mais je ne crois pas que MySQL connaisse :aie: et comme le nombrer d'échantillon n'est pas fixe :aie:
28/01/2018, 10h59
PhilLU

Merci SergioMaster,
C'est fait...
;)

Citation:

Envoyé par SergioMaster

Bonjour,
Il vaudrait mieux poser la question dans le forum SQL et fouiller d'abord dans celui-ci car la question est assez souvent posée. Voir des mots clés comme PIVOT mais je ne crois pas que MySQL connaisse :aie: et comme le nombrer d'échantillon n'est pas fixe :aie:

Citation:

Envoyé par PhilLU

Merci SergioMaster,
C'est fait...
;)

Mais dommage "ShaiLeTroll, vous n'avez pas la permission d'accéder à cette page"

Je l'ai fait en MySQL 4

Tu commences par faire une requête qui récupère le nombre maximum de Probe si celui ci n'est pas toujours de 1 à 3
Tu génères ensuite le SQL qui va convertir tes données en ligne sous forme de plusieurs colonnes

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
 
CREATE TABLE TEMP.A_PROBE
(
  DATETIME     DATE,
  PROBE        NUMBER,
  TEMPERATURE  NUMBER
);
 
INSERT INTO TEMP.A_PROBE (DATETIME, PROBE, TEMPERATURE) VALUES ( '24/01/2018'      ,       1         ,      21.5          );
INSERT INTO TEMP.A_PROBE (DATETIME, PROBE, TEMPERATURE) VALUES ( '24/01/2018'      ,       2         ,      22.5          );
INSERT INTO TEMP.A_PROBE (DATETIME, PROBE, TEMPERATURE) VALUES ( '24/01/2018'      ,       3         ,      23.5          );
INSERT INTO TEMP.A_PROBE (DATETIME, PROBE, TEMPERATURE) VALUES ( '25/01/2018'      ,       1         ,      18.5          );
INSERT INTO TEMP.A_PROBE (DATETIME, PROBE, TEMPERATURE) VALUES ( '25/01/2018'      ,       2         ,      19.5          );
INSERT INTO TEMP.A_PROBE (DATETIME, PROBE, TEMPERATURE) VALUES ( '25/01/2018'     ,       3         ,      20.5          );
INSERT INTO TEMP.A_PROBE (DATETIME, PROBE, TEMPERATURE) VALUES ( '26/01/2018'      ,       1         ,      15.5          );
INSERT INTO TEMP.A_PROBE (DATETIME, PROBE, TEMPERATURE) VALUES ( '26/01/2018'      ,       3         ,      15.5          );
INSERT INTO TEMP.A_PROBE (DATETIME, PROBE, TEMPERATURE) VALUES ( '27/01/2018'     ,       1         ,     17.5          );
INSERT INTO TEMP.A_PROBE (DATETIME, PROBE, TEMPERATURE) VALUES ( '27/01/2018'      ,       2         ,      18.5   );
 
SELECT MAX(PROBE) FROM TEMP.A_PROBE

Tu es d'accord que générer cette requête en DELPHI avec le MAX n'a rien de difficile avec une petite boucle

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
    SELECT DATETIME, MAX(PROBE_1) AS PROBE_1, MAX(PROBE_2) AS PROBE_2, MAX(PROBE_3) AS PROBE_3
    FROM
      (
        SELECT DATETIME, TEMPERATURE AS PROBE_1, NULL  AS PROBE_2, NULL  AS PROBE_3
        FROM TEMP.A_PROBE
        WHERE PROBE = 1 
        UNION
        SELECT DATETIME, NULL  AS PROBE_1, TEMPERATURE AS PROBE_2, NULL  AS PROBE_3
        FROM TEMP.A_PROBE
        WHERE PROBE = 2
        UNION
        SELECT DATETIME, NULL  AS PROBE_1, NULL  AS PROBE_2, TEMPERATURE AS PROBE_3
        FROM TEMP.A_PROBE
        WHERE PROBE = 3
      )  
    GROUP BY DATETIME

Alors là c'est une version avec UNION mais je sais que je n'avais pas d'UNION dans le code MySQL
Voici une autre version que l'on peut encore tout à faire générer avec Delphi

Code:

1
2
3
4
5
        SELECT p1.DATETIME, p1.TEMPERATURE AS PROBE_1, p2.TEMPERATURE AS PROBE_3, p2.TEMPERATURE AS PROBE_3
        FROM TEMP.A_PROBE p1
        FULL OUTER JOIN TEMP.A_PROBE p2 ON p1.DATETIME = p2.DATETIME AND p2.PROBE = 2
        FULL OUTER JOIN TEMP.A_PROBE p3 ON p1.DATETIME = p3.DATETIME AND p3.PROBE = 3
        WHERE p1.PROBE = 1

on va faire tout ça en PL\SQL

Code:

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
set serveroutput on;
 
DECLARE
  vProbeCount INTEGER;
  vStatment VARCHAR2(4000);
BEGIN
  SELECT MAX(PROBE) into vProbeCount FROM TEMP.A_PROBE;
 
  vStatment := ' SELECT pm.DATETIME ';
  FOR i IN 1..vProbeCount LOOP
    vStatment := vStatment || ', p'||i||'.TEMPERATURE AS PROBE_'||i; 
  END LOOP;
  vStatment := vStatment || '
FROM (SELECT DISTINCT DATETIME FROM TEMP.A_PROBE) pm '; 
 
  FOR i IN 1..vProbeCount LOOP
    vStatment := vStatment || '
LEFT JOIN TEMP.A_PROBE p'||i||' ON pm.DATETIME = p'||i||'.DATETIME AND p'||i||'.PROBE = '||i;
  END LOOP; 
 
  vStatment := vStatment || '
ORDER BY pm.DATETIME';
 
  dbms_output.put_line(vStatment);
END;

et si l'on ajoute

Code:

1
2
3
INSERT INTO TEMP.A_PROBE (DATETIME, PROBE, TEMPERATURE) VALUES ( '24/01/2018'      ,       4         ,      21.3          );
INSERT INTO TEMP.A_PROBE (DATETIME, PROBE, TEMPERATURE) VALUES ( '27/01/2018'     ,       4         ,     17.3        );
INSERT INTO TEMP.A_PROBE (DATETIME, PROBE, TEMPERATURE) VALUES ( '23/01/2018'      ,       4         ,      20.3          );

cela génère

Code:

1
2
3
4
5
6
7
 SELECT pm.DATETIME , p1.TEMPERATURE AS PROBE_1, p2.TEMPERATURE AS PROBE_2, p3.TEMPERATURE AS PROBE_3, p4.TEMPERATURE AS PROBE_4
FROM (SELECT DISTINCT DATETIME FROM TEMP.A_PROBE) pm 
LEFT JOIN TEMP.A_PROBE p1 ON pm.DATETIME = p1.DATETIME AND p1.PROBE = 1
LEFT JOIN TEMP.A_PROBE p2 ON pm.DATETIME = p2.DATETIME AND p2.PROBE = 2
LEFT JOIN TEMP.A_PROBE p3 ON pm.DATETIME = p3.DATETIME AND p3.PROBE = 3
LEFT JOIN TEMP.A_PROBE p4 ON pm.DATETIME = p4.DATETIME AND p4.PROBE = 4
ORDER BY pm.DATETIME

Code:

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

01/02/2018, 11h42
SergioMaster

Citation:

Envoyé par ShaiLeTroll

Mais dommage "ShaiLeTroll, vous n'avez pas la permission d'accéder à cette page"

Parce que le lien a été modéré pour double post, le bon lien est https://www.developpez.net/forums/d1...l/#post9956819