（三）hive学习笔记——1.3hive的函数

参数只能为array或map
将array类型参数转为1列N行
将map类型参数转为2列N行

4.hive练习1

数据

悟空    A       男 
大海    A       男 
宋宋    B       男 
凤姐    A       女 
婷姐    B       女 
婷婷    B       女

创建hive数据表

hive (default)> create table emp_sex(name string,dept_id string,sex string)row format delimited fields terminated by "t";

导入数据到数据库中

hive (default)> load data local inpath '/opt/data/' into table emp_sex;
Loading data to p_sex
p_sex stats: [numFiles=1, totalSize=78]
OK
Time taken: 0.254 seconds

查询各个部门的男女分别有多少人

hive (default)>  select t1.dept_id,male_count,female_count from (select dept_id,count(*) male_count from emp_sex where sex='男' group by dept_id) t1 join (select dept_id,count(*) female_count from emp_sex where sex='女' group by dept_id) t2 on t1.dept_id=t2.dept_id;

hive (default)>  select dept_id,sum(case sex when '男' then 1 else 0 end)male_count,sum(case sex when '女' then 1 else 0 end)famale_count from emp_sex group by dept_id;

5.hive练习2

数据

孙悟空  白羊座  A
大海    射手座  A
宋宋    白羊座  B
猪八戒  白羊座  A
凤姐    射手座  A

创建hive数据表

hive (default)> create table person_info(name string,constellation string,blood_type string)row format delimited fields terminated by "t";
OK
Time taken: 0.142 seconds

导入数据到数据表中

hive (default)> load data local inpath '/opt/' into table person_info;
Loading data to table default.person_info
Table default.person_info stats: [numFiles=1, totalSize=101]
OK
Time taken: 0.279 seconds

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

hive (default)> select concat(constellation,',',blood_type),concat_ws('|',collect_list(name)) from person_info group by constellation,blood_type;

6.hive练习3

数据

《疑犯追踪》    悬疑,动作,科幻,剧情
《Lie to me》   悬疑,警匪,动作,心理,剧情
《战狼2》       战争,动作,灾难

创建hive数据表

hive (default)> create table movice_info (movice string,category array<string>)row format delimited fields terminated by "t" collection items terminated by ",";
OK
Time taken: 0.078 seconds

导入数据到数据表中

hive (default)> load data local inpath '/opt/' into table movice_info;
Loading data to vice_info
vice_info stats: [numFiles=1, totalSize=151]
OK
Time taken: 0.16 seconds

将电影分类展开

hive (default)> select movice,coll from movice_info Lateral view explode(category) temp1 as coll;

7.hive窗口函数

窗口函数：窗口+函数
窗口：函数运行时计算的数据集的范围Windowing functions:LEAD:LEAD(scalar_expression[,offset][,default]):返回当前行以下N行的指定列的列值！如果找不到，就采用默认值LAG:LAG(scalar_expression[,offset][,default]):返回当前行以上N行的指定列的列值！如果找不到，就采用默认值FIRST_VALUE:FIRST_VALUE(列名,[false(默认)])返回当前窗口指定列的第一个；第二个参数如果为true，代表加入第一个值为null，跳过空值，继续寻找LAST_VALUE:LAST_VALUE(列名,[false(默认)])返回当前窗口指定列的最后一个；第二个参数如果为true，代表加入第一个值为null，跳过空值，继续寻找
函数：运行的函数！
注意：暴怒是所有的函数在运行嗾使可以通过改变窗口的大小，来控制计算的数据集的范围！所有的排名函数和LAG,LEAD，支持使用over()，但是在over()中不能定义window_clause
格式：函数 over(parttition by 字段,order by 字段 window_clause)
特殊情况：①在over()中既没有出现windows_clause，也没有指定order by，窗口默认为rows between UNBOUNDED PRECEDING and NBOUNDED FOLLOWING②在over（）中没有出现windows_clause，指定order by了，窗口默认为rows between UNBOUNDED PRECEDING and CURRENT ROW

数据

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
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

创建hive数据表

hive (default
)> create table business(name string,orderdate string,cost int)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
OK
Time taken: 0.851 seconds

导入数据到数据表中

hive (default)> load data local inpath '/opt/' into table business;
Loading data to table default.business
Table default.business stats: [numFiles=1, totalSize=267]
OK
Time taken: 0.419 seconds

(1)查询在2017年4月份购买过的顾客及总人数

hive (default)> select name,count (*) over(rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) from business where year(orderdate)='2017' and month(orderdate)='04' group by name;

(2)查询顾客的购买明细及月购买总额

hive (default)> select name,substring(orderdate,1,7),cost,sum(cost) over (partition by name,substring(orderdate,1,7) rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING)  from business;

(3)查询顾客的购买明细上述的场景，要将cost按照日期累加

hive (default)> select name,orderdate,cost,sum(cost) over (partition by name,substring(orderdate,1,7) order by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW)  from business;

(4)查看顾客的购买明细及顾客上次的购买时间

hive (default)> select name,orderdate,cost,lag(orderdate,1,'无数据')over(partition by name order by orderdate)from business;

(5)查看顾客的购买明细及顾客下次的购买时间

hive (default)> select name,orderdate,cost,lead(orderdate,1,'无数据')over(partition by name order by orderdate)from business;

(6)查看顾客的购买明细及顾客第一次的购买时间

hive (default)> select name,orderdate,cost,first_value(orderdate,true)over(partition by name,substring(orderdate,1,7) order by orderdate)from business;

(7)查看顾客的购买明细及顾客最后一次的购买时间

hive (default)> select name,orderdate,cost,last_value(orderdate,true)over(partition by name,substring(orderdate,1,7) order by orderdate rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING)from business;

(8)查看顾客的购买明细及顾客最近三次的花费

hive (default)> select name,orderdate,cost,sum(cost)over(partition by name order by orderdate rows between 2 PRECEDING  and CURRENT ROW)from business;

(8)查询前20%时间的订单信息

hive (default)> select * from(select name,orderdate,cost,cume_dist() over(order by orderdate)cdnum from business)tmp where cdnum <=0.2;

8.hive排名函数

RANK:允许并列，一旦有并列的跳号！
ROW_NUMBER:行号!连续的，每行差1
DENSE_RANK：允许并列，但是不跳号！
CUME_DIST:从排序后的第一行到当前值之间数据，占整个数据集的百分比！
PERCECENT_RANK:rank-1/总数据量-1
NTILE:将数据集均分到X个组中，返回每条记录所在的组号
注意：排名函数可以跟over()，但是不能定义window_clause在计算

数据

孙悟空  数学    95
孙悟空  英语    68
大海    语文    94
大海    数学    56
大海    英语    84
宋宋    语文    64
宋宋    数学    86
宋宋    英语    84
婷婷    语文    65
婷婷    数学    85
婷婷    英语    78

创建hive数据表

hive (default)> create table score(name string,subject string,score int)row format delimited fields terminated by "t";
OK
Time taken: 0.072 seconds

导入数据到数据表中

hive (default)> load data local inpath '/opt/' into table score;
Loading data to table default.score
Table default.score stats: [numFiles=1, totalSize=193]
OK
Time taken: 0.159 seconds

(1)按照科目进行排名

 hive (default)> select * ,rank() over(partition by subject order by score desc)from score ;

(2)给每个学生的总分进行排名

 hive (default)> select name,sumscore,rank()over(order by sumscore desc) from(select name ,sum(score ) sumscore from score group by name) tmp;

(3)给每个学生的明细及每个学生的总分和总分排名

 hive (default)> select *,DENSE_RANK() over(order by sumscore desc) from(select *,sum(score) over(partition by name)sumscore from score)tmp;

(4)只查询每个科目的成绩的前两名

 hive (default)> select * from(select *,rank() over(partition by subject order by score desc)rn from score)tmp where rn <=2;

(5)查询学生成绩明细，便显示当前学科的最高分

 hive (default)> select *,max(score)over(partition by subject)from score;

(6)查询学生成绩明细，便显示当前学科的最低分

 hive (default)> select *,min(score)over(partition by subject)from score;