使用SQL Rank函数查询成员最少保单异常：计数错误与排序问题

我有两张数据表：保单表（存储保单名称）与成员详情表。需要用Rank函数识别成员数量最少的保单，但执行SQL后结果异常——返回的成员计数远低于实际值（应为数千级，却显示个位数）。奇怪的是，将Rank函数按降序排序时，计数结果正确。希望解决该问题，实现通过rank=1筛选出成员最少的保单并显示正确计数。

原SQL代码：

select count (distinct member_id) as count_members,
policy_name,
rank () over (order by count(distinct member)) as rank
from member_table t1, policy_table t2
where t1.policy_id = t2.policy_id
and month_id = 202210
group by policy_name

错误结果示例：

问题根源 #

窗口函数排序时误用了字段：你在主查询中统计的是distinct member_id的数量，但窗口函数里写的是count(distinct member)，字段名少了_id。这导致窗口排序时统计的不是成员ID的去重数，而是member字段（比如成员姓名之类的非唯一字段）的去重数，所以结果远低于实际值。

所谓“降序时计数正确”只是错觉——降序只是改变了排序方向，但字段错误的问题依然存在，只是你没注意到统计值的错误而已。

修正后的SQL代码 #

select count(distinct member_id) as count_members,
       policy_name,
       rank() over (order by count(distinct member_id) asc) as rank
from member_table t1
join policy_table t2 on t1.policy_id = t2.policy_id
where month_id = 202210
group by policy_name

额外优化与筛选 #

1. 将老式逗号连接表的写法改为显式JOIN语法，提升代码可读性和维护性
2. 窗口函数中明确指定asc（升序），虽然默认就是升序，但显式写出更清晰，明确是按成员数从小到大排序

如果要直接筛选出成员最少的保单（rank=1），可以用子查询嵌套：

select count_members, policy_name, rank
from (
    select count(distinct member_id) as count_members,
           policy_name,
           rank() over (order by count(distinct member_id) asc) as rank
    from member_table t1
    join policy_table t2 on t1.policy_id = t2.policy_id
    where month_id = 202210
    group by policy_name
) ranked_policies
where rank = 1

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