Hive窗口函数/分析函数

在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的。窗口函数又叫OLAP函数/分析函数，窗口函数兼具分组和排序功能。

窗口函数最重大的关键字是 partition by 和 order by。

具体语法如下：over (partition by xxx order by xxx)

sum,avg,min,max 函数

准备数据

 建表语句:
 create table bigdata_t1(
   cookieid string,
   createtime string,   --day 
   pv int
 ) row format delimited 
 fields terminated by  , ;
 
 加载数据：
load data local inpath  /root/hivedata/bigdata_t1.dat  into table bigdata_t1;

cookie1,2018-04-10,1
cookie1,2018-04-11,5
cookie1,2018-04-12,7
cookie1,2018-04-13,3
cookie1,2018-04-14,2
cookie1,2018-04-15,4
cookie1,2018-04-16,4

开启智能本地模式
SET hive.exec.mode.local.auto=true;

SUM函数和窗口函数的配合使用：结果和ORDER BY相关,默认为升序。

 #pv1
 select cookieid,createtime,pv,
 sum(pv) over(partition by cookieid order by createtime) as pv1 
 from bigdata_t1;
 
 #pv2
 select cookieid,createtime,pv,
 sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
 from bigdata_t1;

#pv3
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid) as pv3
from bigdata_t1;

#pv4
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4
from bigdata_t1;

#pv5
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5
from bigdata_t1;

#pv6
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6
from bigdata_t1;


pv1: 分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号
pv2: 同pv1
pv3: 分组内(cookie1)所有的pv累加
pv4: 分组内当前行+往前3行，如，11号=10号+11号，12号=10号+11号+12号，13号=10号+11号+12号+13号， 14号=11号+12号+13号+14号
pv5: 分组内当前行+往前3行+往后1行，如，14号=11号+12号+13号+14号+15号=5+7+3+2+4=21
pv6: 分组内当前行+往后所有行，如，13号=13号+14号+15号+16号=3+2+4+4=13，14号=14号+15号+16号=2+4+4=10

如果不指定rows between,默认为从起点到当前行;

如果不指定order by，则将分组内所有值累加;

关键是理解rows between含义,也叫做window子句：

preceding：往前

following：往后

current row：当前行

unbounded：起点

unbounded preceding 表明从前面的起点

unbounded following：表明到后面的终点

AVG，MIN，MAX，和SUM用法一样。

row_number,rank,dense_rank,ntile函数

ROW_NUMBER()使用

ROW_NUMBER()从1开始，按照顺序，生成分组内记录的序列。

SELECT 
cookieid,
createtime,p
v,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn 
FROM bigdata_t2;

RANK 和 DENSE_RANK使用

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

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

SELECT 
  cookieid,
  createtime,
  pv,
  RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
  DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
  ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 
FROM bigdata_t2 9WHERE cookieid =  cookie1 ;

NTILE

有时会有这样的需求:如果数据排序后分为三部分，业务人员只关心其中的一部分，如何将这中间的三分之一数据拿出来呢?NTILE函数即可以满足。

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

然后可以根据桶号，选取前或后 n分之几的数据。数据会完整展示出来，只是给相应的数据打标签；具体要取几分之几的数据，需要再嵌套一层根据标签取出。

SELECT 
  cookieid,
  createtime,
  pv,
  NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,
  NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2,
  NTILE(4) OVER(ORDER BY createtime) AS rn3 
 FROM bigdata_t2 9ORDER BY cookieid,createtime;

其他一些窗口函数

lag,lead,first_value,last_value 函数

LAG
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

 #pv1
 SELECT cookieid,
   createtime,
   url,
   ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
   LAG(createtime,1, 1970-01-01 00:00:00 ) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,
   LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time 
   FROM bigdata_t4;
 
  last_1_time: 指定了往上第1行的值，default为 1970-01-01 00:00:00   
                            cookie1第一行，往上1行为NULL,因此取默认值 1970-01-01 00:00:00
                            cookie1第三行，往上1行值为第二行值，2015-04-10 10:00:02
                            cookie1第六行，往上1行值为第五行值，2015-04-10 10:50:01
  last_2_time: 指定了往上第2行的值，为指定默认值
                           cookie1第二行，往上2行为NULL
                           cookie1第四行，往上2行为第二行值，2015-04-10 10:00:02
                           cookie1第七行，往上2行为第五行值，2015-04-10 10:50:01

LEAD

与LAG相反
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

SELECT 
cookieid, 
createtime, 
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LEAD(createtime,1, 1970-01-01 00:00:00 ) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,
LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time 
 FROM bigdata_t4;

FIRST_VALUE

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

SELECT cookieid,
  createtime,
 url,
  ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
 FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1 
FROM bigdata_t4;

LAST_VALUE

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

SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1 
FROM bigdata_t4;

如果想要取分组内排序后最后一个值，则需要变通一下：


SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1,
FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2 
FROM bigdata_t4 
ORDER BY cookieid,createtime;

特别注意order by

如果不指定ORDER BY，则进行排序混乱，会出现错误的结果


SELECT cookieid,
 createtime,
 url,
 FIRST_VALUE(url) OVER(PARTITION BY cookieid) AS first2  
FROM bigdata_t4;

cume_dist,percent_rank 函数

这两个序列分析函数不是很常用，注意：序列函数不支持WINDOW子句

CUME_DIST 和order by的排序顺序有关系

CUME_DIST 小于等于当前值的行数/分组内总行数 order 默认顺序正序升序
列如，统计小于等于当前薪水的人数，所占总人数的比例

SELECT  
dept, 
userid, 
sal, 
CUME_DIST() OVER(ORDER BY sal) AS rn1, CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2  FROM bigdata_t3; 

 rn1: 没有partition,所有数据均为1组，总行数为5，
第一行：小于等于1000的行数为1，因此，1/5=0.2
第三行：小于等于3000的行数为3，因此，3/5=0.6
rn2: 按照部门分组，dpet=d1的行数为3,
第二行：小于等于2000的行数为2，因此，2/3=0.6666666666666666

PERCENT_RANK

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

SELECT cookieid,
 createtime,
 url,
 ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
 LAG(createtime,1, 1970-01-01 00:00:00 ) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,
 LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time 
FROM bigdata_t4;
 
 
last_1_time: 指定了往上第1行的值，default为 1970-01-01 00:00:00   
                         cookie1第一行，往上1行为NULL,因此取默认值 1970-01-01 00:00:00
                        cookie1第三行，往上1行值为第二行值，2015-04-10 10:00:02
                       cookie1第六行，往上1行值为第五行值，2015-04-10 10:50:01
 last_2_time: 指定了往上第2行的值，为指定默认值
                           cookie1第一行，往上2行为NULL
                           cookie1第二行，往上2行为NULL
                           cookie1第四行，往上2行为第二行值，2015-04-10 10:00:02
                           cookie1第七行，往上2行为第五行值，2015-04-10 10:50:01

grouping sets,grouping__id,cube,rollup 函数

这几个分析函数一般用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，列如，分小时、天、月的UV数。

GROUPING SETS

grouping sets是一种将多个group by 逻辑写在一个sql语句中的便利写法。

等价于将不同维度的GROUP BY结果集进行UNION ALL。

GROUPING__ID，表明结果属于哪一个分组集合。

 SELECT  
  month, 
  day, 
  COUNT(DISTINCT cookieid) AS uv, 
  GROUPING__ID  
 FROM bigdata_t5  
 GROUP BY month,day  
GROUPING SETS (month,day)  ORDER BY GROUPING__ID;

 grouping_id表明这一组结果属于哪个分组集合，
  根据grouping sets中的分组条件month，day，1是代表month，2是代表day
等价于
 SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM bigdata_t5 GROUP BY month UNION ALL 
  SELECT NULL as month,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM bigdata_t5 GROUP BY day;

再如：

SELECT 
  month,
  day,
  COUNT(DISTINCT cookieid) AS uv,
  GROUPING__ID 
  FROM bigdata_t5 
  GROUP BY month,day 
  GROUPING SETS (month,day,(month,day)) 
  ORDER BY GROUPING__ID;

  等价于
  SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM bigdata_t5 GROUP BY month 
  UNION ALL 
  SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM bigdata_t5 GROUP BY day
  UNION ALL 
  SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM bigdata_t5 GROUP BY month,day;

CUBE

根据GROUP BY的维度的所有组合进行聚合。

SELECT 
   month,
   day,
   COUNT(DISTINCT cookieid) AS uv,
   GROUPING__ID 
   FROM bigdata_t5 
   GROUP BY month,day 
   WITH CUBE 
   ORDER BY GROUPING__ID;

  等价于
  SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM bigdata_t5
  UNION ALL 
  SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM bigdata_t5 GROUP BY month 
  UNION ALL 
  SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM bigdata_t5 GROUP BY day
  UNION ALL 
  SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM bigdata_t5 GROUP BY month,day;

ROLLUP

是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。

 列如，以month维度进行层级聚合：
   SELECT 
   month,
   day,
   COUNT(DISTINCT cookieid) AS uv,
   GROUPING__ID  
   FROM bigdata_t5 
   GROUP BY month,day
   WITH ROLLUP 
  ORDER BY GROUPING__ID;

  --把month和day调换顺序，则以day维度进行层级聚合：

  SELECT 
  day,
  month,
  COUNT(DISTINCT cookieid) AS uv,
  GROUPING__ID  
  FROM bigdata_t5 
  GROUP BY day,month 
  WITH ROLLUP 
 ORDER BY GROUPING__ID;
  （这里，根据天和月进行聚合，和根据天聚合结果一样，由于有父子关系，如果是其他维度组合的话，就会不一样）