Hive常用的窗口函数【高频重点】

╰+攻爆jí腚メ 2023-10-14 11:56 41阅读 0赞

**1、Hive常用的窗口函数：**

按照功能划分：

● 序号函数： rank() / dense\_rank() / row\_number()

●   分布函数：percent\_rank() / cume\_dist()

●   前后函数：lag() / lead()

●   头尾函数：first\_val() / last\_val()

●   聚合函数+窗⼝函数联合：

○ 求和 sum() over()

○ 求最⼤/⼩ max()/min() over()

○ 求平均 avg() over()

●   其他函数：nth\_value() / nfile()

如上，窗⼝函数的⽤法多种多样，不仅有专⻔的的窗⼝函数，还可以与聚合函数配合使⽤。  他们都要跟窗口子句Over（）一起使用才行，放在over（）函数的前面。

**2、各个窗口函数的含义：**

**RANK() ：**   返回一个数字，该数字表示在窗口内当前行的排名，排序相同时会重复，最后显示的总数不会变。

即如果有两个用户的值相同，则并列第一，那么下一个排名应该是第三名。

如：1-2-3-3-5-6

**DENSE\_RANK() ：**   返回一个数字，该数字表示在窗口内当前行的排名，排序相同时会重复，下一个排名将被跳过，最后显示的总数会减少。

即如果有两个用户的值相同，则并列第一，那么下一个排名应该是第二名。

如：1-2-3-3-4-5

**ROW\_NUMBER() ：**   返回一个数字，该数字表示在窗口内当前行的排名，会根据顺序计算，每行都有一个唯一的行号，从1开始，依次递增。   如：1-2-3-4-5

**percent\_rank（）：**  返回所在窗口中当前行的百分比排名

**cume\_dist():**   如果按升序排列，则统计：⼩于等于当前值的⾏数/总⾏数。

如果是降序排列，则统计：⼤于等于当前值的⾏数/总⾏数。

**LAG(col,n)：**  返回所在窗口中当前行前面第n行的值。

**LEAD(col,n)：**  返回指定窗口中当前行后面第n行的值。

**first\_value（）：**  返回所在窗口中第一行的值。

**last\_value（）：**  返回所在窗口中最后一行的值。

**max（）、min（）、sum（）、avg（）、count（）：**  常用的聚合函数。

**NTILE(n)：**  将窗口划分为n个桶，并返回当前行所在桶的编号。可用于where条件中，传入桶的编号返回桶内的数据。

**特别说明：上述的这些函数必须跟着over（）一起使用，不能单独使用。**

**3、  开窗函数 over( ) 中加order by 和 不加 order by的区别：**

如果使⽤环境为hive，over( ) 开窗函数前分排序函数和聚合函数两种。

当为排序函数，如row\_number(),rank()等时，over中的order by只起到窗⼝内排序作⽤。

当为聚合函数，如max，min，count等时，over中的order by不仅起到窗⼝内排序，还起到窗⼝   内从当前⾏到之前所有⾏的聚合（多了⼀个范围）。

**4、  窗⼝函数与普通聚合函数的区别：**

（1）聚合函数是将多条记录聚合为⼀条；窗⼝函数是每条记录都会执⾏，有⼏条记录执⾏完还是⼏条。

（2）窗⼝函数兼具GROUP BY ⼦句的分组功能以及ORDER BY ⼦句的排序功能。但是，PARTITION   BY ⼦句并不具备 GROUP BY ⼦句的汇总功能。

**5、  OVER( )（开窗函数）**   **：**  用于执行基于窗口的聚合计算的函数，它把数据分为不同的窗口，并在每个窗口上执行聚合计算。

Over（）函数的语法如下：

OVER (
        [ PARTITION BY partition_expression, ... ]
        [ ORDER BY sort_expression [ ASC | DESC ], ... ]
        [ ROWS/RANGE BETWEEN frame_start AND frame_end ]
      )

在over（）函数内可以通过partition by指定分区，也可以通过order by指定排序方式，同时还可以根据 rows/range between and 框架子句指定窗口的大小和位置；这三个部分是可选的参数。

> 指定窗口大小和位置的参数如下：
> 
>  CURRENT ROW   ：当前行 
> 
>  n PRECEDING   ：往前   n   行数据 
> 
>  n FOLLOWING   ：往后   n   行数据 
> 
>  UNBOUNDED   ：起点， 
> 
>  UNBOUNDED PRECEDING   表示从前面的起点， 
> 
>  UNBOUNDED FOLLOWING   表示到后面的终点

示例：
    SELECT col1, col2, SUM(col3) OVER (PARTITION BY col1 ORDER BY col2 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sum_col3
    FROM table;

**6、案例展示：**

**1）数据准备：**   **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

**2）创建**   **hive**   **表并导入数据**

create table business(

name string,

orderdate string,

cost int

) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

load data local inpath "/opt/module/data/business.txt" into table

business;

**3）按需求查询数据**

**（3.1）**   **查询顾客及总人数**

select

name,

count(\*) over ()

from business;

**结果显示：**

![up-3a80759286e3805146fbb408142f80f28c3.png][]

**说明：**  如果没有over（）函数的话，那么查询出来的count（\*）只有一行数据，name字段与count（\*）字段匹配不上，所以会出错。而用了over（）函数之后，count（\*）字段的数据会与每一个name字段的数据相匹配，因为over（）函数将整张表作为了一个窗口。

**（3.2）**   **查询在**   **2017**   **年**   **4**   **月份购买过的顾客及总人数**

select

name,

count(\*) over ()

from business

where substring(orderdate,1,7) = '2017-04'

group by name;

**结果显示：**

![up-08679639ff155cb466ad916f164ed1ef6da.png][]

注明：这里用over（），over（）中没有写其他功能，那它会将整个表作为一个整体计算并对应每行数据显示出来。

**substring（字符串，起始位置，截取多少位）：**  substring（）函数用于截取字符串，字符串位置从0开始计算，注意第三个参数代表截取多少位，，而不是截取到字符串哪个位置，可以不填写表示截取到字符串最后一位。

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

select

name,

orderdate,

cost,

sum(cost) over(partition by name, month(orderdate))

from business;

**结果显示：**

![up-66b1fd1b01b9b314eaf748805b3b9b5b21f.png][]

**说明：**   sum(cost) over(partition by   name,   month(orderdate))   表示，按照相同人名、同一月份进行开窗，数据在各自的窗口进行处理，sum（cost）计算的是各个区内的数据，不是整个表的数据。也就是对相同人名、同一月份进行开窗。

**（3.4）**   **将每个顾客的**   **cost**   **按照日期进行累加**

select name,orderdate,cost,

sum(cost) over() as sample1,--所有行相加

sum(cost) over(partition by name) as sample2,--按 name   分组，组内数据相加

sum(cost) over(partition by name order by orderdate) as sample3,--按 name

分组，组内数据累加

sum(cost) over(partition by name order by orderdate rows between

UNBOUNDED PRECEDING and current row ) as sample4 ,--和 sample3   一样   ,   由起点到

当前行的聚合

sum(cost) over(partition by name order by orderdate rows between 1

PRECEDING and current row) as sample5, --当前行和前面一行做聚合

sum(cost) over(partition by name order by orderdate rows between 1

PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行

sum(cost) over(partition by name order by orderdate rows between current

row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行

from business;

注意：   rows   必须跟在   order by   子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量

**（3.5）**   **查看顾客上次的购买时间**

select

name,

orderdate,

cost,

lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate )   as time1,

lag(orderdate,2) over (partition by name order by orderdate) as   time2

from business;

**结果显示：**

![up-fc0e2fdcb48ced7cc6157bf0feabd791662.png][]

**说明：**  lag（col，n，default\_val）函数要用在over（）前面。表示往前n行取数据，其中前面没有行的，则用default\_val代替。LEAD（col，n，default\_val）函数用法和LAG（col，n，default\_val）函数一样，表示往后n行取数据。

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

select name,

orderdate,

cost,

ntile(5) over(order by orderdate) sorted

from business   ;

**结果显示：**

![up-37b48a7b255ee6f6fa771b286f53bfbaeb1.png][]

**说明：**  ntile（n）函数会将数据等分成n份。

select \* from (

select name,

orderdate,

cost,

ntile(5) over(order by orderdate) sorted

from business ) t

where sorted = 1;

**结果显示：**

![up-76fe4f91a660d34ef7278b8acd8bfc4ab2e.png][]

**4**   **）Rank**   **（窗口函数）**

**1**   **）函数说明：就是对数据进行排序，**   **注意要跟over（）一起用才行,并且Over（）函数必须跟着分区或排序条件。**

RANK()   ：   排序相同时会重复，总数不会变，如：1-2-3-3-5-6

DENSE\_RANK()   ：   排序相同时会重复，总数会减少,如：1-2-3-3-4-5

ROW\_NUMBER()   ：   会根据顺序计算,如：1-2-3-4-5

**例:结果显示的是rank（）函数**

select

name,

subject,

score,

**rank() over(partition by subject order by score desc) rp,**

dense\_rank() over(partition by subject order by score desc) drp,

row\_number() over(partition by subject order by score desc) rmp

from score

Where rp < 4   ;

![up-3c2efcab91b2827f8d7b47325ce4ead9164.png][]

[up-3a80759286e3805146fbb408142f80f28c3.png]: https://oscimg.oschina.net/oscnet/up-3a80759286e3805146fbb408142f80f28c3.png
[up-08679639ff155cb466ad916f164ed1ef6da.png]: https://oscimg.oschina.net/oscnet/up-08679639ff155cb466ad916f164ed1ef6da.png
[up-66b1fd1b01b9b314eaf748805b3b9b5b21f.png]: https://oscimg.oschina.net/oscnet/up-66b1fd1b01b9b314eaf748805b3b9b5b21f.png
[up-fc0e2fdcb48ced7cc6157bf0feabd791662.png]: https://oscimg.oschina.net/oscnet/up-fc0e2fdcb48ced7cc6157bf0feabd791662.png
[up-37b48a7b255ee6f6fa771b286f53bfbaeb1.png]: https://oscimg.oschina.net/oscnet/up-37b48a7b255ee6f6fa771b286f53bfbaeb1.png
[up-76fe4f91a660d34ef7278b8acd8bfc4ab2e.png]: https://oscimg.oschina.net/oscnet/up-76fe4f91a660d34ef7278b8acd8bfc4ab2e.png
[up-3c2efcab91b2827f8d7b47325ce4ead9164.png]: https://oscimg.oschina.net/oscnet/up-3c2efcab91b2827f8d7b47325ce4ead9164.png