Hive sql 常见面试题-查询相同时刻多地登陆的用户_hvie lead()查询相同时刻多地登陆的用户

1. 题目需求

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

期望结果如下：

2. 需要用到的表

用户登录明细表：user_login_detail

3. 查询sql

with tmp1 as (

	select user_id, ip_address, login_ts, logout_ts,

        max(logout_ts) over(PARTITION BY user_id ORDER BY login_ts rows between unbounded preceding and 1 preceding) lag_ts,

  	lag(ip_address,1,0) over(PARTITION BY user_id ORDER BY login_ts) lag_ip from user_login_detail

),tmp2 as (

	select user_id, if(lag_ts>login_ts,if(lag_ip!=ip_address,1,0),0) flags from tmp1

)select user_id from tmp2 where flags = 1 group by user_id
1
2
3
4
5
6
7
8
9
10
11
12
13