HIve 刷题——同一时刻异地登录问题

题目描述

从登录明细表（user_login_detail）中查询在相同时刻，多地登陆（ip_address不同）的用户

题目需求

从登录明细表（user_login_detail）中查询在相同时刻，多地登陆（ip_address不同）的用户

期望结果如下：

user_id
<string>
(用户id)

101

102

104

107

需要用到的表：

用户登录明细表：user_login_detail

user_id(用户id)	ip_address(ip地址)	login_ts(登录时间)	logout_ts(登出时间)
101	180.149.130.161	2021-09-21 08:00:00	2021-09-27 08:30:00
102	120.245.11.2	2021-09-22 09:00:00	2021-09-27 09:30:00
103	27.184.97.3	2021-09-23 10:00:00	2021-09-27 10:30:00

参考SQL

解法1

select u.user_id
from user_login_detail u
         join
     user_login_detail u1
     on u.user_id = u1.user_id
         and u.ip_address != u1.ip_address  
         and ((u.login_ts <= u1.login_ts and u.logout_ts >= u1.logout_ts) or
              (u.login_ts >= u1.login_ts and u.logout_ts <= u1.logout_ts) or
              (u.login_ts <= u1.login_ts and u.logout_ts <= u1.logout_ts) or
              (u.login_ts >= u1.login_ts and u.logout_ts <= u1.logout_ts))
group by u.user_id;

这种处理方式笨笨的，直接按要求进行判断，IP不同，登录时间范围有交集；

解法2

select user_id
from (select *
           , sum(flag) over (partition by user_id order by login_ts)            cnt_1
           , sum(flag) over (partition by user_id,ip_address order by login_ts) cnt_2
      from (select user_id, ip_address, login_ts, 1 as flag
            from user_login_detail
            union all
            select user_id, ip_address, logout_ts, -1 as flag
            from user_login_detail) t) i
where cnt_1 >= 2
  and cnt_2 = 1
group by user_id;

利用窗口函数构造辅助列进行判断。