Impala 其他函数大全

Impala数学函数

Impala中其他函数，比如基本类型转换，时间，条件等其他函数

函数列表

cast(expr AS type)

将表达式的值转换为任何其他类型
如果表达式值的类型无法转换为目标类型，则结果为Null

--将字符类型5转换为int型
[master:21000] > select cast('5' as int);
+------------------+
| cast('5' as int) |
+------------------+
| 5                |
+------------------+
--将字符类型5.1转换为double型
[master:21000] > select cast('5.1' as double);
+-----------------------+
| cast('5.1' as double) |
+-----------------------+
| 5.1                   |
+-----------------------+

typeof(type value)

返回与表达式对应的数据类型的名称

--返回字符5.1的数据类型
[master:21000] > select typeof('5.1');
+---------------+
| typeof('5.1') |
+---------------+
| STRING        |
+---------------+
--返回数值5的数据类型
[master:21000] > select typeof(5);
+-----------+
| typeof(5) |
+-----------+
| TINYINT   |
+-----------+

case a when b then c [when d then e]… [else f] end

将表达式与一个或多个可能的值进行比较，并在找到匹配项或未找到时返回相应的结果

[master:21000] > select case 1
    when 1 then 'one'
    when 2 then 'two'
    when 0 then 'zero'
    else 'out of range'
  end;
+---------------------------------------------------------------------------------------+
| case 1 when 1 then 'one' when 2 then 'two' when 0 then 'zero' else 'out of range' end |
+---------------------------------------------------------------------------------------+
| one                                                                                   |
+---------------------------------------------------------------------------------------+
[master:21000] > select case 3
    when 1 then 'one'
    when 2 then 'two'
    when 0 then 'zero'
    else 'out of range'
  end;
+---------------------------------------------------------------------------------------+
| case 3 when 1 then 'one' when 2 then 'two' when 0 then 'zero' else 'out of range' end |
+---------------------------------------------------------------------------------------+
| out of range                                                                          |
+---------------------------------------------------------------------------------------+

case when a then b [when c then d]… [else e] end

测试一系列表达式是否为真，并返回第一个真实表达式的相应结果

[master:21000] > select case 
               >     when 2>1 then '>'
               >     when 2=1 then '='
               >     when 2<1 then '<'
               >     else 'out of range'
               >   end;
+------------------------------------------------------------------------------------------+
| case when 2 > 1 then '>' when 2 = 1 then '=' when 2 < 1 then '<' else 'out of range' end |
+------------------------------------------------------------------------------------------+
| >                                                                                        |
+------------------------------------------------------------------------------------------+

coalesce(type v1, type v2, …)

返回第一个不为空的值，如果所有参数都是空值则为空

[master:21000] > select coalesce(4,2,3,1) as coalesce;
+----------+
| coalesce |
+----------+
| 4        |
+----------+
[master:21000] > select coalesce(null,null,null,null) as coalesce;
+----------+
| coalesce |
+----------+
| NULL     |
+----------+
[master:21000] > select coalesce(null,null,1,null) as coalesce;
+----------+
| coalesce |
+----------+
| 1        |
+----------+

decode(type expression, type search1, type result1,[type search2, type result2], [type default])

将表达式与一个或多个可能的值进行比较，并在找到或未找到匹配项时返回相应的结果

[master:21000] > SELECT decode(1,1,"Monday",2,"Tuesday",3,"Wednesday",4,"Thursday",5,"Friday",6,"Saturday",7,"Sunday","Unknown day")as decode;
+--------+
| decode |
+--------+
| Monday |
+--------+
[master:21000] > SELECT decode(8,1,"Monday",2,"Tuesday",3,"Wednesday",4,"Thursday",5,"Friday",6,"Saturday",7,"Sunday","Unknown day")as decode;
+-------------+
| decode      |
+-------------+
| Unknown day |
+-------------+

if(boolean condition, type ifTrue, type ifFalseOrNull)

判断表达式并根据结果是true，false还是返回相应的结果。

[master:21000] > SELECT if(2>1,"yes","no");
+------------------------+
| if(2 > 1, 'yes', 'no') |
+------------------------+
| yes                    |
+------------------------+

ifnull(type a, type ifNull)

判断值是否为空，如果为空则返回对应结构，否则返回原来的值

[master:21000] > SELECT ifnull(null,"yes");
+---------------------+
| ifnull(null, 'yes') |
+---------------------+
| yes                 |
+---------------------+
[master:21000] > SELECT ifnull("","yes");
+-------------------+
| ifnull('', 'yes') |
+-------------------+
|                   |
+-------------------+

istrue(boolean)

测试一个布尔表达式真假，真则返回true，假则返回false

[master:21000] > select istrue(2>1);
+---------------+
| istrue(2 > 1) |
+---------------+
| true          |
+---------------+

isfalse(boolean)

测试一个布尔表达式真假，真则返回false，假则返回true

[master:21000] > SELECT isfalse(2>1);
+----------------+
| isfalse(2 > 1) |
+----------------+
| false          |
+----------------+

isnotfalse(boolean)

测试一个布尔表达式真假，真则返回true，假则返回false

[master:21000] > SELECT isnotfalse(2>1);
+-------------------+
| isnotfalse(2 > 1) |
+-------------------+
| true              |
+-------------------+

isnottrue(boolean)

测试一个布尔表达式真假，真则返回false，假则返回true

[master:21000] > select isnottrue(2>1);
+------------------+
| isnottrue(2 > 1) |
+------------------+
| false            |
+------------------+

isnull(type a, type ifNull)

[master:21000] > select isnull(null,'yes');
+---------------------+
| isnull(null, 'yes') |
+---------------------+
| yes                 |
+---------------------+

nonnullvalue(expression)

测试一个表达式（任何类型）是否空值，是则返回true

[master:21000] > select nonnullvalue(null);
+--------------------+
| nonnullvalue(null) |
+--------------------+
| false              |
+--------------------+

nullif(expr1,expr2)

如果两个指定的参数是相等的返回空值。如果不相等，则返回expr1的值

[master:21000] > select nullif(1,1);
+--------------+
| nullif(1, 1) |
+--------------+
| NULL         |
+--------------+
[master:21000] > select nullif(1,2);
+--------------+
| nullif(1, 2) |
+--------------+
| 1            |
+--------------+

nullifzero(numeric_expr)

返回空值 如果数字表达式的计算结果为0，则返回表达式的结果。

[master:21000] > select nullifzero(0);
+---------------+
| nullifzero(0) |
+---------------+
| NULL          |
+---------------+
[master:21000] > select nullifzero(1);
+---------------+
| nullifzero(1) |
+---------------+
| 1             |
+---------------+

nullvalue(expression)

测试一个表达式（任何类型）是否为null

[master:21000] > select nullvalue(null);
+-----------------+
| nullvalue(null) |
+-----------------+
| true            |
+-----------------+
[master:21000] > select nullvalue("");
+---------------+
| nullvalue('') |
+---------------+
| false         |
+---------------+

nvl(type a, type ifNull)

测试表达式是否空值，如果不是，则返回表达式结果值

[master:21000] > select nvl(null,"yes");
+------------------+
| nvl(null, 'yes') |
+------------------+
| yes              |
+------------------+

zeroifnull(numeric_expr)

如果数值表达式的计算结果为0，则返回0否则返回表达式的结果

[master:21000] > select zeroifnull(null);
+------------------+
| zeroifnull(null) |
+------------------+
| 0                |
+------------------+
[master:21000] > select zeroifnull(1);
+---------------+
| zeroifnull(1) |
+---------------+
| 1             |
+---------------+

uuid()

随机生成UUID

[master:21000] > select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| d372be61-7a98-46b2-930b-025b24c9e268 |
+--------------------------------------+