SQL窗口函数简介

本文概述

• 数据窗口
• OVER子句
• 排名
• 行、范围和帧
• 还有什么？
• 基本上, SQL窗口功能非常强大

你喜欢讨厌的非常强大的功能(但需要知道)

SQL窗口函数提供了一些极其强大和有用的功能。但是对于许多人来说, 由于它们对标准SQL来说太陌生, 因此它们很难学习和理解, 语法也很奇怪-常常被避免。

窗口函数可以简单地解释为类似于聚合的计算函数, 但是通过GROUP BY子句组合的常规聚合然后隐藏了正在聚合的各个行, 窗口函数可以访问各个行, 并且可以将这些行中的某些属性添加到结果集。

在本SQL窗口函数教程中, 我将帮助你开始使用窗口函数, 解释它们的好处以及使用的时间, 并提供一些实际的示例以帮助你理解这些概念。

数据窗口

SQL中最常用和最重要的功能之一就是能够以特定方式聚合或分组数据行。但是, 在某些情况下, 根据要求的不同, 分组会变得非常复杂。

你是否曾经想遍历查询结果以获得排名, 排名最高的x列表或类似名称？你是否有任何分析项目想要为可视化工具准备适当的数据, 却发现几乎不可能或如此复杂以至于不值得？

窗口功能可以使事情变得简单。在获得查询结果之后, 即在WHERE子句和任何标准聚合之后, 窗口函数将作用于其余行(数据窗口), 并为你提供所需的信息。

我们将要介绍的一些窗口功能包括：

• OVER
• COUNT()
• SUM()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD()
• LAG()

OVER子句

OVER子句是指定窗口函数的子句, 必须始终包含在该语句中。 OVER子句中的默认值是整个行集。例如, 让我们看一下公司数据库中的一个雇员表, 并显示每行的雇员总数, 以及每个雇员的信息, 包括他们在公司成立的时间。

SELECT COUNT(*) OVER() As NumEmployees, firstname, lastname, date_started
FROM Employee
ORDER BY date_started;

像许多窗口函数一样, 上面的代码也可以以更熟悉的非窗口方式编写, 在这个简单的示例中, 还不错：

SELECT
(SELECT COUNT(*) FROM Employee) as NumEmployees, firstname, lastname, date_started
    FROM Employee
ORDER BY date_started;

但是现在, 假设我们希望显示与该行中同一月开始工作的员工数。我们需要将每行的计数范围缩小或限制为该月。怎么做？我们使用窗口PARTITION子句, 如下所示：

SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started)) 
As NumPerMonth, DATENAME(month, date_started)+' '+DATENAME(year, date_started) As TheMonth, firstname, lastname
FROM Employee
ORDER BY date_started;

分区使你可以按一个或多个特定值将窗口过滤为多个部分。每个部分通常称为窗口框架。

更进一步说, 我们不仅要找出同一个月内有多少雇员开始工作, 而且还要显示他们从该月开始的顺序。为此, 我们可以使用熟悉的ORDER BY子句。但是, 在窗口函数中, ORDER BY的行为与查询结束时的行为有所不同。

SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started) 
ORDER BY date_started) As NumThisMonth, DATENAME(month, date_started)+' '+DATENAME(year, date_started) As TheMonth, firstname, lastname, date_started
FROM Employee
ORDER BY date_started;

在这种情况下, ORDER BY修改窗口, 使其从分区的开始(在这种情况下, 即雇员开始的月份和年份)到当前行。因此, 计数在每个分区处重新开始。

排名

窗口功能对于排名很有用。以前, 我们看到使用COUNT聚合功能使我们能够查看员工以什么顺序加入公司。我们还可以使用窗口排名功能, 例如ROW_NUMBER(), RANK()和DENSE_RANK()。

在下个月添加新员工并删除分区后, 可以看到差异：

SELECT 
ROW_NUMBER() OVER (ORDER BY YEAR(date_started), MONTH(date_started)) 
As StartingRank, RANK() OVER (ORDER BY YEAR(date_started), MONTH(date_started)) As EmployeeRank, DENSE_RANK() OVER (ORDER BY YEAR(date_started), MONTH(date_started)) As DenseRank, DATENAME(month, date_started)+' '+DATENAME(year, date_started) As TheMonth, firstname, lastname, date_started
FROM Employee
ORDER BY date_started;

你可以看到差异。 ROW_NUMBER()给出给定分区内的顺序计数(但是如果没有分区, 它将遍历所有行)。 RANK()根据ORDER BY子句给出每一行的排名。它显示平局, 然后跳过下一个排名。 DENSE_RANK也显示平局, 但接着继续下一个连续值, 就好像没有平局一样。

其他排名功能包括：

• CUME_DIST –计算分区中当前行的相对排名
• NTILE –尽可能平均地划分每个窗口分区的行
• PERCENT_RANK –当前行的百分比排名

还要注意在此示例中, 你可以在单个查询中拥有多个Window函数-分区和顺序在每个查询中都可以不同！

行、范围和帧

要在OVER()子句中进一步定义或限制窗口框架, 可以使用ROWS和RANGE。使用ROWS子句, 可以将分区中包含的行指定为当前行之前或之后的行。

SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth ROWS BETWEEN 
UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal'
FROM sales_products;

在此示例中, 窗口框架从第一行到当前行减1, 并且每行的窗口大小继续增加。

范围的工作方式略有不同, 我们可能会得到不同的结果。

SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth RANGE BETWEEN 
UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal'
FROM sales_products;

范围将包括那些窗口框中与当前行具有相同ORDER BY值的行。因此, 如果ORDER BY不是唯一的, 则有可能使用RANGE获得重复项。

有些人将ROWS描述为物理运算符, 而RANGE是逻辑运算符。无论如何, ROWS和RANGE的默认值始终是UNBOUNDED PRECEDING AND CURRENT ROW。

还有什么？

大多数标准的聚合函数与Window函数一起使用。我们已经在示例中看到了COUNT个。其他包括SUM, AVG, MIN, MAX等。

使用窗口功能, 你还可以使用LAG和LEAD以及FIRST_VALUE和LAST_VALUE访问先前的记录和后续的记录。例如, 假设你要在每一行上显示当月的销售数字, 以及上个月的销售数字之间的差异。你可能会执行以下操作：

SELECT id, OrderMonth, OrderYear, product, sales, sales - LAG(sales, 1) OVER (PARTITION BY product ORDER BY OrderYear, OrderMonth) As sales_change
FROM sales_products
WHERE sale_year = 2019;

基本上, SQL窗口功能非常强大

尽管这是对SQL窗口函数的快速介绍, 但希望它会激发你的兴趣, 以了解它们的所有功能。我们了解到, 窗口函数执行的计算与聚合函数的计算类似, 但是其附加的好处是它们可以访问各个行中的数据, 这使它们非常强大。它们始终包含OVER子句, 并且可能包含PARTITION BY, ORDER BY以及大量聚合(SUM, COUNT等)和其他位置函数(LEAD, LAG)。我们还了解了窗口框架以及它们如何封装数据部分。

请注意, 不同风格的SQL可能会以不同方式实现窗口函数, 有些可能未实现所有窗口函数或子句。确保检查所用平台的文档。

如果作为SQL开发人员, 你对调整SQL数据库性能感兴趣, 请查看针对开发人员的SQL数据库性能调整。

快乐开窗！

有关特定实现的更多信息, 请参见：

• PostgreSQL实现的PostgreSQL的Window Functions文档。
• Microsoft的SELECT-OVER子句(Transact-SQL)文档。
• SQL Server中的窗口函数, 对SQL Server实现及其第2部分有一个很好的概述。