想象一個(gè)從未聽(tīng)說(shuō)過(guò)Windows的建筑商?，F(xiàn)在，想象一下發(fā)現(xiàn)窗口函數(shù)的SQL程序員的樂(lè)趣。

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

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

什么是窗函數(shù)？

窗口功能在處理數(shù)據(jù)時(shí)會(huì)查看數(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”所在的行處。然后，您可以在該行的窗口中訪問(wèn)之前或之后的記錄。這非常有用，例如，當(dāng)您想向“倫納德”旁邊的人展示時(shí)。

SQL語(yǔ)法：

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語(yǔ)法：

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í)行上述操作時(shí)，您可以立即看到每個(gè)記錄的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子句，該子句與外部查詢的順序無(wú)關(guān)。在進(jìn)行報(bào)告時(shí)，這一事實(shí)非常有用。此外，Sybase SQL Anywhere和PostgreSQL實(shí)現(xiàn)了SQL標(biāo)準(zhǔn)WINDOW子句，該子句可避免重復(fù)的窗口定義。

SQL語(yǔ)法：

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語(yǔ)法：

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());

請(qǐng)注意，jOOQ使上述WINDOW子句可用于所有支持窗口函數(shù)的SQL數(shù)據(jù)庫(kù)，并在本機(jī)不支持的情況下模擬它。

上面的查詢結(jié)果為：

|     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可以具有限制幀或無(wú)限制幀，如先前使用PRECEDING和FOLLOWING關(guān)鍵字所示?？梢栽趲缀跖c前面的LEAD()/LAG()示例等效的示例中看到這一點(diǎn)：

SQL語(yǔ)法：

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語(yǔ)法：

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子句訪問(wèn)aCURRENT ROW的PRECEDING行，然后僅保留FIRST_VALUE()。從結(jié)果中可以看出，當(dāng)涉及“第一”和“最后”記錄時(shí)，這在語(yǔ)義上略有不同：

|     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)建多個(gè)窗口

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

SQL語(yǔ)法：

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

jOOQ 3.3語(yǔ)法：

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

如下所示，COUNT(*)窗口函數(shù)對(duì)所有具有相同首字母的人進(jìn)行計(jì)數(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ù)

在符合標(biāo)準(zhǔn)的SQL數(shù)據(jù)庫(kù)中，可以通過(guò)添加OVER()子句將每個(gè)聚合函數(shù)（甚至是用戶定義的聚合函數(shù)）轉(zhuǎn)換為窗口函數(shù)。這樣的函數(shù)可以在沒(méi)有任何GROUP BY子句且沒(méi)有對(duì)聚合函數(shù)施加任何其他約束的情況下使用。但是，取而代之的是，窗口函數(shù)只能在SELECTorORDER BY子句中使用，因?yàn)樗鼈冊(cè)趯?shí)例化表源上運(yùn)行。

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

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