有没有一种聚合函数可以在一个组内返回第一个非空值？

Question

我将使用Oracle XE 10g。

请您仔细阅读我的问题。尽管我的用例很奇怪，但还是请耐心看完。

假设我有以下记录：

Table person
Name  YearOfBirth
a     null
a     2001
a     2002
b     1990
b     null
c     null
c     2001
c     2009

基本上，如果我执行以下查询：

select
  p.Name, max(p.YearOfBirth)
from
  person p
group by
  p.Name

那会给我带来具有不同名称的记录，每个不同的名称将与其组内YearOfBirth的最大值配对。在给定示例中，当Name='a'时，YearOfBirth的最大值为2002。

如果max()是一个聚合函数，在给定组中返回列的最大值，那么是否有一个函数可以返回组内第一个非空值？而不是给我最大值，只要找到的第一个值不为空就可以了。 请不要问我为什么不能简单地使用min()或max()。

显然，我不能在这里使用rownum，因为这样做将限制我可以得到的组数。

- supertonsky

---

“first”如何定义？除非你的表是IOT（索引组织表）或你在处理返回自SELECT的行时带有“ORDER BY”，否则表中的行没有定义顺序。 - George3

请定义“first”。表格中的数据是无序的，返回结果的顺序随时可能会改变。只有当“first”可以根据数据来定义时，这个概念才有意义。 - Shannon Severance

@George3：即使在物联网中，也没有定义的顺序，如果执行主键索引的快速全扫描，可能会返回不按主键排序的结果。请参见：http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4514641276987#5363322378640 - Shannon Severance

@Shannon Severance - 很好的观点，在物联网中没有定义的顺序用于检索，只有按主键定义的逻辑存储顺序。 - George3

@Shannon 是的，我知道没有“规范”来检索“第一”行是没有意义的，或者没有一个明确的定义“第一”的定义也是没有意义的。但这正是重点，解决方案本身不应该有获取第一行的基础。这正是“规范”。我知道这没有意义，但是算了，这是一个漫长的故事。以前从未遇到过这种用例。 - supertonsky

我不能代表原帖作者发言，有时候人们只是想要任何不同的记录；“第一”本身并不重要。 - JosephDoggie

Answer 1

我可能误解了您为什么不能使用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

- Adam Wenger

---

我在这里做出的假设是有一个主键驱动顺序，因此“第一”条记录将是一致的。 - Adam Wenger

看起来您没有使用soTableNoNulls中的RowNumber列。如果不需要，最好将其删除。我认为您可以将其缩减为一个CTE而不是两个。（不包括测试数据的CTE。）（CTE =公共表达式，通常在Oracle中称为子查询因子。） - Shannon Severance

谢谢，我回答之后才注意到，现在已经删除了。 - Adam Wenger

Brent Ozar写了一篇关于ROW_NUMBER中PARTITION BY的工作原理的好文章（他的帖子还有其他聚合函数的信息）http://www.brentozar.com/archive/2011/07/leaving-windows-open/。 - Adam Wenger

Oracle的first_value可能会更快。我可以写一个答案，但我仍然不明白OP如何定义“第一”。http://download.oracle.com/docs/cd/E11882_01/server.112/e26088/functions066.htm#i83212 - Shannon Severance

Answer 2

如果您所说的“第一条记录”是指出生年份最小的记录，则可以按照以下步骤进行操作：

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

使用FIRST_VALUE()而不是ROW_NUMBER()的优点在于，前者会在出现平局的情况下返回多行。例如，如果你的数据中有另一个2001年出生的a，那么结果数据将如下所示：

NAME  BIRTH_YEAR
a     2001
a     2001
b     1981
c     2001

ROW_NUMBER() 的解决方案仅返回上述行中的一行。但是，也可以使用 RANK() 解决该问题。

如果有其他定义“第一个”的方法（例如，一个条目日期列），只需在 FIRST_VALUE() 的 ORDER BY 子句中使用它即可。

- David Faber

---

仅供参考，对于寻找T-SQL等效解决方案的任何人都有好处，即使其FIRST_VALUE缺少IGNORE NULLS子句，此解决方案也适用于SQL Server。如果其他值都为null，则可以通过按列DESC排序来简单地解决问题。这有助于避免在枢轴查询中进行许多尴尬的连接，正如我现在第一次发现的那样（感谢您提供的解决方案）。 - SQLServerSteve

Answer 3

这是解决方案：

CREATE OR REPLACE FUNCTION first_agg ( anyelement, anyelement )
RETURNS anyelement AS
$$
    SELECT $1;
$$
LANGUAGE SQL
IMMUTABLE
;

然后：

CREATE AGGREGATE first (
        sfunc    = first_agg,
        basetype = anyelement,
        stype    = anyelement
);

测试一下：

select first((case when a = 1 then null else a end) ORDER BY a NULLS FIRST) from generate_series(1, 100) a; -- => "2"

- Alexi Theodore

Answer 4

我在寻找MSSQL的类似解决方案时发现了这个问题。

上面的解决方案的主要问题是它将省略任何没有非空值的记录。

在这里得到的答案的帮助下，再加上另一个问题的答案，我为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
  UNION ALL SELECT 'd', null
)
SELECT
  Name,
  SUBSTRING(STRING_AGG(YearOfBirth, '|'), 1, CHARINDEX('|', STRING_AGG(YearOfBirth, '|'))-1) AS YearOfBirth
FROM
  soTable
GROUP BY
  Name;

- Travesty3