sql - T-SQL : Best way to replace NULL with most recent non-null value?

Question

假设我有这张表:

+----+-------+
| id | value |
+----+-------+
|  1 |     5 |
|  2 |     4 |
|  3 |     1 |
|  4 |  NULL |
|  5 |  NULL |
|  6 |    14 |
|  7 |  NULL |
|  8 |     0 |
|  9 |     3 |
| 10 |  NULL |
+----+-------+

我想编写一个查询，将用表中该列中非空的最后一个值替换任何 NULL 值。

我想要这样的结果:

+----+-------+
| id | value |
+----+-------+
|  1 |     5 |
|  2 |     4 |
|  3 |     1 |
|  4 |     1 |
|  5 |     1 |
|  6 |    14 |
|  7 |    14 |
|  8 |     0 |
|  9 |     3 |
| 10 |     3 |
+----+-------+

如果以前的值不存在，那么 NULL 是可以的。理想情况下，这应该能够与 ORDER BY 一起工作。例如，如果我 ORDER BY [id] DESC:

+----+-------+
| id | value |
+----+-------+
| 10 |  NULL |
|  9 |     3 |
|  8 |     0 |
|  7 |     0 |
|  6 |    14 |
|  5 |    14 |
|  4 |    14 |
|  3 |     1 |
|  2 |     4 |
|  1 |     5 |
+----+-------+

或者如果我 ORDER BY [value] DESC 会更好:

+----+-------+
| id | value |
+----+-------+
|  6 |    14 |
|  1 |     5 |
|  2 |     4 |
|  9 |     3 |
|  3 |     1 |
|  8 |     0 |
|  4 |     0 |
|  5 |     0 |
|  7 |     0 |
| 10 |     0 |
+----+-------+

我认为这可能涉及某种分析函数——以某种方式对值列进行分区——但我不确定去哪里查看。

Answer 1

您可以使用运行总和来设置组并使用最大值来填充空值。

select id,max(value) over(partition by grp) as value
from (select id,value,sum(case when value is not null then 1 else 0 end) over(order by id) as grp
      from tbl
     ) t

将 over() 子句更改为 order by value desc 以获得问题中的第二个结果。