如何在大表的有序列中獲取最後一個非空值？

我有以下輸入：

id | value 
----+-------
 1 |   136
 2 |  NULL
 3 |   650
 4 |  NULL
 5 |  NULL
 6 |  NULL
 7 |   954
 8 |  NULL
 9 |   104
10 |  NULL

我期待以下結果：

id | value 
----+-------
 1 |   136
 2 |   136
 3 |   650
 4 |   650
 5 |   650
 6 |   650
 7 |   954
 8 |   954
 9 |   104
10 |   104

簡單的解決方案是將表與<關係連接起來，然後選擇 a 中的MAX值GROUP BY：

WITH tmp AS (
 SELECT t2.id, MAX(t1.id) AS lastKnownId
 FROM t t1, t t2
 WHERE
   t1.value IS NOT NULL
   AND
   t2.id >= t1.id
 GROUP BY t2.id
)
SELECT
 tmp.id, t.value
FROM t, tmp
WHERE t.id = tmp.lastKnownId;

但是，此程式碼的簡單執行將在內部創建輸入表行數的平方（O(n^2)）。我希望 t-sql 對其進行優化 - 在塊/記錄級別上，要做的任務非常簡單且線性，本質上是一個 for 循環（O(n)）。

但是，在我的實驗中，最新的 MS SQL 2016 無法正確優化此查詢，導致無法針對大型輸入表執行此查詢。

此外，查詢必須快速執行，使得類似簡單（但非常不同）的基於游標的解決方案不可行。

使用一些記憶體支持的臨時表可能是一個很好的折衷方案，但我不確定它是否可以更快地執行，考慮到我使用子查詢的範例查詢不起作用。

我也在考慮從 t-sql 文件中探勘出一些視窗函式，什麼可以被欺騙來做我想做的事情。例如，累積總和做了一些非常相似的事情，但我無法欺騙它給出最新的非空元素，而不是之前元素的總和。

理想的解決方案是無需過程程式碼或臨時表的快速查詢。或者，使用臨時表的解決方案也可以，但程序上迭代表不是。

Itzik Ben-Gan 在他的文章The Last non NULL Puzzle中給出了此類問題的常見解決方案：

DROP TABLE IF EXISTS dbo.Example;

CREATE TABLE dbo.Example
(
   id integer PRIMARY KEY,
   val integer NULL
);

INSERT dbo.Example
   (id, val)
VALUES
   (1, 136),
   (2, NULL),
   (3, 650),
   (4, NULL),
   (5, NULL),
   (6, NULL),
   (7, 954),
   (8, NULL),
   (9, 104),
   (10, NULL);

SELECT
   E.id,
   E.val,
   lastval =
       CAST(
           SUBSTRING(
               MAX(CAST(E.id AS binary(4)) + CAST(E.val AS binary(4))) OVER (
                   ORDER BY E.id
                   ROWS UNBOUNDED PRECEDING),
           5, 4)
       AS integer)
FROM dbo.Example AS E
ORDER BY
   E.id;

展示：db<>fiddle

引用自：https://dba.stackexchange.com/questions/233610