常用的窗口函数

最新推荐文章于 2024-06-17 15:36:01 发布

简之

最新推荐文章于 2024-06-17 15:36:01 发布

阅读量2w

点赞数 24

分类专栏：大数据文章标签：大数据数据库 mysql

本文链接：https://blog.csdn.net/olizxq/article/details/118652943

版权

大数据专栏收录该内容

13 篇文章 4 订阅

订阅专栏

1、什么是窗口函数

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

作用：

解决排名问题，e.g.每个班级按成绩排名
解决TOPN问题，e.g.每个班级前两名的学生

语法：

select 窗口函数 over (partition by 用于分组的列名， order by 用于排序的列名

分类：

排名函数：row_number()，rank()，dense_rank()
聚合函数：max()，min()，count()，sum()，avg()，median()
向前向后取值：lag()，lead()
百分位：percent_rank()
取值函数：first_value()，last_value()，nth_value()
分箱函数：ntile()

注意：窗口函数是对where后者group by子句处理后的结果进行操作，因此按照SQL语句的运行顺序，窗口函数一般放在select子句中。

2、窗口函数的使用

2.1 排名函数

下面以员工的薪酬表为例说明：

函数	用法
`row_number()`	同薪不同名，相当于行号，例如3000、2000、2000、1000排名后为1、2、3、4
`rank()`	同薪同名，有跳级，例如3000、2000、2000、1000排名后为1、2、2、4
`dense_rank()`	同薪同名，无跳级，例如3000、2000、2000、1000排名后为1、2、2、3

row_number()

以下示例按 SELLERID 对表进行分区并按 QTY 对每个分区进行排序（按升序顺序），然后为每个行分配一个行号。在应用窗口函数结果后，对结果进行排序。

select salesid, sellerid, qty, 
	   row_number() over (partition by sellerid
 						  order by qty asc) as row
from winsales
order by 2,4;

 salesid | sellerid | qty | row
---------+----------+-----+-----
   10006 |        1 |  10 |   1
   10001 |        1 |  10 |   2
   10005 |        1 |  30 |   3
   20001 |        2 |  20 |   1
   20002 |        2 |  20 |   2
   30001 |        3 |  10 |   1
   30003 |        3 |  15 |   2
   30004 |        3 |  20 |   3
   30007 |        3 |  30 |   4
   40005 |        4 |  10 |   1
   40001 |        4 |  40 |   2

rank()

以下示例按销量对表进行排序（默认情况下按升序顺序），并为每个行分配一个排名。排名值 1 为排名最高的值。在应用窗口函数结果后，对结果进行排序：

select salesid, qty,
	   rank() over (order by qty) as rnk
from winsales
order by 2,1;

salesid | qty | rnk
--------+-----+-----
10001 |  10 |  1
10006 |  10 |  1
30001 |  10 |  1
40005 |  10 |  1
30003 |  15 |  5
20001 |  20 |  6
20002 |  20 |  6
30004 |  20 |  6
10005 |  30 |  9
30007 |  30 |  9
40001 |  40 |  11

dense_rank()

以下示例按销量对表进行排序（按降序顺序），并将密集排名和常规排名分配给每个行。在应用窗口函数结果后，对结果进行排序。

select salesid, qty,
       dense_rank() over(order by qty desc) as d_rnk,
       rank() over(order by qty desc) as rnk
from winsales
order by 2,1;

salesid | qty | d_rnk | rnk
---------+-----+-------+-----
10001 |  10 |     5 |   8
10006 |  10 |     5 |   8
30001 |  10 |     5 |   8
40005 |  10 |     5 |   8
30003 |  15 |     4 |   7
20001 |  20 |     3 |   4
20002 |  20 |     3 |   4
30004 |  20 |     3 |   4
10005 |  30 |     2 |   2
30007 |  30 |     2 |   2
40001 |  40 |     1 |   1

2.2 聚合函数

函数	用法
`max()`	最大值
`min()`	最小值
`avg()`	平均值
`sum()`	求和
`median()`	中位数

以下示例显示从数据窗口的开头显示销售 ID、数量和最大数量：

select salesid, qty,
	   max(qty) over (order by salesid rows unbounded preceding) as max
from winsales
order by salesid;

salesid | qty | max
---------+-----+-----
10001 |  10 |  10
10005 |  30 |  30
10006 |  10 |  30
20001 |  20 |  30
20002 |  20 |  30
30001 |  10 |  30
30003 |  15 |  30
30004 |  20 |  30
30007 |  30 |  30
40001 |  40 |  40
40005 |  10 |  40

2.3 向前向后取值

函数	用法
`lag(field,n,default)`	在一次查询中取出当前行的同一字段（field参数）的前面第n行的数据，如果没有用default代替
`lead(field,n,default)`	在一次查询中取出当前行的同一字段（field参数）的后面第n行的数据，如果没有用default代替

通过这两个函数可以在一次查询中取出同一字段的前N行的数据（lag）和后N行的数据（lead）作为独立的列，从而更方便地进行数据过滤，该操作可代替表的自联接，且效率更高。

lag()

以下示例显示已售给买家 ID 为 3 的买家的票数以及买家 3 的购票时间。要将每个销售与买家 3 的上一销售进行比较，查询要返回每个销售的上一销量。由于 1/16/2008 之前未进行购买，则第一个上一销量值为 null：

select buyerid, saletime, qtysold,
lag(qtysold,1) over (order by buyerid, saletime) as prev_qtysold
from sales where buyerid = 3 order by buyerid, saletime;

buyerid |      saletime       | qtysold | prev_qtysold
---------+---------------------+---------+--------------
3 | 2008-01-16 01:06:09 |       1 |
3 | 2008-01-28 02:10:01 |       1 |            1
3 | 2008-03-12 10:39:53 |       1 |            1
3 | 2008-03-13 02:56:07 |       1 |            1
3 | 2008-03-29 08:21:39 |       2 |            1
3 | 2008-04-27 02:39:01 |       1 |            2
3 | 2008-08-16 07:04:37 |       2 |            1
3 | 2008-08-22 11:45:26 |       2 |            2
3 | 2008-09-12 09:11:25 |       1 |            2
3 | 2008-10-01 06:22:37 |       1 |            1
3 | 2008-10-20 01:55:51 |       2 |            1
3 | 2008-10-28 01:30:40 |       1 |            2

lead()

以下示例提供了 SALES 表中于 2008 年 1 月 1 日与 1 月 2 日已售票的事件的佣金以及为后续销售中售票所付的佣金。

select eventid, commission, saletime,
lead(commission, 1) over (order by saletime) as next_comm
from sales where saletime between '2008-01-01 00:00:00' and '2008-01-02 12:59:59'
order by saletime;

eventid | commission |      saletime       | next_comm
---------+------------+---------------------+-----------
6213 |      52.05 | 2008-01-01 01:00:19 |    106.20
7003 |     106.20 | 2008-01-01 02:30:52 |    103.20
8762 |     103.20 | 2008-01-01 03:50:02 |     70.80
1150 |      70.80 | 2008-01-01 06:06:57 |     50.55
1749 |      50.55 | 2008-01-01 07:05:02 |    125.40
8649 |     125.40 | 2008-01-01 07:26:20 |     35.10
2903 |      35.10 | 2008-01-01 09:41:06 |    259.50
6605 |     259.50 | 2008-01-01 12:50:55 |    628.80
6870 |     628.80 | 2008-01-01 12:59:34 |     74.10
6977 |      74.10 | 2008-01-02 01:11:16 |     13.50
4650 |      13.50 | 2008-01-02 01:40:59 |     26.55
4515 |      26.55 | 2008-01-02 01:52:35 |     22.80
5465 |      22.80 | 2008-01-02 02:28:01 |     45.60
5465 |      45.60 | 2008-01-02 02:28:02 |     53.10
7003 |      53.10 | 2008-01-02 02:31:12 |     70.35
4124 |      70.35 | 2008-01-02 03:12:50 |     36.15
1673 |      36.15 | 2008-01-02 03:15:00 |   1300.80
...

2.4 百分位

函数	用法
`percent_rank()`	计算分组内每一行的百分比排名

以下示例计算每个卖家的销售数量的百分比排名：

select sellerid, qty, 
	   percent_rank() over (partition by sellerid order by qty) 
from winsales;

sellerid	qty		percent_rank
----------------------------------------
1		10.00		0.0
1		10.64		0.5
1		30.37		1.0
3		10.04		0.0
3		15.15		0.33
3		20.75		0.67
3		30.55		1.0
2		20.09		0.0
2		20.12		1.0
4		10.12		0.0
4		40.23		1.0

2.5 取值函数

函数	用法
`first_value()`	分组内第一行的值
`last_value()`	分组内最后一行的值
`nth_value(expr, n)`	分组内第n行的值

first_value()

以下示例返回 VENUE 表中每个场地的座位数，同时按容量对结果进行排序（从高到低）。FIRST_VALUE 函数用于选择与框架中的第一行对应的场地的名称：在这种情况下，为座位数最多的行。按州对结果进行分区，以便当 VENUESTATE 值发生更改时，会选择一个新的第一个值。窗口框架是无界的，因此为每个分区中的每个行选择相同的第一个值。

对于加利福利亚，Qualcomm Stadium 具有最大座位数 (70561)，此名称是 CA 分区中所有行的第一个值。

select venuestate, venueseats, venuename,
first_value(venuename)
over(partition by venuestate
order by venueseats desc
rows between unbounded preceding and unbounded following)
from (select * from venue where venueseats >0)
order by venuestate;

venuestate | venueseats |           venuename            |         first_value
-----------+------------+--------------------------------+------------------------------
CA         |      70561 | Qualcomm Stadium               | Qualcomm Stadium
CA         |      69843 | Monster Park                   | Qualcomm Stadium
CA         |      63026 | McAfee Coliseum                | Qualcomm Stadium
CA         |      56000 | Dodger Stadium                 | Qualcomm Stadium
CA         |      45050 | Angel Stadium of Anaheim       | Qualcomm Stadium
CA         |      42445 | PETCO Park                     | Qualcomm Stadium
CA         |      41503 | AT&T Park                      | Qualcomm Stadium
CA         |      22000 | Shoreline Amphitheatre         | Qualcomm Stadium
CO         |      76125 | INVESCO Field                  | INVESCO Field
CO         |      50445 | Coors Field                    | INVESCO Field
DC         |      41888 | Nationals Park                 | Nationals Park
FL         |      74916 | Dolphin Stadium                | Dolphin Stadium
FL         |      73800 | Jacksonville Municipal Stadium | Dolphin Stadium
FL         |      65647 | Raymond James Stadium          | Dolphin Stadium
FL         |      36048 | Tropicana Field                | Dolphin Stadium
...

nth_value(expr, n)

以下示例显示了加利福利亚、佛罗里达和纽约的第三大场地的座位数与这些州的其他场地的座位数的比较情况：

select venuestate, venuename, venueseats,
	   nth_value(venueseats, 3) ignore nulls
	   over(partition by venuestate order by venueseats desc
	   rows between unbounded preceding and unbounded following)
	   as third_most_seats
from (select * 
		  from venue 
	  where venueseats > 0 
	        and venuestate in('CA', 'FL', 'NY'))
order by venuestate;

venuestate |           venuename            | venueseats | third_most_seats
------------+--------------------------------+------------+------------------
CA         | Qualcomm Stadium               |      70561 |            63026
CA         | Monster Park                   |      69843 |            63026
CA         | McAfee Coliseum                |      63026 |            63026
CA         | Dodger Stadium                 |      56000 |            63026
CA         | Angel Stadium of Anaheim       |      45050 |            63026
CA         | PETCO Park                     |      42445 |            63026
CA         | AT&T Park                      |      41503 |            63026
CA         | Shoreline Amphitheatre         |      22000 |            63026
FL         | Dolphin Stadium                |      74916 |            65647
FL         | Jacksonville Municipal Stadium |      73800 |            65647
FL         | Raymond James Stadium          |      65647 |            65647
FL         | Tropicana Field                |      36048 |            65647
NY         | Ralph Wilson Stadium           |      73967 |            20000
NY         | Yankee Stadium                 |      52325 |            20000
NY         | Madison Square Garden          |      20000 |            20000

2.6 分箱函数

函数	用法
`ntile(n)`	将分区中已排序的行划分为大小尽可能相等的n个已排名组（等频分箱）

以下示例将于 2008 年 8 月 26 日购买 Hamlet 门票所付价格划分到四个排名组中。结果集为 17 个行，几乎均匀地划分到排名 1 到 4 中：

select eventname, caldate, pricepaid, 
	   ntile(4) over(order by pricepaid desc) from sales, event, date
where sales.eventid=event.eventid 
	  and event.dateid=date.dateid 
	  and eventname='Hamlet'
	  and caldate='2008-08-26'
order by 4;

eventname |  caldate   | pricepaid | ntile
-----------+------------+-----------+-------
Hamlet    | 2008-08-26 |   1883.00 |     1
Hamlet    | 2008-08-26 |   1065.00 |     1
Hamlet    | 2008-08-26 |    589.00 |     1
Hamlet    | 2008-08-26 |    530.00 |     1
Hamlet    | 2008-08-26 |    472.00 |     1
Hamlet    | 2008-08-26 |    460.00 |     2
Hamlet    | 2008-08-26 |    355.00 |     2
Hamlet    | 2008-08-26 |    334.00 |     2
Hamlet    | 2008-08-26 |    296.00 |     2
Hamlet    | 2008-08-26 |    230.00 |     3
Hamlet    | 2008-08-26 |    216.00 |     3
Hamlet    | 2008-08-26 |    212.00 |     3
Hamlet    | 2008-08-26 |    106.00 |     3
Hamlet    | 2008-08-26 |    100.00 |     4
Hamlet    | 2008-08-26 |     94.00 |     4
Hamlet    | 2008-08-26 |     53.00 |     4
Hamlet    | 2008-08-26 |     25.00 |     4

2.7 移动平均

函数	用法
`n PRECEDING`	前n行
`n FOLLOWING`	后n行
`CURRENT ROW`	当前行
`UNBOUNDED PRECEDING`	窗口第一行
`UNBOUNDED FOLLOWING`	窗口的最后一行

比如：

-- 查询前两行到当前行的平均成绩
SELECT *,
	   AVG(成绩) over (ORDER BY 姓名 ROWS 2 preceding) AS result 
FROM test3;

解释：

rows N proceding：N表示在当前行的前N行，比如，N=2，当前行在第4行，那么该平均值是第2行，第3行，第4行，这三行数据的平均值。

参考资料：

[1] 窗口函数

简之

关注

24
点赞
踩
192

收藏

觉得还不错? 一键收藏
2
评论
常用的窗口函数

1、什么是窗口函数含义：窗口函数也叫OLAP函数（Online Anallytical Processing,联机分析处理），可以对数据进行实时分析处理。作用：解决排名问题，e.g.每个班级按成绩排名解决TOPN问题，e.g.每个班级前两名的学生语法：select 窗口函数 over (partition by 用于分组的列名， order by 用于排序的列名分类：排名函数：row_number()，rank()，dense_rank()聚合函数：max()，min()，count
复制链接

扫一扫

专栏目录