PostgreSQL查询多邀请邮件用户并按邀请量排序展示

需求说明 #

现有invitation表存储用户邮件邀请记录，一个用户可关联多个邮箱，每个邮箱可接收多份邀请。需实现：

• 筛选至少有一个邮箱收到多份邀请的用户
• 展示这些用户的所有邮箱及对应邀请数
• 排序规则：
  1. 按用户的单个邮箱最大邀请量降序排列
  2. 同一用户内，按邮箱邀请数降序排列
• 排除所有邮箱均仅收到1份邀请的用户

表结构 #

create table invitation (
    user_id INT         NOT NULL,
    email   VARCHAR(32) NOT NULL
 );

更新后测试数据 #

insert into invitation (user_id, email)
values
 (33, 'c1@gmail.com'),
 (33, 'c2@gmail.com'),
 (33, 'c3@gmail.com'),
 (11, 'a1@gmail.com'),
 (11, 'a1@gmail.com'),
 (11, 'a2@gmail.com'),
 (2, 'b1@gmail.com'),
 (2, 'b1@gmail.com'),
 (11, 'a1@gmail.com'),
 (11, 'a2@gmail.com'),
 (11, 'a3@gmail.com'),
 (2, 'b1@gmail.com'),
 (11, 'a1@gmail.com'),
 (2, 'b2@gmail.com');

原尝试SQL的问题 #

原SQL仅过滤了单个邮箱邀请数>1的记录，既不符合“保留用户所有邮箱”的要求，也未按用户的最大邮箱邀请量排序，会导致不同用户的邮箱混排：

select user_id, email, count(email) as counter
from invitation
group by email, user_id
having count(email) > 1
ORDER BY counter DESC

正确SQL实现 #

通过CTE分两步处理：先统计每个用户-邮箱的邀请数，再筛选符合条件的用户并获取其最大邮箱邀请量，最后关联排序：

WITH email_stats AS (
    -- 统计每个用户每个邮箱的邀请数
    SELECT 
        user_id,
        email,
        COUNT(*) AS counter
    FROM invitation
    GROUP BY user_id, email
),
user_qualified AS (
    -- 筛选出至少有一个邮箱邀请数>1的用户，并记录其最大邮箱邀请量
    SELECT 
        user_id,
        MAX(counter) AS max_email_counter
    FROM email_stats
    GROUP BY user_id
    HAVING MAX(counter) > 1
)
SELECT 
    es.user_id,
    es.email,
    es.counter
FROM email_stats es
JOIN user_qualified uq ON es.user_id = uq.user_id
ORDER BY 
    uq.max_email_counter DESC, -- 按用户的最大邮箱邀请量降序
    es.counter DESC;           -- 同用户内按邮箱邀请数降序

执行结果说明 #

执行后将得到符合需求的结果：

1. 用户11的最大邮箱邀请量为4（a1@gmail.com），排在首位，其下邮箱按邀请数降序排列（4→2→1）
2. 用户2的最大邮箱邀请量为3（b1@gmail.com），排在第二位，其下邮箱按邀请数降序排列（3→1）
3. 用户33因所有邮箱仅1份邀请，被排除在结果外

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