想象一個從未聽說過Windows的建筑商。現(xiàn)在，想象一下發(fā)現(xiàn)窗口函數(shù)的SQL程序員的樂趣。

一旦掌握了非常特殊的語法，SQL就是一種高度表現(xiàn)力和豐富的語言，在聲明級提供令人難以置信的功能。窗口功能是最酷的功能之一，其酷炫程度與令人難以置信的低受歡迎程度不成比例。

受歡迎程度低的原因僅在于開發(fā)人員沒有意識到這一點。一旦了解了窗口功能，便有可能將它們放置在各處。

什么是窗函數(shù)？

窗口功能在處理數(shù)據(jù)時會查看數(shù)據(jù)的“窗口”。例如：

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

在以上示例中，窗口函數(shù)的處理可能在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);

執(zhí)行上述操作時，您可以立即看到每個記錄的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) |

此類窗口函數(shù)具有自己的ORDER BY子句，該子句與外部查詢的順序無關。在進行報告時，這一事實非常有用。此外，Sybase SQL Anywhere和PostgreSQL實現(xiàn)了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子句可用于所有支持窗口函數(shù)的SQL數(shù)據(jù)庫，并在本機不支持的情況下模擬它。

上面的查詢結果為：

|     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關鍵字所示?？梢栽趲缀跖c前面的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創(chuàng)建多個窗口

通常，您不希望在整個數(shù)據(jù)集上有一個窗口。相反，您可能希望將PARTITION數(shù)據(jù)集放入幾個較小的窗口中。以下示例為名字中的每個首字母創(chuàng)建分區(qū)，類似于電話簿：

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(*)窗口函數(shù)對所有具有相同首字母的人進行計數(shù)：

| 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 |

窗口函數(shù)與聚合函數(shù)

在符合標準的SQL數(shù)據(jù)庫中，可以通過添加OVER()子句將每個聚合函數(shù)（甚至是用戶定義的聚合函數(shù)）轉換為窗口函數(shù)。這樣的函數(shù)可以在沒有任何GROUP BY子句且沒有對聚合函數(shù)施加任何其他約束的情況下使用。但是，取而代之的是，窗口函數(shù)只能在SELECTorORDER BY子句中使用，因為它們在實例化表源上運行。

除了變成窗口函數(shù)集合函數(shù)，也有各種排名函數(shù)和分析功能，這僅適用的與一個OVER()子句。

最好的選擇是啟動CUBRID，DB2，Oracle，PostgreSQL，SQL Server或Sybase SQL Anywhere數(shù)據(jù)庫，并立即開始使用窗口功能！