Compter toutes les valeurs qui maximisent une autre valeur

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) où 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 ?

Regarde du côté de DENSE_RANK.

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

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).

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

Tu penses que tu pourrais faire un sqlfiddle avec une dizaine/vingtaine d’entrée ?

Je pense qu’il y a beaucoup plus simple que ta requête mais j’ai besoin de tester

Voilà. J’ai mis assez d’entrée pour que ce soit un peu représentatif…

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;