T-SQL:用最新的非空值替换 NULL 的最佳方法? [英] T-SQL: Best way to replace NULL with most recent non-null value?

问题描述

假设我有这张表:

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

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

I want to write a query that will replace any NULL value with the last value in the table that was not null in that column.

我想要这个结果:

+----+-------+
| 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:

If no previous value existed, then NULL is OK. Ideally, this should be able to work even with an ORDER BY. So for example, if I 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 |
+----+-------+

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

I think this might involve some kind of analytic function - somehow partitioning over the value column - but I'm not sure where to look.

推荐答案

Itzik Ben-Gan 在此处介绍了最佳方法:最后一个非空谜题

The best way has been covered by Itzik Ben-Gan here:The Last non NULL Puzzle

下面是一个在我的系统上处理 1000 万行并在 20 秒内完成的解决方案

Below is a solution which for 10 million rows and completes around in 20 seconds on my system

SELECT
  id,
  value1,
  CAST(
  SUBSTRING(
  MAX(CAST(id AS binary(4)) + CAST(value1 AS binary(4)))
  OVER (ORDER BY id
  ROWS UNBOUNDED PRECEDING),
  5, 4)
  AS int) AS lastval
FROM dbo.T1;

此解决方案假定您的 id 列已编入索引

This solution assumes your id column is indexed

这篇关于T-SQL:用最新的非空值替换 NULL 的最佳方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！