SQL，力扣题目1747，应该被禁止的 Leetflex 账户

一、力扣链接

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_time
    from 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、同一解题方法需要学会举一反三