如何用SQL按最大活跃月数去重？并处理值相等的情况

给定如下包含重复ID的数据集：

id  | product id | actv mnths | 
123 | ClassA     |   11       |
123 | ClassC     |   1        |
456 | ClassC     |   7        |
456 | ClassA     |   5        |

我们需要保留每个ID下actv mnths（活跃月数）最大的记录，最终得到：

id   | product id | actv mnths | 
    123 | ClassA     |   11       |
    456 | ClassC     |   7        |

以下是几种常用的实现方式，以及活跃月数相等时的处理方案：

一、基础实现方法 #

1. 窗口函数法（推荐，支持大多数现代数据库） #

利用ROW_NUMBER()窗口函数给每个ID分组内的记录排序，取排名第一的记录：

SELECT id, `product id`, `actv mnths`
FROM (
    SELECT 
        id, 
        `product id`, 
        `actv mnths`,
        -- 按ID分组，活跃月数降序排序，给每条记录编序号
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY `actv mnths` DESC) AS rn
    FROM your_table_name
) t
WHERE rn = 1;

替换your_table_name为你的实际表名即可。

2. 子查询关联法（兼容旧版数据库） #

如果你的数据库不支持窗口函数，可以先通过子查询找出每个ID的最大活跃月数，再关联原表筛选对应记录：

SELECT t1.id, t1.`product id`, t1.`actv mnths`
FROM your_table_name t1
INNER JOIN (
    -- 先分组获取每个ID的最大活跃月数
    SELECT id, MAX(`actv mnths`) AS max_actv
    FROM your_table_name
    GROUP BY id
) t2 
ON t1.id = t2.id AND t1.`actv mnths` = t2.max_actv;

二、活跃月数相等时的处理方案 #

当同一个ID下有多条记录的actv mnths相等且都是最大值时，可根据需求选择不同处理方式：

1. 只保留一条（指定规则） #

用ROW_NUMBER()时，会给并列最大值的记录分配不同序号，仅返回一条。如果需要指定保留哪条（比如按产品ID排序），可以修改排序条件：

ROW_NUMBER() OVER (
    PARTITION BY id 
    ORDER BY `actv mnths` DESC, `product id` ASC  -- 活跃月数相同时，选产品ID更小的
) AS rn

2. 保留所有并列最大值的记录 #

改用RANK()或DENSE_RANK()窗口函数，它们会给并列的最大值记录分配相同的序号，从而保留所有符合条件的记录：

SELECT id, `product id`, `actv mnths`
FROM (
    SELECT 
        id, 
        `product id`, 
        `actv mnths`,
        RANK() OVER (PARTITION BY id ORDER BY `actv mnths` DESC) AS rn
    FROM your_table_name
) t
WHERE rn = 1;

3. 随机保留一条 #

如果不需要固定规则，想随机选一条并列记录，可以结合数据库的随机函数，比如MySQL用RAND()，PostgreSQL用RANDOM()：

-- MySQL示例
ROW_NUMBER() OVER (
    PARTITION BY id 
    ORDER BY `actv mnths` DESC, RAND()
) AS rn

内容的提问来源于stack exchange，提问作者Maths12