使用row_number() OVER (partition BY时阈值达标排序结果不符合预期问题

你当前的SQL逻辑存在核心问题：row_number()是在整个product_name, userid分区内计算所有行的行号，之后仅通过case将未达标行的结果替换为0，但达标行仍然保留了全局行号（比如第三行是整个分区的第3行，所以显示3），这就导致达标后的序号没有从1开始递增。

要实现「首次达标行显示1，后续达标行依次递增，未达标行显示0」的需求，需要先标记出达标阶段的行，再对这些行单独计算序号。以下提供两种简洁的实现方式：

方式一：累计标记+分区行号 #

先通过窗口函数标记是否进入达标阶段，再对达标阶段的行单独计数：

select 
    date, 
    cost_amt, 
    cumulative_cost,
    case 
        when has_reached = 1 then 
            row_number() over(partition by product_name, userid, has_reached order by date, order_id) 
        else 0 
    end as threshold_reached_order,
    product_id
from (
    select 
        date, 
        cost_amt, 
        cumulative_cost,
        product_name,
        userid,
        product_id,
        -- 标记当前行及之前是否已达标，一旦达标后续行均为1
        max(case when cumulative_cost >= threshold then 1 else 0 end) 
            over(partition by product_name, userid order by date, order_id rows unbounded preceding) as has_reached
    from table1
    where userid = 'ABCDEFG'
) t
order by userid, product_id;

方式二：直接累计达标行数 #

由于cumulative_cost是累加值，一旦达标后续行必然满足cumulative_cost >= threshold，因此可以直接累计达标行的数量：

select 
    date, 
    cost_amt, 
    cumulative_cost,
    case 
        when cumulative_cost >= threshold then 
            sum(case when cumulative_cost >= threshold then 1 else 0 end) 
                over(partition by product_name, userid order by date, order_id)
        else 0 
    end as threshold_reached_order,
    product_id
from table1
where userid = 'ABCDEFG'
order by userid, product_id;

执行上述任意方案后，结果将符合你的预期：

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