NVL および COALESCE 関数 - Amazon Redshift

NVL および COALESCE 関数

一連の式の中で、Null 以外の最初の式の値を返します。Null 以外の値が見つかると、リスト内の残りの式は評価されません。

NVL は COALESCE と同じです。これらはシノニムです。このトピックでは、両方の構文について説明し、例を示します。

構文

NVL( expression, expression, ... )

COALESCE の構文は同じです。

COALESCE( expression, expression, ... )

すべての式が null の場合、結果は null になります。

これらの関数は、プライマリ値がないか Null の場合にセカンダリ値を返すときに役に立ちます。例えば、クエリを実行すると、使用可能な 3 つの電話番号 (携帯、自宅、職場) のうち最初の電話番号が返されることがあります。関数内の式の順序によって、評価の順序が決まります。

引数

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 式をその 2 列に適用します。

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;

結果は、以下のとおりです。最後の 2 つのレコードには 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 に置き換えることができます。485 の代わりに、funds_collected を合計した結果が 1885 になります。これは、Null を含む 2 つの行が 700 に置き換えられるためです。

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