Forum |  HardWare.fr | News | Articles | PC | S'identifier | S'inscrire | Shop Recherche
891 connectés 

  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  group BY et moyenne de valeurs

 
 


 Mot :   Pseudo :  
 
Bas de page
Auteur Sujet :

group BY et moyenne de valeurs

n°2236676
hppp
Serveur@home
Posté le 31-08-2014 à 20:01:12  profilanswer
 

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
mood
Publicité
Posté le 31-08-2014 à 20:01:12  profilanswer
 

n°2236678
deliriumtr​emens
sic transit intestinal...
Posté le 31-08-2014 à 23:13:21  profilanswer
 

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
n°2236684
hppp
Serveur@home
Posté le 01-09-2014 à 08:01:33  profilanswer
 

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.

n°2236819
hppp
Serveur@home
Posté le 02-09-2014 à 11:23:19  profilanswer
 

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

n°2236830
deliriumtr​emens
sic transit intestinal...
Posté le 02-09-2014 à 15:01:16  profilanswer
 

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
n°2236831
deliriumtr​emens
sic transit intestinal...
Posté le 02-09-2014 à 15:41:17  profilanswer
 

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)


Aller à :
Ajouter une réponse
  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  group BY et moyenne de valeurs

 

Sujets relatifs
HELP macro copie/colle valeurs entre 2 classeurs différentsRécuperer les valeurs checked en php
utiliser les valeurs de deux fichiersProblème group by
Moyen le plus rapide de copier une grosse table SQL en mémoire ?Soiréee User Group C++ Francophone - En faire ++ avec C++
[SQL] ORDER BY selon préférence[XML] Envoyer en argument de script des valeurs du journal d'evenement
recherche un moyen d'integrer du css a un fichier jsextraire des valeurs pour remplir tableau
Plus de sujets relatifs à : group BY et moyenne de valeurs

Forum MesDiscussions.Net, Version 2010.2
(c) 2000-2011 Doctissimo
Page générée en 0.073 secondes

Copyright © 1997-2022 Hardware.fr SARL (Signaler un contenu illicite / Données personnelles) / Groupe LDLC / Shop HFR