NVL 和 COALESCE 函数 - Amazon Redshift

NVL 和 COALESCE 函数

返回表达式系列中不为 null 的第一个表达式的值。当找到非 null 值时,将不计算该列表中的剩余表达式。

NVL 与 COALESCE 相同。它们是同义词。本主题说明了其语法,并提供这两者的示例。

语法

NVL( expression, expression, ... )

用于 COALESCE 的语法是相同的:

COALESCE( expression, expression, ... )

如果所有表达式为 null,则结果为 null。

如果您要在主要值缺失或为 null 时返回次要值,则这些函数非常有用。例如,一个查询可能会返回前三个可用电话号码中的第一个:手机、家庭或工作号码。函数中表达式的顺序决定了计算结果的顺序。

参数

expression

一个要针对 null 状态进行计算的表达式,如列名称。

返回类型

Amazon Redshift 根据输入表达式确定返回值的数据类型。如果输入表达式的数据类型不是通用类型,则会返回错误。

示例

如果列表包含整数表达式,则该函数返回一个整数。

SELECT COALESCE(NULL, 12, NULL); coalesce -------------- 12

此示例与前面的示例相同(不同之处在于它使用 NVL),返回相同的结果。

SELECT NVL(NULL, 12, NULL); coalesce -------------- 12

以下示例返回字符串类型。

SELECT COALESCE(NULL, 'Amazon Redshift', NULL); coalesce -------------- Amazon Redshift

以下示例会导致错误,因为表达式列表中的数据类型有变化。在这种情况下,列表中既有字符串类型,也有数字类型。

SELECT COALESCE(NULL, 'Amazon Redshift', 12); ERROR: invalid input syntax for integer: "Amazon Redshift"

对于本例,您创建一个包含 START_DATE 和 END_DATE 列的表,插入几个包含 null 值的行,然后将 NVL 表达式应用于这两个列。

create table datetable (start_date date, end_date date); insert into datetable values ('2008-06-01','2008-12-31'); insert into datetable values (null,'2008-12-31'); insert into datetable values ('2008-12-31',null);
select nvl(start_date, end_date) from datetable order by 1; coalesce ------------ 2008-06-01 2008-12-31 2008-12-31

NVL 表达式的默认列名称为 COALESCE。以下查询将返回相同的结果:

select coalesce(start_date, end_date) from datetable order by 1;

在以下示例查询中,您创建一个包含示例酒店预订信息的表,然后插入几行。一些记录包含 null 值。

create table booking_info (booking_id int, booking_code character(8), check_in date, check_out date, funds_collected numeric(12,2));

插入以下示例数据。一些记录没有 check_out 日期或 funds_collected 数量。

insert into booking_info values (1, 'OCEAN_WV', '2023-02-01','2023-02-03',100.00); insert into booking_info values (2, 'OCEAN_WV', '2023-04-22','2023-04-26',120.00); insert into booking_info values (3, 'DSRT_SUN', '2023-03-13','2023-03-16',125.00); insert into booking_info values (4, 'DSRT_SUN', '2023-06-01','2023-06-03',140.00); insert into booking_info values (5, 'DSRT_SUN', '2023-07-10',null,null); insert into booking_info values (6, 'OCEAN_WV', '2023-08-15',null,null);

以下查询返回日期列表。如果 check_out 日期不可用,它会列出 check_in 日期。

select coalesce(check_out, check_in) from booking_info order by booking_id;

结果如下。请注意,最后两条记录显示了 check_in 日期。

coalesce ------------ 2023-02-03 2023-04-26 2023-03-16 2023-06-03 2023-07-10 2023-08-15

如果您希望查询为特定函数或列返回 null 值,则可使用 NVL 表达式将这些 null 值替换为其他一些值。例如,聚合函数(如 SUM)在没有要计算的行时会返回 null 值而不是零。您可以使用 NVL 表达式将这些 null 值替换为 700.0。对 funds_collected 求和的结果不是 485,而是 1885,因为值为 null 的两行被替换为 700

select sum(nvl(funds_collected, 700.0)) as sumresult from booking_info; sumresult ------ 1885