mysql - 如何折叠日期/时间范围内的相同行？

Question

我想折叠具有相同 URL 参数的行。这是我当前的查询:

SELECT ADDTIME(date,time) as fecha, bytes, url FROM traffic;

这是结果:

2014-07-07 09:05:56         14K     http://feeds.feedburner.com 
2014-07-07 09:05:56         14K     http://feeds.feedburner.com 
2014-07-07 09:11:56         14K     http://feeds.feedburner.com 
2014-07-07 09:17:37         350K    http://www.cmmail.com   
2014-07-07 09:17:40         5K      http://www.cmmail.com   
2014-07-07 09:17:40         10K     http://www.cmmail.com   
2014-07-07 09:20:26         1K      http://www.google.com   
2014-07-07 09:20:48         1K      http://www.google.com   
2014-07-07 09:20:49         1K      http://www.cronista.com 
2014-07-07 09:20:49         1K      http://www.cronista.com 

我想要的是在每 5 分钟间隔之间对相同的 URL 进行分组，并对 bytes 求和。换句话说，我想要这个结果:

2014-07-07 09:05:00         28K     http://feeds.feedburner.com 
2014-07-07 09:10:00         14K     http://feeds.feedburner.com 
2014-07-07 09:15:00         365K    http://www.cmmail.com   
2014-07-07 09:20:00         2K      http://www.google.com   
2014-07-07 09:20:00         2K      http://www.cronista.com 

Answer 1

换句话说，您需要将您的 fecha 日期时间向下舍入到最接近的 5 分钟标记，然后GROUP BY 修改后的 fecha 列和 url 列。

已经有一些问题涉及舍入时间值；您可以使用来自 How to round a time to the nearest 15 minute segment 的最高投票答案像这样对结果集进行分组:

SELECT 
    FROM_UNIXTIME(
        TRUNCATE(
            UNIX_TIMESTAMP(
                ADDTIME(date, time)
            ) / 300, 0
        ) * 300
    ) as fecha_5_min,
    SUM(bytes) as bytes_total,
    url
FROM
    traffic
GROUP BY fecha_5_min, url;

如果缩进没有让您更清楚该技术，以下是从 fecha 创建 fecha_5_min 的确切步骤:

• 使用 UNIX_TIMESTAMP() 将您的原始 fecha 转换为一些秒数;
• 将该秒数除以 300 秒(即 5 分钟)；
• TRUNCATE() 将结果保留到 0 位小数，去除任何余数；
• 将“三百秒”的数字乘以 300 得到单位秒；
• 使用 FROM_UNIXTIME() 将秒数转换回日期和时间.

---

请务必阅读@Erwin 的回答，了解使用 DIV 而不是 / 以避免 TRUNCATE() 调用的好处.