hive部分函数

目录

1.行转列，列转行

         2.窗口函数

                   window子句

                   4种排名函数

3. window as with as 

---

1.行转列，列转行

行转列：

数据来源weight.txt

//建表语句
create table rowtest(
    name string,
    weight array<string>
)row format delimited 
fields terminated by '\t'
COLLECTION ITEMS terminated by ',';

//将数据插入到表中
 load data local inpath '/usr/local/module/weight.txt' into table rowtest;

//首先了解一下explode函数，传入的参数仅接受array和map类型，一次限制传入一个字段
//可以将里面的array,map里面的内容展开
hive> select explode(weight) from testarray2;
OK
150
170
180
150
180
190

lateral view 可以理解为类似表连接的操作 

hive> select name,col1 from rowtest lateral view explode(weight) t1 as col1;
OK
a	150
a	170
a	180
b	150
b	180
b	190

列转行

数据来自weight2.txt

 做法就是根据左边字段分组（group by），右边的数据通过collect_list()连接起来

这也是通过mapreduce支持的

hive> select name,collect_list(col1) from linetest group by name;
OK
a	[150,170,180]
b	[150,180,190]

 collect_list(clo)不会对clo里面的内容去重

 使用collect_set(clo)会对clo里面的内容进行去重。

2.窗口函数

窗口函数是用来干什么的呢？ 它主要是为了将表中原数据和聚合过后的信息同时展示出来

 window子句：

PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点，
UNBOUNDED FOLLOWING：表示到后面的终点

Hive 提供了两种定义窗口帧的形式：`ROWS` 和 `RANGE`。
两种类型都需要配置上界和下界。
例如，`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` 表示选择分区起始记录到当前记录的所有行；
`SUM(close) RANGE BETWEEN 100 PRECEDING AND 200 FOLLOWING` 则通过 *字段差值* 来进行选择。
如当前行的 `close` 字段值是 `200`，那么这个窗口帧的定义就会选择分区中 `close` 字段值落在 `100` 至 `400` 区间的记录。
如果没有定义窗口帧，则默认为 `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`。 

rows是指定了行数为多少
range是根据值得范围划分，不能确定到底是多少行数据
 

 现在来看一下用法：

//以下内容都是通过班级分区，成绩排序
select *,
row_number() over (partition by clazz order by score desc) as num1,
rank()       over (partition by clazz order by score desc) as num2,
//求当前行与上一行，下一行的平均值，一共三行
avg(score)  over (partition by clazz order by score desc rows between 1 PRECEDING and 1 FOLLOWING) as  avg1,
//求当前当前行和前两行的最大值
max(score)  over (partition by clazz order by score desc rows between  2 PRECEDING and CURRENT ROW) as max1,
//求当前行和上下两行（一共5行）的平均值
avg(score)   over (partition by clazz order by score desc range between 2 PRECEDING and 2 FOLLOWING) as  avg2
from testwos

4种排名函数：

 row_number :排名从1到最后一位，没有重复也没有跳过

1 2 3 4 5 6 7 8 9 10

 rank :排名有重复，  中间有跳过，排名从1到最后不完整,重复的按照小的来

1 2 2 4 5 6 6 6 9 10

dense_rank ：排名有重复，中间没有跳过， 排名从1到最后完整

1 2 2 3 4 5 6 6 6 7

 percent_rank:(rank的结果-1)/(分区内数据的个数-1)  最大的就是0 ，结尾不一定是1

0.000 0.121 0.121 0.301 0.301 0.430 0.430 0.430 0.739  0.739

用法，需要结合窗口函数

 这里就写个row_number，其他的只要在下面语句中替换row_number就可以使用了

对学生表操作， 求男女生年龄排名

hive> select *,row_number() over(partition by gender order by age desc)from students_dt;

数据非常长，截取了部分观察 

 

截取百分比的部分运行结果  

3. window as with as 

当一个窗口函数得over() 内容需要多次使用可以考虑将他存储起来，方便多次使用

//未使用window as
select *,
row_number() over (partition by clazz order by score desc) as num1,
rank()       over (partition by clazz order by score desc) as num2,
from testwos;

//使用window as
select *,
row_number() over w as num1,
rank()       over w as num2,
from testwos
Window w as (partition by clazz order by score desc);

 普通的临时表 ，也可以将他变成一个变量方便我多次使用

//使用with as
with stu as (select students.*,score.score from students left join score on students.id=score.id)
with s1 as ((select id,sum(score) as score from stu group by id))
select * from s1
left join stu 
on s1.id=stu.id;