窗口函数的一些理解

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

简介

窗口函数，别名：开窗函数、Window函数。又称分析函数
常见使用场景为求排名、TopN、累加等涉及到对数据做二次处理且依赖上下文的环境的计算场景
历史：2003年被加入到SQL标准中，2011年的修订中添加了一些增强功能。

语法

function([expr]) over (
　　[partition by partition_expr]
　　[order by order_expr]
　　[frame]
)

 
分区：分区由 partition by 子句定义。如果没有指定 partition by 子句，则整个查询与分析结果集作为一个窗口分区。
排序：排序由 order by 子句定义
框架（窗口）：框架在分区内对行进一步限制。框架元素不适用于排名函数。使用 ROWS BETWEEN start AND end 来定义
 

函数列表

专用窗口函数

  • 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 方差
  • ...
 

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

  • 两者在执行前都会对数据进行分区，窗口函数使用 PARTITION BY,聚合函数使用 GROUP BY。如下图，数据被分为两个区

  • 窗口函数相比于聚合函数多了一个窗口的概念，即分区之后又进行了一次虚拟的切分，把一个分区内的数据分成了多个窗口，并且多个窗口间数据是有重复的。
场景1：窗口宽度不设置，则默认为从第一行到当前行
SELECT
    RANK() OVER ( 
        PARTITION BY `color` 
        ORDER BY `id`
            # Frame 默认为空 等价于 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) 
FROM
    color_table
场景2：窗口宽度为前后各一行
SELECT
    RANK() OVER ( 
        PARTITION BY `color` 
        ORDER BY `id` 
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 
        ) 
FROM
    color_table

 
场景3：窗口宽度为3包含前两行
SELECT
    RANK() OVER ( 
        PARTITION BY `color` 
        ORDER BY `id` 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) 
FROM
    color_table

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

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

窗口范围定义要点总结

ROWS 和 RANGE 的区别

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

ORDER BY 对窗口范围的影响

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

窗口范围定义实例

  • 前后各N行
    • ROWS BETWEEN N PRECEDING AND N FOLLOWING
  • 前N行到前M行
    • ROWS BETWEEN N PRECEDING AND M PRECEDING
  • 第一行到后N行
    • ROWS BETWEEN UNBOUNDED PRECEDING AND N FOLLOWING
  • 前N行到最后一行
    • ROWS BETWEEN N PRECEDING AND UNBOUNDED FOLLOWING
 

使用窗口函数的一些场景

默认数据表为 score

一、求当前行值的占比

利用了不排序的窗口特性
SELECT *, sum( `value` ) over w AS `sum`, `value` / sum( `value` ) over w AS `percent` FROM scores WINDOW w AS ()
 
 

二、累乘

使用对数+累加实现
SELECT *, power(2, sum(log2( `value` )) OVER ( ORDER BY `value` ) ) cum_multi FROM scores
 
 
 

参考文档：

mysql8.0 窗口函数文档
StackOverflow上关于MySQL如何实现rank()的说明
通俗易懂的学会：SQL窗口函数
维基百科：SQL
数据分析｜SQL窗口函数最全使用指南
窗口函数
 

 
 
 
 
 
posted @ 2021-09-17 10:46  Mr..D  阅读(506)  评论(0)  编辑  收藏  举报