oracle 是否有一个聚合函数可以返回组中的第一个非空值?

yacmzcpb  于 2023-05-06  发布在  Oracle
关注(0)|答案(4)|浏览(154)

我用的是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()是返回给定组中某列的最大值的聚合函数,那么是否有一个函数返回组中**第一个不为null的值?不给我最大值,我想要你能找到的第一个值,只要它不是null。
请不要问我为什么不能简单地使用min()或max()。
显然,我不能像有些人建议的那样在这里使用rownum,因为这样做会限制我可以得到的组的数量。

0g0grzrc

0g0grzrc1#

我可能误解了为什么ROW编号不适合您。我没有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
p3rjfoxz

p3rjfoxz2#

如果“第一个”是指出生年份最低的记录,那么您可以执行以下操作:

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

ROW_NUMBER()相比,使用FIRST_VALUE()的优势在于,前者在出现平局时将返回多行。例如,如果你的数据中有另一个出生于2001年的a,那么结果数据将如下所示:

NAME  BIRTH_YEAR
a     2001
a     2001
b     1981
c     2001

ROW_NUMBER()解决方案将只返回上述行中的一行。但是,这也可以通过使用RANK()来解决。
如果有其他方式定义“first”(例如,条目日期列),只需在FIRST_VALUE()ORDER BY子句中使用它。

iibxawm4

iibxawm43#

这就是解决方案:

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"
0qx6xfy6

0qx6xfy64#

我在为MSSQL搜索类似的解决方案时发现了这个问题。
我使用上述解决方案的主要问题是,它将忽略任何没有任何非空值的记录。
在这里的答案的帮助下,结合this other question的答案,我为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;

相关问题