Hive(三)--窗口函数

窗口函数

窗口函数也称为OLAP（Online Analytical Processing）函数，是对一组值进行操作，不需要使用Group by子句对数据进行分组，还能在同一行返回原来行的列和使用聚合函数得到的聚合列。
窗口函数是用于分析用的一类函数，要理解窗口函数要先从聚合函数说起。聚合函数是将某列中多行的值合并为一行，比如sum、count等。 而窗口函数则可以在本行内做运算，得到多行的结果，即每一行对应一行的值。 通用的窗口函数可以用下面的语法来概括：
<窗口函数>() over ( [partition by <列>] [order by <排序列>] [ASC/DESC] (rows | range) <范围条件> )
窗口函数按照功能大致分为四类： 计算、取值、排序、序列

数据准备

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

-- 创建表t_order
create table if not exists t_order(
 name  string,-- 姓名
 orderedate string,-- 购买日期
 cost  int -- 购买数量
)
row format delimited fields terminated by ',';

-- 加载数据
--  1、本地加载
load data local inpath '/testHive/day04/order.csv' overwrite into  table t_order;
--  2、HDFS加载
load data inpath '/user/hive/order.csv' overwrite into table t_order;
 

窗口函数语法

• over开窗
  当我们查询一个表中所有时可以用 * 号，查询总量是可以count(),但如果既要查询所有数据又要查询总量直接使用，count(*)显然是不对的，这是就需要用到窗口函数

-- 查询所有明细
select * from  t_order;
-- 查询总量
select  count(*) from  t_order;
-- 同时查出明细和总量
-- 错误的（未使用窗口函数）
select  *,count(*) from  t_order;
-- 使用窗口函数
select  *,count(*) over() from  t_order;

注意：窗口函数是针对每一行数据的，如果over中没有参数，默认的全部结果集
例题：

-- 需求：查询在2018年1月份购买的顾客购买明细及总人数
-- 方法一
select  *,count(*) over ()
from  t_order
where  substr(orderedate,1,7)='2018-01';
-- 方法二
select  *,count(*) over ()
from  t_order 
where  month(orderedate)=1 and year(orderedate)=2018;

• partition by子句
  在over窗口中进行分区可以使用partition by 子句，对某一列进行分区统计，窗口的大小就是分区的大小

-- 需求：查看顾客的购买明细及月购买总额
select *,sum(cost) over (partition by  year(orderedate),month(orderedate))
from t_order;

• order by
  order by子句会对输入的数据强制排序

select *,sum(cost) over (partition by month(orderedate) order by orderedate) from t_order;

• window 子句
  window子句可以对窗口的结果做更细粒度的划分
  n preceding：往前n条
  n following:往后n条
  current row：当前行
  unbounded:起点
  unbounded preceding：从前面的起点开始
  unbounded following：表示到后面的终点结束

select *,
sum(cost) over() as co1,-- 所有行相加
sum(cost) over(partition by name) as co2,-- 按name分组，组内数据相加
sum(cost) over(partition by name order by  orderedate) as co3,-- 按name分组，组内数据累加
sum(cost) over(partition by name order by  orderedate rows between UNBOUNDED PRECEDING AND current row ) as co4,-- 和col3一样，由起点 到当前行的聚合
sum(cost) over(partition by name order by  orderedate rows between 1 PRECEDING AND current row ) as co5,-- 当前行和前面一行做聚合
sum(cost) over(partition by name order by  orderedate rows between 1 PRECEDING and 1 following ) as co6,-- 但行和前边一行及后面一行
sum(cost) over(partition by name order by  orderedate rows between current row and unbounded following) as co7-- 当前行及及后面所有行
from t_order;

取值

• first_value和last_value
  first_value取分区内排序后，截止到当前行，第一个值
  last_value分组内排序后，截止到当前行，最后一个值

select *,
-- 获取第一次购买的时间
 first_value(orderedate) over (partition by name order by orderedate) as  first,
-- 获取最后一次购买的时间
 last_value(orderedate) over (partition by name order by orderedate) as  last
from t_order;

• lag和lead函数
  lag返回当前数据行的上一行数据
  lead返回当前数据行的下一行数据

-- 查询顾客上次购买的时间
select *,
 lag(orderedate) over  (partition by name order by orderedate) as time
from t_order;

结果如图：null是因为按日期排序，当前记录没有上一条记录

-- 需求：查询顾客下次购买的时间
select *,
lead(orderedate) over  (partition by name order by orderedate) as time
from t_order;

结果如图：null是因为按日期排序，当前记录没有下一条记录

排序

• rank
  排序，排序值相同，名次相同，会在排序中留空

-- 按照价格进行排序，名次相同留空
select *,rank() over(order by cost desc) from t_order;

• dense_rank
  排序，排序值相同，名次相同，会在排序中留空

-- 按照价格进行排序，名次相同不留空
select *,dense_rank() over(order by cost desc) from t_order;

• row_number

-- 按名字分组，组内按日期排序给出序号
select *,row_number() over(partition by name order by orderedate) 
from t_order;

• ntile
  用于对分组数据按照顺序切片分成n片，返回当前切片值
  如果切片不均匀，默认增加第一个切片的分布,如下图saml有5条记录，ntile切片数为4，切片不均，多出来的一条默认增加到第一个切片，故saml有两个切片值为1

select *,ntile(4) over  (partition by name) as sliceValue
from t_order order by name,sliceValue;

例题：打印出截止目前每个人一半(50%)的购买细节

select *
from
(select *,ntile(2) over (partition by name) as sliceValue  from t_order) as t_order1
where sliceValue=1;

序列

• cume_dist
  该函数的值为：小于等于当前值的行数/分组内总行数

-- 按名字分组，统计小于等于当前消费金额的次数所占总次数的比例
select name,cost,cume_dist() over(partition by name order by cost) from t_order;

• percent_rank
  当前行的RANK值-1/分组内总行数-1