SQL 查找多列的第一个非空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2045288/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find first non-null values for multiple columns
提问by Ryan Bair
I'm attempting to get the first non-null value in a set of many columns. I'm aware that I could accomplish this using a sub-query per column. In the name of performance, which really does count in this scenario, I'd like to do this in a single pass.
我试图在一组多列中获取第一个非空值。我知道我可以使用每列的子查询来完成此操作。以性能的名义，这在这种情况下确实很重要，我想一次性完成。
Take the following example data:
以以下示例数据为例：
col1 col2 col3 sortCol
====================================
NULL 4 8 1
1 NULL 0 2
5 7 NULL 3
My dream query would find the first non-null value in each of the data columns, sorted on the sortCol.
我梦想的查询会在每个数据列中找到第一个非空值，并在sortCol.
For example, when selecting the magical aggregate of the first three columns, sorted by the sortColdescending.
例如，选择前三列的神奇聚合时，按sortCol降序排序。
col1 col2 col3
========================
5 7 0
Or when sorting ascending:
或者在升序排序时：
col1 col2 col3
========================
1 4 8
Does anyone know a solution?
有谁知道解决方案？
采纳答案by Evan Carroll
Using first_value()
使用 first_value()
first_value(col)can be used with and OVER (ORDER BY CASE WHEN col IS NOT NULL THEN sortcol ELSE maxvalue END). ELSE maxvalueis required because SQL Server sorts nulls first)
first_value(col)可以与and OVER (ORDER BY CASE WHEN col IS NOT NULL THEN sortcol ELSE maxvalue END). ELSE maxvalue是必需的，因为 SQL Server 首先对空值进行排序）
CREATE TABLE foo(a int, b int, c int, sortCol int);
INSERT INTO foo VALUES
(null, 4, 8, 1),
(1, null, 0, 2),
(5, 7, null, 3);
Now you can see what we have to do to force nulls to sort after the sortcol. To do descyou have to make sure they have a negative value.
现在您可以看到我们必须做些什么来强制空值在sortcol. 为此，desc您必须确保它们具有负值。
SELECT TOP(1)
first_value(a) OVER (ORDER BY CASE WHEN a IS NOT NULL THEN sortcol ELSE 2^31-1 END) AS a,
first_value(b) OVER (ORDER BY CASE WHEN b IS NOT NULL THEN sortcol ELSE 2^31-1 END) AS b,
first_value(c) OVER (ORDER BY CASE WHEN c IS NOT NULL THEN sortcol ELSE 2^31-1 END) AS c
FROM foo;
PostgreSQL
PostgreSQL
PostgreSQL is slightly simpler,
PostgreSQL 稍微简单一些，
CREATE TABLE foo(a,b,c,sortCol)
AS VALUES
(null, 4, 8, 1),
(1, null, 0, 2),
(5, 7, null, 3);
SELECT
first_value(a) OVER (ORDER BY CASE WHEN a IS NOT NULL THEN sortcol END) AS a,
first_value(b) OVER (ORDER BY CASE WHEN b IS NOT NULL THEN sortcol END) AS b,
first_value(c) OVER (ORDER BY CASE WHEN c IS NOT NULL THEN sortcol END) AS c
FROM foo
FETCH FIRST ROW ONLY;
I believe all of this goes away when RDBMS start to adopt IGNORE NULLS. Then it'll just be first_value(a IGNORE NULLS).
我相信当 RDBMS 开始采用IGNORE NULLS. 那么它就只是first_value(a IGNORE NULLS).
回答by Mark Byers
Have you actually performance tested this solution before rejecting it?
在拒绝该解决方案之前，您是否实际对其进行了性能测试？
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
If this is slow it's probably because you don't have an appropriate index. What indexes do you have?
如果这很慢，那可能是因为您没有合适的索引。你有什么指标？
回答by Michael Petito
The problem with implementing this as an aggregation (which you indeed could do if, for example, you implemented a "First-Non-Null" SQL CLR aggregate) is the wasted IO to read every row when you're typically only interested in the first few rows. The aggregation won't just stop after the first non-null even though its implementation would ignore further values. Aggregations are also unordered, so your result would depend on the ordering of the index selected by query engine.
将此作为聚合实现的问题（例如，如果您实现了“First-Non-Null”SQL CLR 聚合，您确实可以这样做）是浪费 IO 来读取每一行，而您通常只对前几行。聚合不会在第一个非空值之后停止，即使它的实现会忽略更多的值。聚合也是无序的，因此您的结果将取决于查询引擎选择的索引的顺序。
The subquery solution, by contrast, reads minimal rows for each query (since you only need the first matching row) and supports any ordering. It will also work on database platforms where it's not possible to define custom aggregates.
相比之下，子查询解决方案为每个查询读取最少的行（因为您只需要第一个匹配的行）并支持任何排序。它也适用于无法定义自定义聚合的数据库平台。
Which one performs better will likely depend on the number of rows and columns in your table and how sparse your data is. Additional rows require reading more rows for the aggregate approach. Additional columns require additional subqueries. Sparser data requires checking more rows within each of the subqueries.
哪个表现更好可能取决于表中的行数和列数以及数据的稀疏程度。额外的行需要为聚合方法读取更多的行。额外的列需要额外的子查询。稀疏数据需要检查每个子查询中的更多行。
Here are some results for various table sizes:
以下是各种表格大小的一些结果：
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
The IO measured here is the number of logical reads. Notice that the number of logical reads for the subquery approach doesn't change with the number of rows in the table. Also keep in mind that the logical reads performed by each additional subquery will likely be for the same pages of data (containing the first few rows). Aggregation, on the other hand, has to process the entire table and involves some CPU time to do so.
这里测量的 IO 是逻辑读取的数量。请注意，子查询方法的逻辑读取数不会随着表中的行数而变化。还要记住，每个附加子查询执行的逻辑读取可能是针对相同的数据页（包含前几行）。另一方面，聚合必须处理整个表并且需要一些 CPU 时间来完成。
This is the code I used for testing... the clustered index on SortCol is required since (in this case) it will determine the order of the aggregation.
这是我用于测试的代码...... SortCol 上的聚集索引是必需的，因为（在这种情况下）它将确定聚合的顺序。
Defining the table and inserting test data:
定义表并插入测试数据：
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;
Querying the table:
查询表：
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;
The CLR "first-non-null" aggregate to test:
要测试的 CLR“first-non-null”聚合：
[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
}
回答by grisaitis
Here's another way to do it. This will be of most use if your database disallows top(N) in subqueries (such as mine, Teradata).
这是另一种方法。如果您的数据库在子查询（例如我的、Teradata）中不允许使用 top(N)，这将最有用。
For comparison, here's the solution the other folks mentioned, using top(1):
为了进行比较，这是其他人提到的解决方案，使用top(1)：
select top(1) Col1
from Table1
where Col1 is not null
order by SortCol asc
In an ideal world, that seems to me like the best way to do it - clean, intuitive, efficient (apparently).
在理想的世界中，这在我看来是最好的方式——干净、直观、高效（显然）。
Alternatively you can do this:
或者你可以这样做：
select max(Col1) -- max() guarantees a unique result
from Table1
where SortCol in (
select min(SortCol)
from Table1
where Col1 is not null
)
Both solutions retrieve the 'first' record along an ordered column. Top(1)does it definitely more elegantly and probably more efficiently. The second method does the same thing conceptually, just with more manual/explicit implementation from a code perspective.
两种解决方案都沿有序列检索“第一”记录。Top(1)它确实更优雅，可能更有效。第二种方法在概念上做同样的事情，只是从代码的角度有更多的手动/显式实现。
The reason for the max()in the root select is that you can get multiple results if the value min(SortCol)shows up in more than one row in Table1. I'm not sure how Top(1)handles this scenario, by the way.
max()在根选择中的原因是，如果值min(SortCol)出现在Table1. Top(1)顺便说一下，我不确定如何处理这种情况。
回答by Chris Chilvers
Not exactly elegant, but it can do it in a single query. Though this will probably render any indexes rather useless, so as mentioned the multiple sub-query method is likely to be faster.
不完全优雅，但它可以在单个查询中完成。尽管这可能会使任何索引变得毫无用处，但如前所述，多子查询方法可能会更快。
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
