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

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;

结果显示：

说明： 如果没有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;

结果显示：

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

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

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

select

name,

orderdate,

cost,

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

from business;

结果显示：

说明： 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;

结果显示：

说明： 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 ;

结果显示：

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

select * from (

select name,

orderdate,

cost,

ntile(5) over(order by orderdate) sorted

from business ) t

where sorted = 1;

结果显示：

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 ;