「aggregate 函式」是對一組值執行計算的函式。COUNT
、MIN
和 MAX
都是 aggregate 函式的範例。
SELECT COUNT(*) as total_count, COUNT(fruit) as non_null_count,
MIN(fruit) as min, MAX(fruit) as max
FROM (SELECT NULL as fruit UNION ALL
SELECT "apple" as fruit UNION ALL
SELECT "pear" as fruit UNION ALL
SELECT "orange" as fruit)
+-------------+----------------+-------+------+
| total_count | non_null_count | min | max |
+-------------+----------------+-------+------+
| 4 | 3 | apple | pear |
+-------------+----------------+-------+------+
以下小節說明 BigQuery 支援的 aggregate 函式。
ANY_VALUE
ANY_VALUE(expression) [OVER (...)]
說明
如果輸入資料列數為零,就會從輸入傳回任何值或傳回 NULL
。
傳回的值非絕對,這表示您可能會在每次使用這個函式時得到不同的結果。
支援的引數類型
不限
選用子句
OVER
:指定範圍。請參閱 Analytic 函式。
傳回的資料類型
符合輸入資料類型。
範例
SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;
+-----------+
| any_value |
+-----------+
| apple |
+-----------+
SELECT
fruit,
ANY_VALUE(fruit) OVER (ORDER BY LENGTH(fruit) ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;
+--------+-----------+
| fruit | any_value |
+--------+-----------+
| pear | pear |
| apple | pear |
| banana | apple |
+--------+-----------+
ARRAY_AGG
ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS]
[ORDER BY key [{ASC|DESC}] [, ... ]] [LIMIT n])
[OVER (...)]
說明
傳回 expression
值的 ARRAY。
支援的引數類型
除 ARRAY 以外的所有資料類型。
選用子句
子句會「以下列順序」套用:
OVER
:指定範圍。請參閱 Analytic 函式。這個子句目前與ARRAY_AGG()
內的其他所有子句都不相容。DISTINCT
:expression
的每個不同值都只會匯總一次到結果中。IGNORE NULLS
或RESPECT NULLS
:如果指定了IGNORE NULLS
,會從結果中排除 NULL 值。如果指定了RESPECT NULLS
或未指定任何一項,會將 NULL 值包含在結果中 (但如果最終查詢結果中包含 NULL 元素,就會產生錯誤)。ORDER BY
:指定值的順序。- 針對每個排序金鑰,預設排序方向是
ASC
。 - NULL:在
ORDER BY
子句的內容中,NULL 是最小的可能值;也就是說,以ASC
順序排序時,NULL 會最先顯示,以DESC
順序排序時,NULL 會最後顯示。 - 浮點資料類型:有關排序與分組,請參閱浮點語意。
- 如果同時指定了
DISTINCT
,排序金鑰必須與expression
相同。 - 如果未指定
ORDER BY
,輸出陣列中的元素順序非絕對,這表示您可能會在每次使用這個函式時得到不同的結果。
- 針對每個排序金鑰,預設排序方向是
LIMIT
:在結果中指定expression
輸入的數量上限。限制n
必須是常數 INT64。
傳回的資料類型
ARRAY
如果輸入資料列數為零,這個函式會傳回 NULL
。
範例
SELECT FORMAT("%T", ARRAY_AGG(x)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+-------------------------+
| array_agg |
+-------------------------+
| [2, 1, -2, 3, -2, 1, 2] |
+-------------------------+
SELECT FORMAT("%T", ARRAY_AGG(DISTINCT x)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+---------------+
| array_agg |
+---------------+
| [2, 1, -2, 3] |
+---------------+
SELECT FORMAT("%T", ARRAY_AGG(x IGNORE NULLS)) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;
+-------------------+
| array_agg |
+-------------------+
| [1, -2, 3, -2, 1] |
+-------------------+
SELECT FORMAT("%T", ARRAY_AGG(x ORDER BY ABS(x))) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+-------------------------+
| array_agg |
+-------------------------+
| [1, 1, 2, -2, -2, 2, 3] |
+-------------------------+
SELECT FORMAT("%T", ARRAY_AGG(x LIMIT 5)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+-------------------+
| array_agg |
+-------------------+
| [2, 1, -2, 3, -2] |
+-------------------+
SELECT FORMAT("%T", ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY x LIMIT 2)) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;
+-----------+
| array_agg |
+-----------+
| [-2, 1] |
+-----------+
SELECT
x,
FORMAT("%T", ARRAY_AGG(x) OVER (ORDER BY ABS(x))) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+----+-------------------------+
| x | array_agg |
+----+-------------------------+
| 1 | [1, 1] |
| 1 | [1, 1] |
| 2 | [1, 1, 2, -2, -2, 2] |
| -2 | [1, 1, 2, -2, -2, 2] |
| -2 | [1, 1, 2, -2, -2, 2] |
| 2 | [1, 1, 2, -2, -2, 2] |
| 3 | [1, 1, 2, -2, -2, 2, 3] |
+----+-------------------------+
ARRAY_CONCAT_AGG
ARRAY_CONCAT_AGG(expression [ORDER BY key [{ASC|DESC}] [, ... ]] [LIMIT n])
說明
從類型 ARRAY 的 expression
中串連元素,傳回單一 ARRAY 作為結果。這個函式會忽略 NULL 輸入陣列,但沿用非 NULL 輸入陣列中的 NULL 元素 (然而,如果最終查詢結果中包含 NULL 元素,就會產生錯誤)。
支援的引數類型
ARRAY
選用子句
子句會「以下列順序」套用:
ORDER BY
:指定值的順序。- 針對每個排序金鑰,預設排序方向是
ASC
。 - 不支援陣列順序,因此排序金鑰不可與
expression
相同。 - NULL:在
ORDER BY
子句的內容中,NULL 是最小的可能值;也就是說,以ASC
順序排序時,NULL 會最先顯示,以DESC
順序排序時,NULL 會最後顯示。 - 浮點資料類型:有關排序與分組,請參閱浮點語意。
- 如果未指定
ORDER BY
,輸出陣列中的元素順序非絕對,這表示您可能會在每次使用這個函式時得到不同的結果。
- 針對每個排序金鑰,預設排序方向是
LIMIT
:在結果中指定expression
輸入的數量上限。限制適用於輸入陣列數,不適用於陣列中的元素數。空陣列會計為 1。系統不會計入 NULL 陣列。限制n
必須是常數 INT64。
傳回的資料類型
ARRAY
如果輸入資料列數為零或 expression
針對所有資料列評估為 NULL,就會傳回 NULL
。
範例
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x)) AS array_concat_agg FROM (
SELECT [NULL, 1, 2, 3, 4] AS x
UNION ALL SELECT NULL
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
+-----------------------------------+
| array_concat_agg |
+-----------------------------------+
| [NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9] |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x))) AS array_concat_agg FROM (
SELECT [1, 2, 3, 4] AS x
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
+-----------------------------------+
| array_concat_agg |
+-----------------------------------+
| [5, 6, 7, 8, 9, 1, 2, 3, 4] |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x LIMIT 2)) AS array_concat_agg FROM (
SELECT [1, 2, 3, 4] AS x
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
+--------------------------+
| array_concat_agg |
+--------------------------+
| [1, 2, 3, 4, 5, 6] |
+--------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x) LIMIT 2)) AS array_concat_agg FROM (
SELECT [1, 2, 3, 4] AS x
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
+------------------+
| array_concat_agg |
+------------------+
| [5, 6, 7, 8, 9] |
+------------------+
AVG
AVG([DISTINCT] expression) [OVER (...)]
說明
傳回非 NULL
輸入值的平均數,如果輸入中包含 NaN
就會傳回 NaN
。
支援的引數類型
任何數字輸入類型,例如 INT64。請注意,針對浮點輸入類型,傳回結果非絕對,這表示您可能會在每次使用這個函式時得到不同的結果。
選用子句
子句會「以下列順序」套用:
OVER
:指定範圍。請參閱 Analytic 函式。這個子句目前與AVG()
內的其他所有子句都不相容。DISTINCT
:expression
的每個不同值都只會匯總一次到結果中。
傳回的資料類型
- 如果輸入類型是 NUMERIC,就會傳回 NUMERIC。
- FLOAT64
範例
SELECT AVG(x) as avg
FROM UNNEST([0, 2, 4, 4, 5]) as x;
+-----+
| avg |
+-----+
| 3 |
+-----+
SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, 4, 4, 5]) AS x;
+------+
| avg |
+------+
| 2.75 |
+------+
SELECT
x,
AVG(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg
FROM UNNEST([0, 2, NULL, 4, 4, 5]) AS x;
+------+------+
| x | avg |
+------+------+
| NULL | NULL |
| 0 | 0 |
| 2 | 1 |
| 4 | 3 |
| 4 | 4 |
| 5 | 4.5 |
+------+------+
BIT_AND
BIT_AND(expression)
說明
對 expression
執行位元 AND 運算並傳回結果。
支援的引數類型
- INT64
傳回的資料類型
INT64
範例
SELECT BIT_AND(x) as bit_and FROM UNNEST([0xF001, 0x00A1]) as x;
+---------+
| bit_and |
+---------+
| 1 |
+---------+
BIT_OR
BIT_OR(expression)
說明
對 expression
執行位元 OR 運算並傳回結果。
支援的引數類型
- INT64
傳回的資料類型
INT64
範例
SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;
+--------+
| bit_or |
+--------+
| 61601 |
+--------+
BIT_XOR
BIT_XOR([DISTINCT] expression)
說明
對 expression
執行位元 XOR 運算並傳回結果。
支援的引數類型
- INT64
選用子句
DISTINCT
:expression
的每個不同值都只會匯總一次到結果中。
傳回的資料類型
INT64
範例
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([5678, 1234]) AS x;
+---------+
| bit_xor |
+---------+
| 4860 |
+---------+
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;
+---------+
| bit_xor |
+---------+
| 5678 |
+---------+
SELECT BIT_XOR(DISTINCT x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;
+---------+
| bit_xor |
+---------+
| 4860 |
+---------+
COUNT
1.
COUNT(*) [OVER (...)]
2.
COUNT([DISTINCT] expression) [OVER (...)]
說明
- 傳回輸入中的資料列數。
- 傳回
expression
評估為NULL
以外任何值的資料列數。
支援的引數類型
expression
可以是任何資料類型。
選用子句
子句會「以下列順序」套用:
OVER
:指定範圍。請參閱 Analytic 函式。DISTINCT
:expression
的每個不同值都只會匯總一次到結果中。
傳回資料類型
INT64
範例
SELECT
COUNT(*) AS count_star,
COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;
+------------+--------------+
| count_star | count_dist_x |
+------------+--------------+
| 4 | 3 |
+------------+--------------+
SELECT
x,
COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
COUNT(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;
+------+------------+--------------+
| x | count_star | count_dist_x |
+------+------------+--------------+
| 1 | 3 | 2 |
| 4 | 3 | 2 |
| 4 | 3 | 2 |
| 5 | 1 | 1 |
+------+------------+--------------+
SELECT
x,
COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
COUNT(x) OVER (PARTITION BY MOD(x, 3)) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;
+------+------------+---------+
| x | count_star | count_x |
+------+------------+---------+
| NULL | 1 | 0 |
| 1 | 3 | 3 |
| 4 | 3 | 3 |
| 4 | 3 | 3 |
| 5 | 1 | 1 |
+------+------------+---------+
COUNTIF
COUNTIF(expression) [OVER (...)]
說明
針對 expression
傳回 TRUE
值的計數。
如果輸入資料列數為零或 expression
針對所有資料列評估為 FALSE
,就會傳回 0
。
支援的引數類型
BOOL
選用子句
OVER
:指定範圍。請參閱 Analytic 函式。
傳回資料類型
INT64
範例
SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, -7, 4, 0]) AS x;
+--------------+--------------+
| num_negative | num_positive |
+--------------+--------------+
| 3 | 4 |
+--------------+--------------+
SELECT
x,
COUNTIF(x<0) OVER (ORDER BY ABS(x) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS num_negative
FROM UNNEST([5, -2, 3, 6, -10, NULL, -7, 4, 0]) AS x;
+------+--------------+
| x | num_negative |
+------+--------------+
| NULL | 0 |
| 0 | 1 |
| -2 | 1 |
| 3 | 1 |
| 4 | 0 |
| 5 | 0 |
| 6 | 1 |
| -7 | 2 |
| -10 | 2 |
+------+--------------+
LOGICAL_AND
LOGICAL_AND(expression)
說明
傳回所有非 NULL
運算式的邏輯 AND。如果輸入資料列數為零或 expression
針對所有資料列評估為 NULL
,就會傳回 NULL
。
支援的引數類型
BOOL
傳回資料類型
BOOL
範例
SELECT LOGICAL_AND(x) AS logical_and FROM UNNEST([true, false, true]) AS x;
+-------------+
| logical_and |
+-------------+
| false |
+-------------+
LOGICAL_OR
LOGICAL_OR(expression)
說明
傳回所有非 NULL
運算式的邏輯 OR。如果輸入資料列數為零或 expression
針對所有資料列評估為 NULL
,就會傳回 NULL
。
支援的引數類型
BOOL
傳回資料類型
BOOL
範例
SELECT LOGICAL_OR(x) AS logical_or FROM UNNEST([true, false, true]) AS x;
+------------+
| logical_or |
+------------+
| true |
+------------+
MAX
MAX(expression) [OVER (...)]
說明
傳回非 NULL
運算式的最大值。如果輸入資料列數為零或 expression
針對所有資料列評估為 NULL
,就會傳回 NULL
。
如果輸入中包含 NaN
,就會傳回 NaN
。
支援的引數類型
除下列項目外的任何資料類型:
ARRAY
STRUCT
選用子句
OVER
:指定範圍。請參閱 Analytic 函式。
傳回資料類型
與用於輸入值的資料類型相同。
範例
SELECT MAX(x) AS max
FROM UNNEST([8, 37, 4, 55]) AS x;
+-----+
| max |
+-----+
| 55 |
+-----+
SELECT x, MAX(x) OVER (PARTITION BY MOD(x, 2)) AS max
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;
+------+------+
| x | max |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8 | 8 |
| 4 | 8 |
| 37 | 55 |
| 55 | 55 |
+------+------+
MIN
MIN(expression) [OVER (...)]
說明
傳回非 NULL
運算式的最小值。如果輸入資料列數為零或 expression
針對所有資料列評估為 NULL
,就會傳回 NULL
。
如果輸入中包含 NaN
,就會傳回 NaN
。
支援的引數類型
除下列項目外的任何資料類型:
ARRAY
STRUCT
選用子句
OVER
:指定範圍。請參閱 Analytic 函式。
傳回資料類型
與用於輸入值的資料類型相同。
範例
SELECT MIN(x) AS min
FROM UNNEST([8, 37, 4, 55]) AS x;
+-----+
| min |
+-----+
| 4 |
+-----+
SELECT x, MIN(x) OVER (PARTITION BY MOD(x, 2)) AS min
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;
+------+------+
| x | min |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8 | 4 |
| 4 | 4 |
| 37 | 37 |
| 55 | 37 |
+------+------+
STRING_AGG
STRING_AGG([DISTINCT] expression [, delimiter] [ORDER BY key [{ASC|DESC}] [, ... ]] [LIMIT n])
[OVER (...)]
說明
傳回由串連非空值取得的值 (STRING 或 BYTES)。
如果指定 delimiter
,串連值會由那個分隔符號分隔;否則,會使用逗號作為分隔符號。
支援的引數類型
STRING BYTES
選用子句
子句會「以下列順序」套用:
OVER
:指定範圍。請參閱 Analytic 函式。這個子句目前與STRING_AGG()
內的其他所有子句都不相容。DISTINCT
:expression
的每個不同值都只會匯總一次到結果中。ORDER BY
:指定值的順序。- 針對每個排序金鑰,預設排序方向是
ASC
。 - NULL:在
ORDER BY
子句的內容中,NULL 是最小的可能值;也就是說,以ASC
順序排序時,NULL 會最先顯示,以DESC
順序排序時,NULL 會最後顯示。 - 浮點資料類型:有關排序與分組,請參閱浮點語意。
- 如果同時指定了
DISTINCT
,排序金鑰必須與expression
相同。 - 如果未指定
ORDER BY
,輸出陣列中的元素順序非絕對,這表示您可能會在每次使用這個函式時得到不同的結果。
- 針對每個排序金鑰,預設排序方向是
LIMIT
:在結果中指定expression
輸入的數量上限。限制適用於輸入字串數,不適用於輸入中的字元或位元組數。空字串會計為 1。系統不會計入 NULL 字串。限制n
必須是常數 INT64。
傳回資料類型
STRING BYTES
範例
SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;
+------------------------+
| string_agg |
+------------------------+
| apple,pear,banana,pear |
+------------------------+
SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+------------------------------+
| string_agg |
+------------------------------+
| apple & pear & banana & pear |
+------------------------------+
SELECT STRING_AGG(DISTINCT fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+-----------------------+
| string_agg |
+-----------------------+
| apple & pear & banana |
+-----------------------+
SELECT STRING_AGG(fruit, " & " ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+------------------------------+
| string_agg |
+------------------------------+
| pear & pear & apple & banana |
+------------------------------+
SELECT STRING_AGG(fruit, " & " LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+--------------+
| string_agg |
+--------------+
| apple & pear |
+--------------+
SELECT STRING_AGG(DISTINCT fruit, " & " ORDER BY fruit DESC LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+---------------+
| string_agg |
+---------------+
| pear & banana |
+---------------+
SELECT
fruit,
STRING_AGG(fruit, " & ") OVER (ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;
+--------+------------------------------+
| fruit | string_agg |
+--------+------------------------------+
| NULL | NULL |
| pear | pear & pear |
| pear | pear & pear |
| apple | pear & pear & apple |
| banana | pear & pear & apple & banana |
+--------+------------------------------+
SUM
SUM([DISTINCT] expression) [OVER (...)]
說明
傳回非空值的總和。
如果運算式是浮點值,總和非絕對,這表示您可能會在每次使用這個函式時得到不同的結果。
支援的引數類型
任何支援的數字資料類型。
選用子句
子句會「以下列順序」套用:
OVER
:指定範圍。請參閱 Analytic 函式。DISTINCT
:expression
的每個不同值都只會匯總一次到結果中。
傳回資料類型
如果輸入是整數,就會傳回 INT64。
如果輸入類型是 NUMERIC,就會傳回 NUMERIC。
如果輸入是浮點值,就會傳回 FLOAT64。
如果輸入中只包含 NULL
,就會傳回 NULL
。
如果輸入中包含 Inf
,就會傳回 Inf
。
如果輸入中包含 -Inf
,就會傳回 -Inf
。
如果輸入中包含 NaN
,就會傳回 NaN
。
如果輸入中包含 Inf
與 -Inf
的組合,就會傳回 NaN
。
範例
SELECT SUM(x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+-----+
| sum |
+-----+
| 25 |
+-----+
SELECT SUM(DISTINCT x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+-----+
| sum |
+-----+
| 15 |
+-----+
SELECT
x,
SUM(x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+---+-----+
| x | sum |
+---+-----+
| 3 | 6 |
| 3 | 6 |
| 1 | 10 |
| 4 | 10 |
| 4 | 10 |
| 1 | 10 |
| 2 | 9 |
| 5 | 9 |
| 2 | 9 |
+---+-----+
SELECT
x,
SUM(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+---+-----+
| x | sum |
+---+-----+
| 3 | 3 |
| 3 | 3 |
| 1 | 5 |
| 4 | 5 |
| 4 | 5 |
| 1 | 5 |
| 2 | 7 |
| 5 | 7 |
| 2 | 7 |
+---+-----+