sql - 如何让 LAG() 在 SQL Server 中忽略 NULLS？

Question

有谁知道如何用字符串替换列中的空值，直到它碰到一个新字符串然后该字符串替换它下面的所有空值？我有一个看起来像这样的列

原始专栏：

PAST_DUE_COL           
91 or more days pastdue        
Null
Null
61-90 days past due          
Null
Null
31-60 days past due
Null
0-30 days past due
Null       
Null
Null            

预期结果栏：

PAST_DUE_COL           
91 or more days past due        
91 or more days past due
91 or more days past due
61-90 days past due          
61-90 days past due 
61-90 days past due 
31-60 days past due
31-60 days past due
0-30 days past due
0-30 days past due      
0-30 days past due
0-30 days past due

本质上，我希望列中的第一个字符串替换它下面的所有空值，直到下一个字符串。然后该字符串将替换它下面的所有空值，直到下一个字符串，依此类推。

标签： sqlsql-serverwindow-functionsgaps-and-islandssql-null

Answer 1

SQL Server 不支持ignore nulls诸如lead()和之类的窗口函数选项lag()，这个问题非常适合。

我们可以通过一些差距和孤岛技术来解决这个问题：

select
    t.*,
    max(past_due_col) over(partition by grp) new_past_due_col
from (
    select 
        t.*,
        sum(case when past_due_col is null then 0 else 1 end)
            over(order by id) grp
    from mytable t
) t

子查询执行每次找到非空值时递增的窗口总和：这定义了包含非空值后跟空值的行组。

然后，外部使用一个窗口max()来检索每个组中的（唯一）非空值。

这假设可以使用一列对记录进行排序（我称之为id）。

DB Fiddle 上的演示：

身份证 | PAST_DUE_COL | grp | new_past_due_col       
-: | :------------------------ | --: | :------------------------
 1 | 逾期 91 天或更长时间 | 1 | 逾期 91 天或以上
 2 | 空                    | 1 | 逾期 91 天或以上
 3 | 空                    | 1 | 逾期 91 天或以上
 4 | 逾期 61-90 天 | 2 | 逾期 61-90 天    
 5 | 空                    | 2 | 逾期 61-90 天    
 6 | 空                    | 2 | 逾期 61-90 天    
 7 | 逾期 31-60 天 | 3 | 逾期 31-60 天    
 8 | 空                    | 3 | 逾期 31-60 天    
 9 | 逾期 0-30 天 | 4 | 逾期 0-30 天     
10 | 空                    | 4 | 逾期 0-30 天     
11 | 空                    | 4 | 逾期 0-30 天     
12 | 空                    | 4 | 逾期 0-30 天