SQL实现多条记录按照时间合并

场景：假设用户一天会有很多次上网，每次上网时间段都有两个字段 login_at、logout_at 表示登录时间和登出时间，如果这样的时间段非常多的会产生很多的记录日志。现在假设用户上一次 logout_at 的时间与下一次的 login_at 相差很小（比如10分钟），则这两条记录可以合并成一条。

表名login_records，表结构如下

思路：使用开窗函数 lag

• 使用lag并按照时间排序，将上一行的结束时间放到当前行的末尾 last_logout_at（由于第一行没有上一行，用 '1970-01-01 00:00:00' 代替）。
• 用 login_at - last_logout_at ，如果时间相差小于10分钟，标记为0，否则标记为1，记为 flag 字段。
• 使用开窗函数从第一行到当前行求和，记为flag_id，flag_id 相同的多行表示需要合并，不相同的不需要合并（因为相差小于10分钟的标记为0，加0等于没有加，所以可以合并）。
• 最后按照 userid, first_2_current_sum 分组，每组内的最小的login_at表示开始，最大的logout_at表示结束。

测试数据：

CREATE TABLE `login_record`
(
    `userid`    int NOT NULL,
    `login_at`  datetime DEFAULT NULL,
    `logout_at` datetime DEFAULT NULL
);
INSERT INTO `login_record`
VALUES (1, '2020-04-18 00:01:00', '2020-04-18 00:08:00'),
       (1, '2020-04-18 00:10:00', '2020-04-18 00:11:00'),
       (1, '2020-04-18 00:30:00', '2020-04-18 00:31:00'),
       (2, '2020-04-18 00:01:00', '2020-04-18 00:08:00'),
       (2, '2020-04-18 00:10:00', '2020-04-18 00:11:00'),
       (2, '2020-04-18 00:30:00', '2020-04-18 00:31:00');
       

完整SQL：

SELECT userid,
       MIN(login_at) AS login_at,
       MAX(logout_at) AS logout_at
FROM (SELECT userid,
             login_at,
             logout_at,
             SUM(flag)
                 OVER (PARTITION BY userid
                     ORDER BY login_at
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS first_2_current_sum
      FROM (SELECT userid,
                   login_at,
                   logout_at,
                   IF((UNIX_TIMESTAMP(login_at) - UNIX_TIMESTAMP(last_logout_at)) < 600, 0, 1) AS flag
            FROM (SELECT userid,
                         login_at,
                         logout_at,
                         LAG(logout_at, 1, '1970-01-01 00:00:00')
                             OVER (PARTITION BY userid ORDER BY login_at) AS last_logout_at
                  FROM login_records
                 ) a
           ) b
     ) c
GROUP BY userid, first_2_current_sum;