Fonction SQL MAX sur chaque colonne sauf date

Version imprimable

01/12/2020, 02h46
MissCookie

Fonction SQL MAX sur chaque colonne sauf date

Bonjour a tous,

Je suis sur un projet et autant dire que je galère...
Les cours de programmation date de ... bientôt 20 ans.

Je m'y remets doucement, mais la je sèche !

J'ai une table XXX qui contient 12 colonnes. (13 en comptant la colonne de référence)

Je souhaite récupérer les valeurs max (et min) de la table accompagnée de la date présente sur la même ligne.

TABLE XXX

N° date a b c d e f ETC

1 01-janv 1 5 8 1 5 10

2 01-juin 5 9 3 10 1 5

3 30-déc 10 2 5 5 10 1

Puis je souhaite injecter ça dans une nouvelle table qui elle se présente sous le forme :

nomTableInitiale nomColonneTableInitiale valeurmax datemax valeurmin datemin

XXX a 10 30-déc 1 01-janv

XXX b 9 01-juin 2 30-déc

XXX c 8 01-janv 3 01-juin

... ... ... ... ... ...

La valeur de la base est déjà dans une variable.

J'ai commencé en passant part un PDO... et a base de SELECT MX()

mais je suis partie dans l'interrogation de toutes mes colonne une par une et je pense bien qu'il doit y avoir une solution plus simple façon balayage...

Si l'un d'entre vous avait la patience de m'aider... c'est pour un projet perso de station météo a base d'Arduino, et j'avoue que je planche sur le sujet depuis plus d'une semaine sans succès....

Le confinement permet de revenir a ses premiers amours :)

Merci de m'avoir lue !
01/12/2020, 03h19
Skunka

Je n'ai pas tout saisi, tu souhaites récupérer deux valeurs (min et max) de chaque enregistrement de ta table avec la date ou les deux enregistrements où tu as la valeur min et max de toute la table avec leur date respectives? Car cela change la requête SQL nécessaire ^^

Je ne suis pas expert SQL mais en fonction de ce que tu souhaites j'essayerais de t'aider au mieux !

Cordialement,
Skunka.
02/12/2020, 00h16
MissCookie

Merci pour cette réponse rapide :D

Je souhaite effectivement récupérer les valeur min et max de chaque colonne avec leurs dates respectives (celle sur la même ligne que les valeurs max et min qui seront forcement différentes).

N°	date	a	b	c	d	e	f
1	01-janv	1	5	8	1	5	10
2	01-juin	5	9	3	10	1	5
3	30-déc	10	2	5	5	10	1

nomTableInitiale	nomColonneTableInitiale	valeurmax	datemax	valeurmin	datemin
XXX	a	10	30-déc	1	01-janv
XXX	b	9	01-juin	2	30-déc
XXX	c	8	01-janv	3	01-juin
...	...	...	...	...	...

Citation:

Envoyé par MissCookie

Merci pour cette réponse rapide :D

Je souhaite effectivement récupérer les valeur min et max de chaque colonne avec leurs dates respectives (celle sur la même ligne que les valeurs max et min qui seront forcement différentes).

En relisant mieux, je pense avoir mieux compris en général, corrige moi si je me trompe. (et ça à l'air plutôt complexe !)

Tu as une table X composé d'un id, date et 11 colonne comportant des valeurs. Tu souhaites récupérer la valeur max et min de ta table parmi toutes les colonnes et lignes avec leur date correspondante et nom de colonne. Ces valeurs et dates, tu souhaites ensuite les insérer dans une autre table Y.

Si je me trompe, n'hésite pas à revenir avec des exemples plus concrets que je puisse mieux comprendre, je suis parfois long à la détente!

Par contre, comptes-tu tirer ces informations de la même table X ou comptes tu tirer ces informations depuis différentes tables pour les insérer dans la table Y ?
Egalement, tu utilise un serveur PHP? Il se pourrait que le traitement soit plus aisé en PHP qu'en SQL.

En attendant ta réponse, je te met les liens qui m'ont aidé à arriver au résultat que je te présente après:
CASE - Pour comparer tes valeurs
max-function-with-case - utiliser un MAX(CASE(...))
mysql-stored-function/ - des fonctions stockées
simulate-an-array-variable-in-mysql - Simuler un tableau en SQL

Bref, pour un peu plus de clarté, je te fourni un code que je n'ai pas tellement le temps de tester, j'essayerais demain (ça fait plus de 2 ou 3 heures que je réfléchis à ton problème et j'aimerais aller dormir :mrgreen: pas d'inquiétude, me remettre dans le SQL ainsi me plaît bien !), il se peut qu'il y est des erreurs de syntaxe ou autre mais c'est un bon début je pense. Tu vas constater que je crée des fonctions stockées afin d'éclater la logique pour la rendre plus maintenable, évolutive et compréhensive.

Fonction pour la valeur max d'une colonne - Pour la fonction min c'est le même principe, il faut juste remplacer MAX() par MIN():
Code:

1 2 3 4 5 6 7 8 9 10 11 DELIMITER $$ CREATE FUNCTION getColonneValeurMax(nomColonne VARCHAR(20)) RETURN INT NOT DETERMINISTIC BEGIN SET valeurMax = SELECT DISTINCT MAX(nomColonne) FROM tableX; RETURN valeurMax; END$$ DELIMITER;
Fonction valeur max de la table - Pour la fonction min c'est le même principe, il faut juste remplacer la fonction max par la min et le nommage des variables:
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 DELIMITER $$ CREATE FUNCTION getValeurMax() RETURN INT NOT DETERMINISTIC BEGIN SET listNomColonne = 'a,b,c,d,e,f,g,h,i,j,k'; SET valeurMax = 0; -- Si tu traites des valeurs négatives met -999999999 WHILE (LOCATE(',', nomColonnes) > 0) DO SET nomColonne = ELT(1, listNomColonne); SET listNomColonne = SUBSTRING(listNomColonne, LOCATE(',', listNomColonne) + 1); SET valeur = getColonneValeurMax(nomColonne); IF (valeur > valeurMax) SET valeurMax = valeur; END IF; END WHILE; RETURN valeurMax; END$$ DELIMITER;
Fonction pour la date max - Pour la min, il faudra appeler la fonction pour la valeur min évidemment:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 DELIMITER $$ CREATE FUNCTION getDateMax() RETURN DATE NOT DETERMINISTIC BEGIN SET valeurMax = getValeurMax(); SET dateMax = ( SELECT date FROM tableX HAVING a = valeurMax OR b = valeurMax OR ... OR -- Tester chaque colonne k = valeurMax LIMIT 1 -- Au cas où deux colonnes possède la même valeur ); RETURN dateMax; END$$ DELIMITER;
Fonction pour le nom de la colonne - Pour la fonction min, inverser les fonctions MAX par les fonctions MIN:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 DELIMITER $$ CREATE FUNCTION getNomColonneMax() RETURN VARCHAR(20) NOT DETERMINISTIC BEGIN SET valeurColA = getColonneValeurMax('a'); SET valeurColB = getColonneValeurMax('b'); -- Pareil pour les autres colonnes SET valeurMax = getValeurMax(); SET colonneMax = CASE WHEN valeurColA = valeurMax THEN 'a' WHEN valeurColB = valeurMax THEN 'b' -- pareil pour les autres colonnes RETURN colonneMax; END$$ DELIMITER;
Ta requête pour insérer les données dans ta table Y:
Code:

1 2 3 4 5 6 7 8 9 10 INSERT INTO tableY ('nomTableInitiale', 'nomColonneMax', 'valeurMax', 'dateMax', 'nomColonneMin', 'valeurMin', 'dateMin') VALUES ( 'tableX', getNomColonneMax(), getValeurMax(), getNomColonneMax(), getNomColonneMin(), getValeurMin(), getNomColonneMin(), )
Bon, j'espère que tout ça est assez clair, je prendrais du temps demain pour faire des tests de mon côté mais si un pro SQL passe par là, un coup de main sera apprécié !

02/12/2020, 10h17
MissCookie

Whaou !
Merci pour ce temps passé !8O
Bon je sens que l'on est pas loin, et ca me rassure car je cherche depuis quelques semaine...

Pour résumer la logique de manière différente :

C'est comme une station météo, et pour répondre a ta question sur le nombre de table, effectivement, l'idée est de d'avoir plusieurs "station météo" a terme(donc d'autres tables A, B, ...)
A chaque mesure on incrémente sur la table X en colonne 1 la date et sur les 11 autres colonnes une mesure différente. (pour les besoin de compréhension je vais parler de Température, humidité, etc...)

Je cherche a connaitre les records haut et bas pour chacune des colonnes (températures,...) sur les dernières 24h (ou semaine ou mois) et donc d'insérer les valeurs dans une autre table indépendante de celles des station qui m'affichera :

en colonne 0 l'ID donné par la base
en colonne 1 de quelle table (station) les mesures sont issue.
en colonne 2 de quelle mesure (temperature, humidité,...) il s'agit
en colonne 3 la valeur max
en colonne 4 la date à laquelle cette valeur Max a été mesurée
en colonne 5 la valeur min
en colonne 6 la date à laquelle cette valeur Min a été mesurée

Pour rien te cacher je veux également une fois cela fait incrémenter ces valeurs sur des périodes constatées différentes mais c'est un autre sujet et je pense que je m'en débrouillerai :P

J'aurais donc a terme dans la table Y "Min et Max" :
11 mesures x 2 couples (2 valeurs (1 max et 1 min) et 2 dates de ses mesures (1 max et 1 min)) x4 périodes (24h,1semaine,1mois, historique) x [N] stations
En résumé :
Ce qui veut dire que a terme j'aurais 2 couples (2 valeurs (1 max et 1 min) et 2 dates de ses mesures (1 max et 1 min)) par "mesure" et par "station météo"

Soit 176 incrémentations par station météo !:fou:

Comme je te l'ai évoquée je ne suis pas une experte en programmation...
Tu évoques la possibilité d'un serveur PHP... et la tu m'as perdue !

Je vais analyser plus en détail tes propositions qui a la première lecture de novice semble toucher au but :)

Merci 1000 fois !

Bon, je viens d'y passer un peu de temps en faisant des tests et je commence à me rappeler pourquoi je me suis éloigné du SQL !

Citation:

Comme je te l'ai évoquée je ne suis pas une experte en programmation...
Tu évoques la possibilité d'un serveur PHP... et la tu m'as perdue !

Pas de soucis, simple curiosité, puisque j'aurais plus facilement réalisé àa en PHP qu'en SQL mais continuons en SQL.

Tes nouvelles explications m'ont, normalement, bien fait comprendre ce que tu souhaites faire, il se peut que j'ai vu plus complexe hier et donc le code suivant sera un peu plus simple !
Insertion des données dans la table que je nomme all_station, celle récupérant les valeurs min et max:

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- Procédure d'insertion des données
DELIMITER //
 
CREATE PROCEDURE insertMesure(IN nomStation VARCHAR(55), IN periode INT(2))
BEGIN
 
    DECLARE mesure VARCHAR(20);
    DECLARE listMesure VARCHAR(250);
    SET listMesure = 'valA,valB,valC,valD,valE,valF,valG,valH,valI,valJ,valK';
    SET nomStation = CONCAT('station_', nomStation);
 
    WHILE LOCATE(',', listMesure) > 0 DO  
        SET mesure = ELT(1, listMesure);
        SET listMesure = SUBSTRING(listMesure, LOCATE(',', listMesure) + 1);
 
        IF periode = 1 THEN -- 24h
            INSERT INTO all_station ('id', 'station', 'mesure', 'valMax', 'dateMax', 'valMin', 'dateMin') VALUES
            (
                null,
                nomStation,
                mesure,
                getMesureMax24H(nomStation, mesure),
                getDateMesureMax24H(nomStation, mesure),
                getMesureMin24H(nomStation, mesure),
                getDateMesureMin24H(nomStation, mesure)
            );
        ELSE IF (periode = 2) -- 1 semaine
            -- Ton insert qui fera appel aux fonctions balayant sur une semaine
        ELSE IF (periode = 3) -- 1 mois
            -- Ton insert qui fera appel aux fonctions balayant sur un mois
        ELSE -- Historique
            -- Ton insert qui fera appel aux fonctions balayant sur tout l'historique
        END IF;
 
    END WHILE;
 
END
 
DELIMITER ;

Il n'est pas complet mais la base est là, tu pourras facilement le compléter. Le nom de la station est paramètre mais tu pourras par la suite automatiser pour chaque station afin d'éviter de lancer la procédure à la main.

Fonctions permettant de récupérer la valeur max et la date:

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
26
27
28
29
30
31
32
33
34
35
36
-- Fonction pour avoir la mesure MAX balayant sur 24h 
-- pour les autres périodes, même fonction en modifiant la clause WHERE pour définir la bonne période 
-- pour la mesure MIN, remplacer MAX() par MIN()
DELIMITER $$
 
CREATE FUNCTION getMesureMax24H(nomStation VARCHAR(55), mesure VARCHAR(55)) 
RETURN DECIMAL(10,2) NOT DETERMINISTIC
BEGIN
 
    DECLARE valeur DECIMAL(10,2);
    SET valeur = (SELECT DISTINCT MAX(mesure) -- Valeur max d'une colonne mesure
        FROM nomStation  -- La table correspondant à la station passé en paramètre à la procédure
        WHERE dateEnregistrement >= (NOW() - INTERVAL 24 HOUR)); -- Balayer les lignes où la date se situe entre maintenant et il y a 24h
 
    RETURN valeur;
END$$
 
DELIMITER ;
 
-- Fonction pour avoir la date
DELIMITER $$
 
CREATE FUNCTION getDateMesureMax24H(nomStation VARCHAR(55), mesure VARCHAR(55)) 
RETURN DATETIME NOT DETERMINISTIC
BEGIN
 
    DECLARE dateEnregistrement DATETIME;
    SET dateEnregistrement = (SELECT DISTINCT date
        FROM nomStation  
        WHERE dateEnregistrement >= (NOW() - INTERVAL 24 HOUR)
        HAVING MAX(mesure)); 
 
    RETURN dateEnregistrement;
END$$
 
DELIMITER ;

Comme indiqué dans les commentaire, il faudra dupliquer cette fonction pour les valeurs max et min pour chaque périodicité (24h1 1 semaine, ...). Il est sûrement possible de refactoriser ces fonctions en deux en leur passant le paramètre période de la procédure pour modifier la clause WHERE dynamiquement. Là, tout est séparé pour plus de compréhensibilité.

Je te met les liens qui m'ont aidé pour ce nouveau code:
create-procedure
mysql-stored-function/
create-stored-procedures-in-php-myadmin - J'utilise pour ma part phpMyAdmin, qui ne me permet pas de mettre directement tout le code mais seulement une partie et de configurer les paramètres et autre graphiquement (un peu relou je trouve mais bon).

Après différents tests, je peut stocker sans problème les procédures et fonctions mais je n'arrive pas encore à les faire fonctionner. phpMyAdmin cherche la table "nomStation" qui est ma variable au lieu de prendre son contenu donc "station_x" et je ne comprends pas pourquoi. J'ai essayé avec @ mais rien à y faire, je suis perdu à ce niveau, désolé.

Je te met également les données qui me servent au test si jamais tu veux te créer une nouvelle base pour faire des tests sans avoir à modifier mon code pour l'adapter à ta base.

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
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
CREATE TABLE station_x
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    dateEnregistrement DATETIME NOT NULL,
    valA DECIMAL(10,2) NOT NULL,
    valB DECIMAL(10,2) NOT NULL,
    valC DECIMAL(10,2) NOT NULL,
    valD DECIMAL(10,2) NOT NULL,
    valE DECIMAL(10,2) NOT NULL,
    valF DECIMAL(10,2) NOT NULL,
    valG DECIMAL(10,2) NOT NULL,
    valH DECIMAL(10,2) NOT NULL,
    valI DECIMAL(10,2) NOT NULL,
    valJ DECIMAL(10,2) NOT NULL,
    valK DECIMAL(10,2) NOT NULL
);
 
CREATE TABLE station_y
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    dateEnregistrement DATETIME NOT NULL,
    valA DECIMAL(10,2) NOT NULL,
    valB DECIMAL(10,2) NOT NULL,
    valC DECIMAL(10,2) NOT NULL,
    valD DECIMAL(10,2) NOT NULL,
    valE DECIMAL(10,2) NOT NULL,
    valF DECIMAL(10,2) NOT NULL,
    valG DECIMAL(10,2) NOT NULL,
    valH DECIMAL(10,2) NOT NULL,
    valI DECIMAL(10,2) NOT NULL,
    valJ DECIMAL(10,2) NOT NULL,
    valK DECIMAL(10,2) NOT NULL
);
 
CREATE TABLE all_station
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    station VARCHAR(55) NOT NULL,
    mesure VARCHAR(55) NOT NULL,
    valMax DECIMAL(10,2) NOT NULL,
    dateMax DATETIME NOT NULL,
    valMin DECIMAL(10,2) NOT NULL,
    dateMin DATETIME NOT NULL
);
 
INSERT INTO station_x VALUES
(null, '2020-12-02 00:00:00', 4, 8, 9.4, 12, 15, 16.7, 89.2, 14, 0.3, 45, 4.8),
(null, '2020-12-02 00:00:00', 7, 5, 4.2, 7.9, 45, 14.2, 85.3, 14, 1, 37.8, 5),
(null, '2020-12-02 00:00:00', 2, 2.8, 4.6, 14.2, 28, 26, 52.1, 7.6, 1.6, 42.2, 4.6),
(null, '2020-11-30 00:00:00', 4.6, 25, 4.6, 15, 26.3, 78, 45, 24, 12.3, 0.8, 14),
(null, '2020-11-25 00:00:00', 8.6, 56.2, 4.3, 12, 23.6, 45, 12.3, 78, 12.69, 48, 52.3),
(null, '2020-11-12 00:00:00', 45, 12.3, 0.2, 15, 1, 4, 85, 2, 12, 12.3, 4),
(null, '2020-11-07 00:00:00', 12.6, 5, 45, 23.6, 96.5, 10, 0.2, 12, 36, 74.38, 5),
(null, '2020-11-01 00:00:00', 12, 0.8, 45, 36.1, 28.4, 14, 11, 0.45, 23, 52.3, 9.5),
(null, '2020-10-28 00:00:00', 11.5, 1.6, 8, 23, 14.7, 65, 12.5, 0.78, 2, 7, 52),
(null, '2020-10-26 00:00:00', 3.6, 5, 58, 8, 5.41, 5, 4.2, 8.6, 56, 23.4, 45.85);
 
INSERT INTO station_y VALUES
(null, '2020-12-02 00:00:00', 4, 8, 9.4, 12, 15, 16.7, 89.2, 14, 0.3, 45, 4.8),
(null, '2020-12-02 00:00:00', 7, 5, 4.2, 7.9, 45, 14.2, 85.3, 14, 1, 37.8, 5),
(null, '2020-12-02 00:00:00', 2, 2.8, 4.6, 14.2, 28, 26, 52.1, 7.6, 1.6, 42.2, 4.6),
(null, '2020-11-30 00:00:00', 4.6, 25, 4.6, 15, 26.3, 78, 45, 24, 12.3, 0.8, 14),
(null, '2020-11-25 00:00:00', 8.6, 56.2, 4.3, 12, 23.6, 45, 12.3, 78, 12.69, 48, 52.3),
(null, '2020-11-12 00:00:00', 45, 12.3, 0.2, 15, 1, 4, 85, 2, 12, 12.3, 4),
(null, '2020-11-07 00:00:00', 12.6, 5, 45, 23.6, 96.5, 10, 0.2, 12, 36, 74.38, 5),
(null, '2020-11-01 00:00:00', 12, 0.8, 45, 36.1, 28.4, 14, 11, 0.45, 23, 52.3, 9.5),
(null, '2020-10-28 00:00:00', 11.5, 1.6, 8, 23, 14.7, 65, 12.5, 0.78, 2, 7, 52),
(null, '2020-10-26 00:00:00', 3.6, 5, 58, 8, 5.41, 5, 4.2, 8.6, 56, 23.4, 45.85);

Bref, je vais replancher sur le sujet ce soir ou demain, peut-être que la solution est sous mes yeux ^^

02/12/2020, 22h00
MissCookie

Oulala !
J'y panne rien...
Je regarde et je te dis !

Je pensais faire mes requêtes en PHP depuis la page "record"...
va falloir que je regarde...

--EDIT--

Quel boulot ! Merci beaucoup même si je t'avouerais que je ne sais pas encore comment faire en sorte que cela se mette en œuvre…

Je suis HS, je vais me coucher et me repenche la dessus demain.

Merci encore et encore.