求助：Snowflake中row_number函数在单层CTE与直接查询返回异常

问题场景 #

我有一个数据集，同一个人在一个月内可能有多条记录，希望仅保留每组的首条数据，因此使用了row_number()函数。但遇到了异常情况：

• 在单层查询或单层CTE中，某个人/月份组明明仅包含两行数据，row_number()返回的序号却为4和5，rank()函数结果也相同。
• 但改用两层CTE（第二层仅查询第一层的全部数据）时，窗口函数的序号恢复正常，从1开始计数。

异常代码示例（单层查询） #

select 
    person_id, date,
    year(date) as year, month(date) as month,
    row_number() over(partition by person_id, month order by date)
from 
    data
where 
    (person_id, date, extract(year from date), 
     extract(month from date)) not in (select person_id, min_date, 
                                              extract(year from min_date), 
                                              extract(month from min_date) from first_date)

异常代码示例（单层CTE） #

with cte_1 as (
    select person_id, date,
    year(date) as year, month(date) as month
    from data
    where 
        (
        person_id, 
        date, 
        extract(year from date), 
        extract(month from date)
        ) not in (
            select person_id, 
            min_date, 
            extract(year from min_date), 
            extract(month from min_date) from first_date
        )
)
select *, row_number() over(partition by person_id, month order by date)
from cte_1

正常代码示例（两层CTE） #

with cte_1 as (
    select person_id, date,
    year(date) as year, month(date) as month
    from data
    where 
        (
        person_id, 
        date, 
        extract(year from date), 
        extract(month from date)
        ) not in (
            select person_id, 
            min_date, 
            extract(year from min_date), 
            extract(month from min_date) from first_date
        )
)
,cte_2 as (
select *
from cte_1
)

select *, row_number() over(partition by person_id, month order by date)
from cte_2

原因分析 #

这是数据库查询优化器执行计划异常导致的，核心问题是窗口函数的计算时机提前：

• 在单层查询或单层CTE的写法中，优化器可能做出了错误的逻辑推导，将窗口函数的计算步骤提前到WHERE条件过滤之前。也就是说，先给全量数据（包括后续会被WHERE排除的行）计算row_number序号，之后再过滤掉不符合条件的行，剩下的行就保留了原本的序号，因此出现了4、5这类非起始的序号。
• 两层CTE的写法相当于强制数据库对第一层CTE的结果进行物化（Materialize），也就是先执行完第一层的WHERE过滤，生成一个干净的临时数据集，之后再基于这个物化后的数据集计算窗口函数。此时窗口函数是针对过滤后的正确分组计算，序号自然从1开始。

额外建议 #

1. 简化WHERE条件：不需要同时传递date的年、月信息，因为date本身已经包含完整的时间维度，仅保留(person_id, date)即可，减少冗余计算的同时，也能降低优化器误判的概率：

-- 简化后的WHERE条件
where (person_id, date) not in (select person_id, min_date from first_date)

2. 查看执行计划：可以对比两种写法的执行计划，单层写法的窗口函数算子会出现在过滤算子之前，而两层CTE的执行计划中会先出现物化节点，再执行窗口函数，这能直观验证上述分析。

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