条件関数と式（Conditional Functions And Expressions）

条件関数と式

引数の一つをif-else方式で評価して返す関数。

CASE

概要:

Sql

CASE WHEN condition THEN result
   [WHEN ...]
   [ELSE default_result]
END

入力:

一つまたは複数の WHEN condition THEN result 句が使用され、式にはオプションで ELSE default_result 句を含めることができます。すべての condition はブール式である必要があります。

出力: 対応する WHEN condition が true に評価される場合は result 式の一つ、すべての WHEN condition 句が false に評価される場合は default_result が返されます。オプションの ELSE default_result 句が欠落していて、すべての WHEN condition 句が false に評価される場合は null が返されます。

説明: CASE式は、他のプログラミング言語のif/else文をシミュレートする一般的な条件式です。条件の結果が真であれば、条件に続く結果式の値が返され、以降のwhen句はスキップされて処理されません。

Sql

SELECT CASE WHEN 1 > 2 THEN 'elastic'
   WHEN 2 <= 3 THEN 'search'
   END AS "case";
   case
---------------
search

Sql

SELECT CASE WHEN 1 > 2 THEN 'elastic'
   WHEN 2 > 10 THEN 'search'
   END AS "case";
   case
---------------
null

Sql

SELECT CASE WHEN 1 > 2 THEN 'elastic'
   WHEN 2 > 10 THEN 'search'
   ELSE 'default'
   END AS "case";
   case
---------------
default

バリアントとして、CASE式は他のプログラミング言語のswitch-caseに似た構文で表現できます:

Sql

CASE expression
   WHEN value1 THEN result1
   [WHEN value2 THEN result2]
   [WHEN ...]
   [ELSE default_result]
END

この場合、内部的に次のように変換されます:

Sql

CASE WHEN expression = value1 THEN result1
   [WHEN expression = value2 THEN result2]
   [WHEN ...]
   [ELSE default_result]
END

Sql

SELECT CASE 5
   WHEN 1 THEN 'elastic'
   WHEN 2 THEN 'search'
   WHEN 5 THEN 'elasticsearch'
   END AS "case";
   case
---------------
elasticsearch

Sql

SELECT CASE 5
   WHEN 1 THEN 'elastic'
   WHEN 2 THEN 'search'
   WHEN 3 THEN 'elasticsearch'
   ELSE 'default'
   END AS "case";
   case
---------------
default

すべての結果式は互換性のあるデータ型でなければなりません。より具体的には、すべての結果式は最初の非NULL結果式と互換性のあるデータ型である必要があります。例えば:

次のクエリの場合:

Sql

CASE WHEN a = 1 THEN null
   WHEN a > 2 THEN 10
   WHEN a > 5 THEN 'foo'
END

エラーメッセージが返され、fooがデータ型keywordであり、期待されるデータ型integer（結果10に基づく）と一致しないことが示されます。

条件バケット化

CASEは、クエリ内でGROUP BYキーとして使用され、カスタムバケットを作成し、それらのバケットに説明的な名前を割り当てることができます。例えば、キーの値が多すぎる場合や、単にその値の範囲が各値よりも興味深い場合、CASEは次の例のようにカスタムバケットを作成できます:

Sql

SELECT count(*) AS count,
  CASE WHEN NVL(languages, 0) = 0 THEN 'zero'
   WHEN languages = 1 THEN 'one'
   WHEN languages = 2 THEN 'bilingual'
   WHEN languages = 3 THEN 'trilingual'
   ELSE 'multilingual'
  END as lang_skills
FROM employees
GROUP BY lang_skills
ORDER BY lang_skills;

このクエリを使用すると、値0, 1, 2, 3の通常のグループ化バケットを説明的な名前で作成でき、すべての値*

= 4は多言語*バケットに入ります。

COALESCE

概要:

Sql

COALESCE(
   expression,
   expression,
   ...)

入力:

…

N番目の式

COALESCEは任意の数の引数を取ることができます。

出力: 式の一つまたは null

説明: NULLでない最初の引数を返します。すべての引数がNULLの場合、nullを返します。

Sql

SELECT COALESCE(null, 'elastic', 'search') AS "coalesce";
   coalesce
---------------
elastic

Sql

SELECT COALESCE(null, null, null, null) AS "coalesce";
   coalesce
---------------
null

GREATEST

概要:

Sql

GREATEST(
   expression,
   expression,
   ...)

入力:

…

N番目の式

GREATESTは任意の数の引数を取ることができ、すべての引数は同じデータ型でなければなりません。

出力: 式の一つまたは null

説明: NULLでない最大の値を持つ引数を返します。すべての引数がNULLの場合、nullを返します。

Sql

SELECT GREATEST(null, 1, 2) AS "greatest";
   greatest
---------------
2

Sql

SELECT GREATEST(null, null, null, null) AS "greatest";
   greatest
---------------
null

IFNULL

概要:

Sql

IFNULL(
   expression,
   expression)

入力:

出力: 1番目の式がNULLの場合は2番目の式、それ以外は1番目の式。

説明: 引数が2つだけのCOALESCEのバリアント。NULLでない最初の引数を返します。すべての引数がNULLの場合、nullを返します。

Sql

SELECT IFNULL('elastic', null) AS "ifnull";
   ifnull
---------------
elastic

Sql

SELECT IFNULL(null, 'search') AS "ifnull";
   ifnull
---------------
search

IIF

概要:

Sql

IIF(
   expression,
   expression,
   [expression])

入力:

出力: 1番目の式（条件）が true に評価される場合は2番目の式を返します。false に評価される場合は3番目の式を返します。3番目の式が提供されていない場合は null を返します。

説明: プログラミング言語の標準的なIF THEN ELSE ロジックを実装する条件関数です。3番目の式が提供されていない場合、条件が false に評価されると null が返されます。

Sql

SELECT IIF(1 < 2, 'TRUE', 'FALSE') AS result1, IIF(1 > 2, 'TRUE', 'FALSE') AS result2;
   result1    |    result2
---------------+---------------
TRUE           |FALSE

Sql

SELECT IIF(1 < 2, 'TRUE') AS result1, IIF(1 > 2 , 'TRUE') AS result2;
   result1    |    result2
---------------+---------------
TRUE           |null

IIF関数は、CASE式をシミュレートするために、より複雑なロジックを実装するために組み合わせることができます。例えば:

Sql

IIF(a = 1, 'one', IIF(a = 2, 'two', IIF(a = 3, 'three', 'many')))

ISNULL

概要:

Sql

ISNULL(
   expression,
   expression)

入力:

出力: 1番目の式がNULLの場合は2番目の式、それ以外は1番目の式。

説明: 引数が2つだけのCOALESCEのバリアント。NULLでない最初の引数を返します。すべての引数がNULLの場合、nullを返します。

Sql

SELECT ISNULL('elastic', null) AS "isnull";
   isnull
---------------
elastic

Sql

SELECT ISNULL(null, 'search') AS "isnull";
   isnull
---------------
search

LEAST

概要:

Sql

LEAST(
   expression,
   expression,
   ...)

入力:

…

N番目の式

LEASTは任意の数の引数を取ることができ、すべての引数は同じデータ型でなければなりません。

出力: 式の一つまたは null

説明: NULLでない最小の値を持つ引数を返します。すべての引数がNULLの場合、nullを返します。

Sql

SELECT LEAST(null, 2, 1) AS "least";
   least
---------------
1

Sql

SELECT LEAST(null, null, null, null) AS "least";
   least
---------------
null

NULLIF

概要:

Sql

NULLIF(
   expression,
   expression)

入力:

出力: 2つの式が等しい場合は null、そうでなければ1番目の式を返します。

説明: 2つの入力式が等しい場合に null を返し、そうでない場合は1番目の式を返します。

Sql

SELECT NULLIF('elastic', 'search') AS "nullif";
   nullif
---------------
elastic

Sql

SELECT NULLIF('elastic', 'elastic') AS "nullif";
   nullif:s
---------------
null

NVL

概要:

Sql

NVL(
   expression,
   expression)

入力:

出力: 1番目の式がNULLの場合は2番目の式、それ以外は1番目の式。

説明: 引数が2つだけのCOALESCEのバリアント。NULLでない最初の引数を返します。すべての引数がNULLの場合、nullを返します。

Sql

SELECT NVL('elastic', null) AS "nvl";
   nvl
---------------
elastic

Sql

SELECT NVL(null, 'search') AS "nvl";
   nvl
---------------
search