SQL查询以获取设定时间段内的平均值 [英] SQL query to obtain an Average value for set periods of time

问题描述

我有一个MySQL数据库，用于存储功率读数，每分钟添加一次读数. (即每天1,440个读数).

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

使用PHP，我想根据这些数据生成一个图形，但是我不希望图形上有1,440个点.我可能会选择将其分成15分钟的块(这将给我96分).另外，我不想简单地取第十五个值，因为这会产生误导性的结果.我想做的是使用一个SQL查询，该查询以15分钟的块返回数据，并给出平均功率读数.

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

是否有一个SQL函数可以为我执行此操作?还是我必须在我的PHP代码中执行此计算?

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?

推荐答案

这是您的查询:

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`;

请根据您的架构随意替换表(tablea)和列(time和power)的名称.

Please feel free to replace the table (tablea) and columns (time and power) names according to your schema.

希望这会有所帮助.

这篇关于SQL查询以获取设定时间段内的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！