MySQL中按网关ID分组计算单列时间记录总时长的问题

嘿，这个问题我之前也碰到过！咱们一步步来解决它：

首先得明确核心逻辑：咱们要算的总时长，应该是同一网关下，按时间戳排序后，相邻时间戳的差值之和；如果某个网关只有一条记录，那总时长自然是0，毕竟没有前后时间间隔可言。

第一步：给每个网关的记录配对“下一条时间戳” #

咱们可以用窗口函数LEAD()来搞定这个——它能给每个网关分组里的每条记录，自动找到下一条记录的时间戳。如果是分组里的最后一条记录，它会返回NULL，刚好方便我们后续处理：

SELECT 
    gateway,
    timestamp,
    LEAD(timestamp) OVER (PARTITION BY gateway ORDER BY timestamp) AS next_timestamp
FROM your_table_name;

第二步：计算总秒数（处理单条记录的情况） #

接下来就可以用TIMESTAMPDIFF()计算每条记录和下一条的时间差（用秒做单位，后面转格式更方便）。这里要注意，最后一条记录的next_timestamp是NULL，直接算会出错，所以用COALESCE()把这种情况的差值设为0，然后按网关求和：

WITH gateway_time_intervals AS (
    SELECT 
        gateway,
        timestamp,
        LEAD(timestamp) OVER (PARTITION BY gateway ORDER BY timestamp) AS next_timestamp
    FROM your_table_name
)
SELECT 
    gateway,
    SUM(COALESCE(TIMESTAMPDIFF(SECOND, timestamp, next_timestamp), 0)) AS total_seconds
FROM gateway_time_intervals
GROUP BY gateway;

第三步：把总秒数转成“时:分:秒”格式 #

现在有了总秒数，咱们把它拆成时、分、秒就行。要是想让格式更规范（比如不足两位补0，像01:02:03而不是1:2:3），就用LPAD()函数来补位：

WITH gateway_time_intervals AS (
    SELECT 
        gateway,
        timestamp,
        LEAD(timestamp) OVER (PARTITION BY gateway ORDER BY timestamp) AS next_timestamp
    FROM your_table_name
),
total_seconds_per_gateway AS (
    SELECT 
        gateway,
        SUM(COALESCE(TIMESTAMPDIFF(SECOND, timestamp, next_timestamp), 0)) AS total_seconds
    FROM gateway_time_intervals
    GROUP BY gateway
)
SELECT 
    gateway,
    CONCAT(
        LPAD(FLOOR(total_seconds / 3600), 2, '0'), ':',
        LPAD(FLOOR((total_seconds % 3600) / 60), 2, '0'), ':',
        LPAD(total_seconds % 60, 2, '0')
    ) AS total_duration
FROM total_seconds_per_gateway;

特殊情况验证：单条记录的网关 #

当某个网关只有一条记录时，LEAD()返回NULL，COALESCE()会把这条记录的差值设为0，求和后总秒数是0，最终格式会是00:00:00，完全符合预期。

另一种场景：如果总时长是“最后一条减第一条” #

要是你的需求其实是每个网关的事件总时长等于最后一条时间戳减去第一条时间戳（比如记录的是事件的开始、过程点和结束，总时长就是从开始到结束），那可以简化成这个写法：

WITH gateway_time_range AS (
    SELECT 
        gateway,
        MIN(timestamp) AS start_time,
        MAX(timestamp) AS end_time
    FROM your_table_name
    GROUP BY gateway
)
SELECT 
    gateway,
    CONCAT(
        LPAD(FLOOR(TIMESTAMPDIFF(SECOND, start_time, end_time) / 3600), 2, '0'), ':',
        LPAD(FLOOR((TIMESTAMPDIFF(SECOND, start_time, end_time) % 3600) / 60), 2, '0'), ':',
        LPAD(TIMESTAMPDIFF(SECOND, start_time, end_time) % 60, 2, '0')
    ) AS total_duration
FROM gateway_time_range;

这种情况下，单条记录的开始和结束时间相同，总时长也是00:00:00，同样能完美处理。

内容的提问来源于stack exchange，提问作者amorettim