sql-server - 使用 T-SQL 在时间序列数据中前向填充空值的有效方法

Question

我有一个表，其中包含大部分为空值的时间序列数据，我想用最后一个已知值填充所有空值。

我有一些解决方案，但它们比在 Pandas 中执行等效的 DataFrame.fillna(method='ffill') 操作慢得多。

我正在使用的代码/数据的简化版本:

select d.[date], d.[price],
       (select top 1 p.price from price_table p
        where p.price is not null and p.[date] <= p.[date]
        order by p.[date] desc) as ff_price
from price_table d

生成表格

date       price ff_price
---------- ----- --------
2016-07-11 0.79  0.79
2016-07-12 NULL  0.79
2016-07-13 NULL  0.79
2016-07-14 0.69  0.69
2016-07-15 NULL  0.69
...
2016-09-21 0.88  0.88
...

我有超过 1 亿行，因此这需要相当长的时间。

Answer 1

这看起来像是一个“经典”的间隙和岛屿问题。 假设您没有使用 2008 年或更早版本(它们(几乎)完全不受支持)，这应该会为您带来您想要的结果:

WITH CTE AS(
    SELECT [date],
           price,
           COUNT(CASE WHEN price IS NOT NULL THEN 1 END) OVER (ORDER BY [date]
                                                               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Grp
    FROM price_table p)
SELECT [date],
       price,
       MIN(price) OVER (PARTITION BY grp) AS ff_price
FROM CTE;

db<>fiddle