选择按HOUR（）分组的时间序列(Select time series grouped by HOUR() with wrap around)

 我有一张温度读数表，需要得到过去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点1分查询第一行应该是前一天上午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（最后一个） 

I have a table with temperature readings and need to get the average temp for each hour the last 24 hours but "wrapped around" current hour.
 
I use this statement: 
 
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 is a timestamp. I would like the ordered result to "wrap" around current hour, so if I query at 10.01am first row should be 9am the previous day and last row 10am today.
 
From the above query I get this:
 
+-----------+---------+
| 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
 
Edit 22.10: I use the following test statements:
 
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
 
Below statement produces the average sorted by hour but I would like the oldest average to be the first row
 
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 |
+-----------+---------+
 
So I would like the order to be (pointTime): 17 (first row), 19, 20, 7, 9 (last)

原文：https://stackoverflow.com/questions/40172077