SQL 窗口函数的速查表

SQL 窗口函数的速查表，包括窗口函数的语法、窗口函数列表以及相关示例，内容适用于 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite 等关系型数据库。

窗口函数概述

窗口函数（Window Function）基于一个滑动窗口，也就是与当前行相关的一组数据行为其计算出一个结果；通常也称为分析函数（Analytic 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;

ORDER BY

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

窗口大小

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，窗口到分区的最后一行结束。

以下是窗口选项的一些示例：

第一个窗口使用 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() 和 DENSE_RANK() 函数必须指定 ORDER BY 选项，ROW_NUMBER() 函数可以不指定 ORDER BY 选项。

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

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

下图演示了 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) 函数的效果：

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

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

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

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

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

聚合窗口函数

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

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

 

参考：

 

posted @ 2022-12-08 10:25  残城碎梦  阅读(416)  评论(0)  编辑  收藏  举报