如何将DATETIME(3)列按每2个元素分组并计算时间差

核心思路 #

先通过窗口函数ROW_NUMBER()给按occurred_at升序排列的记录生成连续行号，再通过行号计算分组ID（每2条为一组），最后基于分组ID做聚合操作。

1. 用GROUP_CONCAT()拼接每组时间 #

SELECT
    (ROW_NUMBER() OVER(ORDER BY occurred_at) - 1) DIV 2 AS group_id,
    GROUP_CONCAT(occurred_at ORDER BY occurred_at SEPARATOR ', ') AS grouped_times
FROM events
GROUP BY group_id
ORDER BY group_id;

2. 计算每组内两个时间的TIMEDIFF #

因为每组固定2条记录，我们可以先把行号按组内位置标记，再通过分组ID关联计算差值：

WITH ranked_events AS (
    SELECT
        occurred_at,
        (ROW_NUMBER() OVER(ORDER BY occurred_at) - 1) DIV 2 AS group_id,
        ROW_NUMBER() OVER(PARTITION BY (ROW_NUMBER() OVER(ORDER BY occurred_at) - 1) DIV 2 ORDER BY occurred_at) AS pos_in_group
    FROM events
)
SELECT
    r1.group_id,
    TIMEDIFF(r2.occurred_at, r1.occurred_at) AS time_diff
FROM ranked_events r1
JOIN ranked_events r2 ON r1.group_id = r2.group_id AND r1.pos_in_group = 1 AND r2.pos_in_group = 2
ORDER BY r1.group_id;

为什么取模2的方法没成功？ #

如果直接用ROW_NUMBER() % 2分组，行号1%2=1、2%2=0、3%2=1、4%2=0，这样会把第1、3条分到一组，第2、4条分到一组，完全不符合你要的“第1与第2、第3与第4”的分组逻辑。而用(行号-1) DIV 2可以让前两条的分组ID都是0，接下来两条是1，以此类推，正好匹配需求。

内容的提问来源于stack exchange，提问作者Paul-Marie