我有一個名為 events 的 MySQL 8.0.23 表，其架構如下：
eventUID - 整數 NOT NULL
camtimestamp - MySQL 日期時間戳
方向 - 字串 - “In”或“Out”
propUID - 整數
在單個 SELECT 陳述句中，我試圖按小時確定過去 24 小時有多少汽車“進”，有多少汽車“出”。這是我正在嘗試的（尚未內置 24 小時限制）。
select camtimestamp,count(*) from events where direction ="In" and propUID = 7 group by year(camtimestamp),month(camtimestamp),day(camtimestamp),hour(camtimestamp);
這是我得到的一個樣本。
2022-02-14 22:02:40 38
2022-02-14 21:56:56 15
2022-02-14 20:55:30 47
2022-02-14 19:59:18 51
2022-02-14 18:59:50 36
2022-02-14 17:52:04 10
2022-02-14 16:58:01 16
2022-02-14 15:59:00 36
2022-02-14 14:58:52 44
我還有一個名為 datehourlist 的表，我可以使用它加入我的 SELECT。
樣本資料：
2019-05-01 00:00:00
2019-05-01 01:00:00
2019-05-01 02:00:00
2019-05-01 03:00:00
2019-05-01 04:00:00
2019-05-01 05:00:00
2019-05-01 06:00:00
2019-05-01 07:00:00
2019-05-01 08:00:00
還：
mysql> select min(datehour) from datehourlist;
---------------------
| min(datehour) |
---------------------
| 2019-05-01 00:00:00 |
---------------------
1 row in set (0.02 sec)
mysql> select max(datehour) from datehourlist;
---------------------
| max(datehour) |
---------------------
| 2040-12-31 00:00:00 |
---------------------
1 row in set (0.02 sec)
datehourlist 包含從 2019 年 5 月 1 日到 2040 年 12 月 31 日的每個小時。
這是我真正想要的示例：
下面的第 1 列是一個舍入的分組時間戳（而上面的第 1 列是一個非舍入的實際時間戳）
如果該小時沒有資料，則下面的第 1 列不會跳過該小時。
下面的第 2 列是該小時的“In”計數。
下面的第 3 列是該小時的“Out”計數。
2019-05-02 06:00:00 5 10
2019-05-02 07:00:00 127 10
2019-05-02 08:00:00 0 0
2019-05-02 09:00:00 115 10
2019-05-02 10:00:00 71 10
2019-05-02 11:00:00 147 10
2019-05-02 12:00:00 140 10
我應該使用什么 SELECT 陳述句來獲得我需要的輸出？
另外，我將如何優化該 SELECT 陳述句？
在事件中，我有 50 萬個事件，并且每天增長 100 個。
預先感謝您的幫助。
感謝您這么快就提供了一個很好的解決方案。
SELECT dhl.datehour datehour,
COALESCE(SUM(ev.direction = 'In'), 0) `In`,
COALESCE(SUM(ev.direction = 'Out'), 0) `Out`
FROM datehourlist dhl
LEFT JOIN events ev
ON DATE_FORMAT(ev.camtimestamp, '%Y-%m-%d %H:00:00') = dhl.datehour
WHERE ev.camtimestamp >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') - INTERVAL 24 HOUR
AND ev.camtimestamp < DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')
AND ev.propUID = 7
GROUP BY dhl.datehour;
uj5u.com熱心網友回復：
首先，我們需要一個trunc_to_hour()函式，它接受一個任意的DATETIMEorTIMESTAMP值并回傳其小時的開始。就是這個。
DATE_FORMAT(camtimestamp, '%Y-%m-%d %H:00:00')
其次，我們需要一個可以處理最近 24 小時的 WHERE 運算式。就是這個。
WHERE camtimestamp >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') - INTERVAL 24 HOUR
AND camtimestamp < DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')
對于示例時間戳2021-03-14 16:04:30，這給出了以下內容。
WHERE camtimestamp >= `2021-03-13 16:00:00`
AND camtimestamp < '2021-03-14 16:00:00`
也就是說，它選擇最近 24 個完整時鐘小時的記錄。如果您想要迄今為止的小時數，您可能需要調整這個 WHERE 運算式。
第三，我們需要條件和（對于 In 和 Out）。
碰巧運算式direction = 'In'給出了1when directionis In，0whendirection是其他字串（如Out），如果direction它本身是 NULL，則給出 NULL。所以
SUM(direction='In')
計算滿足該標準的行。
第四，當 SUM 為空時，我們希望顯示為零。像這樣。
COALESCE(SUM(direction='In'),0)
第五，我們可以像這樣把它放在一起：
SELECT DATE_FORMAT(ev.camtimestamp, '%Y-%m-%d %H:00:00') datehour,
COALESCE(SUM(ev.direction = 'In'), 0) `In`,
COALESCE(SUM(ev.direction = 'Out'), 0) `Out`
FROM events ev
WHERE ev.camtimestamp >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') - INTERVAL 24 HOUR
AND ev.camtimestamp < DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')
AND ev.propUID = 7
GROUP BY DATE_FORMAT(ev.camtimestamp, '%Y-%m-%d %H:00:00')
這給了你你的結果集。但是，如果這些時間沒有記錄，它仍然可能會丟失一些時間。
因此，第六，我們可以將它加入到您預先存在的每小時日歷表中，如下所示：
SELECT dhl.datehour datehour,
COALESCE(SUM(ev.direction = 'In'), 0) `In`,
COALESCE(SUM(ev.direction = 'Out'), 0) `Out`
FROM datehourlist dhl
LEFT JOIN events ev
ON DATE_FORMAT(ev.camtimestamp, '%Y-%m-%d %H:00:00') = dhl.datehour
WHERE ev.camtimestamp >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') - INTERVAL 24 HOUR
AND ev.camtimestamp < DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')
AND ev.propUID = 7
GROUP BY dhl.datehour
那應該這樣做。（未除錯。）
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/444050.html