使用 SQL Server 2012 的 Last_Value() 函数时忽略空值 [英] Ignore null values when using SQL Server 2012's Last_Value() function

问题描述

我正在使用 SQL Server 2012 并且有一个类似这样的值表.它填充了事件数据.

I am using SQL Server 2012 and have a table of values that look like this. It is populated with event data.

FldType Date                    Price   Size
--------------------------------------------
2       2012-08-22 00:02:01     9140    1048
0       2012-08-22 00:02:02     9140    77
1       2012-08-22 00:02:03     9150    281
2       2012-08-22 00:02:04     9140    1090
0       2012-08-22 00:02:05     9150    1
1       2012-08-22 00:02:06     9150    324
2       2012-08-22 00:02:07     9140    1063

我想跟踪 3 种字段类型 (0,1,2) 中每一种的最新值，以便最终输出看起来像这样.

I would like to track the lastest value for each of the 3 field types (0,1,2) so that the final output looks like this.

Date                Price0  Size0   Price1  Size1   Price2  Size2
-----------------------------------------------------------------
2012-08-22 00:02:01  NULL   NULL    NULL    NULL    9140    1048
2012-08-22 00:02:02  9140   77      NULL    NULL    9140    1048
2012-08-22 00:02:03  9140   77      9150    281     9140    1048
2012-08-22 00:02:04  9140   77      9150    281     9140    1090
2012-08-22 00:02:05  9150   1       9150    281     9140    1090
2012-08-22 00:02:06  9150   1       9150    324     9140    1090
2012-08-22 00:02:07  9150   1       9150    324     9140    1063

不幸的是，它没有忽略后续的空值，所以我得到了这个.

Unfortunately, it is not ignoring subsequent null values so I get this instead.

Date                Price0  Size0   Price1  Size1   Price2  Size2
-----------------------------------------------------------------
2012-08-22 00:02:01  NULL   NULL    NULL    NULL    9140    1048
2012-08-22 00:02:02  9140   77      NULL    NULL    NULL    NULL    
2012-08-22 00:02:03  NULL   NULL    9150    281     NULL    NULL
2012-08-22 00:02:04  NULL   NULL    NULL    NULL    9140    1090
2012-08-22 00:02:05  9150   1       NULL    NULL    NULL    NULL    
2012-08-22 00:02:06  NULL   NULL    9150    324     NULL    NULL    
2012-08-22 00:02:07  NULL   NULL    NULL    NULL    9140    1063

我当前的查询看起来像这样

My current query looks like this

SELECT [Date],
    LAST_VALUE(Price0) OVER (PARTITION BY FldType ORDER BY [Date] ) AS Price0,
    LAST_VALUE(Size0) OVER (PARTITION BY FldType ORDER BY [Date]) AS Size0,
    LAST_VALUE(Price1) OVER (PARTITION BY FldType ORDER BY [Date] ) AS Price1,
    LAST_VALUE(Size1) OVER (PARTITION BY FldType ORDER BY [Date]) AS Size1,
    LAST_VALUE(Price2) OVER (PARTITION BY FldType ORDER BY [Date] ) AS Price2,
    LAST_VALUE(Size2) OVER (PARTITION BY FldType ORDER BY [Date]) AS Size2
FROM ( 
SELECT FldType, [Date], Price, Size,
    CASE WHEN FldType = 0 THEN Price END as Price0,
    CASE WHEN FldType = 0 THEN Size END as Size0,
    CASE WHEN FldType = 1 THEN Price END as Price1,
    CASE WHEN FldType = 1 THEN Size END as Size1,
    CASE WHEN FldType = 2 THEN Price END as Price2,
    CASE WHEN FldType = 2 THEN Size END as Size2
FROM [RawData].[dbo].[Events]   
) as T1
ORDER BY [Date]

在确定最新值时，是否有某种方法可以让 SQL Server 2012 忽略空值?或者有没有更好的方法不使用 Last_Value() 函数?

Is there some way to have SQL Server 2012 ignore null values when determining the lastest value? Or is there a better approach not using Last_Value() function?

总而言之，我试图实现两件事.

To summarize I am trying to achieve two thing.

1. 将 Price 和 Size 列拆分为 6 列(2 列 x 3 字段类型)
2. 跟踪每一列中的最新值.

1. Split the Price and Size columns into 6 columns (2 columns x 3 field types)
2. Keep track of the latest value in each of these columns.

任何建议都会被采纳.

推荐答案

我不确定您是否可以使用 LAST_VALUE 来实现，除非您可能添加 PIVOT.

I'm not sure you can do it with LAST_VALUE, unless you add a PIVOT maybe.

此外，您需要单独处理 Size 和 Price，因为它们来自不同的行.因此，这可以实现您想要的分解.

Also, you need to treat Size and Price separately because they come from different rows. So, this achieves what you want be breaking it down.

DECLARE @source TABLE (FldType int, DateCol DateTime, Price int, Size int);

INSERT @source VALUES
    (2, '2012-08-22 00:02:01', 9140, 1048),(0, '2012-08-22 00:02:02', 9140, 77),
    (1, '2012-08-22 00:02:03', 9150, 281),(2, '2012-08-22 00:02:04', 9140, 1090),
    (0, '2012-08-22 00:02:05', 9150, 1),(1, '2012-08-22 00:02:06', 9150, 324),
    (2, '2012-08-22 00:02:07', 9140, 1063);


SELECT
    S.DateCol, Xp0.Price0, Xs0.Size0, Xp1.Price1, Xs1.Size1, Xp2.Price2, Xs2.Size2
FROM
    @source S
    OUTER APPLY
    (SELECT TOP 1 S0.Price AS Price0 FROM @source S0 WHERE S0.FldType = 0 AND S0.DateCol <= S.DateCol ORDER BY S0.DateCol DESC) Xp0
    OUTER APPLY
    (SELECT TOP 1 S1.Price AS Price1 FROM @source S1 WHERE S1.FldType = 1 AND S1.DateCol <= S.DateCol ORDER BY S1.DateCol DESC) Xp1
    OUTER APPLY
    (SELECT TOP 1 S2.Price AS Price2 FROM @source S2 WHERE S2.FldType = 2 AND S2.DateCol <= S.DateCol ORDER BY S2.DateCol DESC) Xp2
    OUTER APPLY
    (SELECT TOP 1 S0.Size AS Size0 FROM @source S0 WHERE S0.FldType = 0 AND S0.DateCol <= S.DateCol ORDER BY S0.DateCol DESC) Xs0
    OUTER APPLY
    (SELECT TOP 1 S1.Size AS Size1 FROM @source S1 WHERE S1.FldType = 1 AND S1.DateCol <= S.DateCol ORDER BY S1.DateCol DESC) Xs1
    OUTER APPLY
    (SELECT TOP 1 S2.Size AS Size2 FROM @source S2 WHERE S2.FldType = 2 AND S2.DateCol <= S.DateCol ORDER BY S2.DateCol DESC) Xs2
ORDER BY
    DateCol;

另一种方法是通过触发器或一些 ETL 维护一个单独的表，为您做总结.

The other way is to maintain a separate table via triggers or some ETL that does it the summary for you.

这篇关于使用 SQL Server 2012 的 Last_Value() 函数时忽略空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！