用结果集中的系列中最新的非NULL值替换NULL值（SQL Server 2008 R2） [英] replace NULL values with latest non-NULL value in resultset series (SQL Server 2008 R2)

问题描述

对于SQL Server 2008 R2

for SQL Server 2008 R2

我有一个看起来像这样的结果集（请注意[price]是数字，下面的NULL表示
NULL值，结果集按product_id和时间戳排序）

I have a resultset that looks like this (note [price] is numeric, NULL below represents a NULL value, the result set is ordered by product_id and timestamp)

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

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

I want to transform that to a result set that (essentially) copies a non-null value from the latest preceding row, to produce a resultset that looks like this:

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所需。）

I don't find any aggregate/windowing function that will allow me to do this (again this ONLY needed for SQL Server 2008 R2.)

我希望找到一个可以为我完成此工作的分析聚合函数，例如...

I was hoping to find an analytic aggregate function that do this for me, something like...

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

但我似乎找不到任何内容在窗口中执行累计最新非空值的方法（将窗口绑定到前几行，而不是整个分区）

But I don't seem to find any way to do a "cumulative latest non-null value" in the window (to bound the window to the preceding rows, rather than the entire partition)

除了创建一个表值用户定义函数，是否有任何内置函数可以完成此操作？

Aside from creating a table-valued user defined function, is there any builtin that would accomplish this?

更新：

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

Apparently, this functionality is available in the 'Denali' CTP, but not in SQL Server 2008 R2.

LAST_VALUE http://msdn.microsoft。 com / zh-cn /library/hh231517%28v=SQL.110%29.aspx

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

I just expected it to be available in SQL Server 2008. It's available in Oracle (since 10gR2 at least), and I can do something similar in MySQL 5.1, using a local variable.

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

推荐答案

您可以尝试以下操作：

*更新了**

-- 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

这篇关于用结果集中的系列中最新的非NULL值替换NULL值（SQL Server 2008 R2）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！