Hive(三)分组、聚合、排序、窗口函数
最近看了一遍hive的文档，本文是为了记录文档中将来会可用东西，并非最全的《文档》，望谅解
一：建表语句
drop table window_test;
create external table if not exists window_test
(
name string,
score string
)
row format delimited
fields terminated by '|'
location '/hive/table/window_test';
二：排序函数
| 关键字 | 含义 |
| sortby | 分区内有序 |
| orderby | 全局有序 |
| distrbuteby | 分区函数 |
| clusterby |
sortby distrbuteby字段相同 只能升序排列 |
三：函数操作
1.sum与over实现定制sum
//查询当前行和下两行score总分的数据
select name, score, sum(score)over(order by name rows between current row and 2 following) from window_test;
//查询当前行和上两行score总分的数据
select name, score, sum(score)over(order by name rows between 2 preceding and current row) from window_test;
//查询当前行和以下行score总分的数据
select name, score, sum(score)over(order by name rows between current row and unbounded following) from window_test;
//查询当前行和以上行score总分的数据
select name, score, sum(score)over(order by name rows between unbounded preceding and current row) from window_test;
2.排序
//排序函数 每一个name分组后从1开始排序，相同的数据列序号相同，后一个加一
select name, score, rank()over(partition by name order by score) from window_test;
//排序函数 每一个name分组后从1开始排序，相同的数据列序号相同，后一个不加一
select name, score, dense_rank()over(partition by name order by score) from window_test;
//排序函数 每一个name分组后从1开始排序，相同的数据列序号不相同
select name, score, row_number()over(partition by name order by score) from window_test;
3.第一个值、最后一个值
//分组内排正序取第一个score
select name, score, first_value(score)over(partition by name order by score) from window_test;
//分组内排正序取最后一个score
select name, score, last_value(score)over(partition by name order by score range between unbounded preceding and unbounded following) from window_test;
//函数的结果是错误的,必须要前面一大段
//select name, score, last_value(score)over(partition by name order by score) from window_test;
4.数据动态等级划分
//根据分数将数据分为三个区域
select name, score, ntile(3)over(order by score) from window_test;
5.上一行、下一行
//下两行的score提过来,没有填充NULL
select name, score, lead(score,2)over(partition by name order by score) from window_test;
//上两行的score提过来,没有填充NULL
select name, score, lag(score,2)over(partition by name order by score) from window_test;
6.占比函数
//小于当前值SCORE的人数占总人数的占比（name分组）
select name, score, cume_dist()over(partition by name order by score) from window_test;
7.行列转
//行转列
select name,concat_ws(',',collect_list(score)) as score_value from window_test group by name;
//列转行
select name,score,score_value from window_test lateral view explode(split(score,','))num as score_value;
四：聚合函数
1.GROUPING SET、grouping__id
select grouping__id,month,day,count(1) from tranods_dev.user_detail group by month,day grouping sets((month,day),month,day,());
grouping__id：grouping sets()里面的值，0代表(month,day)，1代表month，2代表day，3代表count(1)
2.with rollup
select grouping__id,month,day,count(1) from tranods_dev.user_detail group by month,day with rollup order by grouping__id ;
结果：all，group by month，group by month,day 以左边的数据为主，依次递减
3.cube
select grouping__id,month,day,count(1) from tranods_dev.user_detail group by cube(month,day) order by grouping__id ;
结果：全量维度数据group by
4.配置
set hive.new.job.grouping.set.cardinality = 30;
这条设置的意义在于告知解释器，group by之前，每条数据复制量在30份以内。2^维度数 大于5的时候就是32
