如何用GROUP BY+MIN替代RANK() OVER(PARTITION BY...)优化SQL查询性能？

问题根源 #

原窗口函数rank() over(partition by ap.person_id order by ap.working_date asc)的核心逻辑是每个person_id仅保留最早入职日期对应的单行记录，因此结果集为150万条（对应150万个唯一person_id）。改用GROUP BY后结果骤增至3400万，问题出在两处：

• GROUP BY字段冗余：你把start_date、first_name、last_name、email_address都加入了GROUP BY。同一个person_id可能存在多条记录（比如多次申请、信息更新），只要这些字段有差异，就会生成独立分组，导致行数暴增。
• 关联条件错误：join applicants_cte apps on apps.person_key = apps.person_key是无效过滤，相当于没筛选applicants_cte中的person_id，进一步扩大了结果范围。

修正方案 #

要实现“每个person_id仅取最早入职日期记录”，正确的做法是先单独计算每个person_id的最早入职日期，再关联回原表过滤对应行，而非直接将所有字段混入GROUP BY。

修正后的SQL代码 #

with applicants_cte as (
    select person_key
    from applicants.people
    where "date" between '01-01-2022' and '01-01-2023'
    group by person_key
),
person_earliest_working_date as (
    -- 单独计算每个person_id的最早入职日期
    select 
        ap.person_id,
        min(ap.working_date) as earliest_working_date
    from applicants.people ap
    join applicants_cte apps on ap.person_key = apps.person_key -- 修正关联条件
    group by ap.person_id
),
list_of_applicants as (
    select 
        ped.earliest_working_date,
        ap.person_id,
        date(ap.start_date) as start_date,
        a.first_name,
        a.last_name,
        b.email_address
    from applicants.people ap
    -- 关联过滤出最早日期的记录
    join person_earliest_working_date ped 
        on ap.person_id = ped.person_id 
        and ap.working_date = ped.earliest_working_date
    join information a on a.person_key = ap.person_key -- 修正原代码中ap_id_key的笔误
    join contact_information b on b.person_info_id = ap.id_key
    join applicants_cte apps on ap.person_key = apps.person_key
    where date(ap.start_date) IS NOT NULL
    -- 处理同一person_id在最早日期有多条记录的情况（按需选择）
    -- PostgreSQL 用distinct on
    distinct on (ap.person_id)
    -- MySQL 可改用row_number()窗口函数（仅保留一行）
    -- row_number() over(partition by ap.person_id order by ap.id_key) as rn
    -- 然后在外层加where rn = 1
)
select count(*) from list_of_applicants

关键说明 #

1. 修正关联条件：将无效的apps.person_key = apps.person_key改为ap.person_key = apps.person_key，确保只包含指定时间范围内的person_id。
2. 拆分计算逻辑：用person_earliest_working_date临时表单独计算每个person_id的最早入职日期，避免冗余字段干扰分组。
3. 精准过滤行：通过person_id和working_date的匹配，确保仅保留每个person_id最早入职日期的记录。
4. 去重处理：如果同一个person_id在最早日期当天有多条记录，根据数据库类型选择去重方式（示例中给出PostgreSQL和MySQL的两种方案）。

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