oracle 是否有一个聚合函数可以返回组内的第一个非空值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7789215/
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
Is there an aggregate function that could return first non-null value within a group?
提问by supertonsky
I'm using Oracle XE 10g.
我正在使用 Oracle XE 10g。
Please I beg you to read my question carefully. I have a weird use case for this but please bear with it.
请我请求你仔细阅读我的问题。我有一个奇怪的用例，但请耐心等待。
Let's say I have the following records:
假设我有以下记录：
Table person
Name YearOfBirth
a null
a 2001
a 2002
b 1990
b null
c null
c 2001
c 2009
Basically if I do the following query:
基本上，如果我执行以下查询：
select
p.Name, max(p.YearOfBirth)
from
person p
group by
p.Name
That will give me records with distinct Names and each distinct name will be paired to maximum value of YearOfBirth within its group. In the given example the group where Name='a', the maximum YearOfBirth is 2002.
这将为我提供具有不同名称的记录，并且每个不同的名称将与其组内的 YearOfBirth 的最大值配对。在给定的示例中 Name='a' 的组中，最大 YearOfBirth 是 2002。
If max() is an aggregate function that returns the maximum value of a column in a given group, is there a function that returns the first value within the groupthat is not null? Instead of giving me the maximum value, I want the first value you could find as long as it is not null.
如果 max() 是一个聚合函数，它返回给定组中列的最大值，是否有一个函数返回组中第一个不为空的值？我不想给我最大值，我想要第一个你能找到的值，只要它不为空。
Please don't ask me why I can't simply use min() or max() instead.
请不要问我为什么不能简单地使用 min() 或 max() 来代替。
Obviously I can't use rownum here as some might suggest because doing so will limit the number of groups I could get.
显然，我不能像某些人建议的那样在这里使用 rownum，因为这样做会限制我可以获得的组数。
回答by Adam Wenger
I may be misunderstanding why ROW NUMBER would not work for you. I do not have Oracle, but I did test this in SQL Server, and I believe it provides the results you requested:
我可能误解了为什么 ROW NUMBER 对您不起作用。我没有 Oracle，但我确实在 SQL Server 中对此进行了测试，我相信它提供了您要求的结果：
WITH soTable AS
(
SELECT 'a' AS Name, null AS YearOfBirth
UNION ALL SELECT 'a', 2001
UNION ALL SELECT 'a', 2002
UNION ALL SELECT 'b', 1990
UNION ALL SELECT 'b', null
UNION ALL SELECT 'b', 1994
UNION ALL SELECT 'b', 1981
UNION ALL SELECT 'c', null
UNION ALL SELECT 'c', 2009
UNION ALL SELECT 'c', 2001
)
, soTableNoNulls AS
(
SELECT so.Name, so.YearOfBirth, ROW_NUMBER() OVER (PARTITION BY so.Name ORDER BY so.Name ASC) AS RowNumber
FROM soTable AS so
WHERE so.YearOfBirth IS NOT NULL
)
SELECT nn.Name, nn.YearOfBirth
FROM soTableNoNulls AS nn
WHERE nn.RowNumber = 1
回答by David Faber
If by "first" you mean the record with the lowest birth year, then you can do the following:
如果“第一”是指出生年份最低的记录，那么您可以执行以下操作：
WITH s1 AS
(
SELECT 'a' AS name, NULL AS birth_year FROM dual
UNION ALL SELECT 'a', 2001 FROM dual
UNION ALL SELECT 'a', 2002 FROM dual
UNION ALL SELECT 'b', 1990 FROM dual
UNION ALL SELECT 'b', null FROM dual
UNION ALL SELECT 'b', 1994 FROM dual
UNION ALL SELECT 'b', 1981 FROM dual
UNION ALL SELECT 'c', null FROM dual
UNION ALL SELECT 'c', 2009 FROM dual
UNION ALL SELECT 'c', 2001 FROM dual
)
SELECT name, birth_year FROM (
SELECT name, birth_year
, FIRST_VALUE(birth_year IGNORE NULLS) OVER ( PARTITION BY name ORDER BY birth_year ) AS first_birth_year
FROM s1
) WHERE birth_year = first_birth_year
The advantage of using FIRST_VALUE()over ROW_NUMBER()is that the former will return multiple rows in the event of ties. For example, if you had another aborn in 2001 in your data, then the resulting data would look like this:
使用FIRST_VALUE()over的好处ROW_NUMBER()是前者会在出现平局时返回多行。例如，如果您的数据中有另一个a出生于 2001 年的人，那么结果数据将如下所示：
NAME BIRTH_YEAR
a 2001
a 2001
b 1981
c 2001
The ROW_NUMBER()solution would return only one of the above rows. However, that could also be solved by using RANK().
该ROW_NUMBER()解决方案将仅返回上述行之一。但是，这也可以通过使用RANK().
If there is some other way of defining "first" (e.g., an entry date column), simply use that in the ORDER BYclause of FIRST_VALUE().
如果有其他定义“第一”的方式（例如，输入日期列），只需在 的ORDER BY子句中使用它FIRST_VALUE()。
