PostgreSQL中筛选同手机号且同邮箱非空记录的方案咨询

核心需求：从百万级数据表中，找出同一手机号分组内，邮箱重复且非空的所有记录（例如5个同手机号用户里，2个共用同一非空邮箱，需提取这2条记录）。针对你之前SQL的逻辑错误、性能问题及兼容性问题，给出以下可行方案：

方案一：先定位重复组合再关联原表（高效兼容） #

SELECT u.*
FROM "user" u
JOIN (
    -- 先找出所有「同手机号下重复的非空邮箱」组合
    SELECT mobile_phone, email
    FROM "user"
    WHERE email IS NOT NULL
    GROUP BY mobile_phone, email
    HAVING COUNT(*) > 1
) dup 
ON u.mobile_phone = dup.mobile_phone AND u.email = dup.email;

优势： #

• 逻辑清晰：子查询仅扫描一次表，筛选出符合条件的(mobile_phone, email)组合，再关联原表获取完整记录
• 兼容性：PostgreSQL 13及以上版本均支持，无版本兼容问题
• 性能可控：配合索引可大幅提速

方案二：窗口函数单扫描实现（最优性能） #

SELECT id, mobile_phone, last_name, email
FROM (
    SELECT 
        *,
        -- 计算每条记录所在「手机号+邮箱」分组的总条数
        COUNT(*) OVER (PARTITION BY mobile_phone, email) AS email_count
    FROM "user"
    WHERE email IS NOT NULL
) t
-- 筛选分组条数>1的记录（即重复的邮箱）
WHERE email_count > 1;

优势： #

• 性能最优：仅需一次全表扫描（或索引扫描），避免多次关联查询
• 代码简洁：无需额外关联子查询，逻辑直观
• 兼容性：PostgreSQL 10及以上就支持窗口函数，完全覆盖你提到的13.7/14.7版本

关键优化建议 #

1. 添加复合索引：针对查询字段创建索引，可让百万级数据查询速度提升数倍

CREATE INDEX idx_user_mobile_email ON "user" (mobile_phone, email);

2. 避免保留字冲突：user是PostgreSQL的保留字，表名需用双引号"user"包裹，否则可能在低版本出现语法错误
3. 修正之前的SQL逻辑问题：
   • 你最初的GROUP BY包含了id（主键），每个分组仅一条记录，COUNT(*) > 1永远不成立，无法得到结果
   • 关联子查询的写法会导致每条记录触发一次子查询，百万级数据下会产生大量重复扫描，性能极差

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