標準 SQL 中的分析函式概念

分析函式概念

本主題說明分析函式在 BigQuery 中的運作方式。如需 BigQuery 支援的其他分析函式說明,請參閱下列參考主題:導覽函式編號函式匯總分析函式

在資料庫中,「分析函式」是計算一組資料列匯總值的函式。分析函式會對一組輸入資料列計算函式來傳回每個資料列的單一值,而匯總函式則是會傳回一組資料列的單一匯總值,這是兩者之間的差異。

分析函式可以透過簡要的方式來表示複雜的分析運算作業,是一種功能十分強大的機制,而且可以提供有效率的評估方式,若以其他方式執行評估,會涉及耗費大量資源的 Self-JOIN (自我連結),或者必須在 SQL 查詢之外進行運算。

分析函式在 SQL 標準及某些商用資料庫中又稱為「(分析) 窗型函式」。這是因為分析函式會對一組資料列進行評估,這稱為 windowwindow frame。 在其他某些資料庫中,它們可能稱為「線上分析處理」(OLAP) 函式。

簡化語法:

analytic_function_name ( [ argument_list ] )
  OVER (
    [ PARTITION BY partition_expression_list ]
    [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
    [ window_frame_clause ]
  )

分析函式需要使用 OVER 子句,用於定義接受分析函式評估的 window frameOVER 子句中包含下列三個選用子句。BigQuery 會以撰寫順序評估 OVER 子句的附屬子句。

  • PARTITION BY 子句會將輸入資料列分成分區,這與 GROUP BY 類似,但並沒有實際結合資料列與相同的金鑰。
  • ORDER BY 子句會指定每個分區內的順序。
  • window_frame_clause 定義了目前分區內的 window frame

OVER 子句也可能是空的 (OVER()),在此情況下,window frame 包含所有輸入資料列。

分析函式會在匯總 (GROUP BY 與非分析匯總函式) 之後評估。

範例:假設有一家公司想要為每個部門建立一個排行榜,顯示每位員工的「資深排名」,也就是顯示哪些員工在公司待的時間最長。資料表 Employees 中包含資料欄 NameStartDateDepartment

下列查詢會計算部門每位員工的排名:

SELECT firstname, department, startdate,
  RANK() OVER ( PARTITION BY department ORDER BY startdate ) AS rank
FROM Employees;

計算程序概念如圖 1 所示。

Markdown 圖片圖 1:分析函式插圖

BigQuery 會依 OVER 子句的附屬子句出現順序進行評估:

  1. PARTITION BY:資料表會先依 department 分成兩個分區。
  2. ORDER BY:每個分區中的員工資料列會按 startdate 排序。
  3. 範圍:無。RANK() 不允許使用 window frame 子句,因為它適用於所有編號函式
  4. RANK():資深排名會針對 window frame 期間的每個資料列進行計算。

分析函式語法

analytic_function_name ( [ argument_list ] )
  OVER { window_name | ( [ window_specification ] ) }

window_specification:
  [ window_name ]
  [ PARTITION BY partition_expression_list ]
  [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
  [ window_frame_clause ]

window_frame_clause:
{ ROWS | RANGE }
{
  { UNBOUNDED PRECEDING | numeric_expression PRECEDING | CURRENT ROW }
  |
  { BETWEEN window_frame_boundary_start AND window_frame_boundary_end }
}

window_frame_boundary_start:
{ UNBOUNDED PRECEDING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_boundary_end:
{ UNBOUNDED FOLLOWING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

分析函式能夠以純量運算式或純量運算式的運算元的形式應用在查詢作業中,但只適用於以下兩種情況:

  • SELECT 清單。如果分析函式出現在 SELECT 清單中,此函式的 argument_list 就無法參照同一個 SELECT 清單中引入的別名。
  • ORDER BY 子句。如果分析函式出現在查詢的 ORDER BY 子句中,此函式的 argument_list 就可以參照 SELECT 清單別名。

此外,分析函式無法參照本身的 argument_listOVER 子句中的其他分析函式,即使間接透過別名參照也是如此。

無效:

SELECT ROW_NUMBER() OVER () AS alias1
FROM Singers
ORDER BY ROW_NUMBER() OVER(PARTITION BY alias1)

在以上查詢中,分析函式 alias1 解析為分析函式:ROW_NUMBER() OVER()

OVER 子句

語法:

OVER { window_name | ( [ window_specification ] ) }

window_specification:
  [ window_name ]
  [ PARTITION BY partition_expression_list ]
  [ ORDER BY sort_specification_list ]
  [ window_frame_clause ]

OVER 子句有三個可能的元件:

  • PARTITION BY 子句
  • ORDER BY 子句
  • window_frame_clausewindow_name,這參照在 WINDOW 子句中定義的 window_specification

如果 OVER 子句為空,OVER(),分析函式會對包含所有輸入資料列的單一分區進行計算,這表示它將會為每個輸出資料列產生相同的結果。

PARTITION BY 子句

語法:

PARTITION BY expression [, ... ]

PARTITION BY 子句會將輸入資料列分成不同的分區,分析函式會對這些分區進行獨立的評估。PARTITION BY 子句中允許有多個 expressions

expression 的資料類型必須可分組且支援分區。這表示 expression 不能是下列任一資料類型:

  • 浮點
  • 結構
  • 陣列

這個清單與 GROUP BY 不支援的資料類型清單幾乎完全相同,另外會排除浮點類型 (請參閱 BigQuery 資料類型說明中,位於上方的「資料類型屬性」表中的「可分組」)。

如果沒有 PARTITION BY 子句,BigQuery 會將完整輸入當成單一分區處理。

ORDER BY 子句

語法:

ORDER BY expression [ ASC | DESC ] [, ... ]

ORDER BY 子句定義了每個分區內的順序。如果沒有 ORDER BY 子句,分區內的資料列順序非絕對。某些分析函式需要使用 ORDER BY;這在每個分析函式系列的部分中都有提到。即使有 ORDER BY 子句,某些函式也不對在 window frame 之內排序敏感 (例如 COUNT)。

OVER 子句內的 ORDER BY 子句與一般的 ORDER BY 子句一致,其中:

  • 不可以有多個 expressions
  • expression 必須具有支援排序的類型。
  • 每個 expression 都允許選用 ASC/DESC 指定。
  • NULL 值會排序為可能的最小值 (ASC 時為最先顯示,DESC 則最後顯示)

資料類型支援與一般 ORDER BY 子句完全相同,其中下列類型支援排序:

  • 陣列
  • 結構

如果 OVER 子句包含 ORDER BY 子句,但不包含 window_frame_clauseORDER BY 會將 window_frame_clause 隱含定義為:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

如果 window_frame_clauseORDER BY 子句都不存在,window frame 會預設為完整分區。

Window Frame 子句

語法:

{ ROWS | RANGE }
{
  { UNBOUNDED PRECEDING | numeric_expression PRECEDING | CURRENT ROW }
  |
  { BETWEEN window_frame_boundary_start AND window_frame_boundary_end }
}

window_frame_boundary_start:
{ UNBOUNDED PRECEDING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_boundary_end:
{ UNBOUNDED FOLLOWING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_clause 定義了 window frame,這段期間在分區內的目前資料列前後,分析函式會對此期間進行評估。 window_frame_clause 允許實體界線範圍 (由 ROWS 定義) 與邏輯界線範圍 (由 RANGE 定義)。如果 OVER 子句包含 ORDER BY 子句,但不包含 window_frame_clauseORDER BY 會將 window_frame_clause 隱含定義為:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

如果 window_frame_clauseORDER BY 子句都不存在,window frame 會預設為完整分區。

numeric_expression 只能是常數或查詢參數,這兩者都必須有非負值。否則,BigQuery 會提供錯誤。

Window frame 子句的範例:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  • 包含完整分區。
  • 使用範例:對分區進行總計計算。
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • 包含分區之前或包括目前資料列在內的所有資料列。
  • 使用範例:計算累計總和。
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
  • 包含目前資料列之前兩個與之後兩個之間的所有資料列。
  • 使用範例:計算移動平均線。

如果 window_frame_spec 使用 BETWEEN 子句:

  • window_frame_boundary_start 必須指定不晚於 window_frame_boundary_end 的邊界開始的邊界。這會產生下列結果:
    1. 如果 window_frame_boundary_start 包含 CURRENT ROWwindow_frame_boundary_end 不能包含 PRECEDING
    2. 如果 window_frame_boundary_start 包含 FOLLOWINGwindow_frame_boundary_end 不能包含 CURRENT ROWPRECEDING
  • window_frame_boundary_start 沒有預設值。

否則,指定的 window_frame_spec 邊界代表界線範圍的開始,界線範圍邊界的結束預設為「目前資料列」。因此,

ROWS 10 PRECEDING

相當於

ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
資料列

ROWS 式界線範圍會根據與目前資料列之間的偏移來計算 window frame。例如,以下界線範圍就定義了目前資料列前後 (最大) 大小為五的界線範圍。

ROWS BETWEEN 2 PRECEDING and 2 FOLLOWING

window_frame_clause 中的 numeric_expression 會解讀為從目前資料列算起的資料列數,必須是常數、非負整數。它也可以是查詢參數。

如果特定資料列的 window frame 超出分區的開始或結束,window frame 將只包含來自那個分區內的資料列。

範例:假設下列資料表具有資料欄 zxy

z x y
1 5 AA
2 2 AA
3 11 AB
4 2 AA
5 8 AC
6 10 AB
7 1 AB

以下列分析函式為例:

SUM(x) OVER (PARTITION BY y ORDER BY z ROWS BETWEEN 1 PRECEDING AND 1
FOLLOWING)

PARTITION BY 子句根據分區的 y 值將資料表分成 3 個分區,ORDER BY 依據資料列的 z 值排列每個分區內的資料列。

3 個分區中的分區 1:

z x y
1 5 AA
2 2 AA
4 2 AA

3 個分區中的分區 2:

z x y
3 11 AB
6 10 AB
7 1 AB

3 個分區中的分區 3:

z x y
5 8 AC

在下列資料表中,粗體表示目前正在評估的資料列,填色儲存格表示這個資料列在 window frame 內的所有列。

  • 針對 y = AA 分區中的第一個資料列,window frame 只包含 2 個資料列,因為並沒有先前資料列,即使 window_frame_spec 表示範圍大小為 3 也是如此。對於第一個資料列而言,分析函式的結果是 7。
z x y
1 5 AA
2 2 AA
4 2 AA
  • 針對分區中的第二個資料列,window frame 包含全部 3 個資料列。 針對第二個資料列,分析函式的結果是 9。
z x y
1 5 AA
2 2 AA
4 2 AA
  • 針對分區中的最後一個資料列,window frame 只包含 2 個資料列,因為後面已經沒有資料列了。針對第三個資料列,分析函式的結果是 4。
z x y
1 5 AA
2 2 AA
4 2 AA
RANGE

RANGE 式界線範圍會根據以目前資料列的 ORDER BY 金鑰為基礎的目前資料列前後之資料列邏輯範圍計算 window frame。 提供的範圍值會對目前資料列的金鑰進行加減,以定義 window frame 的開始或結束範圍邊界。

除非範圍如下,否則必須指定 ORDER BY 子句:

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

會將 window_frame_clause 中的 numeric_expression 解讀為與 ORDER BY 金鑰的目前資料列值之間的偏移。numeric_expression 必須具有數字類型。目前不支援 DATE 與 TIMESTAMP。此外,numeric_expression 必須是常數、非負數的整數或參數。

RANGE 式界線範圍中,ORDER BY 子句中最多只能有一個 expression,且 expression 必須有數字類型。

有一個分區的 RANGE 式界線範圍範例:

SELECT x, COUNT(*) OVER ( ORDER BY x
  RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING ) AS count_x
FROM T;

在下列資料表中,粗體表示目前正在評估的資料列,填色儲存格表示這個資料列在 window frame 內的所有列。

  • 針對資料列 1,x = 5,因此 COUNT(*) 將只包含 3 <=x <= 7 的資料列
x count_x
5 1
2
11
2
8
10
1
  • 針對資料列 2,x = 2,因此 COUNT(*) 將只包含 0 <= x <= 4 的資料列
x count_x
5 1
2 3
11
2
8
10
1
  • 針對資料列 3,x = 11,因此 COUNT(*) 將只包含 9 <= x <= 13 的資料列
x count_x
5 1
2 3
11 2
2
8
10
1

WINDOW 子句

語法:

WINDOW window_definition [, ...]
window_definition: window_name AS ( window_specification )

WINDOW 子句定義了已命名界線的清單,它的 window_name 可在 SELECT 清單中的分析函式中參照。當您要針對多個分析函式使用相同的 window_frame_clause 時,這很有用。

WINDOW 子句只能出現在 SELECT 子句的結尾,如查詢語法說明所示。

已命名的界線

當您定義 WINDOW 子句之後,可以在分析函式中使用已命名的界線,但只能在 SELECT 清單中使用;您不可以在 ORDER BY 子句中使用已命名的界線。已命名的界線可以自己顯示,也可以內嵌在 OVER 子句內。已命名的界線可以參照 SELECT 清單別名。

範例:

SELECT SUM(x) OVER window_name FROM ...
SELECT SUM(x) OVER (
  window_name
  PARTITION BY...
  ORDER BY...
  window_frame_clause)
FROM ...

當內嵌在 OVER 子句內時,與 window_name 相關聯的 window_specification 必須與同一個 OVER 子句中的 PARTITION BYORDER BY 和 window_frame_clause 相容。

下列規則適用於已命名的界線:

  • 您只能參照 SELECT 清單中的已命名界線;您無法在 ORDER BY 子句、外部查詢或任何子查詢中參照它們。
  • 界線 W1 (已命名或未命名) 可能使用下列規則參照已命名的界線 NW2:
    1. 如果 W1 是已命名的界線,參照的已命名界線 NW2 必須位於相同 WINDOW 子句中的 W1 之前。
    2. W1 不可包含 PARTITION BY 子句
    3. W1 與 NW2 不可能都包含 ORDER BY 子句
    4. NW2 不可包含 window_frame_clause
  • 如果 (已命名或未命名) 界線 W1 參照已命名的界線 NW2,結果界線指定會使用以下方式定義:
    1. NW2 中的 PARTITION BY (如果有的話)。
    2. W1 或 NW2 中的 ORDER BY (如果指定任一項);它們不可能都有 ORDER BY 子句。
    3. W1 中的 window_frame_clause (如果有的話)。

本主題說明分析導覽函式的運作方式。如需 BigQuery 支援的分析導覽函式相關說明,請參閱導覽函式參考資料

導覽函式通常會對目前資料列之窗框內的不同資料列,計算某些 value_expressionOVER 子句語法在各導覽函式之間是不同的。

OVER 子句使用規範:

  • PARTITION BY:選用。
  • ORDER BY
    1. PERCENTILE_CONT 與 PERCENTILE_DISC 不允許使用。
    2. FIRST_VALUELAST_VALUENTH_VALUELEADLAG 為必要。
  • window_frame_clause
    1. PERCENTILE_CONTPERCENTILE_DISCLEADLAG 不允許使用。
    2. FIRST_VALUELAST_VALUENTH_VALUE 為選用。

對於所有導覽函式,結果資料類型與 value_expression 的類型相同。

編號函式

本主題說明分析編號函式的運作方式。如需 BigQuery 支援的分析編號函式相關說明,請參閱編號函式參考資料

編號函式可根據整數值在指定窗體內的位置,將整數值指派給每個資料列。

RANK()DENSE_RANK()ROW_NUMBER() 的範例:

SELECT x,
  RANK() OVER (ORDER BY x ASC) AS rank,
  DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank,
  ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) AS row_num
FROM ...
x rank dense_rank row_num
1 1 1 1
2 2 2 2
2 2 2 3
5 4 3 4
8 5 4 5
10 6 5 6
10 6 5 7
  • RANK(): 如果 x=5,rank 會傳回 4,因為 RANK() 按之前窗體排序群組中的對等體數遞增。
  • DENSE_RANK():如果 x=5,dense_rank 會傳回 3,因為 DENSE_RANK() 一律按 1 遞增,絕對不會跳過值。
  • ROW_NUMBER(): 如果 x=5,row_num 會傳回 4。

匯總分析函式

BigQuery 可以讓您以分析函式的形式來使用特定的匯總函式

使用這些函式,OVER 子句會直接附加到匯總函式呼叫之後;函式呼叫語法會保持不變。這些分析函式類似於相對應的匯總函式,都會執行匯總作業,但會專門針對每一個資料列的相關界線範圍執行。這些分析函式的結果資料類型與本身的匯總函式結果資料類型相同。

如需 BigQuery 支援的匯總分析函式相關說明,請參閱匯總分析函式參考資料

本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁