窗口函数的一些理解

Posted on Views:

Tips:
  MySQL 8.0以下不支持窗口函数，非要使用的话参见：https://stackoverflow.com/questions/3333665/rank-function-in-mysql

简介

窗口函数，别名：开窗函数、Window函数。又称分析函数

常见使用场景为求排名、TopN、累加等涉及到对数据做二次处理且依赖上下文的环境的计算场景

历史：2003年被加入到SQL标准中，2011年的修订中添加了一些增强功能。

语法

1
2
3
4
5
function([expr]) over (
    [partition by partition_expr]
    [order by order_expr]
    [frame]
)

分区：分区由 partition by 子句定义。如果没有指定 partition by 子句，则整个查询与分析结果集作为一个窗口分区。

排序：排序由 order by 子句定义

框架（窗口）：框架在分区内对行进一步限制。框架元素不适用于排名函数。使用 

1
ROWS BETWEEN start AND end

来定义，细节参见 https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html

函数列表

专用窗口函数

  • row_number()

    • 取行号

  • rank()

    • 取排名 排名相同则值一样，但会占用后续排名的位置 ， 比如 1、1、1、4

  • dense_rank()

  • percent_rank()

    • 计算分区内排名小于当前行的数据条数占总条数（不包含排名最高的行）的比例

  • cume_dist()

    • 计算分区内排名小于等于当前行的数据条数占总条数的比例

  • lag(expr [, N[, default]])

    • 返回分区内排名小于当前行的第前N行位置的值 expr 代表可以不仅仅可以取字段，也可以在取的同时对字段做操作 N默认为1 default默认为null

  • lead(expr [, N[, default]])

    • 与lag相反，取分区内排名大于当前行的第前N行位置的值

  • ntitle(N)

    • 将分区内的数据分为N组,返回值为组序号（最小为1）

  • first_value(expr)

    • 取当前窗口范围内的第一行，lag和leag是相对位置, first_value是绝对位置，

  • last_value(expr)

    • 取当前窗口范围内的最后一行

  • nth_value(expr, N)

    • 取当前窗口范围内的第N行

可用于窗口函数的聚合函数

  • avg
  • count
  • sum
  • min
  • max
  • bit_and
  • stddev 标准差
  • variance 方差
  • …

窗口函数与聚合函数的对比

  1. 两者在执行前都会对数据进行分区，窗口函数使用 PARTITION BY,聚合函数使用 GROUP BY。

    如下图，数据被分为两个区

  2. 窗口函数相比于聚合函数多了一个窗口的概念，即分区之后又进行了一次虚拟的切分，把一个分区内的数据分成了多个窗口，并且多个窗口间数据是有重复的。

    场景1：窗口宽度不设置，则默认为从第一行到当前行

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT
    	RANK() OVER ( 
    		PARTITION BY `color` 
    		ORDER BY `id`
                # Frame 默认为空 等价于 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    		) 
    FROM
    	color_table

    场景2：窗口宽度为前后各一行

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT
    	RANK() OVER ( 
    		PARTITION BY `color` 
    		ORDER BY `id` 
    		ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 
    		) 
    FROM
    	color_table

    场景3：窗口宽度为3包含前两行

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT
    	RANK() OVER ( 
    		PARTITION BY `color` 
    		ORDER BY `id` 
    		ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    		) 
    FROM
    	color_table

  3. 窗口函数的每次操作数据范围为窗口数据的行数，移动步长为1。聚合函数的每次操作数据范围为分区数据的行数，移动步长为分区行数。

  4. 聚合函数也可以在窗口函数中使用，但作用范围缩小为对每个窗口内的数据进行聚合操作

窗口范围定义要点总结

ROWS 和 RANGE 的区别

  • ROWS：框架由开始行和结束行的位置来定义，偏移量是行号与当前行号的差异。这个比较常用。
  • RANGE：框架由值范围内的行定义，偏移量是行值与当前行值的差异。这个不太常用。

ORDER BY 对窗口范围的影响

  • 没有ORDER BY, 则窗口范围是整个分区。因为没有排序，所以每一行都是对等的。
  • 有ORDER BY，则窗口范围为分区内第一行到当前行。

窗口范围定义实例

  • 前后各N行

    1
    ROWS BETWEEN N PRECEDING AND N FOLLOWING

  • 前N行到前M行

    1
    ROWS BETWEEN N PRECEDING AND M PRECEDING

  • 第一行到后N行

    1
    ROWS BETWEEN UNBOUNDED PRECEDING AND N FOLLOWING

  • 前N行到最后一行

    1
    ROWS BETWEEN N PRECEDING AND UNBOUNDED FOLLOWING

使用窗口函数的一些场景

默认数据表为 scores

id name value
1 Alice 1
2 Bob 2
3 Alice 3
4 Bob 4

一、求当前行值的占比

利用了不排序的窗口特性

1
2
3
4
5
6
7
SELECT
	*,
	sum( `value` ) over w AS `sum`,
	`value` / sum( `value` ) over w AS `percent` 
FROM
	scores 
WINDOW w AS ()
id name value sum percent
1 Alice 1 10 0.1000
2 Bob 2 10 0.2000
3 Alice 3 10 0.3000
4 Bob 4 10 0.4000

二、累乘

使用对数+累加实现

1
2
3
4
5
6
7
SELECT
	*,
	power(2,
          sum(log2( `value` )) OVER ( ORDER BY `value` ) 
         ) cum_multi
FROM
	scores
id name value cum_multi
1 Alice 1 1
2 Bob 2 2
3 Alice 3 6
4 Bob 4 24

参考文档

mysql8.0 窗口函数文档
    https://dev.mysql.com/doc/refman/8.0/en/window-functions.html
StackOverflow上关于MySQL如何实现rank()的说明
    https://stackoverflow.com/questions/3333665/rank-function-in-mysql
通俗易懂的学会：SQL窗口函数
    https://zhuanlan.zhihu.com/p/92654574
维基百科：SQL
    https://zh.wikipedia.org/wiki/SQL
数据分析｜SQL窗口函数最全使用指南
    https://zhuanlan.zhihu.com/p/120269203
窗口函数
    https://help.aliyun.com/document_detail/63972.html