一、力扣链接
LeetCode_1747
二、题目描述
表: LogInfo
+-------------+----------+ | Column Name | Type | +-------------+----------+ | account_id | int | | ip_address | int | | login | datetime | | logout | datetime | +-------------+----------+ 该表可能包含重复项。 该表包含有关Leetflex帐户的登录和注销日期的信息。 它还包含了该账户用于登录和注销的网络地址的信息。 题目确保每一个注销时间都在登录时间之后。
编写解决方案，查找那些应该被禁止的Leetflex帐户编号 account_id 。 如果某个帐户在某一时刻从两个不同的网络地址登录了，则这个帐户应该被禁止。
可以以 任何顺序 返回结果。
三、目标拆解
四、建表语句
Create table If Not Exists LogInfo (account_id int, ip_address int, login datetime, logout datetime)
Truncate table LogInfo
insert into LogInfo (account_id, ip_address, login, logout) values ('1', '1', '2021-02-01 09:00:00', '2021-02-01 09:30:00')
insert into LogInfo (account_id, ip_address, login, logout) values ('1', '2', '2021-02-01 08:00:00', '2021-02-01 11:30:00')
insert into LogInfo (account_id, ip_address, login, logout) values ('2', '6', '2021-02-01 20:30:00', '2021-02-01 22:00:00')
insert into LogInfo (account_id, ip_address, login, logout) values ('2', '7', '2021-02-02 20:30:00', '2021-02-02 22:00:00')
insert into LogInfo (account_id, ip_address, login, logout) values ('3', '9', '2021-02-01 16:00:00', '2021-02-01 16:59:59')
insert into LogInfo (account_id, ip_address, login, logout) values ('3', '13', '2021-02-01 17:00:00', '2021-02-01 17:59:59')
insert into LogInfo (account_id, ip_address, login, logout) values ('4', '10', '2021-02-01 16:00:00', '2021-02-01 17:00:00')
insert into LogInfo (account_id, ip_address, login, logout) values ('4', '11', '2021-02-01 17:00:00', '2021-02-01 17:59:59')五、过程分析
1、添加辅助列is_login 和 is_logout，分别赋值为1 和 -1
2、登入和等出的记录纵向拼接，按账户、网址、日期（day）分组求时间的最值后（同一天在同一网址登录的可能会存在重复数据，根据题意这里不应该+1），时间与最大最小值相等的过滤出来，对辅助列进行累加
3、根据累加的值，如果大于1表示在两个地址登录
六、代码实现
with t1 as(
select account_id, ip_address, login time, 1 is_login
from LogInfo
union all
select account_id, ip_address, logout, -1 is_logout
from LogInfo
)
# select * from t1;
,t2 as(select distinct account_id, ip_address, is_login, time,max(time) over(partition by account_id, ip_address, left(time, 10)) max_time,min(time) over(partition by account_id, ip_address, left(time, 10)) min_timefrom t1
)
# select * from t2;
,t3 as(
select account_id, ip_address,sum(is_login) over(partition by account_id order by time, is_login desc) rn
from t2 where time = max_time or time = min_time
)
select distinct account_id from t3 where rn > 1;七、结果验证
八、小结
1、适时添加辅助列有助于解题
2、union all 纵向拼接，不去重
3、窗口函数使用聚合函数时不应该使用order by，会变成移动窗口，而不是对组内全体数据聚合，或者根据情况进行选择
4、同一解题方法需要学会举一反三
