group BY et moyenne de valeurs

group BY et moyenne de valeurs - SQL/NoSQL - Programmation

Marsh Posté le 31-08-2014 à 20:01:12    

Bonjour à tous,
 
Je cherche à faire une requette sql pour faire le moyenne de 5 valeurs aquise toutes les minutes et de les grouper ensuite toutes les 5minutes après.
 

Code :
  1. mysql> SELECT *  FROM  `sondes_v2`  LIMIT 0 , 30;
  2. +----+----------------+----------+------------+-------------+--------+
  3. | id | adresse_64     | heure    | date       | type_valeur | valeur |
  4. +----+----------------+----------+------------+-------------+--------+
  5. | 14 | 013A204062A925 | 16:28:00 | 2014-08-31 |  temp       |  25.43 |
  6. | 15 | 013A204062A925 | 16:29:00 | 2014-08-31 |  temp       |  25.62 |
  7. | 16 | 013A204062A925 | 16:30:00 | 2014-08-31 |  temp       |  25.68 |
  8. | 17 | 013A204062A925 | 16:31:00 | 2014-08-31 |  temp       |  25.75 |
  9. | 18 | 013A204062A925 | 16:32:00 | 2014-08-31 |  temp       |  25.68 |
  10. | 19 | 013A204062A925 | 16:33:00 | 2014-08-31 |  temp       |  25.75 |
  11. | 20 | 013A204062A925 | 16:34:00 | 2014-08-31 |  temp       |  25.75 |
  12. | 21 | 013A204062A925 | 16:35:00 | 2014-08-31 |  temp       |  25.81 |
  13. | 22 | 013A204062A925 | 16:36:00 | 2014-08-31 |  temp       |  25.75 |
  14. | 23 | 013A204062A925 | 16:37:00 | 2014-08-31 |  temp       |  25.75 |
  15. | 24 | 013A204062A925 | 16:38:00 | 2014-08-31 |  temp       |  25.81 |
  16. | 25 | 013A204062A925 | 16:39:00 | 2014-08-31 |  temp       |  25.87 |
  17. | 26 | 013A204062A925 | 16:40:00 | 2014-08-31 |  temp       |  25.81 |
  18. | 27 | 013A204062A925 | 16:41:00 | 2014-08-31 |  temp       |  25.81 |
  19. | 28 | 013A204062A925 | 16:42:00 | 2014-08-31 |  temp       |  25.87 |
  20. | 29 | 013A204062A925 | 16:43:00 | 2014-08-31 |  temp       |     26 |
  21. | 30 | 013A204062A925 | 16:44:00 | 2014-08-31 |  temp       |     26 |
  22. | 31 | 013A204062A925 | 16:45:00 | 2014-08-31 |  temp       |   26.6 |
  23. | 32 | 013A204062A925 | 16:46:00 | 2014-08-31 |  temp       |  25.87 |
  24. | 33 | 013A204062A925 | 16:47:00 | 2014-08-31 |  temp       |     26 |
  25. | 34 | 013A204062A925 | 16:48:00 | 2014-08-31 |  temp       |     26 |
  26. | 35 | 013A204062A925 | 16:49:00 | 2014-08-31 |  temp       |  26.12 |
  27. | 36 | 013A204062A925 | 16:50:00 | 2014-08-31 |  temp       |   26.6 |
  28. | 37 | 013A204062A925 | 16:51:00 | 2014-08-31 |  temp       |     26 |
  29. | 38 | 013A204062A925 | 16:52:00 | 2014-08-31 |  temp       |  25.87 |
  30. | 39 | 013A204062A925 | 16:53:00 | 2014-08-31 |  temp       |  25.81 |
  31. | 40 | 013A204062A925 | 16:54:00 | 2014-08-31 |  temp       |  25.62 |
  32. | 41 | 013A204062A925 | 16:55:00 | 2014-08-31 |  temp       |   25.5 |
  33. | 42 | 013A204062A925 | 16:56:00 | 2014-08-31 |  temp       |   25.5 |
  34. | 43 | 013A204062A925 | 16:57:00 | 2014-08-31 |  temp       |  25.37 |
  35. +----+----------------+----------+------------+-------------+--------+


 
En gros je voudrais :
 

Code :
  1. mysql> SELECT *  FROM  `sondes_v2`  LIMIT 0 , 30;
  2. +----+----------------+----------+------------+-------------+--------+
  3. | id | adresse_64     | heure    | date       | type_valeur | valeur moyenne |
  4. +----+----------------+----------+------------+-------------+--------+
  5. | 16 | 013A204062A925 | 16:30:00 | 2014-08-31 |  temp       |  moyenne |
  6. | 21 | 013A204062A925 | 16:35:00 | 2014-08-31 |  temp       |  moyenne |
  7. | 26 | 013A204062A925 | 16:40:00 | 2014-08-31 |  temp       |  moyenne |
  8. | 31 | 013A204062A925 | 16:45:00 | 2014-08-31 |  temp       |  moyenne |
  9. | 36 | 013A204062A925 | 16:50:00 | 2014-08-31 |  temp       |  moyenne |
  10. | 41 | 013A204062A925 | 16:55:00 | 2014-08-31 |  temp       |  moyenne |
  11. +----+----------------+----------+------------+-------------+--------+


 
auriez vous déjà fait se genre de requête? car j'en ai trouvé sur internet mais elles sont super complexe.
 
Merci de votre aide.


Message édité par hppp le 31-08-2014 à 20:05:52
Reply

Marsh Posté le 31-08-2014 à 20:01:12   

Reply

Marsh Posté le 31-08-2014 à 23:13:21    

Pour faire un arrondi aux 5 minutes, tu peux utiliser une formule
 

Code :
  1. 5 * ceil(minutes / 5)


 
Que tu peux utiliser pour grouper tes valeurs. Ensuite il suffit de faire un AVG sur la valeur pour laquelle tu veux une moyenne, les GROUP BY qui vont bien et le tour est joué.
 
Donc pour la requête, en partant du principe que la colonne "heure" est de type time  (je cochonne pour afficher l'heure, mais tu as l'idée)
 

Code :
  1. select
  2.    max(id), 
  3.    adresse_64, 
  4.    concat(hour(heure),':', 5 * ceil(minute(heure) / 5), ':00') 
  5.    type_valeur,
  6.    `date`,
  7.    avg(valeur)                 
  8. from sondes_v2
  9. group by adresse_64, type_valeur, date, 5 * ceil(minute(heure) /5);


 
 
Voir un petit sqlFiddle
 
Cela dit (mais c'est un détail), je peine à comprendre pourquoi tu as heure et date dans des champs différents, un datetime ferait aussi bien l'affaire, non ?

Message cité 1 fois
Message édité par deliriumtremens le 31-08-2014 à 23:19:10
Reply

Marsh Posté le 01-09-2014 à 08:01:33    

deliriumtremens a écrit :

Pour faire un arrondi aux 5 minutes, tu peux utiliser une formule
 

Code :
  1. 5 * ceil(minutes / 5)


 
Que tu peux utiliser pour grouper tes valeurs. Ensuite il suffit de faire un AVG sur la valeur pour laquelle tu veux une moyenne, les GROUP BY qui vont bien et le tour est joué.
 
Donc pour la requête, en partant du principe que la colonne "heure" est de type time  (je cochonne pour afficher l'heure, mais tu as l'idée)
 

Code :
  1. select
  2.    max(id), 
  3.    adresse_64, 
  4.    concat(hour(heure),':', 5 * ceil(minute(heure) / 5), ':00') 
  5.    type_valeur,
  6.    `date`,
  7.    avg(valeur)                 
  8. from sondes_v2
  9. group by adresse_64, type_valeur, date, 5 * ceil(minute(heure) /5);


 
 
Voir un petit sqlFiddle
 
Cela dit (mais c'est un détail), je peine à comprendre pourquoi tu as heure et date dans des champs différents, un datetime ferait aussi bien l'affaire, non ?


 
Super, merci beaucoup elle marche nikel.
 
Dans ma tête je trouvais ça mieux de séparer Heure et Date si je voulais manipuler plus facilement.

Reply

Marsh Posté le 02-09-2014 à 11:23:19    

Salut,
 
Je me suis rendu compte que j'ai un petit problème, quand j'arrive à 19:00 il me marque 19:60. J'ai regardé avec :
 

Code :
  1. CASE
  2. WHEN
  3. THEN
  4. ELSE


 
Mais j'arrive pas à modifier facilement 19:60 en 19:00.
 
Auriez vous une solution simple?
 
Merci

Reply

Marsh Posté le 02-09-2014 à 15:01:16    

Ah ouais c'est pas tout faux :sweat:  
 
En plus, 16:60 n'est pas groupé avec 17:00, ce qui est aussi ennuyeux.
 
Bon, ben y a peut-être plus élégant, mais ça devrait aller mieux avec un modulo 60 sur les minutes, par contre il faut aussi régler le "heure + 1", pour les minutes entre 56 et 59.
 
Ce qui nous donne :
 

Code :
  1. select
  2.    max(s.id),
  3.    s.adresse_64,
  4.    concat(lpad(s1.heure, 2, '0'),':', lpad(s1.minute, 2, '0'), ':00')
  5.    type_valeur,
  6.    date,
  7.    avg(valeur) 
  8. from sondes_v2 s
  10. join (select
  11.         id,
  12.         case when minute(heure) between 56 and 59 then hour(heure) + 1 else hour(heure)  end as heure,
  13.         5 * ceil(minute(heure) / 5) % 60 as minute             
  14.       from sondes_v2) s1 on s1.id = s.id
  15. group by s.adresse_64, s1.heure, s1.minute, s.type_valeur, s.date, s.type_valeur


 
et le SqlFiddlecorrigé


Message édité par deliriumtremens le 02-09-2014 à 15:01:35
Reply

Marsh Posté le 02-09-2014 à 15:41:17    

Sinon, tu peux simplifier en groupant sur les 5 minutes "précédentes" (donc 16:01, 16:02 iront avec 16:00 au lieu d'aller avec 16:05 comme dans l'autre version).
 
Comme ça, pas besoin de se préoccuper de heure + 1 pour les 5 dernières minutes.
 
 

Code :
  1. select
  2.    max(id),
  3.    adresse_64,
  4.    concat(lpad(hour(heure), 2, '0'),':', lpad(5 * (minute(heure) div 5), 2, '0'), ':00')
  5.    type_valeur,
  6.    date,
  7.    avg(valeur), count(*)
  8. from sondes_v2
  9. group by adresse_64, type_valeur, hour(heure), 5 * (minute(heure) div 5)

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed