Hive-空字段赋值，时间类函数，分支函数，字符串拼接，COLLECT_SET()，EXPLODE()，窗口函数

空字段赋值NVL

    NVL：给值为NULL的数据赋值，格式：NVL(string, replace_with)。如果string为NULL，则返回replace_with的值（可以为字段名），否则返回string。如果两个参数都为NULL，则返回NULL。

select nvl(salary,0) from employee;

原数据		|		结果
161			|		161
949			|		946
NULL		|		0
852			|		852
NULL		|		0

时间类

• date_format：格式化时间。日期必须是xxxx-xx-xx的形式（用“-”连接），时间必须是xx：xx：xx的形式。

select date_format('2020-6-1','yyyy-MM-dd HH:mm:ss');

2020-06-01 00:00:00

• date_add / date_sub：时间与天数相加 / 相减

select date_add('2020-05-31',1);	2020-06-01
select date_add('2020-01-01',-1);	2019-12-31

• datediff：计算相差的天数（前减后）

select datediff('2020-05-31','2021-01-01');		-215

• from_unixtime() 和 unix_timestamp()：通常这两个结合使用，获取当前时间。不过这两个方法已被弃用，使用current_timestamp()

SELECT from_unixtime(unix_timestamp());

2020-08-22 12:42:09

• current_timestamp()：获取当前时间

select current_timestamp();

2020-08-22 12:44:03.575

• current_date()：获取当前日期

select current_date();

2020-08-22

• 一系列时间函数：

select
	year(current_timestamp()),
	month(current_timestamp()),
	day(current_timestamp()),
	hour(current_timestamp()),
	minute(current_timestamp()),
	second(current_timestamp());.

_c0     _c1     _c2     _c3     _c4     _c5
2020    8       22      12      48      2

• dayofmonth，dayofweek，weekofyear，

select dayofmonth(current_timestamp());	22
select dayofweek(current_timestamp());	7（星期日是第一天）
select weekofyear(current_date);	34

Hive-获取本月的第一天，本月的最后一天，本月的天数

字符串类

• regexp_replace(string1,string2,replace_with)：替换，将string1中的string2替换为replace_with。

select regexp_replace('2020/5/31','/','-');	2020-5-31

替换空白字符
regexp_replace(key, '\n|\t|\r', '') keyword
regexp_replace(key, '\\s+', '')

• split(string,regex)：第二个参数是正则表达式，按正则表达式切割字符串

select split('oneAtwoBthreeC','[ABC]');

["one","two","three"]

• substr(string,startindex,length)：从startindex开始，切割长度为length的字符串

select substr('2020-05-31',1,7);	2020-05

分支函数

    需求：假设有部门表dep，现要计算各个部门的男女人数

create table dep(
	name string,
	dept string,
	gender string)
row format delimited fields terminated by '\t';

name	dept    gender
Jack	A      	male
Rose	B      	female
Diana	A   	female
Lily	B      	female
Mike	B      	male
Bob		B      	male
Lucy	A      	female
Harry	B      	male
Rachel	B      	female

    结果如下：

dept    male	female
A       1       2
B       3       3

select
    dept,
    sum(case gender when 'male' then 1 else 0 end) male,
    sum(case gender when 'female' then 1 else 0 end) female
from
    dep
group by
    dept;

    或者使用if

select
    dept,
    sum(if(gender='male',1,0)) male,
    sum(if(gender='female',1,0)) female
from
    dep
group by
    dept;

字符串拼接

• concat(string1,string2…)：将任意个字符串（可以是字段名）拼接成一个字符串

select concat('A','-','B','-','C');		A-B-C

• concat_ws(separator,string1,string2…)：第一个参数是分隔符，后面的每个字符串均用分隔符拼接在一起。如果分隔符为NULL，返回值也为NULL。该函数会跳过分隔符后面为NULL的参数。

select concat_ws('-','A','B','C');		A-B-C
select concat_ws(NULL,'A','B');			NULL
select concat_ws('-','A','B',NULL,'C');	A-B-C

COLLECT_SET(column)

    功能：该函数是聚合函数，将某字段的值汇总成一行，产生array类型字段（多行转一行）。
    需求：根据部门表，统计每个部门的男生和女生各有哪些人。

dept    gender    list
A       female	  Diana,Lucy
A       male      Jack
B       female    Rose