本文为您提供数据分析支持的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中的元素数目。
NVL 如果value值为NULL,返回default_value,否则返回value
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:必填。searchexpression的值匹配时的返回值。
    • default:可选。如果所有的搜索项都不匹配,则返回此default值,如果未指定,则返回NULL。
    说明
    • 至少要指定三个参数。
    • 所有的result类型必须一致或为NULL。不一致的数据类型会引发异常。
    • 所有的searchexpression类型必须一致,否则会引发异常。
  • 返回值说明
    • 如果匹配,返回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。
  • 返回值说明

    返回值类型和参数valueTruevalueFalseOrNull的类型一致。

  • 示例
    --返回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)); 

NVL

  • 命令格式
    nvl(T <value>, T <default_value>)
  • 命令说明

    如果value值为NULL,返回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的值输出为-

    执行以下表达式:

    nvl(c1,'00000')
    nvl(c2,0)
    nvl(c3,'-')

    结果如下:

    +-----+------------+-----+
    | _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 |
    +-----+------------+-----+

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。