將開始和結束時間轉換為簡單的圖表

我一直在為這個問題苦苦掙扎一段時間。

我有一個sessions儲存會話開始和結束時間的表。

我要存檔的是一個簡單的圖表，它顯示了過去 24 小時內合理間隔內的活動會話數。

我目前的解決方案是每 15 分鐘查詢一次活動會話的數量，並將它們儲存在一個單獨的表中，其中只有一個時間戳和一個數字列。這基本上是重複的數據，不是很準確。

以下查詢產生所需的結果，但效率非常低：

CREATE TEMPORARY TABLE timings (tstamp timestamp);
INSERT INTO timings (tstamp) VALUES (CURRENT_TIMESTAMP - 0 MINUTE), (CURRENT_TIMESTAMP - 15 MINUTE), [...];
SELECT tstamp, (SELECT COUNT(*) FROM sessions WHERE tstamp BETWEEN starttime AND endtime) AS 'numSessions' FROM timings;

我正在使用 MariaDB 10.1。10.2 版添加了 CTE，並且可以進行更新。

我已經升級並使用了 CTE。以下查詢在大約一半的時間內執行。

WITH RECURSIVE tstamp_cte AS (
   SELECT 0 AS n, CURRENT_TIMESTAMP AS tstamp FROM DUAL
   UNION ALL
   SELECT n + 1, tstamp - INTERVAL 15 MINUTE FROM tstamp_cte WHERE n < 96
)
SELECT tstamp, (SELECT COUNT(*) FROM sessions WHERE starttime < tstamp AND endtime >= tstamp OR endtime IS NULL) AS 'sessionCount'
FROM tstamp_cte

引用自：https://dba.stackexchange.com/questions/177110