PostgreSQL按年龄分组查询各分组Top10邮箱域名问题排查

问题根因 #

你编写的排名逻辑未按年龄组分区排名，而是对全量所有年龄组的域名统一排序，才会出现筛选排名<=10仅覆盖单个年龄组的错误。

修正后查询（两种业务场景可选） #

场景1：每组严格返回最多10条，并列域名随机取（总结果最多50行，符合预期输出要求） #

如果要求5个年龄组加起来最多50行，不需要保留并列排名，直接用row_number()并在窗口函数中添加PARTITION BY agegroup按年龄组分区：

with users as (
    select a.*, 
      extract(year from age(dob)) age,
      substr(email, position('@' in email)+1, 1000) domain
    from user_table a
   ),
   useragegroup as (
    select a.*,
     case when age between 0 and 18 then '0-18'
          when age between 19 and 29 then '19-29'
          when age between 30 and 49 then '30-49' 
          when age between 50 and 65 then '50-65'
          else '66-up'
     end agegroup
    from users a
   ),
   rank as (
     select agegroup, domain, count(*) as user_cnt,
       -- 核心修正：添加PARTITION BY agegroup实现按年龄组内独立排名
       row_number() over (partition by agegroup order by count(*) desc) r
     from useragegroup a
     group by agegroup, domain
   )
   select a.*
   from rank a
   where r<=10;

场景2：每组保留并列排名，最多10个名次（单组可能超过10条，总结果>50行） #

如果需要保留相同计数域名的并列排名，换用dense_rank()即可，此时同组排名第10的域名有多少个就返回多少个，不会截断：

with users as (
    select a.*, 
      extract(year from age(dob)) age,
      substr(email, position('@' in email)+1, 1000) domain
    from user_table a
   ),
   useragegroup as (
    select a.*,
     case when age between 0 and 18 then '0-18'
          when age between 19 and 29 then '19-29'
          when age between 30 and 49 then '30-49' 
          when age between 50 and 65 then '50-65'
          else '66-up'
     end agegroup
    from users a
   ),
   rank as (
     select agegroup, domain, count(*) as user_cnt,
       dense_rank() over (partition by agegroup order by count(*) desc) r
     from useragegroup a
     group by agegroup, domain
   )
   select a.*
   from rank a
   where r<=10;

测试数据注意事项 #

你提供的测试插入语句所有用户dob都固定为2005-01-01，计算年龄后只会落在单个年龄组，测试时需要调整dob生成逻辑，覆盖所有5个年龄组：

insert into user_table (
    first, last, email, dob
)
select
    left(md5(i::text), 3),
    left(md5(random()::text), 3),
    'user_' || i || '@' || (
    CASE (RANDOM() * 14)::INT
      WHEN 0 THEN 'gmail'
      WHEN 1 THEN 'hotmail'
      WHEN 2 THEN 'apple'
      WHEN 3 THEN 'icloud'
      WHEN 4 THEN 'aol'
      WHEN 5 THEN 'usa'
      WHEN 6 THEN 'govt'
      WHEN 7 THEN 'stackoverflow'
      WHEN 8 THEN 'random'
      WHEN 9 THEN 'domain'
      WHEN 10 THEN 'subby'
      WHEN 11 THEN 'youtube'
      WHEN 12 THEN 'google'
      WHEN 13 THEN 'triple'
      WHEN 14 THEN 'pixar'
    END
  ) || '.com' AS email,
    -- 随机生成1950-2005年的出生日期，覆盖所有年龄组
    ('1950-01-01'::date + (random() * 20000)::int) as dob
from generate_series(1, 500) s(i);

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