SQL 分窗函数

本文来源于“mysql分窗函数_SQL 窗函数”、“SQL学习笔记 - 窗口函数OVER”两篇文章，有侵犯可直接联系作者删除。

一、为什么需要窗函数？

既要对数据进行分组，又要对每个组进行某种聚合运算，同时还要对每行输出对应的聚合运算的结果（用 group by 很难实现）。举例：

有一组数据，包含以下字段：城市，姓名，年龄，性别。需求是找出同龄人以及城市人数，按照如下格式输出：城市，姓名，年龄，性别，人数，同龄人人数。

二、什么是窗函数？

窗口函数是 SQL 中一类特别的函数。和聚合函数相似，窗口函数的输入也是多行记录。不同的是，聚合函数的作用于由 GROUP BY 子句聚合的组，而窗口函数则作用于一个窗口， 这里，窗口是由一个 OVER 子句 定义的多行记录。

聚合函数对其所作用的每一组记录输出一条结果，而窗口函数对其所作用的窗口中的每一行记录输出一条结果。

• Perform calculations on an already generated result set ( a window).（在已生成的结果集上执行计算）
• Aggregate calculation(without having to group your data)（允许使用聚合函数时不用进行GROUP BY分组）
• Similar to subqueries in SELECT. （类似于 SELECT 中的子查询）
• Running totals, rankings, and moving averages, etc.（可计算累加值，排序，移动平均值等）
• Processed after every part of query except ORDER BY.（执行顺序在其他各部分之后，但在ORDER BY 之前）
• Uses information in result set rather than database.（使用结果集中的数据而非数据库）
• Available in PostgreSQL, Oracle, MySQL, SQLServer, and SQLite.（适用于 PostgreSQL、Oracle、MySQL、SQLServer 和 SQLite）

其他说法

• 窗口函数是在所有JOIN，WHERE，GROUP BY和HAVING子句之后以及ORDER BY，LIMIT和SELECT DISTINCT之前的结果集上执行的

三、语法

FUNCTION(value) OVER ([PARTITION BY field] [ORDER BY field]
 [ROWS BETWEEN <start> AND <finish>])
注:[]中的内容可省略，根据实际情况选择使用。

PARTITION BY （分区定义）： range of calculation根据指定（1个或多个）字段进行分区，类似GROUP BY。partition_clause将行分成块或分区。两个分区由分区边界分隔。窗口功能在分区内执行，并在跨越分区边界时重新初始化。此外不同的 partition by 是互不影响的，比如上面那道题，可以同时写

COUNT(*)OVER(partition by age)

COUNT(*)OVER(partition by city)

ORDER BY（顺序定义） ： order of rows when running calculation 根据指定字段进行排序

ROWS BETWEEN AND （框架定义）：滑动窗口

四、常用函数

4.1 专用窗口函数

RANK()：计算排序，如果存在相同位次的记录，为相同的值分配相同的数字，但会跳过之后的位次。

DENSE_RANK()：同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。

ROW_NUMBER() ：从1开始，返回每组内部排序后的顺序编号（组内连续的唯一的)

SELEECT 
  goals,
  RANK() OVER(ORDER BY goals DESC) AS goals_rank,
  DENSE_RANK() OVER(ORDER BY goals DESC) AS goals_dense_rank,
  ROW_NUMBER() OVER(ORDER BY goals DESC) AS row_number 
FROM grade
ORDER BY goals DESC;

结果如下：

goals	goals_rankg	oals_dense_rank	row_number
10	1	1	1
10	1	1	2
9	3	2	3
9	3	2	4
7	5	3	5

LAG(column, n)：returns column's value at the row n rows before the current row. 返回当前行之前第n行的值（n省略时默认为1，表示返回当前行前1行的值）。

LEAD(column, n) : returns column's value at the row n row after the current row. 返回当前行之后第n行的值。

FIRST_VALUE(column)：return the first value in the table or partition. 返回表中或分区中第一个值。

LAST_VALUE(column)：return the last value in the table or partition. 返回表中或分区中最后一个值。

NTILE(n)：splits data into n approximately equal pages. 将数据分为近乎相等的n等份（比如由于数据量大，需要将数据平均分配到n个并行的进程分别计算，此时就可以用到该函数）。

4.2 聚合函数

SUM, AVG, COUNT, MAX, MIN 也可以用于窗口函数。

4.3 滑动窗口（Sliding Window）

In addition to calculating aggregate and rank information, window functions can also be used to calculate information that changes with each subsequent row in a data set. These types of window functions are called sliding windows.除了计算汇总、聚合和排序等，窗口函数还可以用于计算随数据集中的每个后续行而变化的信息。这类窗口功能称为滑动窗口。

Sliding windows are functions that perform calculations relative to the current row of a data set. 滑动窗口是执行相对于数据集当前行的计算的功能。

You can use sliding windows to calculate a wide variety of information that aggregates one row at a time down your data set -- running totals, sums, counts, and averages in any order you need. 您可以使用滑动窗口来计算各种各样的信息，这些信息在您的数据集中一次聚合一行——按您需要的任何顺序运行总计、总和、计数和平均值。

A sliding window calculation can also be partitioned by one or more column just like a non-sliding window. 滑动窗口计算也可以像非滑动窗口一样按一列或多列进行分区。

ROWS BETWEEN <start> AND <finish>

可用于start 和finish 的关键字有：

• PRECEDING : n PRECEDING means n rows before the current row 当前行的之前第n行
• FOLLOWING : n FOLLOWING means n row after the current row 当前行之后的第n行
• UNBOUNDED PRECEDING : every row since the beginning of the data set 数据集的开始
• UNBOUNDED FOLLOWING : every row to the end of the data set 数据集的末尾
• CURRENT ROW : tells SQL that you want to stop your calculation at the current row 当前行

五、其他案例

Hive窗口函数 over()详解