T-SQL:用最新的非空值替换 NULL 的最佳方法?
假设我有这张表:
+----+-------+
| 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
相关文章