mysql - 如何使用unix时间戳计算MySQL GROUP BY边界

Question

出于性能原因，我需要创建按天汇总的数据的物化 View 。时间戳存储为 UNIX 时间戳。

为了允许更新(替换)到物化 View 的表中，我希望它有一个时间戳，同样采用 UNIX 时间戳格式，与一天的开始(或结束)相匹配。该时间戳将成为表键的一部分。每当基表数据发生更改时，我就可以仅更新该特定日期的 View 表(同样是为了性能)。

我当前的聚合语句如下所示:

REPLACE INTO aggregate_data (channel_id, type, timestamp, value)
SELECT channel_id, 'day' AS type, MAX(timestamp) AS timestamp, SUM(value) AS value
FROM data
GROUP BY channel_id, YEAR(FROM_UNIXTIME(timestamp/1000)), DAYOFYEAR(FROM_UNIXTIME(timestamp/1000));

如何使 MAX(timestamp) 匹配当天的边界 (24:00)，而不是最后聚合数据的时间戳？

Answer 1

不确定我是否正确理解你的问题。您正在寻找这样的东西吗？

REPLACE INTO aggregate_data (channel_id, type, timestamp, value)
SELECT channel_id, 'day' AS type, 

UNIX_TIMESTAMP(DATE_FORMAT(FROM_UNIXTIME(MAX(timestamp)), '%Y-%m-%d 23:59:59'))
AS timestamp, 

SUM(value) AS value
FROM data
GROUP BY channel_id, YEAR(FROM_UNIXTIME(timestamp/1000)), DAYOFYEAR(FROM_UNIXTIME(timestamp/1000));