mysql - 选择按 HOUR() 分组的时间序列并进行环绕

Question

我有一个包含温度读数的表格，需要获取过去 24 小时内每个小时的平均温度，但“环绕”当前时间。

我使用这个声明:

SELECT DISTINCT HOUR(readAt) pointTime
              , ROUND(AVG(temperature),1) avgTemp 
           FROM TempReadings 
          WHERE readAt BETWEEN DATE(NOW() - INTERVAL 1 DAY) AND NOW()) 
            AND temperature IS NOT NULL 
          GROUP 
             BY pointTime;

readAt 是一个时间戳。我希望排序结果能够“环绕”当前时间，因此如果我在上午 10.01 查询，第一行应该是前一天上午 9 点，最后一行应该是今天上午 10 点。

从上面的查询我得到这个:

+-----------+---------+
| pointTime | avgTemp |
+-----------+---------+
|         5 |    23.2 |
|         6 |    12.9 |
|         7 |    11.6 |
|         8 |    14.3 |
|         9 |    10.4 |
|        10 |    12.5 |
|        17 |     0.0 |
|        18 |    23.3 |
|        19 |    14.4 |
|        20 |    14.6 |
|        21 |    17.1 |
+-----------+---------+

11 rows in set (0.00 sec)                 ------------------ formatted as codes

编辑22.10:我使用以下测试语句:

CREATE TABLE TempReadings (
readAt DATETIME NOT NULL,
temperature FLOAT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=big5;

INSERT INTO TempReadings VALUES ((NOW() - INTERVAL 13 HOUR), 10);
INSERT INTO TempReadings VALUES ((NOW()), 20);
INSERT INTO TempReadings VALUES ((NOW() - INTERVAL 2 HOUR), 30);
INSERT INTO TempReadings VALUES ((NOW() - INTERVAL 14 HOUR), 40);
INSERT INTO TempReadings VALUES ((NOW() - INTERVAL 16 HOUR), 50);

SELECT * FROM TempReadings;
+---------------------+-------------+
| readAt              | temperature |
+---------------------+-------------+
| 2016-10-21 20:15:38 |          10 |
| 2016-10-22 09:15:38 |          20 |
| 2016-10-22 07:15:38 |          30 |
| 2016-10-21 19:15:38 |          40 |
| 2016-10-21 17:15:38 |          50 |
+---------------------+-------------+e

下面的语句生成按小时排序的平均值，但我希望最旧的平均值位于第一行

SELECT DISTINCT(HOUR(readAt)) as pointTime, ROUND(AVG(temperature), 1) AS avgTemp FROM TempReadings WHERE (readAt BETWEEN DATE(NOW() - INTERVAL 1 DAY) AND NOW()) AND temperature IS NOT NULL GROUP BY pointTime;

+-----------+---------+
| pointTime | avgTemp |
+-----------+---------+
|         7 |    30.0 |
|         9 |    20.0 |
|        17 |    50.0 |
|        19 |    40.0 |
|        20 |    10.0 |
+-----------+---------+

所以我希望顺序是(pointTime):17(第一行)、19、20、7、9(最后一行)

Answer 1

一种方法使用date_format():

SELECT HOUR(readAt) pointTime,
       ROUND(AVG(temperature),1) avgTemp 
FROM TempReadings 
WHERE temperature IS NOT NULL AND
      date_format(readAt, '%Y-%m-%d %h') BETWEEN
          date_format(now() - interval 25 hour, '%Y-%m-%d %h') and 
          date_format(now() - interval 1 hour, '%Y-%m-%d %h')
GROUP BY pointTime
ORDER BY MIN(readAt);