问题描述
对于 SQL Server 2008 R2
for SQL Server 2008 R2
我有一个看起来像这样的结果集(注意 [price] 是数字，下面的 NULL 表示NULL值，结果集按product_id和timestamp排序)
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/en-us/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)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！