每15分钟MySQL组时代时间戳记

276

克里斯·劳伦斯（Chris Lawrence）：

我有一个数据库，该数据库以很高的频率收集一些数据，并记录每个条目的时间戳（存储为纪元时间，而不是mysql datetime）。例如

timestamp               rssi    sender
-------------------------------------------
1592353967.171600       -67     9EDA3DBFFE15
1592353967.228000       -67     9EDA3DBFFE15
1592353967.282900       -62     E2ED2569BE2
1592353971.892600       -67     9EDA3DBFFE15
1592353973.962900       -61     2ADE2E4597B2
...

我的目标是能够在15分钟的时间间隔内获得所有行的计数，经过研究可以使用进行获取GROUP BY。理想情况下，最终输出将如下所示

timestamp               count
------------------------------
1592352000 (8:00pm EST)   38    
1592352900 (8:15pm EST)   22
1592353800 (8:30pm EST)   0 <----- Important, must include periods with 0 entries
1592354700 (8:45pm EST)   61
...

我主要遇到两件事的问题：1.能够显示结果中的时间戳记间隔2.显示一个时间段内0行的间隔

我当前的尝试如下，并且在正确的轨道上，因为该时间段内的数据实际上是正确的

 Showing rows 0 - 23 (24 total, Query took 0.0264 seconds.)

SELECT count(*) AS total, MINUTE(FROM_UNIXTIME(timestamp)) AS minute
FROM requests
WHERE timestamp >= 1592352000 AND timestamp < (1592352000 + 3600)  
GROUP BY MINUTE(FROM_UNIXTIME(timestamp))

total   minute  
55  32  
89  33  
64  34  
55  35  
87  36  
82  37  
90  38  
69  39  
74  40  
47  41  
89  42  
53  43  
71  44  
87  45  
72  46  
83  47  
86  48  
83  49  
113 50  
76  51  
77  52  
88  53  
81  54  
28  55

此数据是正确的，但是此处未显示期间（该小时的前30分钟没有数据，因此第一个minute条目从32开始）。还尝试通过使用获取每15分钟

SELECT count(*) AS total, MINUTE(FROM_UNIXTIME(timestamp)) AS minute
FROM requests
WHERE timestamp >= 1592352000 AND timestamp < (1592352000 + 3600)  
GROUP BY MINUTE(FROM_UNIXTIME(timestamp)) DIV 15

#1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wave_master.requests.timestamp' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

任何帮助将不胜感激。

尼克：

您可以使用“数字”表在小时（0-3）内生成15分钟周期，然后LEFT JOIN将其计数（按15分钟周期分组），用0 COALESCE代替NULL值：

SELECT periods.period * 900 + 1592352000 AS timestamp,
       FROM_UNIXTIME(periods.period * 900 + 1592352000) AS time,
       COALESCE(counts.total, 0) AS total
FROM (
  SELECT 0 period
  UNION ALL SELECT 1 
  UNION ALL SELECT 2
  UNION ALL SELECT 3
) periods
LEFT JOIN (
  SELECT (timestamp - 1592352000) DIV 900 AS period,
         COUNT(*) AS total
  FROM requests
  WHERE timestamp >= 1592352000 AND timestamp < 1592352000 + 3600
  GROUP BY period
) counts ON counts.period = periods.period

输出（用于您问题中的数据以及其他几个值）：

timestamp   time                    total
1592352000  2020-06-17 00:00:00     1
1592352900  2020-06-17 00:15:00     1
1592353800  2020-06-17 00:30:00     5
1592354700  2020-06-17 00:45:00     0

dbfiddle上的演示

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。