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

SQL窗口函数提供了一些非常强大和有用的特性. But for many, 因为它们对标准SQL来说太陌生了, 它们很难学习和理解, 有奇怪的语法并且经常被避免使用.

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

在这个SQL窗口函数教程中, 我会让你们从窗函数开始, 解释一下这些好处，以及你什么时候会使用它们, 给你们一些真实的例子来帮助你们理解概念.

A Window into Your Data

SQL中最常用和最重要的特性之一是以特定方式聚合或分组数据行的能力. 然而，在某些情况下，分组可能变得极其复杂，这取决于需要什么.

您是否曾经想要循环查询结果以获得排名, a top x list, or similar? 您是否有任何想要为可视化工具准备数据的分析项目, 但发现它几乎不可能，或者太复杂了，不值得?

窗口函数可以使事情变得更容易. 在获得查询结果之后- i.e., after the WHERE 子句和任何标准聚合，窗口函数将作用于剩余的行 window (数据)，得到你想要的.

我们将要学习的一些窗口函数包括:

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

Over Easy

The OVER 子句用于指定窗口函数，并且必须始终包含在语句中. The default in an OVER 子句是整个行集. As an example, 让我们看一下公司数据库中的员工表，并在每行显示员工总数, 还有每个员工的信息, 包括他们创立公司的时候.

SELECT COUNT(*) OVER()作为NumEmployees, 名字，姓氏，起始日期
FROM Employee
ORDER BY date_started;

NumEmployees	firstname	lastname	date_started
3	John	Smith	2019-01-01 00:00:00.000
3	Sally	Jones	2019-02-15 00:00:00.000
3	Sam	Gordon	2019-02-18 00:00:00.000

The above, like many window functions, 也可以用一种更熟悉的非窗口方式来写——哪一种, in this simple example, isn’t too bad:

SELECT
(SELECT COUNT(*) FROM Employee)作为NumEmployees, 名字，姓氏，起始日期
    FROM Employee
ORDER BY date_started;

But now, 假设我们希望显示与该行中的员工在同一个月开始工作的员工数量. 我们需要将每行的计数缩小或限制到当月. How is that done? We use the window PARTITION clause, like so:

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

NumPerMonth	TheMonth	Firstname	Lastname
1	January 2019	John	Smith
2	February 2019	Sally	Jones
2	February 2019	Sam	Gordon

分区允许您按某个或多个值将窗口筛选为部分. 每个部分通常被称为窗口 frame.

To take it further, 假设我们不仅想知道有多少员工在同一个月开始工作, 但是我们想要显示他们在那个月开始的顺序. 为此，我们可以使用熟悉的方法 ORDER BY clause. 然而，在窗口函数中， ORDER BY 的行为与查询结束时的行为略有不同.

SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started)， YEAR(date_started)) 
ORDER BY date_started)
    DATENAME(month,date_started)+' '+DATENAME(year,date_started)
    名字，姓氏，起始日期
FROM Employee
ORDER BY date_started;

NumThisMonth	TheMonth	Firstname	lastname
1	January 2019	John	Smith
1	February 2019	Sally	Jones
2	February 2019	Sam	Gordon

In this case, ORDER BY 修改窗口，使其从分区的开始(在本例中为员工开始工作的月份和年份)转到当前行. 因此，计数在每个分区重新开始.

Rank It

窗口函数对于排序非常有用. 之前我们用 COUNT 聚合功能使我们能够查看员工加入公司的顺序. 我们也可以使用窗口排序函数，例如 ROW_NUMBER(), RANK(), and DENSE_RANK().

当我们在下个月添加一个新员工后，就可以看到差异了, and remove the partition:

SELECT 
ROW_NUMBER() OVER (ORDER BY YEAR(date_started)，MONTH(date_started)) 
As StartingRank,
    RANK() OVER (ORDER BY YEAR(date_started)，MONTH(date_started))
    DENSE_RANK() OVER (ORDER BY YEAR(date_started)，MONTH(date_started))
    DATENAME(month,date_started)+' '+DATENAME(year,date_started)
    名字，姓氏，起始日期
FROM Employee
ORDER BY date_started;

StartingRank	EmployeeRank	DenseRank	TheMonth	firstname	lastname	date_started
1	1	1	January 2019	John	Smith	2019-01-01
2	2	2	February 2019	Sally	Jones	2019-02-15
3	2	2	February 2019	Sam	Gordon	2019-02-18
4	4	3	March 2019	Julie	Sanchez	2019-03-19

You can see the differences. ROW_NUMBER() 给出给定分区内的顺序计数(但没有分区时), it goes through all rows). RANK() 的基础上给出每行的秩 ORDER BY clause. 它显示并列，然后跳过下一个排名. DENSE_RANK 也显示并列，但接着显示下一个连续的值，就好像没有并列一样.

其他排名功能包括:

• CUME_DIST —计算分区内当前行的相对排名
• NTILE —将每个窗口分区的行尽可能等分
• PERCENT_RANK -当前行的百分比排名

还请注意，在本例中，可以在单个查询中使用多个Window函数，并且每个查询中的分区和顺序都可以不同!

行，范围和框架，哦，我的天

来进一步定义或限制您的窗口框架 OVER() clause, you can use ROWS and RANGE. With the ROWS clause, 可以将分区中包含的行指定为当前行之前或之后的行.

SELECT OrderYear, OrderMonth, TotalDue，
    SUM(TotalDue) OVER(ORDER year, ORDER month之间的行) 
作为'LaggingRunningTotal'
FROM sales_products;

In this example, 窗框从第一行移动到当前行- 1, 每一行的窗口大小继续增加.

范围稍有不同，我们可能会得到不同的结果.

SELECT OrderYear, OrderMonth, TotalDue，
    总和(总到期)超过(按订单年，订单月之间的范围) 
作为'LaggingRunningTotal'
FROM sales_products;

范围将包括窗口框架中具有相同的行 ORDER BY values as the current row. 因此，您有可能使用 RANGE if the ORDER BY is not unique.

Some describe ROWS 作为一名物理操作员 RANGE is a logical operator. 的默认值 ROWS and RANGE are always 前一行和当前行无界.

What Else?

大多数标准聚合函数与Window函数一起工作. We’ve seen COUNT in the examples already. Others include SUM, AVG, MIN, MAX, etc.

使用窗口函数，您还可以使用。来访问之前的记录和随后的记录 LAG and LEAD, and FIRST_VALUE and LAST_VALUE. For example, 假设您希望在每行显示当前月的销售数字, 和上个月销售数字的差额. 你可以这样做:

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

基本上，SQL窗口函数是非常强大的

虽然这是对SQL窗口函数的快速介绍, 希望它能激发你的兴趣，看看他们能做什么. 我们了解到窗口函数执行计算的方式类似于聚合函数, 但是附加的好处是，它们可以访问单个行中的数据, 这使得它们非常强大. They always contain the OVER clause, and may contain PARTITION BY, ORDER BY，以及大量的聚合(SUM, COUNT, etc.)及其他定位功能(LEAD, LAG). 我们还学习了窗口框架以及它们如何封装数据段.

注意，不同风格的SQL可能以不同的方式实现窗口函数, 有些可能没有实现所有的窗口函数或子句. 确保检查您正在使用的平台的文档.

If, as a SQL developer，您对调优SQL数据库性能感兴趣 面向开发人员的SQL数据库性能调优.

Happy windowing!

有关具体实现的更多信息，请参见:

• PostgreSQL’s Window Functions PostgreSQL实现的文档.
• SELECT - OVER语句(Transact-SQL) docs by Microsoft.
• SQL Server中的窗口函数 关于SQL Server实现的一个很好的概述 part 2.