使用rank()与CTE的SQL查询返回记录条数差异咨询

两个查询选用了不同逻辑的排名窗口函数，本身就不是等价写法，返回行数不同是符合函数预期的正常现象，和子查询/CTE的写法、外层字段选择无关。 #

两个窗口函数的行为差异 #

• 第一个查询用的是rank()
  排名规则：分区内排序字段值相等的行会拿到相同排名，后续排名会直接跳过被占用的序号。
  举个实际场景：同一个acc_num分区下有3条记录，load_date分别为2024-01-01、2024-01-01、2024-01-02，按load_date升序排名时，前两条记录的rk值都是1，第三条的rk值直接为3。此时过滤rk=1会返回前2条记录，单个账号可能对应多条结果。
• 第二个查询用的是row_number()
  排名规则：分区内每一行都会拿到唯一的连续递增序号，从1开始依次排布，和排序字段值是否重复无关。
  还是上面的3条记录样例，即使前两条load_date完全相同，排序后也只会有1条记录拿到rn=1，剩下两条依次拿到rn=2、rn=3。此时过滤rn=1只会返回1条记录，单个账号最多对应1条结果。

行数差异触发条件 #

只要rat_code=123的数据集里，存在任意一个acc_num分区下有多条记录的load_date同为分区内最小值，第一个查询的返回行数就会大于第二个，差值就是这些并列排名为1的记录总数。

补充说明：第二个查询CTE阶段用select *取全量表字段、外层仅返回4个字段的写法，只会影响最终返回的字段数量，不会改变返回行数，不是造成差异的原因。

逻辑对齐方案 #

根据实际要实现的业务逻辑二选一调整即可：

• 如果要实现「每个账号仅返回1条最早日期的记录」，把第一个查询里的rank()替换为row_number()
• 如果要实现「每个账号返回所有符合最早日期的记录」，把第二个查询里的row_number()替换为rank()

原查询1代码 #

select trans.* from
  ( select 
   acc_num,
   acc_type,
   trans_amount,
   load_date,
   rank() over(partition by acc_num order by load_date) as rk
   from monetary
   where rat_code = 123
 ) trans
where trans.rk =1;

原查询2代码 #

with a as (
select *, 
row_number() over(partition by acc_num order by load_date) as rn
from monetary
where rat_code = 123 )
select 
acc_num,
acc_type,
trans_amount,
load_date
from a
where rn =1;

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