如何按ID分组筛选客户行：取高频值，EMP为最高则取次高

数据表定义与测试数据 #

CREATE TABLE tableName 
(
    id  INT,
    customer    VARCHAR(512),
    region  VARCHAR(512),
    cost    INT
);

INSERT INTO tableName (id, customer, region, cost) VALUES ('1', 'EMP', 'Europe', '80');
INSERT INTO tableName (id, customer, region, cost) VALUES ('1', 'y', 'North America', '80');
INSERT INTO tableName (id, customer, region, cost) VALUES ('1', 'y', 'North America', '60');
INSERT INTO tableName (id, customer, region, cost) VALUES ('1', 'z', 'South America', '90');
INSERT INTO tableName (id, customer, region, cost) VALUES ('2', 'z', 'Europe', '40');
INSERT INTO tableName (id, customer, region, cost) VALUES ('2', 'z', 'South America', '60');
INSERT INTO tableName (id, customer, region, cost) VALUES ('2', 'EMP', 'Middle East', '60');
INSERT INTO tableName (id, customer, region, cost) VALUES ('2', 'z', 'PACIFIC', '70');
INSERT INTO tableName (id, customer, region, cost) VALUES ('2', 'a', 'PACIFIC', '70');
INSERT INTO tableName (id, customer, region, cost) VALUES ('2', 'a', 'PACIFIC', '70');
INSERT INTO tableName (id, customer, region, cost) VALUES ('3', 'EMP', 'Carribean', '90');
INSERT INTO tableName (id, customer, region, cost) VALUES ('3', 'EMP', 'Middle East', '70');
INSERT INTO tableName (id, customer, region, cost) VALUES ('3', 'k', 'South America', '80');
INSERT INTO tableName (id, customer, region, cost) VALUES ('4', 'EMP', 'Africa', '80');
INSERT INTO tableName (id, customer, region, cost) VALUES ('4', 'EMP', 'Central America', '80');
INSERT INTO tableName (id, customer, region, cost) VALUES ('4', 'EMP', 'Africa', '70');

需求说明 #

customer字段取值包括x、y、z及EMP，需实现以下逻辑：

• 按id分组，获取组内出现次数最多的customer对应的所有行；
• 若组内出现次数最多的是EMP，则取出现次数第二多的非EMP customer对应的所有行；
• 若某id下所有行的customer都是EMP，则保留该id下的所有EMP行。

预期结果 #

• id=1：保留customer='y'的2行（y出现2次，EMP和z各1次）；
• id=2：保留customer='z'的3行（z出现3次，a出现2次，EMP出现1次）；
• id=3：保留customer='k'的1行（EMP出现2次，但k是唯一非EMP，取k）；
• id=4：保留所有3行EMP（全为EMP）。

遇到的问题 #

尝试分别筛选非EMP行取最高出现次数、筛选EMP行，但无法正确关联两者，出现GROUP BY错误。想知道如何移除包含非EMP行的id下的EMP行，仅保留那些全为EMP的id对应的EMP行。

尝试的SQL语句 #

-- 尝试单独筛选EMP行并分组
select * from tableName where customer= 'EMP' group by id order by count(customer) desc
-- 尝试单独筛选非EMP行并分组
select * from tableName where customer!= 'EMP' group by id order by count(customer) desc

-- 尝试关联两个结果集
select q1.* 
from (select * from tableName where customer !='EMP' 
      group by id order by count(customer) desc) q1 
      left outer join
      (select * from tableName where customer = 'EMP' group by id order by count(customer ) desc) q2 
      on q1.id  = q2.id 

union

select q1.* 
from (select * from tableName where customer ='EMP' 
      group by id order by count(customer) desc) q1 
      right outer join
      (select * from tableName where customer= 'EMP' group by id order by count(customer) desc) q2 
      on q1.id != q2.id;

解决方案 #

使用窗口函数可以高效实现需求，核心思路是先对每个id内的customer进行计数和优先级排序，再筛选出符合条件的行：

WITH customer_stats AS (
    SELECT 
        *,
        -- 统计当前id下当前customer的出现次数
        COUNT(*) OVER (PARTITION BY id, customer) AS cnt,
        -- 对当前id内的customer排序：非EMP按次数降序，EMP排最后；若无非EMP则EMP排第一
        ROW_NUMBER() OVER (
            PARTITION BY id 
            ORDER BY 
                CASE WHEN customer != 'EMP' THEN cnt ELSE 0 END DESC,
                customer != 'EMP' DESC
        ) AS rn
    FROM tableName
)
SELECT id, customer, region, cost
FROM customer_stats
WHERE rn = 1;

逻辑解释 #

1. customer_stats CTE：
   • cnt：计算每个id+customer组合的出现次数；
   • rn：对每个id内的customer进行排序：非EMP的按出现次数降序排列，EMP的优先级最低；如果id全是EMP，则EMP的排序为1。
2. 最终筛选：取每个id中排序为1的customer对应的所有行，即可满足需求。

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