窗口函数–可能是有史以来最酷的SQL功能

想象一个从未听说过Windows的建筑商。现在，想象一下发现窗口函数的SQL程序员的乐趣。

一旦掌握了非常特殊的语法，SQL就是一种高度表现力和丰富的语言，在声明级提供令人难以置信的功能。窗口功能是最酷的功能之一，其酷炫程度与令人难以置信的低受欢迎程度不成比例。

受欢迎程度低的原因仅在于开发人员没有意识到这一点。一旦了解了窗口功能，便有可能将它们放置在各处。

什么是窗函数？

窗口功能在处理数据时会查看数据的“窗口”。例如：

FIRST_NAME | ------------ Adam       | <-- UNBOUNDED PRECEDING ...        | Alison     | Amanda     | Jack       | Jasmine    | Jonathan   | <-- 1 PRECEDING Leonard    | <-- CURRENT ROW Mary       | <-- 1 FOLLOWING Tracey     | ...        | Zoe        | <-- UNBOUNDED FOLLOWING

在以上示例中，窗口函数的处理可能在CURRENT ROW“ Leonard”所在的行处。然后，您可以在该行的窗口中访问之前或之后的记录。这非常有用，例如，当您想向“伦纳德”旁边的人展示时。

SQL语法：

SELECT   LAG(first_name, 1)      OVER(ORDER BY first_name) "prev",   first_name,    LEAD(first_name, 1)      OVER(ORDER BY first_name) "next" FROM people ORDER BY first_name

jOOQ语法：

select(    lag(PEOPLE.FIRST_NAME, 1)      .over().orderBy(PEOPLE.FIRST_NAME).as("prev"),    PEOPLE.FIRST_NAME,    lead(PEOPLE.FIRST_NAME, 1)      .over().orderBy(PEOPLE.FIRST_NAME).as("next")) .from(PEOPLE) .orderBy(PEOPLE.FIRST_NAME);

执行上述操作时，您可以立即看到每个记录的FIRST_NAME值如何引用前面和后面的名字：

|     PREV | FIRST_NAME |     NEXT | |----------|------------|----------| |   (null) |       Adam |   Alison | |     Adam |     Alison |   Amanda | |   Alison |     Amanda |     Jack | |   Amanda |       Jack |  Jasmine | |     Jack |    Jasmine | Jonathan | |  Jasmine |   Jonathan |  Leonard | | Jonathan |    Leonard |     Mary | |  Leonard |       Mary |   Tracey | |     Mary |     Tracey |      Zoe | |   Tracey |        Zoe |   (null) |

此类窗口函数具有自己的ORDER BY子句，该子句与外部查询的顺序无关。在进行报告时，这一事实非常有用。此外，Sybase SQL Anywhere和PostgreSQL实现了SQL标准WINDOW子句，该子句可避免重复的窗口定义。

SQL语法：

SELECT   LAG(first_name, 1) OVER w "prev",   first_name,    LEAD(first_name, 1) OVER w "next" FROM people WINDOW w AS (ORDER first_name) ORDER BY first_name DESC

jOOQ 3.3语法：

WindowDefinition w = name("w").as(   orderBy(PEOPLE.FIRST_NAME));  select(    lag(PEOPLE.FIRST_NAME, 1).over(w).as("prev"),    PEOPLE.FIRST_NAME,    lead(PEOPLE.FIRST_NAME, 1).over(w).as("next")) .from(PEOPLE) .window(w) .orderBy(PEOPLE.FIRST_NAME.desc());

请注意，jOOQ使上述WINDOW子句可用于所有支持窗口函数的SQL数据库，并在本机不支持的情况下模拟它。

上面的查询结果为：

|     PREV | FIRST_NAME |     NEXT | |----------|------------|----------| |   Tracey |        Zoe |   (null) | |     Mary |     Tracey |      Zoe | |  Leonard |       Mary |   Tracey | | Jonathan |    Leonard |     Mary | |  Jasmine |   Jonathan |  Leonard | |     Jack |    Jasmine | Jonathan | |   Amanda |       Jack |  Jasmine | |   Alison |     Amanda |     Jack | |     Adam |     Alison |   Amanda | |   (null) |       Adam |   Alison |

使用框架定义

Windows可以具有限制帧或无限制帧，如先前使用PRECEDING和FOLLOWING关键字所示。可以在几乎与前面的LEAD()/LAG()示例等效的示例中看到这一点：

SQL语法：

SELECT   FIRST_VALUE(first_name)     OVER(ORDER BY first_name ASC          ROWS 1 PRECEDING) "prev",   first_name,   FIRST_VALUE(first_name)     OVER(ORDER BY first_name DESC          ROWS 1 PRECEDING) "next" FROM people ORDER BY first_name ASC

jOOQ语法：

select(   firstValue(PEOPLE.FIRST_NAME)     .over().orderBy(PEOPLE.FIRST_NAME.asc())            .rowsPreceding(1).as("prev"),   PEOPLE.FIRST_NAME,   firstValue(PEOPLE.FIRST_NAME)     .over().orderBy(PEOPLE.FIRST_NAME.desc())            .rowsPreceding(1).as("next")) .from(PEOPLE) .orderBy(FIRST_NAME.asc());

上面的示例使用不同的ORDER BY子句访问aCURRENT ROW的PRECEDING行，然后仅保留FIRST_VALUE()。从结果中可以看出，当涉及“第一”和“最后”记录时，这在语义上略有不同：

|     PREV | FIRST_NAME |     NEXT | |----------|------------|----------| |     Adam |       Adam |   Alison | |     Adam |     Alison |   Amanda | |   Alison |     Amanda |     Jack | |   Amanda |       Jack |  Jasmine | |     Jack |    Jasmine | Jonathan | |  Jasmine |   Jonathan |  Leonard | | Jonathan |    Leonard |     Mary | |  Leonard |       Mary |   Tracey | |     Mary |     Tracey |      Zoe | |   Tracey |        Zoe |      Zoe |

使用PARTITION BY创建多个窗口

通常，您不希望在整个数据集上有一个窗口。相反，您可能希望将PARTITION数据集放入几个较小的窗口中。以下示例为名字中的每个首字母创建分区，类似于电话簿：

SQL语法：

SELECT   first_name,    LEFT(first_name, 1),   COUNT(*) OVER(PARTITION BY LEFT(first_name, 1)) FROM people ORDER BY first_name

jOOQ 3.3语法：

select(    PEOPLE.FIRST_NAME,    left(PEOPLE.FIRST_NAME, 1),    count().over().partitionBy(      left(PEOPLE.FIRST_NAME, 1))) .from(PEOPLE) .orderBy(FIRST_NAME);

如下所示，COUNT(*)窗口函数对所有具有相同首字母的人进行计数：

| FIRST_NAME | LEFT | COUNT | |------------|------|-------| |       Adam |    A |     3 | |     Alison |    A |     3 | |     Amanda |    A |     3 | |       Jack |    J |     3 | |    Jasmine |    J |     3 | |   Jonathan |    J |     3 | |    Leonard |    L |     1 | |       Mary |    M |     1 | |     Tracey |    T |     1 | |        Zoe |    Z |     1 |

窗口函数与聚合函数

在符合标准的SQL数据库中，可以通过添加OVER()子句将每个聚合函数（甚至是用户定义的聚合函数）转换为窗口函数。这样的函数可以在没有任何GROUP BY子句且没有对聚合函数施加任何其他约束的情况下使用。但是，取而代之的是，窗口函数只能在SELECTorORDER BY子句中使用，因为它们在实例化表源上运行。

除了变成窗口函数集合函数，也有各种排名函数和分析功能，这仅适用的与一个OVER()子句。

最好的选择是启动CUBRID，DB2，Oracle，PostgreSQL，SQL Server或Sybase SQL Anywhere数据库，并立即开始使用窗口功能！