Hive常用函数介绍(窗口函数)

��

1.窗口函数介绍

窗口函数，也叫也叫OLAP函数（Online Anallytical Processing，联机分析处理），可对数据库数据进行实时分析处理。

2.窗口函数入门

2.1 函数说明

窗口函数，也可以叫做开窗函数，其从本质来看是将 MySQL 中一些复杂的查询封装成了窗口的形式，进行数据统计时使用并且操 作十分方便，窗口函数为统计时使用的聚合函数指定一个聚合的范围。

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化

2.2 函数使用

 源数据：

+----------------+---------------------+----------------+| business.name  | business.orderdate  | business.cost  |+----------------+---------------------+----------------+| jack           | 2021-07-07          | 110            || tony           | 2021-07-08          | 115            || jack           | 2021-08-03          | 123            || tony           | 2021-07-04          | 129            || jack           | 2021-07-05          | 146            || jack           | 2021-10-06          | 142            || tony           | 2021-07-07          | 150            || jack           | 2021-07-08          | 155            || mart           | 2021-10-08          | 162            || mart           | 2021-10-09          | 168            || neil           | 2021-11-10          | 112            || mart           | 2021-10-11          | 175            || neil           | 2021-12-12          | 180            || mart           | 2021-10-13          | 194            |+----------------+---------------------+----------------+

计算每个人消费总额，并且按照名字进行分组

 select name,cost, sum(cost) over(partition by name)  from business;

可以看到，这里有4个人，所以开了4个窗口，并且每个窗口都统计了单个人的总消费金额

了解了基本的用途之后，我们开始来使用几个基础的函数

• CURRENT ROW：当前行
• n PRECEDING：往前 n 行数
• n FOLLOWING：往后 n 行数据
• UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点
• UNBOUNDED FOLLOWING：表示到后面的终点

  我将结合案例进行演示，以便更好的理解

  案例1：求当前日期的消费总额以及截至当前日期的总消费总额。

  思路讲解：利用开窗函数的机制，我们可以在求和的时候进行开窗统计，首先根据日期进行排序，并将上一行的值与本行相加，便可以得出当前日期的总消费金额，以此类推。

   select name,orderdate,cost, sum(cost) over(order by orderdate  range between  unbounded preceding and current row)    from business;

   

  3.窗口函数使用

  3.1取值函数

  first_value()：返回分组类第一个值

  last_value()：返回分组类最后一个值

   select name,cost, first_value(cost) over() `first`, last_value(cost) over() `last` from business;

    nth_value()：返回分组内第N行

   select name,cost, rank() over() `id`, nth_value(cost,2) over() `nth2`, nth_value(cost,4) over() `nth4`, nth_value(cost,6) over() `nth6`  from business;

  lag()：从当前行开始往前去第N行，默认为NULL

  lead()：从当前行开始往后去第N行，默认为NULL

  需求：查看每个人上次以及下次的购买时间

   select name,cost, lag(orderdate,1,null) over(partition by name order by orderdate) `lag`, lead(orderdate,1,null) over(partition by name order by orderdate) `lead`  from business;

   ntile()：用于将分组数据按照顺序切分成n片

  selectname,cost,orderdate,ntile(5) over(order by orderdate) sortedfrombusiness;

   percent_rank()：统计当前行在整个有序窗口中占的百分比数，返回0到1之间变化

  selectname,cost,orderdate,percent_rank() over(order by orderdate)frombusiness;

   

  3.2 排序函数

  rank()：有重复值时候间断排序，例如 1，2，2，4，4，6

  dense_rank()：有重复值时候不间断排序，例如 1，2，2，3，3，4

  row_number()：不间断排序，有重复值时，会根据内置算法进行排序，1，2，3，4

  张三20李四27王五29赵六22田七26马八24黄九23罗十28郑三27吴四20高五25薛六22楚七22朱八27

  用三种方法对年纪进行排序

  selectname,age,rank() over(order by age) `rank`,dense_rank() over(order by age) `dense_rank()`,row_number() over(order by age) `row_number` from stu;

  3.3 聚合函数 

  sum()：求和

  avg()：平均值

  max()：最大值

  min()：最小值

  count()：计数统计

  select name,age,sum(age) over()`sum`,avg(age) over()`avg`,max(age) over()`max`,min(age) over()`min`,count() over()`count`from stu;

   

  hive中关于窗口函数的讲解就介绍这么多，希望各位看客多多支持