其他函数 - 阿里云物联网平台

本文为您提供数据分析支持的CAST、DECODE、LEAST、ARRAY、SIZE、NVL等函数的命令格式、参数说明及示例。

函数	功能
CAST	将表达式的结果转换成目标类型。
COALESCE	返回列表中第一个非NULL的值。
DECODE	实现`if-then-else`分支选择的功能。
GREATEST	返回输入参数中最大的一个。
LEAST	返回输入参数中最小的一个。
UUID	返回一个随机ID。
CASE WHEN表达式	根据表达式的计算结果，灵活地返回不同的值。
IF表达式	判断指定的条件是否为真。
SIZE	返回指定MAP中的K/V对数或ARRAY中的元素数目。
CRYPTO	对指定值进行加密。
DEVICENAME	返回当前设备名称。
TOPIC	返回Topic分段信息。
MESSAGEID	返回物联网平台生成的消息ID。

CAST

命令格式
```
cast(<expr> as <type>)
```
命令说明
将expr的结果转换成目标数据类型，用法如下：
- 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。
参数说明
- expr：必填。表达式，待转换数据来源。
- type：必填。目标数据类型。
返回值说明
返回值为转换后的目标数据类型。
示例
- 示例1：常见用法。
```
--返回1。
    cast('1' as bigint);
```
- 示例2：异常用法，如果转换不成功或遇到不支持的类型转换，则会返回NULL。
```
--返回NULL。
    cast('abc' as bigint);
```

COALESCE

命令格式
```
coalesce(<expr1>, <expr2>, ...)
```
命令说明
返回<expr1>, <expr2>, ...中第一个非NULL的值，如果列表中所有的值都是NULL，则返回NULL。
参数说明
expr：必填。待验证的值。所有值类型必须相同或为NULL，不一致的数据类型会返回报错。至少要有一个参数非NULL，否则会返回报错。
返回值说明
返回值类型和参数类型相同。

示例

示例1：常见用法。

--返回1。
    coalesce(null,null,1,null,3,5,7);

示例2：参数值类型不一致，返回报错。

--返回报错，无法识别abc。
    coalesce(null,null,1,null,abc,5,7);

示例3：参数值无非NULL值，返回报错。

--返回报错，至少有一个参数值非NULL。
    coalesce(null,null,null,null);

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选项有重复且匹配时，会返回第一个值。
- 通常，在计算NULL=NULL时返回NULL，但在该函数中，NULL与NULL的值是相等的。

示例

当customer_id的值为1时，返回Taobao；值为2时，返回Alipay；值为3时，返回Aliyun；值为NULL时，返回NA；其他场景返回Others。

decode(customer_id,
'1', 'Taobao',
'2', 'Alipay',
'3', 'Aliyun',
Null, 'NA',
'Others');

等效于如下语句：

if customer_id = 1 then
result := 'Taobao';
elsif customer_id = 2 then
result := 'Alipay';
elsif customer_id = 3 then
result := 'Aliyun';
...
else
result := 'Others';
end if;

GREATEST

命令格式
```
greatest(<var1>, <var2>, …)
```
命令说明
返回输入参数中最大的值。
参数说明
var：必填。可以为BIGINT、DOUBLE、DECIMAL、DATETIME或STRING数据类型。如果所有的参数值都为NULL，则返回NULL。
返回值说明
- 返回输入参数中的最大值。当不存在隐式转换时，返回值同输入参数类型。
- NULL为最小值。
- 当输入参数类型不同时，DOUBLE、BIGINT、DECIMAL、STRING之间的比较转为DOUBLE类型；STRING、DATETIME的比较转为DATETIME类型。不允许其它的隐式转换。

LEAST

命令格式
```
least(<var1>, <var2>, …)
```
命令说明
返回输入参数中最小的值。
参数说明
var ：必填。输入参数值，为BIGINT、DOUBLE、DECIMAL、DATETIME或STRING数据类型。如果所有参数值都为NULL，则返回NULL。
返回值说明
- 输入参数中的最小值。当不存在隐式转换时，返回值同输入参数类型。
- 当有类型转换时，DOUBLE、BIGINT、STRING之间的转换返回DOUBLE类型；STRING、DATETIME之间的转换返回DATETIME类型；DECIMAL和DOUBLE、BIGINT、STRING之间的转换返回DECIMAL类型。不允许其它的隐式类型转换。
- NULL为最小值。

UUID

命令格式
```
string uuid()
```
命令说明
返回一个随机ID，格式为29347a88-1e57-41ae-bb68-a9edbdd9****。
说明
UUID返回的是一个随机的全局ID，其重复的概率很小。

CASE WHEN表达式

命令格式

提供以下两种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类型，则会返回报错。
- 不允许其它类型之间的转换。

示例

根据shop_name的不同情况得出所属区域。

case  
when shop_name is null then 'default_region'
when shop_name like 'hang%' then 'zj_region'
end as region 
from sale_detail;

IF表达式

命令格式

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

命令说明
判断testCondition是否为真。如果为真，返回valueTrue的值，否则返回valueFalseOrNull的值。
参数说明
- testCondition：必填。要判断的表达式，BOOLEAN类型。
- valueTrue：必填。表达式testCondition为True时，返回的值。
- valueFalseOrNull：表达式testCondition为False时，返回的值，可以设为NULL。
返回值说明
返回值类型和参数valueTrue或valueFalseOrNull的类型一致。
示例
```
--返回200。
    if(1=2,100,200); 
```

SIZE

命令格式
```
INT size(map)
INT size(array)
```
命令说明
size(map)返回给定map中K、V对数。size(array)返回给定的array中的元素数目。
参数说明
- map：MAP类型的数据。
- array：ARRAY类型的数据。

示例

--返回2。
    size(map('a',123,'b',456)); 
--返回3。
    size(map('a',123,'b',456,'c',789)); 
--返回2。
    size(array('a','b'));
--返回3。 
    size(array(123,456,789));

CRYPTO

命令格式
```
crypto(field,String)
```
命令说明
根据String算法对field的值进行加密。
参数说明
field：必填。任意类型值。
String：算法字符串。可选：MD2、MD5、SHA1、SHA-256、SHA-384、SHA-512。

DEVICENAME

命令格式
```
deviceName()
```
命令说明
返回当前设备名称。

TOPIC

命令格式
```
topic(INT number)
```
命令说明
返回Topic分段信息。
如，有一个Topic：/alDbcLe****/TestDevice/user/set。使用函数topic()，则返回整个Topic；使用topic(1)，则返回Topic的第一级类目alDbcLe****；使用topic(2)，则返回第二级类目TestDevice，以此类推。
参数说明
number：INT类型。非必填。

MESSAGEID

命令格式
```
messageId()
```
命令说明
返回物联网平台生成的消息ID。