SQL进阶技巧：如何寻找同一批用户【最新面试题】

会飞的一十六 2024-12-23

点击上方【蓝色】字体关注我们

01 场景描述

用户登录时间不超过10分钟的视为同一批用户，找出以下用户哪些属于同一批用户(SQL实现)

例如:

user_name time

a 2024-10-01 09:55

b 2024-10-01 09:57

c 2024-10-01 10:01

d 2024-10-01 23:55

e 2024-10-01 23:58

f 2024-10-02 00:03

g 2024-10-02 08:32

h 2024-10-02 08:33

结果:

group user_name

1 a,b,c

2 d,e,f

3 g,h

02 数据准备

create table user_login as
    (select stack(
                    8,
                    'a', '2024-10-01 09:55',
                    'b', '2024-10-01 09:57',
                    'c', '2024-10-01 10:01',
                    'd', '2024-10-01 23:55',
                    'e', '2024-10-01 23:58',
                    'f', '2024-10-02 00:03',
                    'g', '2024-10-02 08:32',
                    'h', '2024-10-02 08:33'
            ) as (user_name, times))

03 问题分析

步骤1：求出上一行的值

select user_name
      , times
      , lag(times, 1, 0) over (order by times) lag_times
 from user_login

步骤2：计算当前行与上一行的分钟级差值

select user_name
     , times
     , NVL((unix_timestamp(times, 'yyyy-MM-dd hh:mm') - unix_timestamp(lag_times, 'yyyy-MM-dd hh:mm'))  60, 0) min_diff
from (select user_name
           , times
           , lag(times, 1, 0) over (order by times) lag_times
      from user_login) t

步骤3：判断当差值小于10min时记为0，大于10记为1，利用断点分组方法找出分组标记

select user_name
     , times
     , min_diff
     , sum(case when min_diff < 10 then 0 else 1 end ) over(order by times) flg
from (select user_name
           , times
           , NVL((unix_timestamp(times, 'yyyy-MM-dd hh:mm') - unix_timestamp(lag_times, 'yyyy-MM-dd hh:mm'))  60,
                 0) min_diff
      from (select user_name
                 , times
                 , lag(times, 1, 0) over (order by times) lag_times
            from user_login) t) t

步骤4: 将同一标记分组中的数据进行合并

最终的SQL如下：

select grp + 1 grp,
       concat_ws(',',collect_list(user_name))  user_name
from
(select user_name
      , times
      , min_diff
      , sum(case when min_diff < 10 then 0 else 1 end) over (order by times) grp
 from (select user_name
            , times
            , NVL((unix_timestamp(times, 'yyyy-MM-dd hh:mm') - unix_timestamp(lag_times, 'yyyy-MM-dd hh:mm'))  60,
                  0) min_diff
       from (select user_name
                  , times
                  , lag(times, 1, 0) over (order by times) lag_times
             from user_login) t) t) t
group by grp