如何基于datetime_initial和datetime_final合并连续同类别数据？

问题描述 #

原始数据：

category      datetime_initial       datetime_final

party         2022-12-26 11:17:47    2022-12-26 12:20:49
party         2022-12-26 12:20:49    2022-12-26 12:32:24
party         2022-12-26 12:32:24    2022-12-26 12:41:37
party         2022-12-26 12:41:37    2022-12-26 15:04:21
home          2022-12-26 15:04:21    2022-12-26 16:17:39   
home          2022-12-26 16:17:39    2022-12-16 18:45:13
party         2022-12-16 18:45:13    2022-12-16 18:46:08

期望输出：

category      datetime_initial       datetime_final

party         2022-12-26 11:17:47    2022-12-26 15:04:21
home          2022-12-26 15:04:21    2022-12-16 18:45:13
party         2022-12-16 18:45:13    2022-12-16 18:46:08

需求是将连续相同category的记录合并，保留该段连续区间的起始和结束时间，当category切换时生成新的记录。

解决方案（SQL实现） #

假设你的数据表名为activity，字段对应category、datetime_initial、datetime_final，可以用窗口函数实现分组合并：

WITH grouped_data AS (
    SELECT 
        category,
        datetime_initial,
        datetime_final,
        -- 标记新分组：当前行category与上一行不同时，记为1，否则0
        CASE 
            WHEN LAG(category) OVER (ORDER BY datetime_initial) != category 
            THEN 1 
            ELSE 0 
        END AS is_new_group
    FROM activity
),
group_ids AS (
    SELECT 
        *,
        -- 累加标记值，生成每个连续组的唯一ID
        SUM(is_new_group) OVER (ORDER BY datetime_initial) AS group_id
    FROM grouped_data
)
SELECT 
    category,
    MIN(datetime_initial) AS datetime_initial,
    MAX(datetime_final) AS datetime_final
FROM group_ids
GROUP BY group_id, category
ORDER BY datetime_initial;

代码解释 #

1. 第一步（grouped_data）：用LAG()函数获取当前行的上一行category，对比后标记是否为新分组的开始。
2. 第二步（group_ids）：通过累加is_new_group的值，为每一段连续相同的category生成唯一的分组ID。
3. 第三步：按分组ID和category分组，取每组的最早起始时间和最晚结束时间，最后按起始时间排序得到结果。

测试验证 #

如果需要快速测试，可以先创建临时表插入测试数据：

CREATE TABLE activity (
    category VARCHAR(20),
    datetime_initial DATETIME,
    datetime_final DATETIME
);

INSERT INTO activity VALUES
('party', '2022-12-26 11:17:47', '2022-12-26 12:20:49'),
('party', '2022-12-26 12:20:49', '2022-12-26 12:32:24'),
('party', '2022-12-26 12:32:24', '2022-12-26 12:41:37'),
('party', '2022-12-26 12:41:37', '2022-12-26 15:04:21'),
('home', '2022-12-26 15:04:21', '2022-12-26 16:17:39'),
('home', '2022-12-26 16:17:39', '2022-12-16 18:45:13'),
('party', '2022-12-16 18:45:13', '2022-12-16 18:46:08');

执行前面的查询语句即可得到期望的合并结果。

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