SQL 窗口函数速查表

SQL 窗口函数速查表

大家好!我是只谈技术不剪发的 Tony 老师。今天分享一个 SQL 窗口函数的速查表,包括窗口函数的语法、窗口函数列表以及相关示例,内容适用于 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite 等关系型数据库。

如果需要 PDF 版本,点此下载

窗口函数概述

窗口函数(Window Function)基于一个滑动窗口,也就是与当前行相关的一组数据行为其计算出一个结果;通常也称为分析函数(Analytic Function)。

Window Function

我们知道,聚合函数(Aggregate Function)用于将一组数据汇总成一个结果;而窗口函数则为每一行数据计算出一个结果。它们的区别如下图所示:

Aggregate Function
窗口函数的语法如下:

SELECT column1, column2,
  window_function OVER (
    PARTITION BY ...
    ORDER BY ...
    frame_clause) AS column_alias
FROM table_name;

其中,window_function 是窗口函数的名称;OVER 子句包含三个可选项:分区(PARTITION BY)、排序(ORDER BY)以及窗口大小(frame_clause)。

以下是一个窗口函数的示例:

SELECT city, month, 
  sum(sold) OVER (
    PARTITION BY city 
    ORDER BY month 
    RANGE UNBOUNDED PRECEDING) total
FROM sales;

该查询返回了不同城市,按照月份排序后,累计到每个月份的总销量;OVER 子句中各个选项的作用在下文中进行介绍。

窗口函数还提供了命名窗口的功能:

SELECT column1, column2,
  window_function1 OVER window_name 
  window_function2 OVER window_name 
FROM table_name
WINDOW window_name AS (
  PARTITION BY ...
  ORDER BY ...
  frame_clause);

当多个窗口函数的 OVER 子句完全相同,命名窗口可以简化函数的输入。MySQL、PostgreSQL、SQLite 支持命名窗口,Oracle、SQL Server 不支持。

PARTITION BY

OVER 子句中的 PARTITION BY 选项用于定义分区,作用类似于 GROUP BY 分组;如果指定了分区选项,窗口函数将会分别针对每个分区单独进行分析;否则,所有数据作为一个整体进行分析。

以下查询按照不同 city 统计总销量:

SELECT month, city, sold,
  sum(sold) OVER (
    PARTITION BY city ) sum
FROM sales;

PARTITION BY

ORDER BY

OVER 子句中的 ORDER BY 选项用于指定分区内的排序方式,与 ORDER BY 子句的作用类似;排序选项通常用于数据的排名分析。下图演示了按照 city 分区、按照 month 排序之后的数据:

ORDER BY

窗口大小

OVER 子句中的 frame_clause 选项用于指定一个滑动的窗口。窗口总是位于分区范围之内,是分区的一个子集。指定了窗口之后,分析函数不再基于分区进行计算,而是基于窗口内的数据进行计算。

指定窗口大小的语法如下:

ROWS | RANGE | GROUPS BETWEEN frame_start AND frame_end

其中,ROWS 表示以行为单位计算窗口的偏移量;RANGE 表示以数值(例如 10 天之内)为单位计算窗口的偏移量;GROUPS 以组(ORDER BY 排序相同的数据为一组)为单位计算窗口的偏移量,只有 PostgreSQL、SQLite 支持 GROUPS 选项。

frame_start 用于定义窗口的起始位置,可以指定以下内容之一:

  • UNBOUNDED PRECEDING,窗口从分区的第一行开始;
  • N PRECEDING,窗口从当前行之前的第 N 行、范围 N 之内或者第 N 个组开始;
  • CURRENT ROW,窗口从当前行开始。

frame_end 用于定义窗口的结束位置,可以指定以下内容之一:

  • CURRENT ROW,窗口到当前行结束;
  • M FOLLOWING,窗口到当前行之后的第 M 行、范围 M 之内或者第 M 个组结束;
  • UNBOUNDED FOLLOWING,窗口到分区的最后一行结束。

Window Frame
以下是窗口选项的一些示例:

窗口选项
第一个窗口使用 ROWS 选项,包含了前后各 1 行以及当前行;第二个窗口使用 RANGE 选项,包含了当前行的数值减去 1(4-1=3)到当前行的数值加上 1(4+1=5)之间的所有数据;第三个窗口使用 GROUPS 选项,包含了前后各 1 组(ORDER BY 排序相同的数据为一组)和当前行。

如果没有指定窗口大小选项,默认使用的窗口如下:

  • 如果指定了 ORDER BY, 默认窗口为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW;
  • 如果没有指定 ORDER BY, 默认窗口为 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

SQL 子句逻辑执行顺序

窗口函数可以用于 SELECT 列表或者 ORDER BY 子句中,但是不能出现在其他子句中。各种 SQL 子句的逻辑执行顺序如下:

  1. FROM、JOIN
  2. WHERE
  3. GROUP BY
  4. 聚合函数
  5. HAVING
  6. 窗口函数
  7. SELECT
  8. DISTINCT
  9. UNION、INTERSECT、EXCEPT、MINUS
  10. ORDER BY
  11. OFFSET
  12. LIMIT、FETCH、TOP

常用窗口函数

  • 排名窗口函数
    • RANK
    • DENSE_RANK
    • PERCENT_RANK
    • ROW_NUMBER
    • NTILE
    • CUME_DIST
  • 取值窗口函数
    • LAG
    • LEAD
    • FIRST_VALUE
    • LAST_VALUE
    • NTH_VALUE
  • 聚合窗口函数
    • AVG
    • SUM
    • COUNT
    • MAX
    • MIN

排名窗口函数

  • RANK(),计算每行数据在其分区中的名次;如果存在名次相同的数据,后续的排名将会产生跳跃。
  • DENSE_RANK(),计算每行数据在其分区中的名次;存在名次相同的数据,后续的排名不会跳跃。
  • PERCENT_RANK(),与 RANK() 相同,但是以百分比的形式显示每行数据的名次,取值范围 [0-1]。
  • ROW_NUMBER(),为分区中的每行数据分配一个唯一序列号,从 1 开始分配。
  • NTILE(), 将分区内的数据分为 N 等份,为每行数据计算其所在的位置。
  • CUME_DIST(), 计算每行数据在其分区内的累积分布, 也就是排在该行数据之前的所有数据所占的比率,取值范围 (0-1]。

排名窗口函数不支持动态的窗口大小(frame_clause),而是以整个分区(PARTITION BY)作为分析的窗口。

下图演示了 ROW_NUMBER()、RANK() 以及 DENSE_RANK() 函数的效果:

rank
RANK() 和 DENSE_RANK() 函数必须指定 ORDER BY 选项,ROW_NUMBER() 函数可以不指定 ORDER BY 选项。

下图演示了 CUME_DIST() 和 PERCENT_RANK() 函数的效果:

cume dist
CUME_DIST() 和 PERCENT_RANK() 函数必须指定 ORDER BY 选项。

下图演示了 NTILE() 函数的效果:

ntile
NTILE() 函数必须指定 ORDER BY 选项。

取值窗口函数

  • LAG(expr, offset, default), 返回分区中当前行之前第 offset 行对应的 expr 。 offset 和 default 可选,默认值分别为 1 和 NULL。
  • LEAD(expr, offset, default), 返回分区中当前行之后第 offset 行的对应expr 。 offset 和 default 可选,默认值分别为 1 和 NULL。
  • FIRST_VALUE(expr), 返回窗口内第一行对应的 expr 。
  • LAST_VALUE(expr), 返回窗口内最后一行对应的 expr 。
  • NTH_VALUE(expr, n), 返回窗口内第 n 行对应的 expr 。

LAG 和 LEAD 函数不支持动态的窗口大小(frame_clause),而是以整个分区(PARTITION BY)作为分析的窗口。

下图演示了 LAG(expr, offset, default) 和 LEAD(expr, offset, default) 函数的效果:

lead
LAG(expr, offset, default) 和 LEAD(expr, offset, default) 函数必须指定 ORDER BY 选项。

下图演示了 FIRST_VALUE(expr) 和 LAST_VALUE(expr) 函数的效果:

FIRST_VALUE
FIRST_VALUE(expr) 和 LAST_VALUE(expr) 函数可以不指定 ORDER BY 选项。

下图演示了 NTH_VALUE(expr, n) 函数的效果:

NTH_VALUE
SQL Server 不支持 NTH_VALUE(expr, n) 函数。NTH_VALUE(expr, n) 函数可以不指定 ORDER BY 选项。

聚合窗口函数

  • AVG(expr),窗口内数据行的平均值;
  • SUM(expr),窗口内数据行的和值;
  • COUNT(expr),窗口内数据行的计数;
  • MAX(expr),窗口内数据行的最大值;
  • MIN(expr),窗口内数据行的最小值。

聚合函数通常也可以作为窗口函数使用,可以用于计算数据的累计总值和移动平均值。聚合窗口函数可以不指定 ORDER BY 选项。

如果你点击了收藏⭐,请不要忘了关注❤️、评论📝、点赞👍!

  • 20
    点赞
  • 59
    收藏
  • 打赏
    打赏
  • 11
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:酷酷鲨 设计师:CSDN官方博客 返回首页
评论 11

打赏作者

不剪发的Tony老师

为 5 个 C 币而折腰。

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值