为多列找到第一个非空值

问题描述：

我试图在一组多列中获取第一个非空值。我知道我可以使用每列的子查询来完成此操作。以表演的名义，在这种情况下确实算得上，我想一次性做到这一点。为多列找到第一个非空值

看看下面的示例数据：

col1  col2  col3  sortCol 
==================================== 
NULL  4  8  1 
1  NULL  0  2 
5  7  NULL  3

我的梦想查询会发现在每一个数据列的第一个非空值，排序在。

例如，选择前三列的神奇聚合物时，按降序排序。

col1  col2  col3 
======================== 
5  7   0

或排序时上升：

col1  col2  col3 
======================== 
1  4   8

有谁知道一个解决方案吗？

你需要的第一个非空列，或者第一个非空行？ – feihtthief 2010-01-11 22:09:13

你是否只需要第一排，或者你是否需要整套？ sortCol是独一无二的吗？ – feihtthief 2010-01-11 22:27:02

@feihtthief：每列中的第一个非空值。我认为示例输出应该很好地显示所需的效果。 @Mark Byers：由于我没有一个解决方案可以一次性工作，我只能猜测它的性能，但子查询方法还有很多不足之处。在我的实际表格中，我需要以这种方式卷起大约20行。与子查询方法，指标并不是特别有用。我相信单扫描方法有很多列可能会快得多。 – EvilRyry 2010-01-11 22:32:48

答

在拒绝它之前，您是否已经实际测试过此解决方案？

SELECT 
    (SELECT TOP(1) col1 FROM Table1 WHERE col1 IS NOT NULL ORDER BY SortCol) AS col1, 
    (SELECT TOP(1) col2 FROM Table1 WHERE col2 IS NOT NULL ORDER BY SortCol) AS col2, 
    (SELECT TOP(1) col3 FROM Table1 WHERE col3 IS NOT NULL ORDER BY SortCol) AS col3

如果这很慢，可能是因为您没有合适的索引。你有什么指数？

现在在SortCol上有一个索引正在被有效地使用。经过一番调查，我重建了大幅帮助过的指数。这个解决方案现在大概在10ms左右，这可能够用了。我想我可以通过添加一些其他列来消除更多的时间，以消除目前占总时间2/3的RID查找。 – EvilRyry 2010-01-12 15:01:14

答

不完美，但它可以在单个查询中完成。尽管这可能会使任何索引都变得毫无用处，如前所述，多重子查询方法可能会更快。


create table Foo (data1 tinyint, data2 tinyint, data3 tinyint, seq int not null) 
go 

insert into Foo (data1, data2, data3, seq) 
values (NULL, 4, 8, 1), (1, NULL, 0, 2), (5, 7, NULL, 3) 
go 

with unpivoted as (
    select seq, value, col 
    from (select seq, data1, data2, data3 from Foo) a 
    unpivot (value FOR col IN (data1, data2, data3)) b 
), firstSeq as (
    select min(seq) as seq, col 
    from unpivoted 
    group by col 
), data as (
    select b.col, b.value 
    from firstSeq a 
    inner join unpivoted b on a.seq = b.seq and a.col = b.col 
) 
select * from data pivot (min(value) for col in (data1, data2, data3)) d 
go 

drop table Foo 
go

答

与实施本为聚合（你如果，例如，您实施了“第一非空” SQL CLR聚合确实可以做）的问题是浪费的IO读取每一行，当你通常只对前几行感兴趣。第一个非null即使其实现忽略更多值，聚合也不会停止。聚合也是无序的，所以你的结果将取决于查询引擎选择的索引的顺序。

相比之下，子查询解决方案为每个查询读取最小行（因为您只需要第一个匹配的行）并支持任何排序。它也适用于无法定义自定义聚合的数据库平台。

哪一个性能更好可能取决于表格中行和列的数量以及数据的稀疏程度。其他行需要为聚合方法读取更多行。其他列需要额外的子查询。稀疏数据需要检查每个子查询中的更多行。

下面是一些结果关于各种表的大小：

Rows Cols Aggregation IO CPU Subquery IO CPU 
3  3     2 0    6 0 
1728 3     8 63   6 0 
1728 8     12 266   16 0

的IO这里测量的是逻辑读取数。请注意，子查询方法的逻辑读取次数不会随着表中的行数而改变。另请注意，每个附加子查询执行的逻辑读取可能会针对相同的数据页（包含前几行）。另一方面，聚合必须处理整个表并且需要一些CPU时间来完成。

这是我用于测试的代码...SortCol上的聚集索引是必需的，因为（在这种情况下）它将确定聚合的顺序。

定义表和插入测试数据：

CREATE TABLE Table1 (Col1 int null, Col2 int null, Col3 int null, SortCol int); 
CREATE CLUSTERED INDEX IX_Table1 ON Table1 (SortCol); 

WITH R (i) AS 
(
SELECT null 

UNION ALL 

SELECT 0 

UNION ALL 

SELECT i + 1 
FROM R 
WHERE i < 10 
) 
INSERT INTO Table1 
SELECT a.i, b.i, c.i, ROW_NUMBER() OVER (ORDER BY NEWID()) 
FROM R a, R b, R c;

查询表：

SET STATISTICS IO ON; 

--aggregation 
SELECT TOP(0) * FROM Table1 --shortcut to convert columns back to their types 
UNION ALL 
SELECT 
dbo.FirstNonNull(Col1), 
dbo.FirstNonNull(Col2), 
dbo.FirstNonNull(Col3), 
null 
FROM Table1; 


--subquery 
SELECT 
    (SELECT TOP(1) Col1 FROM Table1 WHERE Col1 IS NOT NULL ORDER BY SortCol) AS Col1, 
    (SELECT TOP(1) Col2 FROM Table1 WHERE Col2 IS NOT NULL ORDER BY SortCol) AS Col2, 
    (SELECT TOP(1) Col3 FROM Table1 WHERE Col3 IS NOT NULL ORDER BY SortCol) AS Col3;

的CLR “第一非空” 聚集体进行测试：

[Serializable] 
[SqlUserDefinedAggregate(
    Format.UserDefined, 
    IsNullIfEmpty = true, 
    IsInvariantToNulls = true, 
    IsInvariantToDuplicates = true, 
    IsInvariantToOrder = false, 
#if(SQL90) 
    MaxByteSize = 8000 
#else 
    MaxByteSize = -1 
#endif 
)] 
public sealed class FirstNonNull : IBinarySerialize 
{ 
    private SqlBinary Value; 

    public void Init() 
    { 
    Value = SqlBinary.Null; 
    } 

    public void Accumulate(SqlBinary next) 
    { 
    if (Value.IsNull && !next.IsNull) 
    { 
    Value = next; 
    } 
    } 

    public void Merge(FirstNonNull other) 
    { 
    Accumulate(other.Value); 
    } 

    public SqlBinary Terminate() 
    { 
    return Value; 
    } 

    #region IBinarySerialize Members 

    public void Read(BinaryReader r) 
    { 
    int Length = r.ReadInt32(); 

    if (Length < 0) 
    { 
    Value = SqlBinary.Null; 
    } 
    else 
    { 
    byte[] Buffer = new byte[Length]; 
    r.Read(Buffer, 0, Length); 

    Value = new SqlBinary(Buffer); 
    } 
    } 

    public void Write(BinaryWriter w) 
    { 
    if (Value.IsNull) 
    { 
    w.Write(-1); 
    } 
    else 
    { 
    w.Write(Value.Length); 
    w.Write(Value.Value); 
    } 
    } 

    #endregion 
}

答

这是另一种方法。如果你的数据库在子查询中不允许top（N）（比如我的Teradata），这将是最有用的。

为了便于比较，这里的解决方案的其他人提及的，使用top(1)：

select top(1) Col1 
from Table1 
where Col1 is not null 
order by SortCol asc

在一个理想的世界，这似乎对我来说，最好的方式做到这一点 - 干净，直观，高效的（显然）。

或者你可以这样做：

select max(Col1) -- max() guarantees a unique result 
from Table1 
where SortCol in (
    select min(SortCol) 
    from Table1 
    where Col1 is not null 
)

这两种解决方案检索沿着有序列“第一”的纪录。 Top(1)确实更优雅，可能更有效。第二种方法在概念上做同样的事情，只是从代码角度来看更多的手动/显式实现。

根选择max()的原因在于，如果值min(SortCol)在Table1的多行中出现，您可以获得多个结果。顺便提一下，我不确定Top(1)如何处理这种情况。

为多列找到第一个非空值

相关推荐