代码之家 › 专栏 › 技术社区 › Ryan Bair

查找多个列的第一个非空值

sql-server sql

Ryan Bair · 技术社区 · 15 年前

我正在尝试获取一组列中的第一个非空值。我知道我可以使用每列的子查询来完成这项工作。以性能的名义,这在本场景中确实很重要,我想在一次传递中完成。

以以下数据为例:

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

我的梦想查询将在每个数据列中找到第一个非空值,按 sortCol .

例如,当选择前三列的神奇聚合时,按 索特科尔 下降的。

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

或升序排序时:

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

有人知道解决办法吗?

4 回复 | 直到 11 年前

Mark Byers 15 年前

在拒绝之前,你真的测试过这个解决方案的性能吗?

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

如果速度很慢,可能是因为没有合适的索引。你有什么索引?

Michael Petito 15 年前

将其实现为聚合(例如,如果实现了“第一个非空”的sql clr聚合,则确实可以这样做)的问题是,当您通常只对前几行感兴趣时,浪费了读取每一行的io。聚合不会在第一个非空值之后停止,即使它的实现会忽略更多的值。聚合也是无序的,因此结果将取决于查询引擎选择的索引的顺序。

相比之下,子查询解决方案读取每个查询的最小行(因为您只需要第一个匹配的行)并支持任何排序。它还可以在数据库平台上工作,在这些平台上无法定义自定义聚合。

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

以下是各种表格大小的一些结果:

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
 }

Chris Chilvers 15 年前

不是很优雅,但它可以在一个查询中完成。尽管这可能会使任何索引变得毫无用处,因此如前所述,多个子查询方法可能更快。


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

william_grisaitis 11 年前

这是另一种方法。如果数据库不允许子查询(如mine、teradata)中的top(n),那么这将是最有用的。

作为比较,下面是其他人提到的解决方案,使用 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
)

两个解决方案都沿着有序列检索“first”记录。 Top(1) 它确实更优雅,也许更有效。第二个方法在概念上也做了同样的事情,只是从代码的角度来看有更多的手动/显式实现。

原因是 max() 在根select中,如果 min(SortCol) 出现在 Table1 . 我不知道怎么做 顶部(1) 顺便说一下,处理这个场景。