這是一篇關於SQL分析提供的常見其他函數的文檔 - IoT Platform

SQL分析提供了開發過程中常見的其他函數，您可以根據實際需要選擇合適的函數。本文為您提供SQL分析支援的CAST、DECODE、LEAST等函數的命令格式、參數說明及樣本。

函數	功能
BETWEEN AND運算式	篩選滿足區間條件的資料。
CASE WHEN運算式	根據運算式的計算結果，靈活地返回不同的值。
CAST	將運算式的結果轉換為目標資料類型。
COALESCE	返回參數列表中第一個非NULL的值。
DECODE	實現`if-then-else`分支選擇的功能。
GREATEST	返回輸入參數中最大的值。
IF	判斷指定的條件是否為真。
LEAST	返回輸入參數中最小的值。
NULLIF	比較兩個入參是否相等。
NVL	指定值為NULL的參數的返回結果。

BETWEEN AND運算式

命令格式
```
<a> [NOT] between <b> and <c>
```
命令說明
篩選滿足a的值位於b和c之間或不在b和c之間的資料。
參數說明
- a：必填。待篩選的欄位。
- b、c：必填。指定的區間條件。資料類型必須與a的資料類型保持一致。
傳回值說明
返回滿足條件的資料。
如果a、b或c為空白，返回結果為空白。
樣本
查詢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

命令說明
根據value或_condition的計算結果，靈活地返回不同的result值。
參數說明
- value：必填。比較的值。
- _condition：必填。指定判斷條件。
- result：必填。傳回值。
傳回值說明
- 如果result類型只有BIGINT、DOUBLE，統一轉為DOUBLE後，再返回結果。
- 如果result類型中有STRING類型，則統一轉為STRING後，再返回結果。如果無法進行類型轉換，例如BOOLEAN類型無法轉換為STRING類型，則會返回報錯。
- 不允許其他類型之間的轉換。

樣本

例如表sale_detail的欄位為shop_name string, customer_id string, total_price double,，包含資料如下。

+------------+-------------+-------------+------------+------------+
| 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)：在將字串轉為BIGINT資料類型時，如果字串中是以整型表達的數字，則會直接將它們轉為BIGINT類型。如果字串中是以浮點數或指數形式表達的數字，則會先轉為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);
--返回
+------+
| _c0  |
+------+
| false |
+------+
```
  - STRING為非Null 字元串。
```
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。
說明
- 所有的result資料類型必須一致或為NULL。不一致的資料類型會返回報錯。
- 所有的search和expression資料類型必須一致，否則會返回報錯。
傳回值說明
- 如果匹配，返回result。
- 如果沒有匹配，返回default。
- 如果沒有指定default，返回NULL。
- 如果search選項有重複且匹配時，會返回第一個值。
- 通常，SQL分析在計算NULL=NULL時返回NULL，但在該函數中，NULL與NULL的值是相等的。

樣本

例如表sale_detail的欄位為shop_name string, customer_id string, total_price double,，包含資料如下。

+------------+-------------+-------------+------------+------------+
| 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是否為真。如果為真，返回valueTrue的值，否則返回valueFalseOrNull的值。
參數說明
- testCondition：必填。要判斷的運算式，BOOLEAN類型。
- valueTrue：必填。運算式testCondition為True時，返回的值。
- valueFalseOrNull：運算式testCondition為False時，返回的值，可以設為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分析支援的所有資料類型。
傳回值說明
返回NULL或expr1。

樣本

--返回2。
select nullif(2, 3);
--返回NULL。
select nullif(2, 2);
--返回3。
select nullif(3, null);

NVL

命令格式
```
nvl(T <value>, T <default_value>)
```
命令說明
如果value值為NULL，返回default_value，否則返回value。兩個參數的資料類型必須一致。
參數說明
- value：必填。輸入參數。T指代輸入資料類型，可以是SQL分析支援的所有資料類型。
- default_value：必填。替換後的值。必須與value的資料類型保持一致。

樣本

例如表t_data的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 |
+-----+------------+-----+