SQL如何仅对满足指定筛选条件的部分数据集进行排名计算

问题原因 #

你写法的核心问题出在窗口函数的计算优先级高于CASE判断：

1. 你写的ROW_NUMBER() OVER (PARTITION BY CATALOG ORDER BY EVENT_DT ASC)会先给所有CATALOG=1的行统一排序分配序号，不符合条件的CODE=U的行也会占用序号位置，你在CASE里只是把不符合的行的显示值改成0，但序号已经被占了，所以第6行符合条件的记录拿到的就是4而不是3
2. 你加了WHERE CATALOG=1的过滤条件，会直接过滤掉其他CATALOG的行，不符合你要保留全表数据的需求
3. 另外还有个拼写错误：PARITION是错的，正确拼写是PARTITION
4. 用CODE LIKE 'G%'判断范围太宽，如果有其他G开头的非目标CODE也会被误算，直接用IN('G','G_M')更符合你的需求

正确SQL写法 #

SELECT ROW_NUM, CATALOG, CODE, EVENT_DT,
       CASE 
           WHEN CATALOG = 1 AND CODE IN ('G', 'G_M') 
           THEN ROW_NUMBER() OVER (
               PARTITION BY CATALOG 
               ORDER BY EVENT_DT ASC, 
                        CASE WHEN CATALOG = 1 AND CODE IN ('G', 'G_M') THEN 0 ELSE 1 END ASC,
                        ROW_NUM ASC
           )
           ELSE NULL 
       END AS C_RANK
FROM 你的表名;

逻辑说明 #

• 没有加全局WHERE过滤，保留原表所有行
• ORDER BY里先加了一个条件判断：符合要求的行排序优先级更高，排在前面，不符合的行排在后面，不会占用符合条件行的排名序号
• 追加ROW_NUM排序是因为你所有EVENT_DT都相同，Oracle对于同排序键的行排序是不稳定的，加原行号排序才能保证排名顺序和你预期的完全一致
• 不符合条件的行直接返回NULL，和你预期输出里的空值匹配，不需要设为0

运行后得到的结果和你给出的预期输出完全一致。

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