如何从多列中检索"last"非零值？

Question

考虑一个有 11 列的有序表：
timespan, val1, val2, val3, val4, val5, val6, val7, val8, val9 and val10

假设一组记录如下：

timespan     val1  val2  val3  val4  val5  val6  val7  val8  val9  val10
10/09/2011      0     0    60    80    40     0     0    40    80      0
10/10/2011      0    10    90    30    70    50    50    70    30     90
10/11/2011     10     0    20     0     0    60    60     0     0     20

我需要一个SQL查询（对于SQL Server 2012），它返回所有列的最后一个（及时）非零值，val1，val2，...，也就是说，

val1  val2  val3  val4  val5  val6  val7  val8  val9  val10
  10    10    20    30    70    60    60    70    30     20

类似的问题可以在Subquery中找到：如何从列中检索最后一个非零值？ 但它仅适用于一列，并且包含更多列（如本例所示）的泛化似乎不切实际。

Answer 1

您可以使用

first_value()：

select distinct first_value(val1) over (order by sign(val1) desc, timespan desc) as val1,
       first_value(val2) over (order by sign(val2) desc, timespan desc) as val2,
       . . .
from t;

通常，我反对使用 select distinct 作为聚合查询的替代品。 遗憾的是，SQL Server 支持将first_value()作为窗口函数，但不提供等效的聚合。

注意：sign()函数用于将零值放在最后。 如果可以有负值，请使用 abs(sign()) 。

Answer 2

另一种选择是快速取消透视，然后是透视

例

Select *
 From  (
           Select top 1 with ties item,value
            From  YourTable
            UnPivot ( Value for Item in (val1,val2,val3,val4,val5,val6,val7,val8,val9,val10) ) u
            Where value<>0
            Order by Row_Number() over (Partition By item Order by timespan desc)
       ) src
 Pivot (max(value) For item in (val1,val2,val3,val4,val5,val6,val7,val8,val9,val10) ) p

返回

val1    val2    val3    val4    val5    val6    val7    val8    val9    val10
10      10      20      30      70      60      60      70      30      20

Answer 3

您可以使用如下所示的内容。1.逻辑是首先取消透视值，然后删除0个条目，然后将最后一个非零值计算为row_num = 1。

2. 然后再次旋转以获得结果。

查询如下

create table t
(timespan date,val1 int,val2 int,val3 int,val4 int,val5 int,val6 int,val7 int,val8 int,val9 int,val10 int);
insert into t values
('10/09/2011', 0, 0,60,80,40, 0, 0,40,80, 0)
,('10/10/2011', 0,10,90,30,70,50,50,70,30,90)
,('10/11/2011',10, 0,20, 0, 0,60,60, 0, 0,20);
select * 
    from
(
    select 
        value, Columns 
    from
    (
        select 
            timespan,
            value, 
            Columns, 
            row_number() over(partition by Columns order by timespan desc) r
        from
        (select * from t)s
        unpivot
        ( 
            value for Columns in 
                ([val1],[val2],[val3],[val4],[val5],[val6],[val7],[val8],[val9],[val10])
        )up
        where value<>0
    ) t 
    where r=1
 )s
pivot
(
    max(value) for Columns in 
        ([val1],[val2],[val3],[val4],[val5],[val6],[val7],[val8],[val9],[val10])
)p

查看工作演示