Hive｜Spark 函数详解

持续更新…

基本比较运算函数

注意: 在比较比较运算函数中, 对于一些可能存在为 NULL 的情况, 一定要增加逻辑判断和处理

=、==

expr1 = expr2 - 如果 expr1 equals expr2 则返回 true , 否则返回 false

① 为 NULL 的情况

如果 expr1 或 expr2 任意一个或两个都为 NULL ，则返回结果一定为 NULL

② 类型转换

expr1 和 expr2 必须为相同的类型或者可以转换成公共的的类型, 而且必须是可以在相等比较中使用的类型. Map 类型目前不支持. 对于 array | struct 等复杂类型，字段的数据类型必须是可以排序的.

-- 比较相同类型,且都不为 NULL
> SELECT 2 = 2;
 true
-- 比较不同的类型, 但是可以转换为公共的类型
> SELECT 1 = '1';
 true
-- expr1 和 expr2 为 NULL 的情况, 只有有 NULL , 则一定返回 NULL
> SELECT true = NULL;
 NULL
> SELECT NULL = NULL;
 NULL

试一下 array 类型, 这里需要注意一下

-- 我们创建两个数组(元素相同,但是顺序不同), 然后进行比较,
> SELECT array(1,2,3) = array(3,2,1);
 FALSE

-- 我们创建两个数组(元素相同,顺序相同), 然后进行比较,
> SELECT array(1,2,3) = array(3,2,1);
 TRUE

!

! expr - 逻辑非

-- 只有这三种情况
> SELECT ! true;
 false
> SELECT ! false;
 true
> SELECT ! NULL;
 NULL

!=、<>

说明：在 SPARK 和 HIVE 中, <> 和 != 的用法相同

expr1 != expr2 - 如果 expr1 not equals expr2 则返回 true , 否则返回 false. 和 = 的情况相反

① 注意数据为 NULL 的情况, 参考 =

② 注意类型转换的情况, 参考 =

> SELECT 1 != 2;
 true
> SELECT 1 != '2';
 true
> SELECT true != NULL;
 NULL
> SELECT NULL != NULL;
 NULL

<=>

expr1 <=> expr2 - 在 expr1 和 expr2 都不为 NULL 的情况下, 用法和 = 相同; 如果 expr1 和 expr2 都为 NULL 返回 true , 如果 expr1 和 expr2 任意一个为 NULL , 则返回 NULL

① 注意数据为 NULL 的情况, 参考 =

② 注意类型转换的情况, 参考 =

> SELECT 2 <=> 2;
 true
> SELECT 1 <=> '1';
 true
> SELECT true <=> NULL;
 false
> SELECT NULL <=> NULL;
 true

<、<=

expr1 < expr2 - 如果 expr1 小于 expr2 则返回 true , 否则返回 false

expr1 <= expr2 - 如果 expr1 小于等于 expr2 则返回 true , 否则返回 false

① 注意数据为 NULL 的情况, 参考 =

② 注意类型转换的情况, 参考 =

> SELECT 1 < 2;
 true
> SELECT 1.1 < '1';
 false
> SELECT to_date('2009-07-30 04:17:52') < to_date('2009-07-30 04:17:52');
 false
> SELECT to_date('2009-07-30 04:17:52') < to_date('2009-08-01 04:17:52');
 true
> SELECT 1 < NULL;
 NULL
> SELECT '1' < 1;
 true

>、>=

expr1 > expr2 - 如果 expr1 大于 expr2 则返回 true , 否则返回 false

expr1 >= expr2 - 如果 expr1 大于等于 expr2 则返回 true , 否则返回 false

① 注意数据为 NULL 的情况, 参考 =

② 注意类型转换的情况, 参考 =

> SELECT 2 > 1;
 true
> SELECT 2 > '1.1';
 true
> SELECT to_date('2009-07-30 04:17:52') > to_date('2009-07-30 04:17:52');
 false
> SELECT to_date('2009-07-30 04:17:52') > to_date('2009-08-01 04:17:52');
 false
> SELECT 1 > NULL;
 NULL

特殊比较运算函数

BETWEEN

expr1 [NOT] BETWEEN expr2 AND expr3 - 计算如果 expr1 是否在 expr2 和 expr3 之间的范围内; 使用 NOT 关键字可以实现相反的效果

-- 计算 1, 3, 5, 7 在 2 和 5 之间的元素(该示例为 spark 语法)
> SELECT col1 FROM VALUES 1, 3, 5, 7 WHERE col1 BETWEEN 2 AND 5;
 3
 5

-- 计算 1, 2, 3, 4 在 2 和 4 之间的元素(spark 和 hive 都支持)
> WITH t AS
(
    select 1 as id
    UNION ALL
    select 2 as id
    UNION ALL
    select 3 as id
    UNION ALL
    select 4 as id
)
select id
from t
where id between 2 and 4;

我们试试当数据中存在为 NULL 的情况