[ Hive ] 函数入门

1 内置运算符

1.1 关系运算符

关系运算符是二元运算符,返回boolean类型结果,多用于where子句过滤条件

-- is null空值判断
select 1 from dual where 'itcast' is null;

-- is not null 非空值判断
select 1 from dual where 'itcast' is not null;

-- like比较： _表示任意单个字符 %表示任意数量字符
-- 否定比较： NOT A like B
select 1 from dual where 'itcast' like 'it_';
select 1 from dual where 'itcast' like 'it%';
select 1 from dual where not 'itcast' like 'hadoo_';

-- rlike：确定字符串是否匹配正则表达式，是REGEXP_LIKE()的同义词。
select 1 from dual where 'itcast' rlike '^i.*t$';
select 1 from dual where '123456' rlike '^\\d+$';  -- 判断是否全为数字
select 1 from dual where '123456aa' rlike '^\\d+$';

-- regexp：功能与rlike相同 用于判断字符串是否匹配正则表达式
select 1 from dual where 'itcast' regexp '^i.*t$';

1.2 算术运算符

加法操作: +
减法操作: -
乘法操作: *
除法操作: /
取整操作: div
取余操作: %
位与操作: &
位或操作: |
位异或操作: ^
位取反操作: ~

1.3 逻辑运算符

与操作: A AND B
或操作: A OR B
非操作: NOT A 、!A
在:A IN (val1, val2, …)
不在:A NOT IN (val1, val2, …)
逻辑是否存在: [NOT] EXISTS (subquery)

-- 非操作: NOT A 、!A   如果A为FALSE，则为TRUE；如果A为NULL，则为NULL。否则为FALSE。
select 1 from dual where not 2>1;
select 1 from dual where !2=1;
-- 在:A IN (val1, val2, ...)  如果A等于任何值，则为TRUE。
select 1 from dual where 11 in(11,22,33);
-- 不在:A NOT IN (val1, val2, ...) 如果A不等于任何值，则为TRUE
select 1 from dual where 11 not in(22,33,44);
-- 逻辑是否存在: [NOT] EXISTS (subquery) 如果子查询返回至少一行，则为TRUE。
select A.* from A
where exists (select B.id from B where A.id = B.id)

2 函数入门

2.1 函数分类

1. 内置函数:数值类型函数、日期类型函数、字符串类型函数、集合函数、条件函数等
2. 用户自定义函数
3. 以上两类根据函数的输入输出行数进行分类，比如：UDF、UDAF、UDTF

2.2 字符串函数

字符串长度函数：length
字符串反转函数：reverse
字符串连接函数：concat 等价于 ||
带分隔符字符串连接函数：concat_ws(separator, [string | array(string) ]+)
字符串截取函数：substr,substring(str, pos[, len])
字符串转大写函数：upper,ucase
字符串转小写函数：lower,lcase
去空格函数：trim
左边去空格函数：ltrim
右边去空格函数：rtrim
正则表达式替换函数：regexp_replace(str, regexp, rep)
正则表达式解析函数：regexp_extract(str, regexp[, idx])
URL解析函数：parse_url
json解析函数：get_json_object
空格字符串函数：space
重复字符串函数：repeat(str, n)
首字符ascii函数：ascii
左补足函数：lpad
右补足函数：rpad
分割字符串函数: split(str, regex)
集合查找函数: find_in_set

2.3 日期函数

获取当前日期: current_date
获取当前时间戳: current_timestamp
UNIX时间戳转日期函数: from_unixtime
获取当前UNIX时间戳函数: unix_timestamp
日期转UNIX时间戳函数: unix_timestamp
指定格式日期转UNIX时间戳函数: unix_timestamp
抽取日期函数: to_date
日期转年函数: year
日期转月函数: month
日期转天函数: day
日期转小时函数: hour
日期转分钟函数: minute
日期转秒函数: second
日期转周函数: weekofyear
日期比较函数: datediff
日期增加函数: date_add
日期减少函数: date_sub

--获取当前日期: current_date
select current_date();

--获取当前时间戳: current_timestamp
--同一查询中对current_timestamp的所有调用均返回相同的值。
select current_timestamp();

--获取当前UNIX时间戳函数: unix_timestamp(获取的是第0时区的时间戳,如果想获取服务器所在时区的时间戳,select unix_stamp(current_timestamp()))
select unix_timestamp();

--UNIX时间戳转日期函数: from_unixtime
select from_unixtime(1618238391);
select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');

--日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp("2011-12-07 13:01:03");

--指定格式日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');

--日期转周函数: weekofyear 返回指定日期所示年份第几周
select weekofyear('2009-07-30 04:17:52');

--日期比较函数: datediff  日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2012-12-08','2012-05-09');

--日期增加函数: date_add
select date_add('2012-02-28',10);

--日期减少函数: date_sub
select date_sub('2012-01-1',10);

2.4 数学函数

取整函数(四舍五入): round
指定精度取整函数: round
向下取整函数: floor
向上取整函数: ceil
取随机数函数: rand
二进制函数: bin
进制转换函数: conv
绝对值函数: abs

2.5 集合函数

集合元素size函数: size(Map< K.V >) size(Array< T >)
取map集合keys函数: map_keys(Map< K.V >)
取map集合values函数: map_values(Map< K.V >)
判断数组是否包含指定元素: array_contains(Array< T >, value)
数组排序函数:sort_array(Array< T >)

2.6 条件函数

if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
空判断函数: isnull( a )
非空判断函数: isnotnull ( a )
空值转换函数: nvl(T value, T default_value)
非空查找函数: COALESCE(T v1, T v2, …),返回参数中第一个非空值
条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
nullif( a, b ): 如果a = b，则返回NULL；否则返回NULL。否则返回一个
assert_true: 如果’condition’不为真，则引发异常，否则返回null

2.7 类型转换函数

-- 任意数据类型之间转换:cast
select cast(12.14 as bigint);
select cast(12.14 as string);

2.8 数据脱敏函数

--mask
--将查询回的数据，大写字母转换为X，小写字母转换为x，数字转换为n。
select mask("abc123DEF");
select mask("abc123DEF",'-','.','^'); --自定义替换的字母

--mask_first_n(string str[, int n]
--对前n个进行脱敏替换
select mask_first_n("abc123DEF",4);

--mask_last_n(string str[, int n])
select mask_last_n("abc123DEF",4);

--mask_show_first_n(string str[, int n])
--除了前n个字符，其余进行掩码处理
select mask_show_first_n("abc123DEF",4);

--mask_show_last_n(string str[, int n])
select mask_show_last_n("abc123DEF",4);

--mask_hash(string|char|varchar str)
--返回字符串的hash编码。
select mask_hash("abc123DEF");