06 | 使用共同IP地址问题

---

一、问题定义与业务场景

目标：识别共同使用过3个及以上相同IP地址的用户对，用于检测潜在的黑产团伙或账号共享行为。

核心需求：

1. 找出共享至少3个相同IP地址的用户对
2. 统计共享的IP数量
3. 列出共享的具体IP地址

业务场景：

• 金融风控：识别养号团伙
• 社交平台：检测账号买卖行为
• 游戏反作弊：打击工作室多开
• 企业安全：监控异常账号关联

---

二、数据准备与建表语句

2.1 建表语句

-- 用户登录日志表
CREATE TABLE user_login_logs (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id VARCHAR(20) NOT NULL COMMENT '用户ID',
    ip_address VARCHAR(15) NOT NULL COMMENT 'IP地址',
    login_time DATETIME NOT NULL COMMENT '登录时间',
    INDEX idx_user (user_id),
    INDEX idx_ip (ip_address),
    INDEX idx_time (login_time)
) COMMENT '用户登录日志表';

2.2 样例数据

-- 插入登录日志
INSERT INTO user_login_logs (user_id, ip_address, login_time) VALUES
-- 用户U001与U002共享3个IP
('U001', '192.168.1.100', '2025-06-01 09:00:00'),
('U001', '192.168.1.101', '2025-06-01 10:00:00'),
('U001', '192.168.1.102', '2025-06-01 11:00:00'),
('U001', '192.168.1.103', '2025-06-01 12:00:00'),

('U002', '192.168.1.100', '2025-06-01 09:05:00'),
('U002', '192.168.1.101', '2025-06-01 10:05:00'),
('U002', '192.168.1.102', '2025-06-01 11:05:00'),
('U002', '192.168.1.104', '2025-06-01 11:15:00'),

-- 用户U001与U003共享2个IP（不足3个）
('U003', '192.168.1.100', '2025-06-01 13:00:00'),
('U003', '192.168.1.103', '2025-06-01 14:00:00'),

-- 用户U002与U003共享1个IP
('U003', '192.168.1.104', '2025-06-01 15:00:00'),

-- 用户U004与U005共享3个IP
('U004', '10.0.0.1', '2025-06-01 16:00:00'),
('U005', '10.0.0.1', '2025-06-01 16:30:00'),
('U004', '10.0.0.2', '2025-06-01 17:00:00'),
('U005', '10.0.0.2', '2025-06-01 17:30:00'),
('U004', '10.0.0.3', '2025-06-01 18:00:00'),
('U005', '10.0.0.3', '2025-06-01 18:30:00');

2.3 数据关系说明

用户对	共享IP	共享数量	是否满足条件
U001-U002	192.168.1.100,192.168.1.101,192.168.1.102	3	✅
U004-U005	10.0.0.1,10.0.0.2,10.0.0.3	3	✅
U001-U003	192.168.1.100,192.168.1.103	2	❌
U002-U003	192.168.1.104	1	❌

---

三、SQL解决方案

3.1 分析思路

1）首先保证每个用户同一个IP只有一条记录，否则关联会导致结果数据重复。将所有用户登录记录按照用户ID和登录IP去重。

SELECT
  user_id,
  ip_address 
FROM
  user_login_logs 
GROUP BY
  user_id,
  ip_address;

2）使用IP进行关联，找到使用相同IP的记录。另外，自关联会导致同一个用户使用相同IP的记录重复，这些记录需要去重和剔除。

WITH tmp AS ( 
  SELECT
    user_id,
    ip_address 
  FROM
    user_login_logs 
  GROUP BY
    user_id,
    ip_address
) 
SELECT
t1.user_id,
t2.user_id,
t1.ip_address 
FROM
  tmp AS t1
  JOIN tmp AS t2 ON t1.ip_address = t2.ip_address 
WHERE
  t1.user_id < t2.user_id

3）根据用户组计算使用共同IP的个数

WITH tmp AS ( 
  SELECT
    user_id,
    ip_address 
  FROM
    user_login_logs 
  GROUP BY
    user_id,
    ip_address
) 
SELECT
t1.user_id,
t2.user_id,
count(t1.ip_address)
FROM
  tmp AS t1
  JOIN tmp AS t2 ON t1.ip_address = t2.ip_address 
WHERE
  t1.user_id < t2.user_id
 group by 
	t1.user_id,
	t2.user_id

4）查询共同使用过3个以上IP的用户对

WITH tmp AS ( 
  SELECT
    user_id,
    ip_address 
  FROM
    user_login_logs 
  GROUP BY
    user_id,
    ip_address
) 
SELECT
t1.user_id,
t2.user_id,
count(t1.ip_address),
GROUP_CONCAT(t1.ip_address ORDER BY t1.ip_address) AS shared_ips
FROM
  tmp AS t1
  JOIN tmp AS t2 ON t1.ip_address = t2.ip_address 
WHERE
  t1.user_id < t2.user_id
 GROUP BY 
	t1.user_id,
	t2.user_id
 HAVING count(t1.ip_address) >= 3

3.2 查询结果