表结构和数据如下
hive> select * from student;
OK
zs math 75
zs chinese 81
lisi chinese 76
lisi math 89
wangwu math 100
wangwu chinese 81
wangwu english 99
zhaoliu math NULL
zhaoliu english 48
zhaoliu chinese NULL
1、concat 连接
concat 起到连接的功能，用法如下：
CONCAT(字串1, 字串2, 字串3, ...): 将字串1、字串2、字串3，等字串连在一起。
hive> select concat(kecheng,'_',fenshu) as a from student;
OK
math_75
chinese_81
chinese_76
math_89
math_100
chinese_81
english_99
NULL
english_48
NULL
2、lpad 左填充
lpad(str,len,padstr) 即向当前字符串填充到多少位
示例如下
lpad经常和bin() 连用 将10进制转为2进制 转化成2进制并进行左填充
hive> select lpad(kecheng,10,'0') as a from student;
OK
000000math
000chinese
000chinese
000000math
000000math
000chinese
000english
000000math
000english
000chinese
4、bin(x) 整数转2进制
bin(x) 即将整数转为2进制的字符串
hive> select bin(4);
OK
100
5、grouping sets 根据不同维度进行聚合
GROUPING SETS（xxx,xxxx） 根据不同维度进行聚合，等价于不同的维度union all
比如
hive> select * from student group by name,kecheng grouping_sets(name,kecheng);
OK
NULL chinese NULL
NULL english NULL
NULL math NULL
lisi NULL NULL
wangwu NULL NULL
zhaoliu NULL NULL
zs NULL NULL
上面说明了就是相当于分别以name 和kecheng 分组 然后union到一起的
7、concat_ws 和collect_set (列转行操作)
concat_ws (separator, str1, str2,...) 使用指定的分割符来拼接各个字符串
hive> select concat_ws(',','aaa','bbb');
OK
aaa,bbb
collect_set() 返回一个消除了重复元素的对象集合, 其返回值类型是 array
它和concat_ws结合的场景较多
比如 现在有张表test
hive> select * from test;
OK
1 zeng 18
1 zeng 18
2 zhou 17
3 wang 18
2 zhou 18
1 zeng 19
hive> select name,concat_ws(',',collect_set(age)) as aaa from test group by name;
OK
wang 18
zeng 18,19
zhou 17,18
9、 lateral view explode (列转行 UDTF)
explode 就是将hive一行中复杂的array或者map结构拆分成多行。explode 相当于是个UDTF
但是hive不允许我们在UDTF函数之外，再添加其它select语句。
比如
select name,explode(split(id,',')) from test1;
FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions
所以我们可以让 explode 和LATERAL VIEW连用
Lateral view 其实就是用来和像类似explode这种UDTF函数联用的。lateral view 会将UDTF生成的结果放到一个虚拟表中，然后这个虚拟表会和输入行即每个game_id进行join 来达到连接UDTF外的select字段的目的。
lateral view使用场景:
- 在udtf前面用
- 在from table后面用
参考资料:https://blog.csdn.net/oopsoom/article/details/26001307
10、unix_timestamp (日期转为时间戳)
unix_timestamp() 只能转化日期为秒级的时间戳 而不是毫秒的
默认格式为“yyyy-MM-dd HH:mm:ss“
例如
hive> select unix_timestamp('2015-09-07 02:46:43');
OK
1441565203
如果时间格式为别的 可以自己设置
例如
hive> select unix_timestamp('20190627 13:01:03','yyyyMMdd HH:mm:ss');
OK
1561611663
如果需要毫秒级别的时间戳 可以自己写UDF
11、nvl(表达式1，表达式2) 判定是否为NULL
它的含义就是 如果表达式1 为null 就返回表达式2 如果表达式1不为null 就返回表达式1 如下图
hive> select nvl(null,2);
OK
2
12、INSTR(str,substr)
INSTR()函数返回字符串中子字符串第一次出现的位置
> select instr('hive','v');
OK
3
13、coalesce
返回第一个不为null的值
> SELECT coalesce(NULL, 1, NULL);
OK
1
