函数（hive）

1.NVL(空字段赋值)
nvl函数，当查询的结果为null时默认值为后面的参数，100

select nvl(salary,100) from  emp;

2.case
统计emp表中的，每一个部门的男女数量

select
dept_id,
count(*) total,
--sum(case sex when 'man'  then 1 when 'renyao'  then 0.5 else 0 end) male,
sum(case sex when 'man'  then 1 else 0 end) male,
sum(case sex when 'woman'  then 1 else 0 end) female,
from emp
group by dept_id;

3.行转列
统计血型相同、星座相同的人数，并把血型和星座拼接，把人名也进行拼接

select
constellation,--星座
blood_type,--血型
sum(*)
from person 
group by constellation,blood_type;

//collect_list不去重，collect_set去重
//concat连接两个字段

select
--拼接血型和星座
concat(constellation,blood_type) cb
--把查询到的人名放入collect_list里，然后使用concat_ws进行拼接
concat_ws("|",collect_list(name)) rentou
from person 
group by constellation,blood_type;

4.行转列
gaoyuanyuan0 87,98,97,60
gaoyuanyuan1 88,98,94,60
gaoyuanyuan2 89,98,97,60,100

create table if not exists arr2(
name string,
score array<string>
)row format delimited
fields terminated by "\t"
collection items terminated by ","
;

select name,scoe[2] from arr2;

explode展开:

select explode(score) from arr2;