Hive的一些基本函数（二）

一、窗口函数

当查询的要求，既要明细查询又要统计查询的时候，这时候考虑开窗，通过over语法让两种查询同时执行

比如：需求：查询每个订单的信息，以及订单的总数

姓名,购买日期,购买数量
saml,2018-01-01,10
saml,2018-01-08,55
tony,2018-01-07,50
saml,2018-01-05,46
tony,2018-01-04,29
tony,2018-01-02,15
saml,2018-02-03,23
mart,2018-04-13,94
saml,2018-04-06,42
mart,2018-04-11,75
mart,2018-04-09,68
mart,2018-04-08,62
neil,2018-05-10,12
neil,2018-06-12,80


-1. 创建order表:
create table if not exists t_order
(
    name      string,
    orderdate string,
    cost      int
)  row format delimited fields terminated by ',';
-2. 加载数据:
load data local inpath "/xxxx/xxx/xxxx.txt" into table t_order;


--不使用开窗函数的写法
select *,(select count(1) from t_order) as `订单总数` from t_order ;

--使用开窗函数的写法：
select *, count(*) over() from t_order;

窗口函数是针对每一行数据的.
如果over中没有指定参数,默认窗口大小为全部结果集

需求：查询在2018年1月份购买过的顾客购买明细及总次数

select *,count(*) over()
from t_order
where substr(orderdate,1,7) = '2018-01';

需求：查询在2018年1月份购买过的顾客购买明细及总人数。

select *,count(distinct name) over()
from t_order
where substr(orderdate,1,7) = '2018-01';

distribute by子句:

在over窗口中进行分组,对某一字段进行分组统计,窗口大小就是同一个组的所有记录（按照所给字段分组）

案例：查看顾客的购买明细及月购买总额

select *,sum(cost) over(distribute by substr(orderdate,1,7) ) from t_order ;

sort by子句

用在分组后表，会强制排序，当使用排序时，窗口会在组内逐行变大

案例：查看顾客的购买明细及每个顾客的月购买总额,并且按照日期降序排序

select *,sum(cost) over(distribute by name,month(orderdate) sort by orderdate desc ) from t_order ;

我们可以使用partiton by + order by 组合替代 distribute by +sort组合

select *,sum(cost) over(partition by name,month(orderdate) order by orderdate desc ) from t_order ;

也可以在窗口函数中，只写排序，窗口大小是全表记

select *,sum(cost) over(order by orderdate desc ) from t_order ;

window 子句

如果要对窗口的结果做更细粒度的划分,那么就使用window子句,常见的有下面几个
PRECEDING：往前 
FOLLOWING：往后 
CURRENT ROW：当前行 
UNBOUNDED：起点，
UNBOUNDED PRECEDING：表示从前面的起点， 
UNBOUNDED FOLLOWING：表示到后面的终点

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 t_order;

注意：默认mysql老版本没有支持,在最新的8.0版本中支持, Oracle和Hive中都支持窗口函数

二、序列函数

1、NTILE

        ntile 是Hive很强大的一个分析函数。可以看成是：它把有序的数据集合 平均分配 到 指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1

select name,orderdate,cost,
ntile(3) over(partition by name) -- 按照name进行分组,在分组内将数据切成3份
from t_order;

-- 运行结果如下：
mart    2018-04-13      94      1
mart    2018-04-08      62      1
mart    2018-04-09      68      2
mart    2018-04-11      75      3
neil    2018-06-12      80      1
neil    2018-05-10      12      2
saml    2018-02-03      23      1
saml    2018-04-06      42      1
saml    2018-01-05      46      2
saml    2018-01-08      55      2
saml    2018-01-01      10      3
tony    2018-01-02      15      1
tony    2018-01-04      29      2
tony    2018-01-07      50      3

        需求：获取一个表中，所有消费记录中，每一个人，最后50%的消费记录

select name,orderdate,cost from (
select name,orderdate,cost,
ntile(2) over(partition by name order by orderdate ) as xuhao
from t_order ) t where t.xuhao=2;

2、lag 和lead函数

lag返回当前数据前n行数据

lead返回当前数据后n行数据

需求:查询顾客上次购买的时间

select * ,lag(orderdate,1) over( partition by name order by orderdate ) from t_order;

mart    2018-04-08      62      NULL
mart    2018-04-09      68      2018-04-08
mart    2018-04-11      75      2018-04-09
mart    2018-04-13      94      2018-04-11
neil    2018-05-10      12      NULL
neil    2018-06-12      80      2018-05-10
saml    2018-01-01      10      NULL
saml    2018-01-05      46      2018-01-01
saml    2018-01-08      55      2018-01-05
saml    2018-02-03      23      2018-01-08
saml    2018-04-06      42      2018-02-03
tony    2018-01-02      15      NULL
tony    2018-01-04      29      2018-01-02
tony    2018-01-07      50      2018-01-04

select * ,lag(orderdate,1,'1990-01-01') over( partition by name order by orderdate ) from t_order;

mart    2018-04-08      62      1990-01-01
mart    2018-04-09      68      2018-04-08
mart    2018-04-11      75      2018-04-09
mart    2018-04-13      94      2018-04-11
neil    2018-05-10      12      1990-01-01
neil    2018-06-12      80      2018-05-10
saml    2018-01-01      10      1990-01-01
saml    2018-01-05      46      2018-01-01
saml    2018-01-08      55      2018-01-05
saml    2018-02-03      23      2018-01-08
saml    2018-04-06      42      2018-02-03
tony    2018-01-02      15      1990-01-01
tony    2018-01-04      29      2018-01-02
tony    2018-01-07      50      2018-01-04

3、first_value 和last_value

first_value 取分组内排序后，截止到当前行，第一个值

last_value 分组内排序后，截止到当前行，最后一个值

select name,orderdate,cost,
   first_value(orderdate) over(partition by name order by orderdate) as time1,
   last_value(orderdate) over(partition by name order by orderdate) as time2
from t_order;

name    orderdate       cost    time1   time2
mart    2018-04-08      62      2018-04-08      2018-04-08
mart    2018-04-09      68      2018-04-08      2018-04-09
mart    2018-04-11      75      2018-04-08      2018-04-11
mart    2018-04-13      94      2018-04-08      2018-04-13
neil    2018-05-10      12      2018-05-10      2018-05-10
neil    2018-06-12      80      2018-05-10      2018-06-12
saml    2018-01-01      10      2018-01-01      2018-01-01
saml    2018-01-05      46      2018-01-01      2018-01-05
saml    2018-01-08      55      2018-01-01      2018-01-08
saml    2018-02-03      23      2018-01-01      2018-02-03
saml    2018-04-06      42      2018-01-01      2018-04-06
tony    2018-01-02      15      2018-01-02      2018-01-02
tony    2018-01-04      29      2018-01-02      2018-01-04
tony    2018-01-07      50      2018-01-02      2018-01-07

三、排名函数

      1、row_number

                row_number从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列

效果如下：
98		1
97		2
97		3
96		4
95		5
95		6

没有并列名次情况，顺序递增

        2、rank（）

生成数据项在分组中的排名，排名相等会在名次中留下空位

效果如下：
98		1
97		2
97		2
96		4
95		5
95		5
94		7
有并列名次情况，顺序跳跃递增

        3、dense_rank()

        生成数据项在分组中的排名，排名相等会在名次中不会留下空位

       

效果如下：
98		1
97		2
97		2
96		3
95		4
95		4
94		5
有并列名次情况，顺序递增

案例演示：

1 gp1808 80
2 gp1808 92
3 gp1808 84
4 gp1808 86
5 gp1808 88
6 gp1808 70
7 gp1808 98
8 gp1808 84
9 gp1808 86
10 gp1807 90
11 gp1807 92
12 gp1807 84
13 gp1807 86
14 gp1807 88
15 gp1807 80
16 gp1807 92
17 gp1807 84
18 gp1807 86
19 gp1805 80
20 gp1805 92
21 gp1805 94
22 gp1805 86
23 gp1805 88
24 gp1805 80
25 gp1805 92
26 gp1805 94
27 gp1805 86


create table if not exists stu_score(
userid int,
classno string,
score int
)
row format delimited 
fields terminated by ' ';

load data local inpath '/xxx/xxxxx/xxxxxx.txt' overwrite into table stu_score;

需求一：对每个班级的每次考试按照考试成绩倒序

select *,dense_rank() over(partition by classno order by  score desc) from stu_score;

select *,dense_rank() over(order by score desc) `全年级排名`  from stu_score;

需求二：获取每次考试的排名情况

select *,
-- 没有并列，相同名次依顺序排
row_number() over(distribute by classno sort by score desc) rn1,
-- rank()：有并列，相同名次空位
rank() over(distribute by classno sort by score desc) rn2,
-- dense_rank()：有并列，相同名次不空位
dense_rank() over(distribute by classno sort by score desc) rn3
from stu_score;

需求三：求每个班级的前三名

select * from (
select * ,dense_rank() over(partition by classno order by score desc) as paiming from stu_score) t  where  paiming <=3;