这是一篇关于SQL分析提供的常见其他函数的文档_物联网平台(IoT)-阿里云帮助中心

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为非空字符串。
```
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 |
+-----+------------+-----+