一般的な SQL 分析関数の構文と使用方法 - IoT Platform

SQL 分析機能は、開発プロセスで一般的に使用されるその他の関数を備えています。ビジネス要件に基づいて関数を呼び出すことができます。このトピックでは、CAST、DECODE、LEAST などの関数の構文とパラメーターについて説明します。また、関数の呼び出し例も示します。

関数	説明
BETWEEN AND 式	指定された範囲内または範囲外の値を返します。
CASE WHEN 式	式の計算結果に基づいて値を返します。
CAST	式の結果を指定されたデータ型に変換します。
COALESCE	パラメーターリスト内の最初の非 NULL 値を返します。
DECODE	`IF-THEN-ELSE` ロジックを実装します。
GREATEST	入力パラメーターの最大値を返します。
IF	指定された条件が真かどうかを確認します。
LEAST	入力パラメーターの最小値を返します。
NULLIF	2 つの入力パラメーターの値が同じかどうかを確認します。
NVL	値が NULL であるパラメーターの戻り値を指定します。

BETWEEN AND 式

構文
```
<a> [NOT] between <b> and <c>
```
説明
a フィールドの値を返します。値は b と c の間の範囲内または範囲外である必要があります。
説明
- a: 必須。このパラメーターは、値を取得するフィールドを指定します。
- b および c: 必須。2 つのパラメーターは値の範囲を指定します。2 つのパラメーターのデータ型は、a パラメーターのデータ型と同じである必要があります。
戻り値
指定された範囲内または範囲外の値が返されます。
a、b、または c パラメーターが空の場合、NULL が返されます。
例
sal が 1000 から 1500 までのデータをクエリします。サンプルステートメント:
```
select * from emp where sal between 1000 and 1500;
```

CASE WHEN 式

構文

SQL 分析機能は、次の形式の case when 式をサポートしています。

case <value>
when <value1> then <result1>
when <value2> then <result2>
...
else <resultn>
end

case
when (<_condition1>) then <result1>
when (<_condition2>) then <result2>
when (<_condition3>) then <result3>
...
else <resultn>
end

説明
result の値を value または _condition の計算結果に基づいて返します。
パラメーター
- value: 必須。比較に使用される値。
- _condition: 必須。比較に使用される条件。
- result: 必須。戻り値。
戻り値
- すべての result 値のデータ型が BIGINT と DOUBLE のみである場合、値はデータ型が DOUBLE 型に変換された後に返されます。
- STRING 型の result 値が存在する場合、値は返される前に STRING 型に変換されます。データ型の変換がサポートされていない場合は、エラーが返されます。たとえば、BOOLEAN 型のデータは STRING 型に変換できません。
- 他のデータ型間の変換は許可されていません。

例

shop_name (STRING)、customer_id (STRING)、および total_price (DOUBLE) 列を含む sale_detail テーブルのデータに基づいて result の値を返します。テーブル内のデータ:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

サンプルステートメント:

select 
case  
when region='china' then 'default_region'
when region like 'shang%' then 'sh_region'
end as region 
from sale_detail;

サンプル結果:

+------------+
| region     |
+------------+
| default_region |
| default_region |
| default_region |
| sh_region  |
| sh_region  |
| sh_region  |
+------------+

CAST

構文
```
cast(<expr> as <type>)
```
説明
expr パラメーターで指定されたデータソースの型を、type パラメーターで指定された型に変換します。
パラメーター
- expr: 必須。データ型を変換するデータソース。
- type: 必須。データを変換するデータ型。使用方法:
  - cast(double as bigint): DOUBLE 型の値を BIGINT 型に変換します。
  - cast(string as bigint): STRING 型の値を BIGINT 型に変換します。文字列が INTEGER 形式で表された数字で構成されている場合、文字列は BIGINT 型に変換されます。文字列が FLOAT または EXPONENTIAL 形式で表された数字で構成されている場合、文字列は DOUBLE 型に変換された後、BIGINT 型に変換されます。
  - cast(string as datetime) および cast(datetime as string) には、デフォルトの日付形式 yyyy-mm-dd hh:mi:ss が使用されます。
戻り値
- 指定されたデータ型の値が返されます。
例
- 例 1: 一般的な使用方法。サンプルステートメント:
```
-- 戻り値は 1 です。
select cast('1' as bigint);
```
- 例 2: STRING 型の値を BOOLEAN 型に変換します。STRING 型の値が空の文字列の場合、false が返されます。それ以外の場合、true が返されます。サンプルステートメント:
  - STRING 型の値が空の文字列です。
```
select cast("" as boolean);
-- 戻り値は false です。
+------+
| _c0  |
+------+
| false |
+------+
```
  - STRING 型の値が空でない文字列です。
```
select cast("false" as boolean);
-- 戻り値は true です。
+------+
| _c0  |
+------+
| true |
+------+
```

COALESCE

構文
```
coalesce(<expr1>, <expr2>, ...)
```
説明
<expr1>、<expr2>、... 内の最初の非 NULL 値を返します。
説明
expr: 必須。確認する値。
戻り値
戻り値のデータ型は、入力パラメーターのデータ型と同じです。

例

一般的な使用方法。サンプルステートメント:

-- 戻り値は 1 です。
select coalesce(null,null,1,null,3,5,7);

DECODE

構文

decode(<expression>, <search>, <result>[, <search>, <result>]...[, <default>])

説明
IF-THEN-ELSE ロジックを実装します。
説明
- expression: 必須。比較する式。
- search: 必須。expression との比較に使用される検索項目。
- result: 必須。search の値が expression の値と一致する場合に返される値。
- default: オプション。式と一致する検索項目がない場合、default の値が返されます。このパラメーターに値が指定されていない場合、NULL が返されます。
説明
- NULL 値を除き、result パラメーターの他のすべての値は同じデータ型である必要があります。値のデータ型が異なる場合、エラーが返されます。
- search と expression の値は同じデータ型である必要があります。それ以外の場合、エラーが返されます。
戻り値
- 式と一致する検索項目がある場合、result の値が返されます。
- 式と一致する検索項目がない場合、default の値が返されます。
- default パラメーターに値が指定されていない場合、NULL が返されます。
- 重複する search 項目が式と一致する場合、最初の検索項目の値が返されます。
- ほとんどの場合、SQL 分析機能は NULL=NULL 式の計算結果として NULL を返します。ただし、この関数は、式内の NULL が同じ値を持つと判断します。

例

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

サンプルステートメント:

-- customer_id の値が c1 の場合、Taobao が返されます。値が c2 の場合、Alipay が返されます。値が c3 の場合、Aliyun が返されます。値が NULL の場合、N/A が返されます。それ以外の場合、Others が返されます。
select
decode(customer_id,
'c1', 'Taobao',
'c2', 'Alipay',
'c3', 'Aliyun',
Null, 'N/A',
'Others') as result
from sale_detail;
-- 前述のステートメントは、次のステートメントと同じです。
if customer_id = c1 then
result := 'Taobao';
elsif customer_id = c2 then
result := 'Alipay';
elsif customer_id = c3 then
result := 'Aliyun';
...
else
result := 'Others';
end if;

サンプル結果:

+------------+
| result     |
+------------+
| Others     |
| Others     |
| Others     |
| Taobao     |
| Alipay     |
| Aliyun     |
+------------+

GREATEST

構文
```
greatest(<var1>, <var2>[,...])
```
説明
入力パラメーターの最大値を返します。
説明
var1 および var2: 必須。BIGINT、DOUBLE、DECIMAL、DATETIME、または STRING 型の値。
戻り値
- 入力パラメーターの最大値が返されます。暗黙的な変換が実行されない場合、戻り値は入力パラメーターと同じデータ型になります。
- 値 NULL は最小値として解釈されます。
- 入力パラメーターのデータ型が異なる場合、DOUBLE、BIGINT、DECIMAL、および STRING 型の入力パラメーターは比較のために DOUBLE 型に変換され、STRING および DATETIME 型の入力パラメーターは比較のために DATETIME 型に変換されます。他のデータ型の暗黙的な変換は許可されていません。

IF

構文

if(<testCondition>, <valueTrue>, <valueFalseOrNull>)

説明
testCondition が真かどうかを確認します。testCondition が真の場合、valueTrue の値が返されます。それ以外の場合、valueFalseOrNull の値が返されます。
説明
- testCondition: 必須。評価する式。値は BOOLEAN 型です。
- valueTrue: 必須。testCondition が真の場合に返される値。
- valueFalseOrNull:testCondition が偽の場合に返される値。このパラメーターは NULL に設定できます。
戻り値
戻り値のデータ型は、valueTrue または valueFalseOrNull のデータ型と同じです。

例

-- 戻り値は 200 です。
select if(1=2, 100, 200);

LEAST

構文
```
least(<var1>, <var2>[,...])
```
説明
入力パラメーターの最小値を返します。
説明
var: 必須。入力パラメーターの値。BIGINT、DOUBLE、DECIMAL、DATETIME、または STRING 型の値。
戻り値
- 入力パラメーターの最小値が返されます。暗黙的な変換が実行されない場合、戻り値は入力パラメーターと同じデータ型になります。
- DOUBLE、BIGINT、および STRING 型の間でデータ型の変換が実行される場合、DOUBLE 型の値が返されます。STRING と DATETIME 型の間でデータ型の変換が実行される場合、DATETIME 型の値が返されます。DECIMAL、DOUBLE、BIGINT、および STRING 型の間でデータ型の変換が実行される場合、DECIMAL 型の値が返されます。他のデータ型の暗黙的な変換は許可されていません。
- 値 NULL は最小値として解釈されます。
- すべての入力パラメーターの値が NULL の場合、NULL が返されます。

例

-- 戻り値は 2 です。
select least(5, 2, 7);

NULLIF

構文
```
T nullif(T <expr1>, T <expr2>)
```
説明
expr1 と expr2 の値を比較します。値が同じ場合、NULL が返されます。値が異なる場合、expr1 の値が返されます。
説明
expr1 および expr2: 必須。任意のデータ型の式。T は入力データの型を指定します。型は、SQL 分析機能でサポートされているデータ型にすることができます。
戻り値
expr1 の値または NULL が返されます。

例

-- 戻り値は 2 です。
select nullif(2, 3);
-- 戻り値は NULL です。
select nullif(2, 2);
-- 戻り値は 3 です。
select nullif(3, null);

NVL

構文
```
nvl(T <value>, T <default_value>)
```
説明
default_value を返します（value が NULL の場合）。それ以外の場合、value が返されます。 value パラメーターと default_value パラメーターは同じデータ型である必要があります。
説明
- value: 必須。入力パラメーター。T は入力データの型を指定します。型は、SQL 分析機能でサポートされているデータ型にすることができます。
- default_value: 必須。NULL を置き換えるために使用される値。 default_value のデータ型は、value のデータ型と同じである必要があります。

例

t_data という名前のテーブルには、c1 string、c2 bigint、c3 datetime の 3 つの列が含まれています。 c1 列は STRING 型です。 c2 列は BIGINT 型です。 c3 列は DATETIME 型です。テーブル内のデータ:

+----+------------+------------+
| c1 | c2 | c3 |
+----+------------+------------+
| NULL | 20 | 2017-11-13 05:00:00 |
| ddd | 25 | NULL |
| bbb | NULL | 2017-11-12 08:00:00 |
| aaa | 23 | 2017-11-11 00:00:00 |
+----+------------+------------+

NVL 関数が呼び出された後、c1 の NULL 値は 00000 として返され、c2 の NULL 値は 0 として返され、c3 の NULL 値は ハイフン (-) として返されます。サンプルステートメント:

select nvl(c1,'00000'),nvl(c2,0),nvl(c3,'-') from nvl_test;
-- サンプル結果:
+-----+------------+-----+
| _c0 | _c1 | _c2 |
+-----+------------+-----+
| 00000 | 20 | 2017-11-13 05:00:00 |
| ddd | 25 | - |
| bbb | 0 | 2017-11-12 08:00:00 |
| aaa | 23 | 2017-11-11 00:00:00 |
+-----+------------+-----+