Compter toutes les valeurs qui maximisent une autre valeur

Le tout avec des GROUP BY

L’auteur de ce sujet a trouvé une solution à son problème.
Auteur du sujet

Bonjour à tous,

J’ai une table MySQL qui stocke les relevés de températures (toutes les 15 min) de ma petite station météo. Cette table contient les champs suivants:

  • id
  • datetime
  • temperature

Je souhaiterais obtenir la répartition en fréquences des horaires où les maxima (ou minima, mais c’est pareil) de température sont atteints, avec une résolution de 24h.

Le résultat final de la requête devrait être un ensemble de couples (temps, fréquence)temps seraient tous les quarts d’heure sur 24h et fréquence indiquerait que le maximum de température a eu lieu fréquence% à temps.

Pour l’instant, j’arrive à trouver quelle a été la température maximale pour chaque jour. En revanche, je n’arrive pas à trouver, pour tous les jours, tous les horaires qui ont connu la température maximale de la journée. Le reste, compter tous les horaires où la température a été maximale, ne devrait pas être difficile.

Pouvez-vous m’aider ?

+0 -0
Auteur du sujet

Regarde du côté de DENSE_RANK.

elegance

J’ai oublié de préciser que j’utilise MySQL 5.7, les fonctions de fenêtrage ne sont donc pas disponibles…

De plus, je ne vois pas comment la fonction DENSE_RANK pourrait m’aider…

Pour contourner l’impossibilité d’utiliser des requêtes de fenêtrage, j’ai réussi à construire cette requête, qui fonctionne:

SELECT DATE_FORMAT(time, '%Hh%i') AS h, COUNT(*)
FROM (
    SELECT 
        t1.time, 
        t1.temperature,
        (
            SELECT MAX(t2.temperature)
            FROM temperature AS t2
            WHERE DATE_FORMAT(t2.time, '%Y %m %d') = DATE_FORMAT(t1.time, '%Y %m %d')
        ) AS max_day
    FROM temperature t1
) t
WHERE ROUND(t.temperature, 1) = ROUND(t.max_day, 1)
GROUP BY h

Le problème, c’est qu’elle est très lente (4min pour environ 21000 enregistrements).

(Tu devrais faire un système de cache de la valeur maximale, pour épargner des requêtes gourmandes à chaque vue)

A-312

Je pense que je vais donc mettre en place un système de ce genre.

+0 -0

Cette réponse a aidé l’auteur du sujet

SELECT DATE_FORMAT(time, '%Hh%i') AS h, COUNT(*) 
FROM 
 ( SELECT t1.time, t1.temperature, 
      (   ###  SELECT MAX(t2.temperature) FROM temperature AS t2 WHERE DATE_FORMAT(t2.time, '%Y %m %d') = 
        DATE_FORMAT(t1.time, '%Y %m %d')   ###
      ) AS max_day FROM temperature t1 
  ) t 
WHERE ROUND(t.temperature, 1) = ROUND(t.max_day, 1) GROUP BY h 

J’ai entouré une partie de la requête avec des ###.

En fait tu demandes au serveur SQL de’exécuter la requête : SELECT MAX(t2.temperature) FROM temperature AS t2 WHERE DATE_FORMAT(t2.time, '%Y %m %d’) = une_certaine_date

Plein de fois … autant de fois qu’il y a des dates différentes.

Tu peux organiser ta requête autrement :

SELECT DATE_FORMAT(t2.time, '%Y %m %d') ,  MAX(t2.temperature) FROM temperature 
Group by DATE_FORMAT(t2.time, '%Y %m %d')

Si on se débrouille bien, cette requête va s’exécuter une seule fois, et nous renvoyer la température MAX pour chaque jour.

Et il va falloir jongler un peu :

select  a.qjour,      DATE_FORMAT(B.time, '%Hh%i')     from 
(
SELECT DATE_FORMAT(t2.time, '%Y %m %d')  as qjour ,  MAX(t2.temperature) as temp_max  FROM temperature 
Group by DATE_FORMAT(t2.time, '%Y %m %d')
)  A,    temperature B 
where DATE_FORMAT(b.time, '%Y %m %d')  = A.qjour  

Cette requête nous renvoie l’heure 'optimale' pour chaque jour. (éventuellement, pour une journée, on peut avoir plusieurs heures, si la température maximale a été atteinte à plusieurs heures différentes.

Et la requête finale :

select best_heure, count(*) from 
(
select  a.qjour,      DATE_FORMAT(B.time, '%Hh%i')  as best_heure   from 
(
SELECT DATE_FORMAT(t2.time, '%Y %m %d')  as qjour ,  MAX(t2.temperature) as temp_max  FROM temperature 
Group by DATE_FORMAT(t2.time, '%Y %m %d')
)  A,    temperature B 
where DATE_FORMAT(b.time, '%Y %m %d')  = A.qjour  
)
group by best_heure

Je pense que cette requête devrait aller assez vite.

+0 -0
Auteur du sujet

Merci bien ! Je n’avais pas pensé au produit cartésien pour résoudre ce problème. La requête est en effet quasiment instantanée.

La requête finale que tu proposes a juste deux petites erreurs (il manque un alias sur une table et le where temperature = max), corrigées dans la version suivante:

select best_heure, count(*) from 
(
select  a.qjour,      DATE_FORMAT(B.time, '%Hh%i')  as best_heure   from 
(
SELECT DATE_FORMAT(t2.time, '%Y %m %d')  as qjour ,  MAX(t2.temperature) as temp_max  FROM temperature t2 Group by DATE_FORMAT(t2.time, '%Y %m %d') 
)  A,    temperature B 
where DATE_FORMAT(b.time, '%Y %m %d')  = A.qjour AND A.temp_max = B.temperature
) C
group by best_heure;
+0 -0
Vous devez être connecté pour pouvoir poster un message.
Connexion

Pas encore inscrit ?

Créez un compte en une minute pour profiter pleinement de toutes les fonctionnalités de Zeste de Savoir. Ici, tout est gratuit et sans publicité.
Créer un compte