case when
姓名 部门 性别
悟空 A 男
八戒 A 男
沙僧 B 男
凤姐 A 女
婷姐 B 女
蜜蜜 B 女
求出不同部门男女各多少人。结果如下:
A 2 1
B 1 2
select deptno,sum(case sex when '男' then 1 else 0 end) male ,sum(case sex when '女' then 1 else 0 end) female from dept group by deptno;
行转列(CONCAT,CONCAT_WC,COLLECT_SET)
name(姓名) | constellation(星座) | blood_type(血型) |
孙悟空 | 白羊座 | A |
唐僧 | 射手座 | A |
沙和尚 | 白羊座 | B |
猪八戒 | 白羊座 | A |
白骨精 | 射手座 | A |
把星座和血型一样的人归类到一起。结果如下:
射手座,A 唐僧|白骨精
白羊座,A 孙悟空|猪八戒
白羊座,B 沙和尚
select concat(constellation,",",blood_type) ,concat_ws("|",collect_set(name)) from person_info group by constellation,blood_type;
说明:COLLECT_SET(col):函数只接受基本数据类型,它的主要作用是将某字段的值进行去重汇总,产生array类型字段。
列转行(LATERAL VIEW EXPLODE)
movie | category |
《疑犯追踪》 | 悬疑,动作,科幻,剧情 |
《Lie to me》 | 悬疑,警匪,动作,心理,剧情 |
《战狼2》 | 战争,动作,灾难 |
结果1:
剧情 《疑犯追踪》|《Lie to me》
动作 《疑犯追踪》|《Lie to me》|《战狼2》
心理 《Lie to me》
悬疑 《疑犯追踪》|《Lie to me》
战争 《战狼2》
灾难 《战狼2》
科幻 《疑犯追踪》
警匪 《Lie to me》
结果2:
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼2》 战争
《战狼2》 动作
《战狼2》 灾难
结果1:select t.category_name,concat_ws("|",collect_set(t.movie)) from (select movie,category_name from movie_info lateral view explode(catagory) tbl_tmp as category_name)t group by t.category_name;
结果2:select name,lateral view explode(category) tbl_tmp as category_name from movie;
函数说明:
EXPLODE(col):将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW
用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias
窗口函数
数据准备:business(name string,orderdate string,cost int)
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
需求:
- 查询在2017年4月份购买过的顾客及总人数
- 查询顾客的购买明细及月购买总额
- 上述的场景, 将每个顾客的cost按照日期进行累加
- 查询每个顾客上次的购买时间
- 查询前20%时间的订单信息
解析:
1.select name,count(*) over() from business where substring(orderdate,1,7)='2017-04' group by name ;
2.select name,cost,orderdate,sum(cost) over(partition by month(orderdate)) from business;
3.
select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,--按name分组,组内数据相加
sum(cost) over(partition by name order by orderdate) as sample3,--按name分组,组内数据累加
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
from business;
4.select name,cost,orderdate ,lag(orderdate,1,'1970-01-01') over(partition by name order by orderdate)from business;
5.select * from(select name,cost,orderdate ,ntile(5) over(order by orderdate) sorted from business)t where t.sorted=1;
函数说明:NTILE(n):把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,NTILE返回此行所属的组的编号。其中参数n为int值
rank函数
需求:计算每门学科成绩排名。
select name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;
函数说明:RANK() 排序相同时会重复,总数不会变;DENSE_RANK() 排序相同时会重复,总数会减少;ROW_NUMBER() 会根据顺序计算