hive之窗口函数

1、窗口函数种类：

　　over()：新建一个字段，指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。

　　current row：当前行。

　　n preceding：往前n行。

　　n following：往后n行

　　unbounded：起点。

　　unbounded preceding：第一行

　　unbounded following：最后一行。

　　lag(col，n)：取前n行的值。

　　lead（col，n）：取后n行的值。

　　ntile（n）：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属组的编号。n为int型。

　　

　　ROWS是物理窗口，从行数上控制窗口的尺寸的；
　　RANGE是逻辑窗口，从列值上控制窗口的尺寸

　　last_value(sal) over(partition by deptno order by sal rows between unbounded preceding and unbounded following)

当省略窗口子句时:
a) 如果存在order by则默认的窗口是unbounded preceding and current row  --当前组的第一行到当前行
b) 如果同时省略order by则默认的窗口是unbounded preceding and unbounded following  --整个组
              
如果省略分组,则把全部记录当成一个组:
a) 如果存在order by则默认窗口是unbounded preceding and current row   --当前组的第一行到当前行
b) 如果这时省略order by则窗口默认为unbounded preceding and unbounded following  --整个组

注意：分区无排序（order by）窗口大小就是每个区中数据，

　　　排序无分区窗口大小的上限就是整个表，此时窗口大小时刻变，但是不会超过上限

　　　分区加排序窗口大小的上限为每个区中数据，此时窗口大小时刻变，但是也不会超过上限

　　总上可知order by每处理一条数据窗口大小就会改变。

2、例题

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

　　表business（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
　（1）查询在2017年4月份购买过的顾客及总人数

　　select * ,count(*) over()

　　from business

　　where orderdate like '2017-04%'

　　group by name；

　　或者

　　select name,count(*) over ()
　　from business
　　where substring(orderdate,1,7) = '2017-04'
　　group by name;

　　解析：先执行from语句，然后执行where语句得出2017年4月只有Jack和mart所在的行，再执行group by语句按照name分成两个组，最后count(*)统计组数即为2人。此时窗口大小为全表但是只有两条数据即两组（因为over括号里为空）

　（2） 查询顾客的购买明细及月购买总额

　　select *,sum(cost) over（partition by month（orderdate））from business；

　　或者

　　select *,sum(cost) over（distribute by month（orderdate））from business；

　　解析：窗口大小为每月那个分区，然后sum(cost)处理每个窗口中的数据。

　（3） 上述的场景,要将cost按照日期进行累加

　　select *,sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) from business;    //partition by和order by

　　或者
　　select *,sum(cost) over(distribute by name sort by orderdate rows between unbounded preceding and current row) from business;    //distribute by和sort by

　　解析：此时以name来分组，即窗口大小为rows between unbounded preceding and current row，聚集函数sum(cost)对窗口中数据进行操作。

　（4） 查询顾客上次的购买时间

　　select *,lag(orderdate,1) over(distribute by name sort by orderdate) from business;

　　解析：先按name分组，再来按orderdate排序，窗口子句lag(orderdate,1)，取当前行的前面一行。

   （5） 查询前20%时间的订单信息

　　select * from (
   　　 select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
  　　  from business
　　) t
　　where sorted = 1;

　　解析：先排序，然后平均对排序后的数据进行标签ntile(5)取名字段为sorted，最后选取标签为1 的行。

注意：SQL有order by而over里面没有，则先SQL排序在运行over

　　   sql有排序（倒序），over()排序子句有，窗口子句无，此时的运算是：sql先选数据但是不排序，而后排序子句先排序并进行分析函数处理（窗口默认为第一行到当前行），最后再进行sql排序

3、新增加序号列NTILE, ROW_NUMBER(), RANK(), DENSE_RANK()

　　HiveQL语句如下：

　　select *,
　　ntile(5) over(partition by name order by orderdate) as ntile1,　　　　　　　　　　#先分区，然后在分区里排序，每个分区从1开始标记。
　　ntile(5) over(order by orderdate) as ntile2,　　　　　　　　　　　　　　　　　  #对整个表先排序，然后将整个表分成五份，第一份标记为1，第二份标记为2，以此类推。
　　row_number() over(partition by name order by orderdate) as row_number2,　　　#同上是标记规则改一下
　　dense_rank() over(partition by name order by orderdate) as dense_rank3,
　　rank() over(partition by name order by orderdate)as rank4
　　from business;
　　row_nubmer(),顺序排序，这个排序函数的特点是相同数据,先查出的排名在前,没有重复值。

　　dense_rank(),这个是连续排序的,比如两条并列第1,则两行数据(这里为rank列)都标为1,下一个排名将是第2名。

　　rank()函数，是跳跃排序,相同数据(这里为sal列相同)排名相同,比如并列第1,则两行数据(这里为rank列)都标为1,下一位将是第3名.中间的2被直接跳过了。

　　ROW_NUMBER():1，2，3，4，5

　　DENSE_RANK()：1，2，3，3，4

　　RANK()：1，2，3，3，5

扩展利用窗口函数和row_number求分组topN，表stu_score(name,score)

　　select name,score
         from(
         　　select name,score,row_number() over(partition by name order by score desc) rank
    　　     from stu_score
         　　)tmp_table
    　where tmp_table.rank<=3;