SQL进阶技巧：用户不同 WiFi 行为区间划分分析 | 断点分组问题

会飞的一十六 2024-09-22

—

场景描述

现有用户扫描或连接 WiFi 记录表 user_wifi_log ，每一行数据表示某时刻用户扫描或连接 WiFi 的日志。

现需要进行用户行为分析，如何划分用户不同 WiFi 行为区间？满足：

1）行为类型分为两种：连接（scan）、扫描（conn）；

2）行为区间的定义为：相同行为类型，且相邻两次行为的时间差不超过 30 分钟；

3）不同行为区间在满足定义的情况下应取到最长；

—

数据准备

with user_wifi_log as
    (
 
select stack(
     9
   ,'2024-01-01 10:01:00', '101', 'cmcc-Starbucks', 'scan' -- 扫描'
   ,'2024-01-01 10:02:00', '101', 'cmcc-Starbucks', 'scan'
   ,'2024-01-01 10:03:00', '101', 'cmcc-Starbucks', 'scan'
   ,'2024-01-01 10:04:00', '101', 'cmcc-Starbucks', 'conn'  -- 连接
   ,'2024-01-01 10:05:00', '101', 'cmcc-Starbucks', 'conn'
   ,'2024-01-01 10:06:00', '101', 'cmcc-Starbucks', 'conn'
   ,'2024-01-01 11:01:00', '101', 'cmcc-Starbucks', 'conn'
   ,'2024-01-01 11:02:00', '101', 'cmcc-Starbucks', 'conn'
   ,'2024-01-01 11:03:00', '101', 'cmcc-Starbucks', 'conn'
  )
   -- 字段：时间，用户，WiFi，状态（扫描、连接）
   as (dt, user_id, wifi, status)
 )
select * from user_wifi_log;

—

问题分析

核心逻辑：以用户、WIFI 分组，结合连续性阈值与行为序列上下文信息，划分行为区间。

本题本质上是断点分组的思想应用

详细步骤：

第一步：依据题意找断点

         select dt
              , user_id
              , wifi
              , status
              , lag_dt
              , lag_status
              , if(lag_status is null
                or lag_dt is null
                   or status <> lag_status
                      or unix_timestamp(dt) - unix_timestamp(lag_dt) > 60 * 30
             , 1, 0)  break_point
    from (
        select *,
            lag(dt, 1) over (partition by user_id, wifi order by dt) as lag_dt,
            lag(status, 1) over (partition by user_id, wifi order by dt) as lag_status
        from user_wifi_log
    ) t1

第二步：构建分组标志

select dt
      , user_id
      , wifi
      , status
      , lag_dt
      , lag_status
      , sum(if(lag_status is null
               or lag_dt is null
               or status <> lag_status
               or unix_timestamp(dt) - unix_timestamp(lag_dt) > 60 * 30
        , 1, 0) ) over(order by dt)      break_point
 from (select *,
              lag(dt, 1) over (partition by user_id, wifi order by dt)     as lag_dt,
              lag(status, 1) over (partition by user_id, wifi order by dt) as lag_status
       from user_wifi_log) t1

步骤三：分组内求出最大，最小时间，即为开始，结束时间

select user_id,
    wifi,
    status,
    min(dt) as start_time,
    max(dt) as end_tiem
from
(select dt
      , user_id
      , wifi
      , status
      , lag_dt
      , lag_status
      , sum(if(lag_status is null
                   or lag_dt is null
                   or status <> lag_status
                   or unix_timestamp(dt) - unix_timestamp(lag_dt) > 60 * 30
                , 1, 0)) over (order by dt) grp_flg
 from (select *,
              lag(dt, 1) over (partition by user_id, wifi order by dt)     as lag_dt,
              lag(status, 1) over (partition by user_id, wifi order by dt) as lag_status
       from user_wifi_log) t1
 ) t2
group by user_id, wifi,status, grp_flg