计算日期/时间部分的平均值 [英] Computing average values over sections of date/time

问题描述

问题:

我有一个传感器读数数据库，其中带有读取传感器时间的时间戳.基本上它看起来像这样:

传感器 |时间戳 |价值

现在我想用这些数据制作一个图表，我想制作几个不同的图表.假设我想要最后一天一个，最后一周一个，最后一个月一个.每个图形的分辨率会有所不同，因此对于日图形，分辨率将为 1 分钟.对于周图，它将是一小时，对于月图，它将是一天或一天​​的四分之一.

所以我想要一个输出是每个分辨率的平均值(例如，天 = 一分钟的平均值，周 = 一小时的平均值等等)

例如:

传感器 |开始 |完 |平均数

如何在 mySQL 中轻松快速地执行此操作?我怀疑它涉及创建一个临时表或排序并将传感器数据与其连接以获得传感器的平均值?但我对 mySQL 的了解充其量是有限的.

有没有真正聪明的方法来做到这一点?

解决方案

SELECT DAY(Timestamp), HOUR(Timestamp), MINUTE(Timestamp), AVG(value)从 mytable通过...分组DAY(Timestamp), HOUR(Timestamp), MINUTE(Timestamp) WITH ROLLUP

WITH ROLLUP 子句在这里生成额外的行，每个 HOUR 和 DAY 的平均值，如下所示:

SELECT DAY(ts), HOUR(ts), MINUTE(ts), COUNT(*)从    (SELECT CAST('2009-06-02 20:00:00' AS DATETIME) AS ts联合所有SELECT CAST('2009-06-02 20:30:00' AS DATETIME) AS ts联合所有SELECT CAST('2009-06-02 21:30:00' AS DATETIME) AS ts联合所有SELECT CAST('2009-06-03 21:30:00' AS DATETIME) AS ts) q通过...分组DAY(ts), HOUR(ts), MINUTE(ts) WITH ROLLUP

<前>2, 20, 0, 12, 20, 30, 12, 20, NULL, 22, 21, 30, 12, 21, NULL, 12, 空, 空, 33, 21, 30, 13, 21, 空, 13、空、空、1空，空，空，4

2, 20, NULL, 2 这里的意思是 COUNT(*) 是 2 对于 DAY = 2>、HOUR = 20 和所有分钟.

Problem:

I have a database of sensor readings with a timestamp for the time the sensor was read. Basically it looks like this:

Sensor | Timestamp | Value

Now I want to make a graph out of this data and I want to make several different graphs. Say I want one for the last day, one for the last week and one for the last month. The resolution of each graph will be different so for the day-graph the resolution would be 1 minute. For the week graph it would be one hour and for the month graph it would be one day, or quarter of a day.

So I would like an output that is the average of each resolution (eg. Day = Average over the minute, Week = Average over the hour and so on)

Ex:

Sensor | Start | End | Average

How do I do this easily and quickly in mySQL? I suspect it invoves creating a temporary table or sorts and joining the sensor data with that to get the average values of the sensor? But my knowledge of mySQL is limited at best.

Is there a really clever way to do this?

解决方案

SELECT  DAY(Timestamp), HOUR(Timestamp), MINUTE(Timestamp), AVG(value)
FROM    mytable
GROUP BY
        DAY(Timestamp), HOUR(Timestamp), MINUTE(Timestamp) WITH ROLLUP

WITH ROLLUP clause here produces extra rows with averages for each HOUR and DAY, like this:

SELECT  DAY(ts), HOUR(ts), MINUTE(ts), COUNT(*)
FROM    (
        SELECT  CAST('2009-06-02 20:00:00' AS DATETIME) AS ts
        UNION ALL
        SELECT  CAST('2009-06-02 20:30:00' AS DATETIME) AS ts
        UNION ALL
        SELECT  CAST('2009-06-02 21:30:00' AS DATETIME) AS ts
        UNION ALL
        SELECT  CAST('2009-06-03 21:30:00' AS DATETIME) AS ts
        ) q
GROUP BY
        DAY(ts), HOUR(ts), MINUTE(ts) WITH ROLLUP

2, 20, 0, 1
2, 20, 30, 1
2, 20, NULL, 2
2, 21, 30, 1
2, 21, NULL, 1
2, NULL, NULL, 3
3, 21, 30, 1
3, 21, NULL, 1
3, NULL, NULL, 1
NULL, NULL, NULL, 4

2, 20, NULL, 2 here means that COUNT(*) is 2 for DAY = 2, HOUR = 20 and all minutes.

这篇关于计算日期/时间部分的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！