在ROW_NUMBER函数中处理NULL值，实现特定排名逻辑

背景与原始数据 #

现有表TABLEA（含ID、DATE、Amount列），筛选部分行插入TABLEB（含ID1、DATE1、Amount1列）。将TABLEA与TABLEB执行LEFT JOIN后得到如下数据：

ID      Date        AMOUNT    ID1      Date1        AMOUNT1    
  ---------------------------------------------------------------
  1       2022-10-17    400       NULL     NULL         NULL     
  1       2022-10-18    300       1       2022-10-18    300       
  2       2022-10-19    432       2       2022-10-19    432       
  2       2022-10-20    100       NULL    NULL          NULL 
  2       2022-10-21    200       NULL    NULL          NULL        
  2       2022-10-22    234       2       2022-10-21    234        
  3       2022-10-22    213       3       2022-10-22    213        
  3       2022-10-22    112       3       2022-10-23    112       
  3       2022-10-22    218       3       NULL          NULL       
  4       2022-10-23    654       4       2022-10-18    654         
  5       2022-10-28    500       NULL    NULL          NULL        

期望结果 #

需要按ID分组、Date排序，为NULL值匹配对应非NULL值的排名，最终结果如下：

ID      Date        AMOUNT    ID1      Date1        AMOUNT1    Row Number
  ----------------------------------------------------------------------------
  1       2022-10-17    400       NULL     NULL         NULL        1
  1       2022-10-18    300       1       2022-10-18    300         1
  2       2022-10-19    432       2       2022-10-19    432         1
  2       2022-10-20    100       NULL    NULL          NULL        1
  2       2022-10-21    200       NULL    NULL          NULL        1
  2       2022-10-22    234       2       2022-10-21    234         2
  3       2022-10-22    213       3       2022-10-22    213         1
  3       2022-10-22    112       3       2022-10-23    112         2
  3       2022-10-22    218       3       NULL          NULL        2
  4       2022-10-23    654       4       2022-10-18    654         1
  5       2022-10-28    500       NULL    NULL          NULL        1

尝试过的SQL #

曾尝试使用row_number/dense_rank/rank函数，但未得到正确结果，尝试的SQL如下：

Select a.ID , a.date , a.amount , b.id1, b.date1, b.amount1 from TableA a
, row_number() over (partition by b.id1 order by case when b.date1 is not null then b.date1 end) ROW Number 
left join TableB b
on a.id = b.id 

具体排名逻辑 #

• ID=1的两行，其中一行ID1/Date1/Amount1为NULL，两行Row Number均为1；
• ID=2的四行，第一行非NULL为1，第二、三行NULL也为1，第四行非NULL为2；
• ID=3的三行，第一行非NULL为1，第二行非NULL为2，第三行NULL为2；
• ID=4、5各一行，Row Number均为1；
• 同一ID下，Date相同但ID1等非NULL的行，需分配不同Row Number。

测试用DDL与DML #

Create table Test
(
ID int,
Date date,
Amount int,
ID1 int,
Date1 date,
Amount1 int
)

Insert into Test (ID, Date, Amount, ID1, Date1, Amount1) values 
(1 ,'2022-10-17',400,NULL,NULL ,NULL ) ,    
(1 ,'2022-10-18',300, 1 ,'2022-10-18', 300 ),       
(2 ,'2022-10-19',432, 2 ,'2022-10-19', 432 ),  
(2 ,'2022-10-20',100,NULL, NULL        ,NULL), 
(2 ,'2022-10-21',200,NULL, NULL        ,NULL),        
(2 ,'2022-10-22',234,2   , '2022-10-21',234 ),       
(3 ,'2022-10-22',213,3   , '2022-10-22',213 ),       
(3 ,'2022-10-22',112,3   , '2022-10-23',112 ),      
(3 ,'2022-10-22',218,3   , NULL        ,NULL),       
(4 ,'2022-10-23',654,4   , '2022-10-18',654 ),        
(5 ,'2022-10-28',500,NULL, NULL        ,NULL)    

解决方案 #

实现思路 #

1. 先为每个ID下的非NULL行（ID1/Date1/Amount1非NULL），按Date1排序生成临时排名；
2. 再通过窗口函数MAX() OVER()，按ID分组、Date排序，取到当前行为止的最大临时排名；
3. 若最大临时排名为NULL（即当前行及之前无有效非NULL行），则默认取1。

完整SQL #

WITH ranked_data AS (
    SELECT 
        *,
        -- 仅为非NULL行生成按Date1排序的排名
        CASE WHEN ID1 IS NOT NULL THEN 
            ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date1) 
        END AS temp_rank
    FROM Test
)
SELECT 
    ID, Date, Amount, ID1, Date1, Amount1,
    -- 填充NULL行的排名，无有效排名时默认取1
    COALESCE(MAX(temp_rank) OVER (PARTITION BY ID ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 1) AS "Row Number"
FROM ranked_data
ORDER BY ID, Date;

验证结果 #

执行上述SQL后，将完全匹配期望的Row Number结果，满足所有排名逻辑要求。

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