使用相同IP的用户

##　使用相同IP的用户

问题:
根据用户登录信息表(uuid,ip,log_in_time), 统计出使用相同ip数超过3个的, 登陆的用户有哪些.
(也可以根据相同ip数, 时间等条件, 判断哪些用户是同一个人)

1- 数据准备

登录信息表: t_log_in_info
	uuid: 用户id, ip: 登录ip, log_in_time: 登录时间

-- 数据准备
WITH t_log_in_info AS (
SELECT * FROM (
  VALUES      (1001, '172.100.10.13', '2023-12-04 11:00:42') , (1003, '172.100.10.13', '2023-12-05 21:01:06')
            , (1002, '172.100.10.12', '2023-12-05 21:01:06') , (1004, '172.100.10.12', '2023-12-05 21:01:06')
            , (1003, '172.100.10.11', '2023-12-05 19:01:52') , (1002, '172.100.10.11', '2023-12-05 19:01:52')
            , (1004, '172.100.10.12', '2023-12-06 09:01:36') , (1003, '172.100.10.12', '2023-12-06 09:01:36')
            , (1004, '172.100.10.11', '2023-12-06 18:01:48') , (1002, '172.100.10.02', '2023-12-06 18:01:48')
            , (1003, '172.100.10.03', '2023-12-07 08:01:43') , (1004, '172.100.10.02', '2023-12-07 08:01:43')
            , (1001, '172.100.10.02', '2023-12-07 08:02:01') , (1002, '172.100.10.03', '2023-12-07 08:02:01')
            , (1005, '172.100.10.03', '2023-12-08 20:01:39') , (1003, '172.100.10.02', '2023-12-08 20:01:39')
            , (1001, '172.100.10.12', '2023-12-08 08:30:36') , (1001, '172.100.10.12', '2023-12-08 08:30:36')
) AS table_name(uuid, ip, log_in_time)
)

2- 代码实现

-- 1. 取出所有用户使用的所有ip, 去重
, t_uuid_ip AS (
SELECT 
    uuid, ip
FROM t_log_in_info
GROUP BY uuid, ip  -- 同一个用户在不同时间相同ip登录
)

uuid	ip
1001	172.100.10.02
1001	172.100.10.12
1001	172.100.10.13
1002	172.100.10.02
1002	172.100.10.03
1002	172.100.10.11
1002	172.100.10.12
1003	172.100.10.02
1003	172.100.10.03
…	…

-- 2. 使用相同ip的用户对应关系
SELECT 
    a.uuid AS a_uuid, a.ip, b.uuid AS b_uuid
FROM t_uuid_ip a 
INNER JOIN t_uuid_ip b 
ON a.ip = b.ip
WHERE a.uuid < b.uuid  -- 排除 1对1, 1对2 2对1 的情况
;

a_uuid	ip	b_uuid
1001	172.100.10.02	1002
1001	172.100.10.02	1003
1001	172.100.10.02	1004
1001	172.100.10.12	1002
1001	172.100.10.12	1003
1001	172.100.10.12	1004
1001	172.100.10.13	1003
1002	172.100.10.02	1003
1002	172.100.10.02	1004
…	…	…

--3. 计算所有用户之间, 共同使用的ip 个数, 取出不小于3个的用户组
SELECT 
      a_uuid, b_uuid, COUNT(ip) AS ip_ct
FROM (
    SELECT 
        a.uuid AS a_uuid, a.ip, b.uuid AS b_uuid
    FROM t_uuid_ip a 
    INNER JOIN t_uuid_ip b 
    ON a.ip = b.ip
    WHERE a.uuid < b.uuid  -- 排除 1对1, 1对2和2对1 的情况
)
GROUP BY a_uuid, b_uuid 
HAVING ip_ct >= 3 ;

a_uuid	b_uuid	ip_ct
1001	1003	3
1002	1003	4
1002	1004	3
1003	1004	3

end