집계 함수는 그룹의 행을 단일 값으로 요약하는 함수입니다. 집계 함수의 예로는 COUNT
, MIN
, MAX
가 있습니다.
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 |
+-------------+----------------+-------+------+
GROUP BY
절과 함께 사용하면 일반적으로 요약된 그룹에 하나 이상의 행이 있습니다. 연결된 SELECT
에 GROUP BY
절이 없거나 특정 집계 함수가 요약할 그룹의 행을 필터링하면 집계 함수가 빈 그룹을 요약해야 할 수 있습니다. 이 경우 COUNT
및 COUNTIF
함수는 0
을 반환하고 다른 모든 집계 함수는 NULL
을 반환합니다.
다음 섹션에서는 Cloud Spanner SQL이 지원하는 집계 함수를 설명합니다.
ANY_VALUE
ANY_VALUE(expression [HAVING {MAX | MIN} expression2])
설명
그룹에서 선택한 일부 행의 expression
을 반환합니다. 선택할 행은 확정되어 있지 않으며 무작위가 아닙니다. 입력으로 행이 생성되지 않으면 NULL
을 반환합니다. 그룹의 모든 행에서 expression
이 NULL
이면 NULL
을 반환합니다.
ANY_VALUE
는 IGNORE NULLS
가 지정된 것과 같이 작동합니다. expression
이 NULL
인 행은 고려되지 않으며 선택되지 않습니다.
지원되는 인수 유형
모두
선택적 절
HAVING MAX
또는 HAVING MIN
: 함수가 집계하는 행 집합을 최댓값 또는 최솟값으로 제한합니다. 자세한 내용은 HAVING MAX 및 HAVING MIN 절을 참조하세요.
반환되는 데이터 유형
입력 데이터 유형과 일치합니다.
예시
SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;
+-----------+
| any_value |
+-----------+
| apple |
+-----------+
ARRAY_AGG
ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS] [HAVING {MAX | MIN} expression2])
설명
expression
값의 ARRAY를 반환합니다.
지원되는 인수 유형
ARRAY를 제외한 모든 데이터 유형.
옵션 절
절은 다음 순서로 적용됩니다.
DISTINCT
: 각각의 고유한expression
값이 한 번만 결과에 집계됩니다.IGNORE NULLS
또는RESPECT NULLS
:IGNORE NULLS
가 지정되면NULL
값이 결과에서 제외됩니다.RESPECT NULLS
가 지정되면 결과에NULL
값이 포함됩니다. 아무것도 지정되지 않으면 결과에NULL
값이 포함됩니다.HAVING MAX
또는HAVING MIN
: 함수가 집계하는 행 집합을 최댓값 또는 최솟값으로 제한합니다. 자세한 내용은 HAVING MAX 및 HAVING MIN 절을 참조하세요.
출력 요소 순서
출력 내 요소의 순서는 확정값이 아닙니다. 즉, 이 함수를 사용할 때마다 다른 결과를 얻을 수 있다는 뜻입니다.
반환되는 데이터 유형
ARRAY
0이 입력된 행이 있는 경우, 이 함수는 NULL
을 반환합니다.
예시
SELECT 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 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 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] |
+-------------------+
ARRAY_CONCAT_AGG
ARRAY_CONCAT_AGG(expression [HAVING {MAX | MIN} expression2])
설명
ARRAY유형의 expression
에서 요소들을 연결하여 단일 ARRAY를 결과로 반환합니다. 이 함수는 NULL 입력 배열은 무시하지만 비 NULL 입력 배열의 NULL 요소는 고려합니다.
지원되는 인수 유형
ARRAY
선택적 절
HAVING MAX
또는 HAVING MIN
: 함수가 집계하는 행 집합을 최댓값 또는 최솟값으로 제한합니다. 자세한 내용은 HAVING MAX 및 HAVING MIN 절을 참조하세요.
출력 요소 순서
출력 내 요소의 순서는 확정값이 아닙니다. 즉, 이 함수를 사용할 때마다 다른 결과를 얻을 수 있다는 뜻입니다.
반환되는 데이터 유형
ARRAY
0이 입력된 행이 있거나 expression
이 모든 행에서 NULL로 평가되는 경우 NULL
을 반환합니다.
예시
SELECT 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] |
+-----------------------------------+
AVG
AVG([DISTINCT] expression [HAVING {MAX | MIN} expression2])
설명
NULL
이외 입력 값들의 평균을 반환하거나, 입력에 NaN
이 포함되어 있는 경우 NaN
을 반환합니다.
지원되는 인수 유형
INT64 등 모든 숫자 입력 유형. 부동 소수점 입력 유형의 경우 반환 결과가 확정값이 아닙니다. 즉, 이 함수를 사용할 때마다 다른 결과를 얻을 수 있다는 의미입니다.
옵션 절
절은 다음 순서로 적용됩니다.
DISTINCT
: 각각의 고유한expression
값이 한 번만 결과에 집계됩니다.HAVING MAX
또는HAVING MIN
: 함수가 집계하는 행 집합을 최댓값 또는 최솟값으로 제한합니다. 자세한 내용은 HAVING MAX 및 HAVING MIN 절을 참조하세요.
반환되는 데이터 유형
입력 | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
출력 | FLOAT64 | 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 |
+------+
BIT_AND
BIT_AND([DISTINCT] expression [HAVING {MAX | MIN} expression2])
설명
expression
에 대해 비트 AND 연산을 수행하여 그 결과를 반환합니다.
지원되는 인수 유형
- INT64
옵션 절
절은 다음 순서로 적용됩니다.
DISTINCT
: 각각의 고유한expression
값이 한 번만 결과에 집계됩니다.HAVING MAX
또는HAVING MIN
: 함수가 집계하는 행 집합을 최댓값 또는 최솟값으로 제한합니다. 자세한 내용은 HAVING MAX 및 HAVING MIN 절을 참조하세요.
반환되는 데이터 유형
INT64
예시
SELECT BIT_AND(x) as bit_and FROM UNNEST([0xF001, 0x00A1]) as x;
+---------+
| bit_and |
+---------+
| 1 |
+---------+
BIT_OR
BIT_OR([DISTINCT] expression [HAVING {MAX | MIN} expression2])
설명
expression
에 대해 비트 OR 연산을 수행하여 그 결과를 반환합니다.
지원되는 인수 유형
- INT64
옵션 절
절은 다음 순서로 적용됩니다.
DISTINCT
: 각각의 고유한expression
값이 한 번만 결과에 집계됩니다.HAVING MAX
또는HAVING MIN
: 함수가 집계하는 행 집합을 최댓값 또는 최솟값으로 제한합니다. 자세한 내용은 HAVING MAX 및 HAVING MIN 절을 참조하세요.
반환되는 데이터 유형
INT64
예시
SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;
+--------+
| bit_or |
+--------+
| 61601 |
+--------+
BIT_XOR
BIT_XOR([DISTINCT] expression [HAVING {MAX | MIN} expression2])
설명
expression
에 대해 비트 XOR 연산을 수행하여 그 결과를 반환합니다.
지원되는 인수 유형
- INT64
옵션 절
절은 다음 순서로 적용됩니다.
DISTINCT
: 각각의 고유한expression
값이 한 번만 결과에 집계됩니다.HAVING MAX
또는HAVING MIN
: 함수가 집계하는 행 집합을 최댓값 또는 최솟값으로 제한합니다. 자세한 내용은 HAVING MAX 및 HAVING MIN 절을 참조하세요.
반환되는 데이터 유형
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(*)
2.
COUNT([DISTINCT] expression [HAVING {MAX | MIN} expression2])
설명
- 입력에 있는 행의 수를 반환합니다.
NULL
이외의 값으로 평가된expression
이 있는 행의 수를 반환합니다.
지원되는 인수 유형
expression
에는 모든 데이터 유형이 지원됩니다. DISTINCT
가 있는 경우 expression
은 그룹화가 가능한 데이터 유형만 지원됩니다.
옵션 절
절은 다음 순서로 적용됩니다.
DISTINCT
: 각각의 고유한expression
값이 한 번만 결과에 집계됩니다.HAVING MAX
또는HAVING MIN
: 함수가 집계하는 행 집합을 최댓값 또는 최솟값으로 제한합니다. 자세한 내용은 HAVING MAX 및 HAVING MIN 절을 참조하세요.
반환 데이터 유형
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 COUNT(*) AS count_star, COUNT(x) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;
+------------+---------+
| count_star | count_x |
+------------+---------+
| 5 | 4 |
+------------+---------+
COUNTIF
COUNTIF([DISTINCT] expression [HAVING {MAX | MIN} expression2])
설명
expression
의 TRUE
값 개수를 반환합니다. 입력 행이 없거나 expression
이 모든 행에 대해 FALSE
또는 NULL
로 평가하는 경우 0
을 반환합니다.
지원되는 인수 유형
BOOL
옵션 절
절은 다음 순서로 적용됩니다.
DISTINCT
: 각각의 고유한expression
값이 한 번만 결과에 집계됩니다.HAVING MAX
또는HAVING MIN
: 함수가 집계하는 행 집합을 최댓값 또는 최솟값으로 제한합니다. 자세한 내용은 HAVING MAX 및 HAVING MIN 절을 참조하세요.
반환 데이터 유형
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 |
+--------------+--------------+
LOGICAL_AND
LOGICAL_AND(expression [HAVING {MAX | MIN} expression2])
설명
NULL
이 아닌 모든 표현식의 논리적 AND를 반환합니다. 0이 입력된 행이 있거나 expression
이 모든 행에서 NULL
로 평가되는 경우 NULL
을 반환합니다.
지원되는 인수 유형
BOOL
선택적 절
HAVING MAX
또는 HAVING MIN
: 함수가 집계하는 행 집합을 최댓값 또는 최솟값으로 제한합니다. 자세한 내용은 HAVING MAX 및 HAVING MIN 절을 참조하세요.
반환 데이터 유형
BOOL
예시
SELECT LOGICAL_AND(x) AS logical_and FROM UNNEST([true, false, true]) AS x;
+-------------+
| logical_and |
+-------------+
| false |
+-------------+
LOGICAL_OR
LOGICAL_OR(expression [HAVING {MAX | MIN} expression2])
설명
NULL
이 아닌 모든 표현식의 논리적 OR을 반환합니다. 0이 입력된 행이 있거나 expression
이 모든 행에서 NULL
로 평가되는 경우 NULL
을 반환합니다.
지원되는 인수 유형
BOOL
선택적 절
HAVING MAX
또는 HAVING MIN
: 함수가 집계하는 행 집합을 최댓값 또는 최솟값으로 제한합니다. 자세한 내용은 HAVING MAX 및 HAVING MIN 절을 참조하세요.
반환 데이터 유형
BOOL
예시
SELECT LOGICAL_OR(x) AS logical_or FROM UNNEST([true, false, true]) AS x;
+------------+
| logical_or |
+------------+
| true |
+------------+
MAX
MAX(expression [HAVING {MAX | MIN} expression2])
설명
NULL
이 아닌 표현식의 최댓값을 반환합니다. 0이 입력된 행이 있거나 expression
이 모든 행에서 NULL
로 평가되는 경우 NULL
을 반환합니다.
입력에 NaN
이 포함된 경우 NaN
을 반환합니다.
지원되는 인수 유형
다음을 제외한 모든 데이터 유형: ARRAY
STRUCT
선택적 절
HAVING MAX
또는 HAVING MIN
: 함수가 집계하는 행 집합을 최댓값 또는 최솟값으로 제한합니다. 자세한 내용은 HAVING MAX 및 HAVING MIN 절을 참조하세요.
반환 데이터 유형
입력값으로 사용된 데이터 유형과 동일합니다.
예시
SELECT MAX(x) AS max
FROM UNNEST([8, 37, 4, 55]) AS x;
+-----+
| max |
+-----+
| 55 |
+-----+
MIN
MIN(expression [HAVING {MAX | MIN} expression2])
설명
NULL
이 아닌 표현식의 최솟값을 반환합니다. 0이 입력된 행이 있거나 expression
이 모든 행에서 NULL
로 평가되는 경우 NULL
을 반환합니다.
입력에 NaN
이 포함된 경우 NaN
을 반환합니다.
지원되는 인수 유형
다음을 제외한 모든 데이터 유형: ARRAY
STRUCT
선택적 절
HAVING MAX
또는 HAVING MIN
: 함수가 집계하는 행 집합을 최댓값 또는 최솟값으로 제한합니다. 자세한 내용은 HAVING MAX 및 HAVING MIN 절을 참조하세요.
반환 데이터 유형
입력값으로 사용된 데이터 유형과 동일합니다.
예시
SELECT MIN(x) AS min
FROM UNNEST([8, 37, 4, 55]) AS x;
+-----+
| min |
+-----+
| 4 |
+-----+
STRING_AGG
STRING_AGG([DISTINCT] expression [, delimiter] [HAVING {MAX | MIN} expression2])
설명
null이 아닌 값을 연결하여 얻은 값(STRING 또는 BYTES)을 반환합니다.
delimiter
가 지정된 경우 연결된 값이 해당 구분 기호로 구분되고, 그렇지 않은 경우 쉼표가 구분 기호로 사용됩니다.
지원되는 인수 유형
STRING BYTES
옵션 절
절은 다음 순서로 적용됩니다.
DISTINCT
: 각각의 고유한expression
값이 한 번만 결과에 집계됩니다.HAVING MAX
또는HAVING MIN
: 함수가 집계하는 행 집합을 최댓값 또는 최솟값으로 제한합니다. 자세한 내용은 HAVING MAX 및 HAVING MIN 절을 참조하세요.
출력 요소 순서
출력 내 요소의 순서는 확정값이 아닙니다. 즉, 이 함수를 사용할 때마다 다른 결과를 얻을 수 있다는 뜻입니다.
반환 데이터 유형
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 |
+-----------------------+
SUM
SUM([DISTINCT] expression [HAVING {MAX | MIN} expression2])
설명
null이 아닌 값의 합계를 반환합니다.
표현식이 부동 소수점 값인 경우, 합계가 확정값이 아닙니다. 즉, 이 함수를 사용할 때마다 다른 결과를 얻을 수 있다는 뜻입니다.
지원되는 인수 유형
지원되는 모든 숫자 데이터 유형.
옵션 절
절은 다음 순서로 적용됩니다.
DISTINCT
: 각각의 고유한expression
값이 한 번만 결과에 집계됩니다.HAVING MAX
또는HAVING MIN
: 함수가 집계하는 행 집합을 최댓값 또는 최솟값으로 제한합니다. 자세한 내용은 HAVING MAX 및 HAVING MIN 절을 참조하세요.
반환 데이터 유형
입력 | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
출력 | INT64 | NUMERIC | FLOAT64 |
특수한 경우:
입력에 NULL
만 포함된 경우 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 SUM(x) AS sum
FROM UNNEST([]) AS x;
+------+
| sum |
+------+
| NULL |
+------+
공통 절
HAVING MAX 및 HAVING MIN 절
대부분의 집계 함수는 HAVING MAX
및 HAVING MIN
라는 두 개의 선택적 절을 지원하는데 이는 함수가 특정 열에서 최댓값 또는 최솟값을 가진 행으로 집계하는 행 집합을 제한합니다. 구문은 일반적으로 다음과 같습니다.
aggregate_function(expression1 [HAVING {MAX | MIN} expression2])
HAVING MAX
: 함수가 집계하는 행 집합을 그룹 내expression2
의 최댓값과 동일한expression2
값을 갖는 항목으로 제한합니다. 최댓값은MAX(expression2)
의 결과와 같습니다.HAVING MIN
: 함수가 집계하는 행 집합을 그룹 내expression2
의 최솟값과 동일한expression2
값을 갖는 항목으로 제한합니다. 최솟값은MIN(expression2)
의 결과와 같습니다.
이러한 절은 expression2
가 모든 행에서 NULL
로 평가되지 않는 한 최댓값 또는 최솟값을 계산할 때 NULL
값을 무시합니다.
이러한 절은 다음 데이터 유형을 지원하지 않습니다.
ARRAY
STRUCT
예시
이 예시에서는 최근 연도인 2001년에 대한 평균 강수량을 반환합니다.
WITH Precipitation AS
(SELECT 2001 as year, 'spring' as season, 9 as inches UNION ALL
SELECT 2001, 'winter', 1 UNION ALL
SELECT 2000, 'fall', 3 UNION ALL
SELECT 2000, 'summer', 5 UNION ALL
SELECT 2000, 'spring', 7 UNION ALL
SELECT 2000, 'winter', 2)
SELECT AVG(inches HAVING MAX year) as average FROM Precipitation
+---------+
| average |
+---------+
| 5 |
+---------+
먼저 이 쿼리는 year
열에서 최댓값이 있는 행을 가져옵니다.
다음과 같이 2개가 있습니다.
+------+--------+--------+
| year | season | inches |
+------+--------+--------+
| 2001 | spring | 9 |
| 2001 | winter | 1 |
+------+--------+--------+
마지막으로 이 쿼리는 inches
열(9 및 1)의 값을 다음과 같은 결과로 평균을 계산합니다.
+---------+
| average |
+---------+
| 5 |
+---------+