OceanBase 窗口函數(shù)說明

分析函數(shù)（也叫窗口函數(shù)）與聚合函數(shù)，都是對行集組（一組行的集合）進行聚合計算，不同的是，聚合函數(shù)每組只能返回一個值（一行），而窗口函數(shù)每組可以返回多個值（多行）。行集組又稱為窗口（Window），由 analytic_clause 定義。而窗口大小取決于實際的行數(shù)或邏輯間隔（例如時間）。組內(nèi)每一行都是基于窗口的邏輯計算的結(jié)果。

觸發(fā)一個分析函數(shù)需要特殊的關(guān)鍵字 OVER 來指定窗口。一個窗口包含三個組成部分：

• 分區(qū)規(guī)范，用于將輸入行分裂到不同的分區(qū)中。這個過程和 GROUP BY 子句的分裂過程相似。

• 排序規(guī)范，用于決定輸入數(shù)據(jù)行在窗口函數(shù)中執(zhí)行的順序。

• 窗口邊界，指定計算數(shù)據(jù)的窗口邊界。默認值為 RANGE UNBOUNDED PRECEDING 。這個邊界包含當前分區(qū)中所有從開始到目前行所有數(shù)據(jù)。

分析函數(shù)是查詢中執(zhí)行的最后一組操作，除了最后的 ORDER BY 子句。在處理窗口函數(shù)之前，必須完成所有 JOIN 以及 WHERE、GROUP BY 和 HAVING 子句。因此，窗口函數(shù)只能出現(xiàn)在選擇列表或 ORDER BY 子句中。

分析函數(shù)通常用于計算累積、移動、居中和報告匯總。

語法

analytic_function

analytic_function([ arguments ]) OVER (analytic_clause)

analytic_clause

[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]

query_partition_clause

PARTITION BY { expr[, expr ]... | ( expr[, expr ]... ) }

order_by_clause

ORDER [ SIBLINGS ] BY{ expr | position | c_alias } [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, { expr | position | c_alias } [ ASC | DESC ][ NULLS FIRST | NULLS LAST ]]...

windowing_clause

{ ROWS | RANGE } { BETWEEN { UNBOUNDED PRECEDING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } } AND{ UNBOUNDED FOLLOWING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } } | { UNBOUNDED PRECEDING | CURRENT ROW| value_expr PRECEDING}}

在以下各節(jié)中將討論此語法的語義。

analytic_function

分析函數(shù)（analytic_function）指定了分析函數(shù)的名稱。

arguments

參數(shù)（arguments），分析函數(shù)采用 0~3 個參數(shù)。參數(shù)可以是任何數(shù)字數(shù)據(jù)類型或可以隱式轉(zhuǎn)換為數(shù)字數(shù)據(jù)類型的任何非數(shù)字數(shù)據(jù)類型。OceanBase 根據(jù)數(shù)據(jù)類型優(yōu)先級確定具有最高數(shù)值優(yōu)先級的參數(shù)，然后將其余參數(shù)隱式轉(zhuǎn)換為這個具有最高數(shù)值優(yōu)先級參數(shù)的數(shù)據(jù)類型。返回類型也是具有最高數(shù)值優(yōu)先級參數(shù)的數(shù)據(jù)類型，除非對單個函數(shù)另有說明。

analytic_clause

分析子句（analytic_clause），使用 OVER analytic_clause 指示函數(shù)在查詢結(jié)果集上操作。此子句在 FROM、WHERE、GROUP BY 和 HAVING 子句之后計算。您可以在選擇列表或按子句順序中用此子句指定分析函數(shù)。若要篩選基于分析函數(shù)的查詢結(jié)果，請將這些函數(shù)嵌套在父查詢中，然后篩選嵌套子查詢的結(jié)果。

query_partition_clause

分區(qū)子句（query_partition_clause），使用 PARTITION BY 子句將查詢結(jié)果集劃分為基于一個或多個 value_expr 的組。如果省略此子句，則函數(shù)將查詢結(jié)果集的所有行視為單個組。

您可以在同一查詢中指定多個分析函數(shù)，每個函數(shù)通過鍵具有相同或不同的分區(qū)。如果您用 query_partition_clause 指定了一個分析函數(shù)，并且被查詢的對象具有并行屬性，那么函數(shù)計算也是并行化的。

value_expr 的有效值是常量、列、非分析函數(shù)、函數(shù)表達式或涉及其中任何一個的表達式。

order_by_clause

使用排序子句 order_by_clause 指定如何在分區(qū)內(nèi)對數(shù)據(jù)進行排序。 對于所有分析函數(shù)，您可以在多個鍵上的分區(qū)中對值進行排序，每個鍵由 value_expr 定義，由排序序列限定。

在每個函數(shù)中，您可以指定多個排序表達式。當使用對值進行排序的函數(shù)時，這樣做特別有用。

當 order_by_clause 對多行產(chǎn)生相同的值時，函數(shù)的行為如下：

• CUME_DIST、DENSE_RANK、NTILE、PERCENT_RANK 和 RANK 為每一行返回相同的結(jié)果。

• 即使有一個基于 order_by_clause 的值，ROW_NUMBER 也會為每一行分配一個不同的值。該值基于行處理的順序，如果 ORDER BY 不能實現(xiàn)總排序，則該順序可能是不確定的。

• 對于其他分析函數(shù)，它的結(jié)果取決于窗口規(guī)則。如果您指定了一個帶有 RANGE 關(guān)鍵字的邏輯窗口，則函數(shù)將為每個行返回相同的結(jié)果。如果您用 ROWS 關(guān)鍵字指定了物理窗口，則結(jié)果是不確定的。

ORDER BY 子句的限制

使用 ORDER BY 子句會受到以下限制：

• 在分析函數(shù)中，order_by_clause 必須使用表達式（expr）。SIBLINGS 關(guān)鍵字無效（僅與分層查詢中相關(guān)）。位置（position）和列別名（c_alias）也無效。否則，該 order_by_clause 與整個查詢或子查詢的排序命令相同。

• 使用 RANGE 關(guān)鍵字的分析函數(shù)可以在其函數(shù)的 ORDER BY 子句中使用多個排序鍵。您需要指定以下窗口：

• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，簡稱 RANGE UNBOUNDED PRECEDING。

• RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

• RANGE BETWEEN CURRENT ROW AND CURRENT ROW

• RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

除了這四個之外，窗口邊界只能在分析函數(shù)的 ORDER BY 子句中有一個排序鍵。 此限制不適用于 ROW 關(guān)鍵字指定的窗口邊界。

ASC 或 DESC 關(guān)鍵字

指定排序序列，ASC 指升序，DESC 指降序。默認為升序（ASC）。

NULLS FIRST 或 NULLS LAST 關(guān)鍵字

order_by_clause 中的 nulls first 和 nulls last， nulls first 表示排序時NULL值作為最小值處理，而 nulls last 表示排序時NULL值作為最大值處理。

windowing_clause

窗口函數(shù)子句（windowing_clause）有一些分析函數(shù)可以使用 windowing_clause。相關(guān)的關(guān)鍵字如下。

ROWS 或 RANGE 關(guān)鍵字

這些關(guān)鍵字為每一行定義一個用于計算函數(shù)結(jié)果的窗口，然后將該函數(shù)應用于窗口中的所有行。窗口從上到下通過查詢結(jié)果集或分區(qū)移動。窗口也稱為 FRAME，OceanBase 同時支持以下窗口語句：

• ROWS：以物理單位（行）指定窗口。

• RANGE：將窗口指定為邏輯偏移量。默認方式是 RANGE UNBOUNDED PRECEDING。您可以在分析函數(shù)中使用窗口函數(shù)，而要使用 windowing_clause，就必須添加 order_by_clause。

windowing_clause 若由 RANGE 子句定義窗口邊界，那么在 order_by_clause 中您只能指定一個表達式。請參閱ORDER BY 子句的限制。具有邏輯偏移的分析函數(shù)返回的值總是確定性的。但是具有物理偏移量的分析函數(shù)返回的值可能會產(chǎn)生不確定的結(jié)果。排序表達式返回唯一的排序才能使具有物理偏移量的分析函數(shù)返回確定的值，因此，您必須在 order_by_clause 中指定多個列實現(xiàn)唯一的排序。

BETWEEN ... AND 關(guān)鍵字

使用 BETWEEN ... AND 子句指定窗口的起點和終點。第一個表達式（AND 之前）定義起點，第二個表達式（AND 之后）定義終點。 如果省略 BETWEEN 并僅指定一個終點，則 OceanBase 將其視為起點，并且終點默認為當前行。

UNBOUNDED PRECEDING 關(guān)鍵字

UNBOUNDED PRECEDING 指示窗口從分區(qū)的第一行開始。這是起點，不是終點。

UNBOUNDED FOLLOWING 關(guān)鍵字

UNBOUNDED FOLLOWING 表示窗口在分區(qū)的最后一行結(jié)束。這是終點，不是起點。

CURRENT ROW 關(guān)鍵字

作為起點，CURRENT ROW 指定了窗口從當前行或當前值開始（取決于分別指定了 ROW 還是 RANGE）。在這種情況下，端點不能為 value_expr PRECEDING。 作為終點，CURRENT ROW 指定窗口在當前行或值處結(jié)束（分別取決于您是否指定了 ROW 或 RANGE）。在這種情況下，起點不能為 value_expr FOLLOWING。

value_expr PRECEDING 或 value_expr FOLLOWING 關(guān)鍵字

• 如果 value_expr FOLLOWING 是起點，則終點必須是 value_expr FOLLOWING。

• 如果 value_expr PRECEDING 是終點，則起點必須是 value_expr PRECEDING。

如果要定義由時間間隔定義的數(shù)值格式的邏輯窗口，則可能需要使用轉(zhuǎn)換函數(shù)。

如果您指定 ROWS：

• value_expr 是物理偏移量。則它必須是一個常數(shù)或表達式，并且必須計算為正數(shù)。

• 如果 value_expr 是起點的一部分，則它必須把起點與終點之前的部分當作一行計算。

如果您指定 RANGE：

• value_expr 是邏輯偏移量。它必須是一個常數(shù)或表達式，其結(jié)果為正數(shù)值或間隔字面量。

• 您只能在 order_by_clause 中指定一個表達式。

• 如果 value_expr 為數(shù)值，則 ORDER BY expr 必須為數(shù)值或 DATE 數(shù)據(jù)類型。

• 如果 value_expr 為間隔值，則 ORDER BY expr 必須為 DATE 數(shù)據(jù)類型。如果您完全省略 windowing_clause，則默認值為 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。