SQL窗口函数

什么是窗口函数

SQL窗口函数为在线分析处理（OLAP）和商业智能（BI）提供了复杂分析和报表统计的功能，例如产品的累计销售额统计、分类排名、同比/环比分析等。这些功能通常很难通过聚合函数和分组操作来实现。

窗口函数（Window Function）可以像聚合函数一样对一组数据进行分析并返回结果，二者的不同之处在于，窗口函数不是将一组数据汇总成单个结果，而是为每一行数据都返回一个结果。聚合函数和窗口函数的区别如下图所示。

以SUM函数为例演示这两种函数的差异，以下语句中的SUM()是一个聚合函数：

SELECT SUM(salary) AS "所有员工月薪总和"
FROM employee

以上SUM函数可作为聚合函数使用，表示将所有员工的数据汇总成一个结果。因此，查询返回了所有员工的月薪总和：

以下语句中的SUM()是一个窗口函数：

SELECT emp_name AS "员工姓名",
       SUM(salary) OVER () AS "所有员工月薪总和"
FROM employee;

其中，关键字OVER表明SUM()是一个窗口函数。括号内为空，表示将所有数据作为一个分组进行汇总。该查询返回的结果如下：

以上查询结果返回了所有的员工姓名，并且通过聚合函数SUM()为每个员工都返回了相同的汇总结果。

从以上示例中可以看出，窗口函数的语法与聚合函数的不同之处在于，它包含了一个OVER子句。OVER子句用于指定一个数据分析的窗口，完整的窗口函数定义如下：

其中window_function是窗口函数的名称，expression是可选的分析对象（字段名或者表达式），OVER子句包含分区（PARTITION BY）、排序（ORDER BY）以及窗口大小（frame_clause）3个选项。

提示：聚合函数将同一个分组内的多行数据汇总成单个结果，窗口函数则保留了所有的原始数据。在某些数据库中，窗口函数也被称为在线分析处理（OLAP）函数，或者分析函数（Analytic Function）。

窗口函数组成部分

1.创建数据分区

窗口函数OVER子句中的PARTITION BY选项用于定义分区，其作用类似于查询语句中的GROUP BY子句。如果我们指定了分区选项，窗口函数将会分别针对每个分区单独进行分析。

例如，以下语句按照不同部门分别统计员工的月薪合计：

SELECT emp_name AS "员工姓名", salary "月薪", dept_id AS "部门编号",
       SUM(salary) OVER (
         PARTITION BY dept_id
       ) AS "部门合计"
FROM employee;

其中，PARTITION BY选项表示按照部门进行分区。查询返回的结果如下：

查询结果中的前3行数据属于同一个部门，因此它们对应的部门合计字段都等于80000（30000+26000+24000）。其他部门的员工采用同样的方式进行统计。

提示：在窗口函数OVER子句中指定了PARTITION BY选项之后，我们无须使用GROUP BY子句也能获得分组统计结果。

如果不指定PARTITION BY选项，表示将全部数据作为一个整体进行分析。

2.分区内的排序

窗口函数OVER子句中的ORDER BY选项用于指定分区内数据的排序方式，作用类似于查询语句中的ORDER BY子句。

排序选项通常用于数据的分类排名。例如，以下语句用于分析员工在部门内的月薪排名：

SELECT emp_name AS "员工姓名", salary "月薪", dept_id AS "部门编号",
       RANK() OVER (
         PARTITION BY dept_id
         ORDER BY salary DESC
       ) AS "部门内排名"
FROM employee;

其中，RANK函数用于计算数据的名次，PARTITION BY选项表示按照部门进行分区，ORDER BY选项表示在部门内按照月薪从高到低进行排序。查询返回的结果如下：