过滤多个IP的重复数据的统计场景

最近有个统计数据的需求，数据如下所示，

id	ip	name	insert_time1	1.1.1.0	a	2025-01-01 00:01:002	1.1.1.1	b	2025-01-01 00:01:013	1.1.1.0	a	2025-01-01 00:00:01

相同name的ip可能有多条，需要提取最新insert_time的记录，

id	ip	name	insert_time1	1.1.1.0	a	2025-01-01 00:01:002	1.1.1.1	b	2025-01-01 00:01:01

创建测试数据，

CREATE TABLE user_log (    id INT PRIMARY KEY AUTO_INCREMENT,    ip VARCHAR(15),    name VARCHAR(50),    insert_time DATETIME);
INSERT INTO user_log (ip, name, insert_time) VALUES('1.1.1.0', 'a', '2025-01-01 00:01:00'),('1.1.1.1', 'b', '2025-01-01 00:01:01'),('1.1.1.0', 'a', '2025-01-01 00:00:01');
select * from user_log;id|ip     |name|insert_time        |--|-------|----|-------------------| 1|1.1.1.0|a   |2025-01-01 00:01:00| 2|1.1.1.1|b   |2025-01-01 00:01:01| 3|1.1.1.0|a   |2025-01-01 00:00:01|

MySQL数据库可以有几种方案，

方案1：使用窗口函数（推荐，MySQL 8.0+）

SELECT id, ip, name, insert_timeFROM (    SELECT *,           ROW_NUMBER() OVER (PARTITION BY name ORDER BY insert_time DESC) AS rn    FROM user_log) tWHERE rn = 1ORDER BY id;

方案2：使用JOIN和子查询

方案3：使用相关子查询

SELECT *FROM user_log t1WHERE insert_time = (    SELECT MAX(insert_time)    FROM user_log t2    WHERE t1.name = t2.name)ORDER BY id;

方案4：使用LEFT JOIN自连接

SELECT t1.*FROM user_log t1LEFT JOIN user_log t2     ON t1.name = t2.name     AND t1.insert_time < t2.insert_timeWHERE t2.id IS NULLORDER BY t1.id;

以上SQL都可以得到如下的数据，

id|ip     |name|insert_time        |--|-------|----|-------------------| 1|1.1.1.0|a   |2025-01-01 00:01:00| 2|1.1.1.1|b   |2025-01-01 00:01:01|

如果数据量少，可能几个方案都可以采用，但如果数据量多，可能要选择合适的逻辑、创建合适的索引，如果单从效率看，窗口函数相对是最优的，因为它只需要访问一次表，其它的几个方案，都需访问两次，但窗口函数只能用到MySQL 8.0以上的，所以很多低版本的数据库，不能用到这特性，因此还需要根据索引字段的应用，来选择合适方案，没有最优的，只有最合适的。