HIVE --- 窗口函数

àì夳堔傛蜴生んèń 2024-03-26 13:06 87阅读 0赞

**目录**

简介

概念

数据准备

聚合函数+over

partition by子句

order by子句

window子句

窗口函数中的序列函数

ntile

rank、dense\_rank、row\_number

LAG、LEAD

first\_value、last\_value

--------------------

### 简介 ###

本文主要介绍hive中的窗口函数.hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析（在线分析处理）。

### 概念 ###

我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的.但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数.

**在深入研究Over字句之前，一定要注意：在SQL处理中，窗口函数都是最后一步执行，而且仅位于Order by字句之前。**

### 数据准备 ###

我们准备一张orders表,字段分别为name,orderdate,cost.数据内容如下:

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

### 聚合函数+over ###

常见聚合函数（count(),sum(),max(),min(),avg()……）

查询在2015年4月份购买过的顾客及总人数,我们便可以使用窗口函数去去实现

select name,count(*) over ()
    from orders
    where substring(orderdate,1,7) = '2015-04'

![f52599dc64524d3488465362c750e166.png][]

在2015年4月一共有5次购买记录,mart购买了4次,jack购买了1次.事实上,大多数情况下,我们是只看去重后的结果的.针对于这种情况,我们有两种实现方式

第一种：

select distinct name, count(*) over ()
    from orders
    where substring(orderdate, 1, 7) = '2015-04';

![d765de90b47948c9922b4bad2ae8ca86.png][]

第二种：

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

![b444cbb466594f4581bf93eb6de7f8a2.png][]

### partition by子句 ###

Over子句之后第一个提到的就是Partition By.Partition By子句也可以称为查询分区子句，非常类似于Group By，都是将数据按照边界值分组，而Over之前的函数在每一个分组之内进行，如果超出了分组，则函数会重新计算.

顾客的购买明细及月购买总额,可以执行如下的sql

select name,orderdate,cost,sum(cost) over(partition by month(orderdate))
    from orders

![c50a0ee621fc47fe82422d0b855716d6.png][]

### order by子句 ###

上述的场景,假如我们想要将cost按照月进行累加.这时我们引入order by子句.

order by子句会让输入的数据强制排序（文章前面提到过，窗口函数是SQL语句最后执行的函数，因此可以把SQL结果集想象成输入数据）。Order By子句对于诸如Row\_Number()，Lead()，LAG()等函数是必须的，因为如果数据无序，这些函数的结果就没有任何意义。因此如果有了Order By子句，则Count()，Min()等计算出来的结果就没有任何意义。

顾客的购买明细及月购买总额以及过程

select name, orderdate, cost, sum(cost) over (partition by name
     order by orderdate) sum from orders;

![da7b65eab69a43b2b120ec23c37cb016.png][]

### window子句 ###

我们在上面已经通过使用partition by子句将数据进行了分组的处理.如果我们想要更细粒度的划分，我们就要引入window子句了.

我们首先要理解两个概念:  
\- 如果只使用partition by子句,未指定order by的话,我们的聚合是分组内的聚合.  
\- 使用了order by子句,未使用window子句的情况下,默认从起点到当前行.

**当同一个select查询中存在多个窗口函数时,他们相互之间是没有影响的.每个窗口函数应用自己的规则.**

window子句：

\- preceding：往前

\- following：往后

\- current row：当前行

\- unbounded：起点

\- unbounded preceding：表示从前面的起点

\- unbounded following：表示到后面的终点

按照name进行分区,按照购物时间进行排序,做cost的累加.

select name,
           orderdate,
           cost,
           -- 全表数据的cost总和
           sum(cost) over ()                                             as row1,
           --个人消费总和
           sum(cost) over (partition by name)                            as row2,
           sum(cost) over (partition by name
               rows between unbounded preceding and unbounded following) as row22,
           --截止到当前日期个人消费总和
           sum(cost) over (partition by name order by orderdate)         as row3,
           --同row3
           sum(cost) over (partition by name order by orderdate
               rows between unbounded preceding and current row)         as row4,
           --当前消费额与上一次消费额的总和
           sum(cost) over (partition by name order by orderdate
               rows between 1 preceding and current row )                as row5,
           --当前消费与上一次消费以及后一次的消费总额
           sum(cost) over (partition by name order by orderdate
               rows between 1 preceding and 1 following )                as row6,
           --当前消费额与后面所有消费的总额
           sum(cost) over (partition by name order by orderdate
               rows between current row and unbounded following)         as row7,
           --当前日期以及后面三次的消费总额
           sum(cost) over (partition by name order by orderdate
               rows between current row and 3 following)                 as row8
    from orders;

![1f1901e0808a485a9dce79377c1f11c8.png][]

### 窗口函数中的序列函数 ###

主要序列函数是不支持window子句的.

hive中常用的序列函数有下面几个:

#### ntile ####

*  ntile(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
 *  ntile不支持rows between，比如 ntile(2) OVER(partition by name order by orderdate rows between 3 preceding and current row)
 *  如果切片不均匀，默认增加第一个切片的分布

select name,
           orderdate,
           cost,
           -- 全局数据切片
           ntile(5) over ()                                      as row1,
           -- 按照name进行分组,在分组内将数据切成3份
           ntile(3) over (partition by name)                     as row2,
           -- 全局按照name升序排列,数据切成3份
           ntile(3) over (order by name )                        as row3,
           -- 按照name分组，在分组内按照cost升序排列,数据切成3份
           ntile(3) over (partition by name order by cost desc ) as row4
    from orders;

![0603533b9eae4e5abafaf973e4889af8.png][]

#### rank、dense\_rank、row\_number ####

<table> 
 <thead> 
  <tr> 
   <th>函数名</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>RANK（）</td> 
   <td>分区中当前行的排名，带有间隙</td> 
  </tr> 
  <tr> 
   <td>DENSE_RANK（）</td> 
   <td>分区中当前行的排名，无间隙</td> 
  </tr> 
  <tr> 
   <td>ROW_NUMBER（）</td> 
   <td>其分区中的当前行数</td> 
  </tr> 
 </tbody> 
</table>

row\_number()从1开始，按照顺序，生成分组内记录的序列,row\_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列  
rank() 生成数据项在分组中的排名，排名相等会在名次中留下空位  
dense\_rank() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

\*\*注意：  
rank和dense\_rank的区别在于排名相等时会不会留下空位.\*\*

为了更直观的看出他们区别，在加一行数据

insert into orders
    values ('jack', '2015-02-04', 42);

select name,
           orderdate,
           cost,
           row_number() over (partition by name order by cost desc ) as row1,
           rank() over (partition by name order by cost desc )       as row2,
           dense_rank() over (partition by name order by cost desc ) as row3
    from orders;

![eef4a413464444049ba4e342ef25c24a.png][]

#### LAG、LEAD ####

<table> 
 <tbody> 
  <tr> 
   <th>函数名</th> 
   <th>描述</th> 
  </tr> 
  <tr> 
   <td>LAG（）</td> 
   <td>分区内滞后当前行的参数值</td> 
  </tr> 
  <tr> 
   <td>LEAD（）</td> 
   <td>分区内当前行前导行的参数值</td> 
  </tr> 
 </tbody> 
</table>

select name,
           cost,
           lag(orderdate, 1, '1900-01-01') over (partition by name order by orderdate) as row1,
           orderdate,
           lead(orderdate) over (partition by name order by orderdate)                 as row2
    from orders;

![7cf572f2db3c48dd9a3a0a29d4fa37f5.png][]

#### first\_value、last\_value ####

first\_value取分组内排序后，**截止到当前行**，第一个值  
last\_value取分组内排序后，**截止到当前行**，最后一个值

select name,
           orderdate,
           cost,
           first_value(orderdate) over (partition by name order by orderdate) as row1,
           last_value(orderdate) over (partition by name order by orderdate)  as row2
    from orders;

![b56afd754e42488cbcdf89b9dd037a86.png][]

**关于Parttion by：**

Parttion by关键字是Oracle中分析性函数的一部分，用于给结果集进行分区。它和聚合函数Group by不同的地方在于它只是将原始数据进行名次排列，能够返回一个分组中的多条记录（记录数不变），而Group by是对原始数据进行聚合统计，一般只有一条反映统计值的结果（每组返回一条）。

> TIPS
> 
>  *  使用rank over()的时候，空值是最大的，如果排序字段为null, 可能造成null字段排在最前面，影响排序结果。
>  *  可以这样： rank over(partition by course order by score desc **nulls last**)

**总结：**

在使用排名函数的时候需要注意以下三点：

1、排名函数必须有 OVER 子句。

2、排名函数必须有包含 ORDER BY 的 OVER 子句。

3、分组内从1开始排序。

[f52599dc64524d3488465362c750e166.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/092a334160824cbab3f6e8cf543e4432.png
[d765de90b47948c9922b4bad2ae8ca86.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/bc98337b240b45008e3f9c668e32fc91.png
[b444cbb466594f4581bf93eb6de7f8a2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/b778f1bf54634bca815e2546bf44f5b9.png
[c50a0ee621fc47fe82422d0b855716d6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/e311cd7987a349af815843b8349552b3.png
[da7b65eab69a43b2b120ec23c37cb016.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/30fb38576b454aa0bd7629911f91d5d2.png
[1f1901e0808a485a9dce79377c1f11c8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/cb5125f8b48445c58e564421da167b77.png
[0603533b9eae4e5abafaf973e4889af8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/274e1c9b25e4479b8586feb85bf8383e.png
[eef4a413464444049ba4e342ef25c24a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/60af8c715d1a419db6e0886ba64ea35a.png
[7cf572f2db3c48dd9a3a0a29d4fa37f5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/ff0fa95d71734e29aa426c732519e8ab.png
[b56afd754e42488cbcdf89b9dd037a86.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/68e709b8984e487e9f21f2fe1ffc1d8d.png