问题描述
假设我有这张表:
+----+-------+ | 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