SQL 小时平均为 24 小时 [英] SQL hour averaging in 24hours
本文介绍了SQL 小时平均为 24 小时的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
您好,我正在尝试使用下面的示例表基于 24 小时温度下的每小时平均值进行 sql 查询.我怎么能这样做?如果您认为这种高频表架构将来也会遇到困难,请告诉我.感谢任何帮助.谢谢.
Hi I'm trying to do sql query based on hourly averaging in 24hours temperature with the sample table below. How can I do so? Let me know if you think this high frequencies tables architecture will face difficulties in future too. Appreciate for any helps. Thank you.
_id Node_ID Modality Timestamp Value
------- ------- ----------- ------------ ------
4930376 2002 Temperature 24/8/15 6:25 30
4930377 2403 Temperature 24/8/15 6:24 30
4930378 2004 Temperature 24/8/15 6:21 30
4930379 2203 Temperature 24/8/15 6:26 29
4930380 2211 Temperature 24/8/15 6:21 29
4930381 2401 Temperature 24/8/15 6:25 33
4930382 2411 Temperature 24/8/15 6:24 30
4930383 2101 Temperature 24/8/15 6:22 31
4930384 2201 Temperature 24/8/15 6:27 30
4930385 2111 Temperature 24/8/15 6:30 36
4930386 2113 Temperature 24/8/15 6:27 35
4934082 3200 Temperature 24/8/15 10:28 33
4934083 2402 Temperature 24/8/15 10:30 32
4934084 2213 Temperature 24/8/15 10:30 33
4934085 2103 Temperature 24/8/15 10:34 36
4934086 2423 Temperature 24/8/15 10:32 32
4934087 2421 Temperature 24/8/15 10:34 32
4934088 2413 Temperature 24/8/15 10:37 32
4934089 2102 Temperature 24/8/15 10:31 35
4934090 2202 Temperature 24/8/15 10:35 32
4934091 2404 Temperature 24/8/15 10:33 35
4934092 2121 Temperature 24/8/15 10:39 36
4934093 2002 Temperature 24/8/15 10:35 33
4940370 2123 Temperature 24/8/15 17:44 36
4940371 2422 Temperature 24/8/15 17:40 33
4940372 2001 Temperature 24/8/15 17:36 35
4940373 2301 Temperature 24/8/15 17:39 32
4940374 2402 Temperature 24/8/15 17:36 32
4940375 2103 Temperature 24/8/15 17:40 36
4940376 2423 Temperature 24/8/15 17:38 33
4940377 2421 Temperature 24/8/15 17:40 33
推荐答案
SELECT AVG(value), _id, Node_ID, Modality, value, Timestamp
FROM yourTable
GROUP BY DATE(Timestamp), HOUR(Timestamp);
这篇关于SQL 小时平均为 24 小时的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文