집계 함수

집계 함수는 그룹의 행을 단일 값으로 요약하는 함수입니다. 집계 함수의 예로는 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 값이 포함됩니다.
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 절을 참조하세요.

반환되는 데이터 유형

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 값이 한 번만 결과에 집계됩니다.
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를 반환합니다.
입력이 부동 소수점 값이면 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       |
+---------+