过的日期/时间段计算平均值

问题描述：

我有传感器读数与用于传感器读取的时间的时间戳的数据库。基本上看起来像这样：

Sensor | Timestamp | Value

现在我想从这些数据中制作一个图表，并且我想制作几个不同的图表。假设我想在最后一天得到一个，最后一个星期要一个，最后一个月要一个。每张图的分辨率会有所不同，因此对于日图，分辨率将为1分钟。对于星期图，它将是一个小时，对于月份图，它将是一天或四分之一天。

所以想的输出即每个分辨率（例如天=平均过分钟，周=平均过小时等。）的平均

例：

Sensor | Start | End | Average

我如何在mySQL中轻松快捷地完成此操作？我怀疑它是否会创建一个临时表或者分类并加入传感器数据以获得传感器的平均值？但是我对mySQL的了解还是有限的。

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

答

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

WITH ROLLUP条款产生与每个HOUR和DAY，这样平均额外行：

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

这里的表示COUNT(*)为2为DAY = 2，HOUR = 20和所有分钟。

这产生的东西接近我期待的结果。 “WITH ROLLUP”做了什么，因为它看起来会产生相同的结果，如果我删除它？ – 2009-06-02 16:51:41

来自MySQL参考手册： “向GROUP BY子句添加WITH ROLLUP修饰符会导致查询产生另一行，显示所有值的总数” – nightcoder 2009-06-02 16:54:29

答

不太结果表你想要的，但在这里是做1分钟的分辨率首发：这里

SELECT sensor,minute(timestamp),avg(value) 
FROM table 
WHERE <time period specifier limits to a single hour> 
GROUP BY sensor, minute(timestamp)

答

我使用的代码非常相似，这种（未经测试，但它从工作中采取）

设置的变量：

$seconds = 3600; 
$start = mktime(...); // say 2 hrs ago 
$end = .... // 1 hour after $start

然后运行查询

SELECT MAX(`when`) AS top_When, MIN(`when`) AS low_When, 
    ROUND(AVG(sensor)) AS Avg_S, 
    (MAX(`when`) - MIN(`when`)) AS dur, /* the duration in seconds of the actual period */ 
    ((floor(UNIX_TIMESTAMP(`when`)/$seconds)) * $seconds) as Epoch 
    FROM `sensor_stats` 
    WHERE `when` >= '$start' AND `when` <= '$end' and duration=30 
    GROUP BY Epoch/*((floor(UNIX_TIMESTAMP(`when`)/$seconds)) * $seconds)*/

的这样做的好处是你可以有任何你想要的时间段 - 甚至不需要让他们在“整数”上，比如一个完整的时钟小时（即使是一个钟点，0-59）。

过的日期/时间段计算平均值

相关推荐