每 15 分钟的 MySQL 组纪元时间戳

Question

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

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

任何帮助将不胜感激。

Answer 1

您可以使用“数字”表在一小时 (0-3) 内生成 15 分钟的时间段，然后LEFT JOIN根据您的计数，按 15 分钟的时间段分组，使用0COALESCE替换值：NULLSELECT periods.period * 900 + 1592352000 AS timestamp,       FROM_UNIXTIME(periods.period * 900 + 1592352000) AS time,       COALESCE(counts.total, 0) AS totalFROM (  SELECT 0 period  UNION ALL SELECT 1   UNION ALL SELECT 2  UNION ALL SELECT 3) periodsLEFT 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                    total1592352000  2020-06-17 00:00:00     11592352900  2020-06-17 00:15:00     11592353800  2020-06-17 00:30:00     51592354700  2020-06-17 00:45:00     0