用结果集中的系列中最新的非NULL值替换NULL值

用结果集中的系列中最新的非NULL值替换NULL值

本文介绍了用结果集中的系列中最新的非NULL值替换NULL值(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 aNULL 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.

我只是希望它在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.

推荐答案

您可以尝试以下操作:

*更新了**

-- 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)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-10 22:10