窗口函数（开窗函数）

窗口函数（开窗函数）

1、相关函数说明

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。这个是重重之重开窗函数。
这下面四个是限制行数的，写在over()函数里面：
CURRENT ROW：当前行
n PRECEDING：往前 n 行数据
n FOLLOWING：往后 n 行数据
UNBOUNDED：起点

	UNBOUNDED PRECEDING 表示从前面的起点，
	UNBOUNDED FOLLOWING 表示到后面的终点 

下面这三个是写在 over() 函数前面的：
LAG(col,n,default_val)：往前第 n 行数据
LEAD(col,n, default_val)：往后第 n 行数据
NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对 于每一行，NTILE 返回此行所属的组的编号。
注意：n 必须为 int 类型。

下面是总结的语法：分为用行限制和用特殊的值限制
用行限制：rows betweent 3 preceding and current row 这个语法是，从当前行的前三行到当前行
unbounded preceding: 当前行的所有行
n preceding： 当前行的n 行
current row：当前行
n following：表示当前行之后的n行
unbounded following：表示当前行之后的所有行
用特殊的值限制：range between interval ‘3’ hour preceding and current row 这个语法是，计算三个小时前的数据当当前行

2、数据准备

name，orderdate，cost 字段。姓名，下单时间，还有花的钱

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

在 linux 里面，创建一个 /opt/datas/business.txt 文件，把这些数据写进去

创建hive表，并且导入数据
输入命令：nohup /opt/hive/bin/hive --service hiveserver2 & 启动hiveserver2
输入命令：hive --service metastore & 启动hive服务
linux创建表插入数据

create table business( 
name string,
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/opt/datas/business.txt" into table business;

表和数据就创建好了

3、需求

（1）查询在 2017 年 4 月份购买过的顾客及总人数
（2）查询顾客的购买明细及月购买总额
（3）上述的场景, 将每个顾客的 cost 按照日期进行累加
（4）查询每个顾客上次的购买时间
（5）查询前 20%时间的订单信息

4、按照需求查询数据

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

首先我们先进行筛选，2017-04的数据，用substring() substring()函数或者like模糊查询都可以，substring() 函数里面有三个参数，第一个是要截取的字段，第二个参数是从什么位置开始，第三个参数是截取几位。

--查询在2017年4月份购买过的顾客以及总人数
-- substring() 函数里面三个参数，第一个参数是字段，第二个参数是从索引0开始，然后第三个参数是取几位

select substring(orderdate,0,7)  from business

select * from business where substring(orderdate,0,7) = '2017-04'

我们先对名字进行分组，都是以名字为分组，然后count(name)，这个是统计名字出现的次数，就完成了，但是这个样子不是想要的，想要的是4月有哪些人买了，然后买了的总人数，这里就两个人，所以总人数是2，我们这里求的是每个人自己买的次数。
输入命令： select name,count(name) from business where substring(orderdate,0,7) = '2017-04' group by name;

我们加上 over() 开窗现在就对了
输入命令：select name,count(name) over() from business where substring(orderdate,0,7) = '2017-04' group by name;

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

我们先查询顾客的购买明细以及购买总额
相当于是把 按照 name 分区，每个用户是一个整体。然后求总额
输入命令： select name,orderdate,cost,sum(cost) over(partition by name) from business;

我们上面求到了每个用户的总额，接下来我们求每个用户的月购买总额
其实只需要加一个，over() 里面在加一个 month(orderdate)，这个 month 是一个函数用来求月份的，现在的结果就是每个用户的每一个月的购买总额了。

输入命令：select name,orderdate,cost,sum(cost) over(partition by name,month(orderdate)) from business;

(3)、上述的场景, 将每个顾客的 cost 按照日期进行累加

我们这里要的效果是，按照人分组，然后按照日期进行累加，比如第一天是50，第二天的是60.那么第二天的值就是50+60，依次按照日期累加，这里就是直接开窗里面。按照name进行分组，然后对 orderdate 进行排序。
输入命令：select name,orderdate,cost,sum(cost) over(partition by name order by orderdate) from business;

这下面是很多种方法的实现，可以了解一下

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

注意：rows 必须跟在 order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分 区中的数据行数量

(4) 查询每个顾客上次的购买时间

这里按照顾客进行分组，然后我们取上一次的购买时间，我们这里就要用到这两个方法了。
lag() 里面三个参数，第一个是哪一个字段，然后第二个参数是往前多少行，我们这里是1，然后还有一个参数是默认值，可有可无。

我们这里是使用，lag() 函数，取前一个时间的数据，然后我们这里对那name，每一个顾客进行分组，然后一定要 order by 进行排序，因为我们这里是取前一个时间，肯定是要排好序，
输入命令： select name,orderdate,lag(orderdate,1) over(partition by name order by orderdate) from business;

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

这个就要用到，剩下的哪个 ntile() 函数了，我们先使用 ntile 函数分为五个组 每个组都有一个编号，然后开窗，对时间进行排序，给他一个别名 groupID，然后我们把它作为子查询，外面再进行查询，因为刚刚给了他一个别称 groupID，然后我们直接还分为了5个组，一共14条数据，前20%也就是1，因为分成了5组，我们分了五个组，所以给他where条件为groupID = 1就解决了
输入命令： select cost,orderdate,cost from (select name,orderdate,cost,ntile(5) over(order by orderdate) groupId from business t1) where groupID = 1;

5、Rank

rank() 排序相同时会重复，总数不会变
dense_rank() 排序相同时会重复，总数会减少
row_number() 会根据顺序计算

1、准备数据

vim /opt/datas/score.txt 目录下，创建一个 score.txt 文件

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

创建表并且导入数据
create table score( name string, subject string, score int) row format delimited fields terminated by "\t"; load data local inpath '/opt/datas/score.txt' into table score;

2、需求

计算每门学科排名
我们先全局排，所有学科放在里面进行排
输入命令：select *,rank() over(order by score) from score;

对学科进行分组，然后对分数进行排序
输入命令：select *,row_number() over(partition by subject order by score) from score;
要是我们要取每个学科的前三名的话，我们先用 rank() 函数进行排序，开窗函数对学科进行分组，我们这里是求前三名所以我们这里是倒序，给他一个别称 rk，然后进行子查询，把这个放进去，然后where rk <= 3 这个求出来的就是 每个学科的前三名了。
select name,subject,score from ( select *,row_number() over(partition by subject order by score desc ) rk from score t1) where rk <= 3;

+