mysql求两个时间段内数据的平均值,SQL查询以获取设定时间段内的平均值

最新推荐文章于 2023-10-14 11:11:31 发布

weixin_39649965

最新推荐文章于 2023-10-14 11:11:31 发布

阅读量1.1k

点赞数

文章标签： mysql求两个时间段内数据的平均值

本文介绍了如何使用SQL查询从每分钟记录一次的电力读数数据库中，以15分钟为间隔计算平均功率，以便在图表中显示更平滑的数据。通过将时间转换并进行分组，可以获取每个15分钟段的平均功率值。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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`) `avgpower`

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.