HQL || SQL练习：用户登录区间合并

• 有一张用户行为日志表ods_user_log, 包含user_id（用户id）、start_time（登录时间）以及end_time（注销时间）
• 问题：求出用户登录的所有最大时间段，比如用户1在10:00登录且11:00注销，用户2在10:30登录且12:00注销，那么最大时间段就是 10:00到12:00

CREATE TABLE ods_user_log (
    user_id INT PRIMARY KEY,
    start_time DATETIME,
    end_time DATETIME
);

INSERT INTO ods_user_log (user_id, start_time, end_time)
VALUES
    (1, '2024-07-01 08:00:00', '2024-07-01 10:00:00'),
    (2, '2024-07-01 09:30:00', '2024-07-01 11:30:00'), 
    (3, '2024-07-01 10:00:00', '2024-07-01 12:00:00'), 
    (4, '2024-07-01 11:00:00', '2024-07-01 13:00:00'), 
    (5, '2024-07-02 08:00:00', '2024-07-02 10:00:00'),
    (6, '2024-07-02 09:00:00', '2024-07-02 11:00:00'), 
    (7, '2024-07-02 10:30:00', '2024-07-02 12:30:00'), 
    (8, '2024-07-02 11:00:00', '2024-07-02 13:00:00'),
    (9, '2024-07-03 08:00:00', '2024-07-03 10:00:00'),
    (10, '2024-07-03 09:00:00', '2024-07-03 11:00:00'); 
  

解析：

要找出用户登录的所有最大时间段，我们只需要两列数据：start_time（登录时间）和 end_time（注销时间）。user_id 并不需要参与计算。

如何判断时间段是否重叠？
可以通过比较当前记录的 start_time 和前一条记录的 end_time（可以用 pre_end_time 表示）来确定：

• 如果当前记录的 start_time <= 前一条记录的 end_time，说明这两个时间段是重叠的。此时，给当前记录打上标签 flag = 0。
• 如果不重叠，则标签为 flag = 1。表示不重叠，为新的区间。

注意：
需要保证记录是按时间顺序的，所以要按照start_time（登录时间）和 end_time（注销时间）升序排列数据。

with t1 as (
  select
   start_time,
   end_time,
   lag(end_time,1) over(order by start_time, end_time) as pre_end_time
  from ods_user_log
)
    select 
      start_time,
      end_time,
      case 
      when start_time <= pre_end_time
      then 0 else 1
      end as flag
  from t1


当执行完上面语句后，过程结果如下：
start_time	           end_time	       flag
2024-07-01 08:00:00	2024-07-01 10:00:00	1
2024-07-01 09:30:00	2024-07-01 11:30:00	0
2024-07-01 10:00:00	2024-07-01 12:00:00	0
2024-07-01 11:00:00	2024-07-01 13:00:00	0
2024-07-02 08:00:00	2024-07-02 10:00:00	1
2024-07-02 09:00:00	2024-07-02 11:00:00	0
2024-07-02 10:30:00	2024-07-02 12:30:00	0
2024-07-02 11:00:00	2024-07-02 13:00:00	0
2024-07-03 08:00:00	2024-07-03 10:00:00	1
2024-07-03 09:00:00	2024-07-03 11:00:00	0

当 flag 从 0 变为 1 时，表示开始了一个新的时间区间，因为没有与前一个时间段重叠。这意味着我们需要新增一个时间交叉分组。
因此，可以对 flag 进行累加，每次累加后的值就是一个新的分组 ID。

t3 as (
  select 
    start_time,
    end_time,
    sum(flag) over(order by start_time, end_time) as group_id
  from t2
)
执行完该语句后结果如下：
start_time	         end_time	     group_id
2024-07-01 08:00:00	2024-07-01 10:00:00	1
2024-07-01 09:30:00	2024-07-01 11:30:00	1
2024-07-01 10:00:00	2024-07-01 12:00:00	1
2024-07-01 11:00:00	2024-07-01 13:00:00	1
2024-07-02 08:00:00	2024-07-02 10:00:00	2
2024-07-02 09:00:00	2024-07-02 11:00:00	2
2024-07-02 10:30:00	2024-07-02 12:30:00	2
2024-07-02 11:00:00	2024-07-02 13:00:00	2
2024-07-03 08:00:00	2024-07-03 10:00:00	3
2024-07-03 09:00:00	2024-07-03 11:00:00	3

最后选择同组的min(start_time) 和max(end_time)即为登录最大时间段

SELECT   
    group_id, -- 每个时间段组的ID
    MIN(start_time) AS start_time, 
    MAX(end_time) AS end_time 
FROM 
    t3
GROUP BY 
    group_id; 
结果如下：
group_id	start_dt	end_dt
1	2024-07-01 08:00:00	2024-07-01 13:00:00
2	2024-07-02 08:00:00	2024-07-02 13:00:00
3	2024-07-03 08:00:00	2024-07-03 11:00:00

整体语句： 

 with t1 as (
   SELECT
     start_time,
     end_time,
     lag(end_time,1) over(order by start_time, end_time) as pre_time
   from ods_user_log
 ),
 t2 as (
   SELECT  
     start_time,
     end_time,
    case 
     when start_time <= pre_time 
    then 0 else 1
   end as flag
 from t1
 ),
 t3 as (
    select 
        start_time,
        end_time,
        sum(flag) over(order by start_time, end_time) as group_id
   from t2
 )
 SELECT
    group_id,
    min(start_time) as start_dt,
    max(end_time) as end_dt
    from t3
    group by group_id

小细节：

如果使用 lag(end_time, 1) over (order by start_time, end_time) as pre_end_time，对于第一条记录，pre_end_time 会是 NULL，从而确保第一条记录始终被视为一个独立的分组，并且分组从 1 开始。

相反，如果使用 lag(end_time, 1, end_time) over (order by start_time, end_time) as pre_end_time 来计算 pre_end_time，那么在处理第一条记录时，由于没有前一条记录，pre_end_time 会被赋值为当前记录的 end_time。这会导致第一条记录与自己“交叉”，从而使分组从 0 开始。

start_time	           end_time	       flag
2024-07-01 08:00:00	2024-07-01 10:00:00	0
2024-07-01 09:30:00	2024-07-01 11:30:00	0
2024-07-01 10:00:00	2024-07-01 12:00:00	0
2024-07-01 11:00:00	2024-07-01 13:00:00	0
2024-07-02 08:00:00	2024-07-02 10:00:00	1
2024-07-02 09:00:00	2024-07-02 11:00:00	0
2024-07-02 10:30:00	2024-07-02 12:30:00	0
2024-07-02 11:00:00	2024-07-02 13:00:00	0
2024-07-03 08:00:00	2024-07-03 10:00:00	1
2024-07-03 09:00:00	2024-07-03 11:00:00	0