在结果集系列中用最新的非NULL值替换NULL值（SQL Server 2008 R2）

Question

针对 SQL Server 2008 R2

我有一个结果集，长这样（注意 [price] 是数值型，在下方的 NULL 表示空值，结果集是按照 product_id 和时间戳排序的）

product timestamp          price 
------- ----------------   -----
   5678 2008-01-01 12:00   12.34
   5678 2008-01-01 12:01    NULL
   5678 2008-01-01 12:02    NULL
   5678 2008-01-01 12:03   23.45
   5678 2008-01-01 12:04    NULL

我希望将其转换为结果集，该结果集（基本上）从最近的前一行中复制非空值，以生成类似于此的结果集：

product timestamp          price  
------- ----------------   -----
   5678 2008-01-01 12:00   12.34
   5678 2008-01-01 12:01   12.34
   5678 2008-01-01 12:02   12.34
   5678 2008-01-01 12:03   23.45
   5678 2008-01-01 12:04   23.45

我没有找到任何聚合/窗口函数可以让我做到这一点（再次强调，这仅适用于 SQL Server 2008 R2）。

我希望能找到一个分析型的聚合函数来帮助我完成这个功能，类似于...

LAST_VALUE(price) OVER (PARTITION BY product_id ORDER BY timestamp)

但我似乎找不到任何方法来执行“累加最新非空值”窗口函数（将窗口限定在前一行而不是整个分区）

除了创建一个表值用户定义函数外，是否有任何内置函数可以实现此功能？

---

更新:

显然，此功能在'Denali' CTP中可用，但不适用于SQL Server 2008 R2。

LAST_VALUE http://msdn.microsoft.com/en-us/library/hh231517%28v=SQL.110%29.aspx

我只是希望它在SQL Server 2008中可用。它在Oracle（至少自10gR2以来）可用，并且我可以在MySQL 5.1中使用本地变量进行类似的操作。

http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/functions083.htm

- spencer7593

---

你指的是哪个在Denali中可用但在2008 R2中不可用的函数？ - marc_s

Answer 1

你可以尝试以下方法：

* 已更新 **

-- Test Data
DECLARE @YourTable TABLE(Product INT, Timestamp DATETIME, Price NUMERIC(16,4))

INSERT INTO @YourTable
SELECT 5678, '20080101 12:00:00', 12.34
UNION ALL
SELECT 5678, '20080101 12:01:00', NULL
UNION ALL
SELECT 5678, '20080101 12:02:00', NULL
UNION ALL
SELECT 5678, '20080101 12:03:00', 23.45
UNION ALL
SELECT 5678, '20080101 12:04:00', NULL

;WITH CTE AS
(
    SELECT *
    FROM @YourTable
)

-- Query
SELECT A.Product, A.Timestamp, ISNULL(A.Price,B.Price) Price
FROM CTE A
OUTER APPLY (   SELECT TOP 1 *
                FROM CTE 
                WHERE Product = A.Product AND Timestamp < A.Timestamp
                AND Price IS NOT NULL
                ORDER BY Product, Timestamp DESC) B

--Results
Product Timestamp   Price
5678    2008-01-01 12:00:00.000 12.3400
5678    2008-01-01 12:01:00.000 12.3400
5678    2008-01-01 12:02:00.000 12.3400
5678    2008-01-01 12:03:00.000 23.4500
5678    2008-01-01 12:04:00.000 23.4500

- Lamak

---

那个APPLY关键字对我来说是新的。我会尝试一下。谢谢！ - spencer7593

@spencer7593 - 此外，您可以在此链接上找到有关“APPLY”运算符的一些文档：http://technet.microsoft.com/zh-cn/library/ms175156.aspx - Lamak

行源比 @YourTable 更复杂... 我宁愿只指定一次行源。是否可以使用公共表达式重写此代码 WITH cte AS (SELECT * FROM @YourTable) SELECT ...？ - spencer7593

@spencer7593 - 好的，我已更改查询以使用CTE。 - Lamak

结果看起来很有前途，似乎对我有效！ - spencer7593

Answer 2

我可以帮助您翻译以下内容：

我有一个包含以下数据的表格。我想要将薪资列中的所有空值更新为前一个值，而不考虑空值。

表格：

id  name    salary
1   A       4000
2   B   
3   C   
4   C   
5   D       2000
6   E   
7   E   
8   F       1000
9   G       2000
10  G       3000
11  G       5000
12  G   

这是适用于我的查询。

select a.*,first_value(a.salary)over(partition by a.value order by a.id) as abc from
(
     select *,sum(case when salary is null then 0 else 1 end)over(order by id) as value from test)a

输出：

id  name    salary  Value   abc
1   A       4000    1     4000
2   B               1     4000
3   C               1     4000
4   C               1     4000
5   D       2000    2     2000
6   E               2     2000
7   E               2     2000
8   F       1000    3     1000
9   G       2000    4     2000
10  G       3000    5     3000
11  G       5000    6     5000
12  G               6     5000

- suriruler

Answer 3

试试这个：

;WITH SortedData AS
(
    SELECT
       ProductID, TimeStamp, Price,
       ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY TimeStamp DESC) AS 'RowNum'
    FROM dbo.YourTable
)
UPDATE SortedData
SET Price = (SELECT TOP 1 Price 
             FROM SortedData sd2
         WHERE sd2.RowNum > SortedData.RowNum 
           AND sd2.Price IS NOT NULL)
WHERE
    SortedData.Price IS NULL

基本上，CTE会创建一个按时间戳（降序）排序的列表-最新的排在前面。每当找到一个NULL值时，将找到包含NOT NULL价格的下一行，并使用该值来更新包含NULL价格的行。

- marc_s