Hive之窗口函数

最新推荐文章于 2025-05-22 21:43:24 发布

转载最新推荐文章于 2025-05-22 21:43:24 发布 · 178 阅读

Hive 专栏收录该内容

14 篇文章

订阅专栏

本文深入讲解SQL窗口函数的使用，包括SUM、COUNT、NTILE、ROW_NUMBER、RANK、DENSE_RANK、LAG、LEAD、FIRST_VALUE和LAST_VALUE等函数的实战应用。通过具体案例，演示如何利用窗口函数进行数据聚合、排名、趋势分析等操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

有关开窗函数的一些练习与案例https://blog.youkuaiyun.com/wangpei1949/article/details/81437574

概念：执行SQL时都会遇到sum()、avg()、max()等等函数，这些函数后面往往需要添加条件，这个时候便引入了窗口函数over（）

给定一堆数据

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

这是order表

在hive中建立一张表t_window,将数据插入进去.

实例

聚合函数+over

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

有两种方法：

第一种：

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

第二种：

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

得到的结果：

name count_window_0 
mart 2 
jack 2

partition by子句

我们想要去看顾客的购买明细及月购买总额,可以执行如下的sql

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

执行结果如下:

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

order by子句

我们在上面的代码中加入order by

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

得到的结果如下：(order by默认情况下聚合从起始行当当前行的数据)

name    orderdate   cost    sum_window_0
jack    2015-01-01  10  10
tony    2015-01-02  15  25
tony    2015-01-04  29  54
jack    2015-01-05  46  100
tony    2015-01-07  50  150
jack    2015-01-08  55  205
jack    2015-02-03  23  23
jack    2015-04-06  42  42
mart    2015-04-08  62  104
mart    2015-04-09  68  172
mart    2015-04-11  75  247
mart    2015-04-13  94  341
neil    2015-05-10  12  12
neil    2015-06-12  80  80

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

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

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

window子句：
- PRECEDING：往前
- FOLLOWING：往后
- CURRENT ROW：当前行
- UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点

我们按照name进行分区,按照购物时间进行排序,做cost的累加.
如下我们结合使用window子句进行查询

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 t_window;

得到查询结果如下：

name    orderdate   cost    sample1 sample2 sample3 sample4 sample5 sample6 sample7
jack    2015-01-01  10  661 176 10  10  10  56  176
jack    2015-01-05  46  661 176 56  56  56  111 166
jack    2015-01-08  55  661 176 111 111 101 124 120
jack    2015-02-03  23  661 176 134 134 78  120 65
jack    2015-04-06  42  661 176 176 176 65  65  42
mart    2015-04-08  62  661 299 62  62  62  130 299
mart    2015-04-09  68  661 299 130 130 130 205 237
mart    2015-04-11  75  661 299 205 205 143 237 169
mart    2015-04-13  94  661 299 299 299 169 169 94
neil    2015-05-10  12  661 92  12  12  12  92  92
neil    2015-06-12  80  661 92  92  92  92  92  80
tony    2015-01-02  15  661 94  15  15  15  44  94
tony    2015-01-04  29  661 94  44  44  44  94  79
tony    2015-01-07  50  661 94  94  94  79  79  50

NTILE
NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值

NTILE不支持ROWS BETWEEN，
比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

如果切片不均匀，默认增加第一个切片的分布
这个函数用什么应用场景呢?假如我们想要每位顾客购买金额前1/3的交易记录,我们便可以使用这个函数.

select name,orderdate,cost,
       ntile(3) over() as sample1 , --全局数据切片
       ntile(3) over(partition by name), -- 按照name进行分组,在分组内将数据切成3份
       ntile(3) over(order by cost),--全局按照cost升序排列,数据切成3份
       ntile(3) over(partition by name order by cost ) --按照name分组，在分组内按照cost升序排列,数据切成3份
from t_window

得到的数据如下：

name    orderdate   cost    sample1 sample2 sample3 sample4
jack    2015-01-01  10  3   1   1   1
jack    2015-02-03  23  3   1   1   1
jack    2015-04-06  42  2   2   2   2
jack    2015-01-05  46  2   2   2   2
jack    2015-01-08  55  2   3   2   3
mart    2015-04-08  62  2   1   2   1
mart    2015-04-09  68  1   2   3   1
mart    2015-04-11  75  1   3   3   2
mart    2015-04-13  94  1   1   3   3
neil    2015-05-10  12  1   2   1   1
neil    2015-06-12  80  1   1   3   2
tony    2015-01-02  15  3   2   1   1
tony    2015-01-04  29  3   3   1   2
tony    2015-01-07  50  2   1   2   3

如上述数据，我们去sample4 = 1的那部分数据就是我们要的结果

ROW_NUMBER

ROW_NUMBER() –从1开始，按照顺序，生成分组内记录的序列
–比如，按照pv降序排列，生成分组内每天的pv名次
ROW_NUMBER() 的应用场景非常多，再比如，获取分组内排序第一的记录;获取一个session中的第一条refer等。

select
  cookieid,
  createtime,
  pv,
  row_number() over (partition by cookieid order by pv desc) as rn
from cookie.cookie2;

查询结果：

cookie1	2015-04-12	7	1
cookie1	2015-04-11	5	2
cookie1	2015-04-16	4	3
cookie1	2015-04-15	4	4
cookie1	2015-04-13	3	5
cookie1	2015-04-14	2	6
cookie1	2015-04-10	1	7
cookie2	2015-04-15	9	1
cookie2	2015-04-16	7	2
cookie2	2015-04-13	6	3
cookie2	2015-04-12	5	4
cookie2	2015-04-11	3	5
cookie2	2015-04-14	3	6
cookie2	2015-04-10	2	7

RANK 和 DENSE_RANK

RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位
DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

select
  cookieid,
  createtime,
  pv,
  rank() over (partition by cookieid order by pv desc) as rn1,
  dense_rank() over (partition by cookieid order by pv desc) as rn2,
  row_number() over (partition by cookieid order by pv desc) as rn3
from cookie2 
where cookieid='cookie1';

执行结果：

row_number：按顺序编号，不留空位
rank：按顺序编号，相同的值编相同号，留空位
dense_rank：按顺序编号，相同的值编相同的号，不留空位

cookie1	2015-04-12	7	1	1	1
cookie1	2015-04-11	5	2	2	2
cookie1	2015-04-16	4	3	3	3
cookie1	2015-04-15	4	3	3	4
cookie1	2015-04-13	3	5	4	5
cookie1	2015-04-14	2	6	5	6
cookie1	2015-04-10	1	7	6	7

LAG和LEAD函数

这两个函数为常用的窗口函数,可以返回上下数据行的数据.
以我们的订单表为例,假如我们想要查看顾客上次的购买时间可以这样去查询

time1取的为按照name进行分组,分组内升序排列,取上一行数据的值.

time2取的为按照name进行分组，分组内升序排列,取上面2行的数据的值,注意当lag函数为设置行数值时,默认为1行.未设定取不到时的默认值时,取null值.

lead函数与lag函数方向相反,取向下的数据.

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

first_value和last_value

first_value取分组内排序后，截止到当前行，第一个值
last_value取分组内排序后，截止到当前行，最后一个值

select name,orderdate,cost,
first_value(orderdate) over(partition by name order by orderdate) as time1,
last_value(orderdate) over(partition by name order by orderdate) as time2
from t_window

查询结果

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