1）nvl

判断一个数值是否为null,如果为null,给一个默认值

select nvl(null,0);
select nvl(123,0); -- 123

2) 函数case when then ....when ...then.. else... end

-- 测试数据
create table emp_sex(
name string,
dept_id string,
sex string)
row format delimited fields terminated by "\t";
load data local inpath '/home/hivedata/casetest.txt' into table emp_sex;
-- 求男女数量。
select sex,count(1) from emp_sex group by sex;

select
sum(case when sex='女' then 1 else 0 end)as `女`,
sum(case when sex='男' then 1 else 0 end)as `男`
from emp_sex;

select
sum(case sex when '女' then 1 else 0 end) as `女`,
sum(case sex when '男' then 1 else 0 end) as `男`
from emp_sex;

hive中的字段的别名，英文别名不要使用单引号或者双引号，直接写就行，如果是中文的别名需要添加反引号``

 3）get_json_object 从json数据中获取值

select get_json_object('{"name":"jack","age":19}','$.age');

4） parse_url 解析一个字符串中的url参数

-- 区分大小写
-- HOST 主机名
-- PATH 路径
-- QUERY 查询参数
select parse_url('http://www.baidu.com/path1/path2?k1=v1&k2=v2','HOST');
select parse_url('http://www.baidu.com/path1/path2?k1=v1&k2=v2','PATH');
select parse_url('http://www.baidu.com/path1/path2?k1=v1&k2=v2','QUERY');
-- 获取参数的值
select parse_url('http://www.baidu.com/path1/path2?k1=v1&k2=v2','QUERY','k1');

5）if(p1,p2,p3)

select if(1>2,'true','false'); -- false
select if(emp_sex.sex != '女',emp_sex.name,' hhh') from emp_sex;
select * from emp;
-- 三个效果一样
select id,if(ld is null ,0,ld) from emp;
select id,nvl(ld,0) from emp;
select id,case when ld is null then 0 else ld end from emp;

6） coalesce(col1,col2,col3...)返回第一个不为空的数据

select coalesce(null,null,null,null,null,123);

7）取模函数 (求余数)

select mod(10,3); -- 1
select mod(3,10); -- 3

8) array_contains

-- array_contains（）作用：判断数组是否包含某元素
-- 语法：array_contains(数组，值），返回布尔类型

select array_contains(array(1,2,3,4,5),3);
select array_contains(array('a','b','c'),'a');
create table courses (
id int,
course string
)
row format delimited
fields terminated by '\t';
load data local inpath '/home/hivedata/contionstest.txt' into table courses;
select * from courses;
select id,
sum(case when course="a" then 1 else 0 end) a ,
sum(case when course="b" then 1 else 0 end) b,
sum(case when course="c" then 1 else 0 end) c,
sum(case when course="d" then 1 else 0 end) d,
sum(case when course="e" then 1 else 0 end) e,
sum(case when course="f" then 1 else 0 end) f
from courses
group by id;
select id,
if(array_contains(collect_set(course),'a'),1,0) a,
if(array_contains(collect_set(course),'b'),1,0) b,
if(array_contains(collect_set(course),'c'),1,0) c,
if(array_contains(collect_set(course),'d'),1,0) d,
if(array_contains(collect_set(course),'e'),1,0) e,
if(array_contains(collect_set(course),'f'),1,0) f
from courses
group by id;

select id,
sum(if(course='a',1,0)) `a`,
sum(if(course='b',1,0)) `b`,
sum(if(course='c',1,0)) `c`,
sum(if(course='d',1,0)) `d`,
sum(if(course='e',1,0)) `e`,
sum(if(course='f',1,0)) `f`
from courses
group by id;