hive随笔4

1.row_number() //窗口函数–一般用于分组中求TopN
需求：每。。。。。前几名。。。。。
//给每个分组数据打上行号
作用：把每一组的每一行都打上数字，1，2，3…然后取自己要用的行
语法：
select * from (select name,data_time,row_number() over(partition by name order cost desc) as rn
from window_t) a where rn=1 ;

数据样式：
用户 停留日期 停留时间
user date1 date2
user1 20190101 10
user1 20190102 20
user1 20190103 30
user2 20190104 40
user2 20190105 30
user2 20190106 20
…

create table test(
user string,
date1 string,
date2 string
)

需求1：user在不同的日期中的最大停留时间
需求1：user在不同的日期中的最大停留时间的前两个
需求3：每一个user在不同的日期中的最大停留时间的前两个

select *,row_nauber() over(partition by user order by date2 desc) as rn from test;
输出为：
user1 20190101 30 1
user1 20190102 20 2
user1 20190103 10 3
user2 20190104 40 1
user2 20190104 30 2
user2 20190104 20 3

select * from (select *,row_nauber() over(partition by user order by date2 desc) as rn from test) w
where w.rn <= 2 ;
输出为：
user1 20190101 30 1
user1 20190102 20 2
user2 20190104 40 1
user2 20190104 30 2

2.later view //行转列
(1)later view 用于和split,explode等udft一起使用，它能够将一行数据拆成多行数据，在此基础上可以
对拆分后的数据进行聚合。later view 首先为原始表的每行调用udft,udft会把一行拆分成一行或者
多行，later view在把结果组合，产生一个支持别名表的虚拟表。
(2)explode函数，参数仅接受array和map类型，不支持两个一起用。所以later view可以解决

语法：
create table later test(name string,weight array)
row format delimited fields terminated by ‘\t’
collection items terminated by ‘,’ ;

select name new_num from later_test laterral view
explode(weight) num as new_num ;

单词统计：
select w.word,count(*) from
(select explode(split,’ ')) word from wc_test) w group by w.word ;

案例：
create table test(
id int,
num array
)

数据：
1 [100,200]
2 [300,400]

select name new_num from later_test laterral view
explode(weight) num as new_num ;

输出为：
1 100
1 200
2 300
2 400

3.列转行，集合不去重函数:collect_list //collect_set --去重 //列转行
语法：collect_list(col)
hive>
select name,age from person;
返回值：array
说明：将col字段合并成一个数组，不去重
举例

create table person(
name string,
age string
)

数据
name age
aa 10
aa 12
aa 15

select name,collect_list(age) from person group by name;
输出为：
aa [“10”,“12”,“15”]

4.向下取整数：floor
语法：floor(double a)
返回值：bigint
说明：返回等于或者小于该double变量的最大的整数
举例
hive>
select floor(301415);
3
select floor(399995);
3
select floor(30);
30