mysql - 如何按带有偏移量的日期时间进行分组？

Question

我有一个有两列的数据库。 day，一个datetime 列，以及overall，一个int。用户被要求每小时记录一次数据。但是，如果用户睡着了，则不会有新数据。

我正在尝试获取每日平均值。我目前有这样的查询:

SELECT day, AVG(overall) AS overall FROM log GROUP BY DATE(day)

但是，如果有凌晨(凌晨 1-4 点)的条目，则会与前一天的平均值分组。例如，2 月 2 日凌晨 1 点应使用 2 月 1 日的数据进行平均。

如何使用偏移量GROUP BY，以便将清晨包含在前一天的平均值中？

Answer 1

您可以通过减去 4 小时来GROUP BY天偏移量来代替按天分组:

SELECT DATE_ADD(t.theDate, INTERVAL 4 HOUR), t.overall
FROM
(
    SELECT DATE_SUB(day, INTERVAL 4 HOUR) AS theDate, AVG(overall) AS overall
    FROM log
    GROUP BY DATE_SUB(day, INTERVAL 4 HOUR)
) t

这将产生将每天的前 4 小时强制回前一天的效果。

我封装了该查询，因为您可能仍希望报告每个实际 24 小时的一天，但使用通过转移的时间表聚合的结果。