SQL两种写法下RANK值异常及排序问题咨询

第一种SQL写法及结果 #

select 
    TRANSACTION_DT,
    SUBSCRIPTION_ID,
    KEY_ID,
    EMAIL,
    PURCHASE_PRODUCT_ID, 
    rank () over (partition by SUBSCRIPTION_ID,KEY_ID order by TRANSACTION_DT desc) as rnk 
from "DC"."BW_BOOKINGS"
where email='abc@gmail.com'

执行结果：

修改后的SQL写法及结果 #

select * from (
    select 
        TRANSACTION_DT,
        SUBSCRIPTION_ID,
        KEY_ID, 
        EMAIL,
        PURCHASE_PRODUCT_ID, 
        rank () over (partition by SUBSCRIPTION_ID,KEY_ID order by TRANSACTION_DT desc) as rnk 
    from "DC"."BW_BOOKINGS"
) t
where email='abc@gmail.com'

执行结果：

问题解答 #

1. 两种写法RNK值差异的原因 #

第一种写法是先过滤出目标用户的数据，再基于这些数据计算rank，此时分区（SUBSCRIPTION_ID,KEY_ID）里只有该用户的4条记录，rank是针对这4条数据排序生成的，所以最新的两条记录RNK为1。

第二种写法是先对全表所有数据计算rank，再过滤目标用户的数据。这意味着计算rank时，分区里包含了该用户之外、同SUBSCRIPTION_ID和KEY_ID的其他记录——这些记录可能有更早/晚的时间，或者数量更多，直接把目标用户的记录rank挤到了3、5的位置。

如果要筛选RNK=1的结果，正确写法是把用户过滤条件放到子查询里，先过滤再计算rank，再在外层筛选RNK=1：

select * from (
    select 
        TRANSACTION_DT,
        SUBSCRIPTION_ID,
        KEY_ID, 
        EMAIL,
        PURCHASE_PRODUCT_ID, 
        rank () over (partition by SUBSCRIPTION_ID,KEY_ID order by TRANSACTION_DT desc) as rnk 
    from "DC"."BW_BOOKINGS"
    where email='abc@gmail.com'
) t
where rnk=1

2. PURCHASE_PRODUCT_ID顺序变化的原因 #

SQL查询在没有指定ORDER BY子句时，返回的行顺序是不确定的。数据库会根据执行计划的最优路径（比如索引、存储顺序、并行处理逻辑等）返回数据，两种写法的执行计划不同，导致行顺序出现差异，这是正常现象。如果需要固定排序结果，必须在查询末尾加上明确的ORDER BY，比如：

select * from (
    select 
        TRANSACTION_DT,
        SUBSCRIPTION_ID,
        KEY_ID, 
        EMAIL,
        PURCHASE_PRODUCT_ID, 
        rank () over (partition by SUBSCRIPTION_ID,KEY_ID order by TRANSACTION_DT desc) as rnk 
    from "DC"."BW_BOOKINGS"
    where email='abc@gmail.com'
) t
where rnk=1
order by TRANSACTION_DT desc, PURCHASE_PRODUCT_ID

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