Hive查询函数的总结和案例（一）

NVL空字段赋值

NVL(value, default_value):如果value为NULL，则NVL函数返回default_value（可以为常量，也可以为某个字段）的值，否则返回value的值，如果两个参数都为NULL，则返回NULL。此函数很简单，此处不写例子。

COALESCE

coalesce(n1, n2, n3…):如果n1为null，选择n2，如果n2为null，选择n3，直到遇到非null值，并返回该非null值。
比如一张表里有多个电话字段，要登记用户的电话：
select name,coalesce(person_tel, home_tel, office_tel) from table;

CASE WHEN和IF

case when有如下两种形式：
case 字段
when 条件值1 then 值1
when 条件值2 then 值2
…
else 默认值 end
或：
case
when 字段=‘条件值1’ then 值1
when 字段=‘条件值2’ then 值2
…
else 默认值 end

if的格式：if(表达式1,表达式2,表达式3)
如果表达式1为true，则返回表达式2的值；如果表达式1为false，则返回表达式3的值。
有如下emp表：

name, dept_id, sex

求出不同部门男女各多少人：

select
    dept_id,
    sum(case sex when '男' then 1 else 0 end) male_count,
    sum(case sex when '女' then 1 else 0 end) female_count
from emp
group by dept_id;
或：
select
    dept_id,
    sum(case when sex='男' then 1 else 0 end) male_count,
    sum(case when sex='女' then 1 else 0 end) female_count
from emp
group by dept_id;

除了使用sum(case)，还可以使用sum(if())达到同样的效果
select
    dept_id,
    sum(if(sex='男', 1, 0)) male_count,
    sum(if(sex='女', 1, 0)) female_count
from emp
group by dept_id;

行转列相关函数

concat(str1/col1, str2/col2…)：返回任意个字符串连接后的结果。
concat_ws(separator, str1, str2…)：和concat作用一样，不同的是以指定分隔符separator连接起来。如果分隔符是null，返回值也是null，这个函数还会跳过分隔符参数后的任何null和空字符串。
collect_set(col)：将字段的所有值进行去重汇总，产生array类型字段。
collect_list(col)：作用和collect_set类似，只是不会去重。

有如下person_info表：

name, constellation, blood_type

把星座和血型一样的人归类到一起：

select
    concat_ws('|',collect_list(tmp.name)),
    tmp.cons_blood
from(
select
    name,
    concat(constellation,',',blood_type) cons_blood
from person_info
) tmp
group by tmp.cons_blood;

列转行相关函数

explode(col)：将一列中复杂的array或map结构拆分成多行。
lateral view：用于和explode，split等用户自定义表生成函数（UDTF）一起使用，能够将一列数据拆分成多行数据后。用在from字句之后，且可以有多个lateral view：lateral view udtf(expression) tableAlias as columnAlias

有如下movie_info表：

movie, category(array<string>类型)

将电影分类中的数组数据展开：

select movie,category_name
from movie_info lateral view explode(category) tmp_table as category_name;