如何使LAG()在SQL Server中忽略NULL? [英] How to make LAG() ignore NULLS in SQL Server?

问题描述

有人知道如何用字符串替换列中的空值，直到命中新的字符串，然后该字符串替换其下的所有空值?我有一栏看起来像这样

Does anyone know how to replace nulls in a column with a string until it hits a new string then that string replaces all null values below it? I have a column that looks like this

原始列:

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

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

Essentially I want the first string in the column to replace all null values below it until the next string. Then that string will replace all nulls below it until the next string and so on.

推荐答案

对于诸如 lead()和 lag()，这个问题很合适.

SQL Server does not support the ignore nulls option for window functions such as lead() and lag(), for which this question was a nice fit.

我们可以通过一些空白和孤岛技术来解决此问题:

We can work around this with some gaps and island technique:

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

子查询的窗口总和每次发现非null值时都会递增:这定义了包含非null值后跟null值的行组.

The subquery does a window sum that increments everytime a non null value is found: this defines groups of rows that contain a non-null value followed by null values.

然后，外部使用窗口 max()检索每个组中的(唯一的)非null值.

Then, the outer uses a window max() to retrieve the (only) non-null value in each group.

这假定一列可以用于排序记录(我称其为 id ).

This assumes that a column can be used to order the records (I called it id).

数据库小提琴上的演示 :

Demo on DB Fiddle:

ID | PAST_DUE_COL            | grp | new_past_due_col       
-: | :---------------------- | --: | :----------------------
 1 | 91 or more days pastdue |   1 | 91 or more days pastdue
 2 | null                    |   1 | 91 or more days pastdue
 3 | null                    |   1 | 91 or more days pastdue
 4 | 61-90 days past due     |   2 | 61-90 days past due    
 5 | null                    |   2 | 61-90 days past due    
 6 | null                    |   2 | 61-90 days past due    
 7 | 31-60 days past due     |   3 | 31-60 days past due    
 8 | null                    |   3 | 31-60 days past due    
 9 | 0-30 days past due      |   4 | 0-30 days past due     
10 | null                    |   4 | 0-30 days past due     
11 | null                    |   4 | 0-30 days past due     
12 | null                    |   4 | 0-30 days past due     

这篇关于如何使LAG()在SQL Server中忽略NULL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！