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

Question

我有一个数据库，它以相当高的频率收集一些数据并记录每个条目的时间戳（存储为纪元时间，而不是 mysql 日期时间）。例如timestamp               rssi    sender-------------------------------------------1592353967.171600       -67     9EDA3DBFFE151592353967.228000       -67     9EDA3DBFFE151592353967.282900       -62     E2ED2569BE21592353971.892600       -67     9EDA3DBFFE151592353973.962900       -61     2ADE2E4597B2...我的目标是能够在 15 分钟的时间间隔内获得所有行的计数，经过研究可以使用GROUP BY. 理想情况下，最终输出看起来像这样timestamp               count------------------------------1592352000 (8:00pm EST)   38    1592352900 (8:15pm EST)   221592353800 (8:30pm EST)   0 <----- Important, must include periods with 0 entries1592354700 (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 minuteFROM requestsWHERE 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 minuteFROM requestsWHERE 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替换值：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