mysql求两个时间段内数据的平均值,SQL查询以获取设定时间
段内的平均值
I have a MySQL database which is used to store power readings, with a reading added once per minute. (i.e. 1,440 readings per day).
time power
---- -----
00:00:00 346
00:01:00 352
00:02:00 247
mysql下载add produce
Using PHP I want to produce a graph from this data, but I don't want 1,440 points on the graph. I might choose to split it into 15 minute chunks (which will give me 96 points). Also, I don't want to simply take every fifteenth value, as this will give misleading results. What I want to do is use an SQL query that returns the data in 15 minutes blocks and gives the power reading as an average.
The output might look something like this:
starttime avgpower
--------- --------
00:00:00 342
00:15:00 490
00:30:00 533
Is there an SQL function that will do this for me? or am I going to have to perform this calculation in my PHP code?
解决⽅案
Here is your query:
SELECT STR_TO_DATE(CONCAT(DATE_FORMAT(`time`, '%H'), ':', (FLOOR(DATE_FORMAT(`time`, '%i') / 15) * 15), ':00'), '%H:%i:%s') `starttime`, AVG(`power`) `avgpo
wer`
FROM `tablea`
GROUP BY `starttime`;
Please feel free to replace the table (tablea) and columns (time and power) names according to your schema.
Hope this helps.

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。