집계 함수

집계 함수 (Aggregate functions)

집계 함수(aggregate function)는 여러 행의 값에 대해 합계, 평균, 개수, 최소/최대, 표준편차, 추정 같은 수학적 계산과 일부 비수학적 연산을 수행해요. 스칼라 함수와의 차이, NULL 처리 방식, 사용 예시를 이해하면 집계 함수를 제대로 활용할 수 있어요.

출처: Snowflake SQL Reference

본문

집계 함수는 여러 행(실제로는 0개, 1개 또는 그 이상의 행)을 입력으로 받아 단일 출력을 생성해요. 반면 스칼라 함수는 한 행을 입력으로 받아 한 행(한 값)을 출력으로 생성해요.

집계 함수는 입력이 0개 행을 포함할 때조차 정확히 하나의 행을 반환해요. 일반적으로 입력이 0개 행이면 출력은 NULL이에요. 그러나 집계 함수는 0개 행이 전달될 때 0, 빈 문자열 또는 다른 값을 반환할 수도 있어요.

함수 목록 (하위 범주별) (List of functions by sub-category)

입문 예시 (Introductory example)

다음 예시는 집계 함수(AVG)와 스칼라 함수(COS)의 차이를 보여줘요. 스칼라 함수는 각 입력 행에 대해 하나의 출력 행을 반환하고, 집계 함수는 여러 입력 행에 대해 하나의 출력 행을 반환합니다.

테이블을 만들고 값으로 채웁니다:

CREATE TABLE simple (x INTEGER, y INTEGER);
INSERT INTO simple (x, y) VALUES
 (10, 20),
 (20, 44),
 (30, 70);

테이블을 조회합니다:

SELECT x, y
 FROM simple
 ORDER BY x,y;

+----+----+
| X | Y |
|----+----|
| 10 | 20 |
| 20 | 44 |
| 30 | 70 |
+----+----+

스칼라 함수는 각 입력 행에 대해 하나의 출력 행을 반환해요.

SELECT COS(x)
 FROM simple
 ORDER BY x;

+---------------+
| COS(X) |
|---------------|
| -0.8390715291 |
| 0.4080820618 |
| 0.1542514499 |
+---------------+

집계 함수는 여러 입력 행에 대해 하나의 출력 행을 반환합니다:

SELECT SUM(x)
 FROM simple;

+--------+
| SUM(X) |
|--------|
| 60 |
+--------+

집계 함수와 NULL 값 (Aggregate functions and NULL values)

일부 집계 함수는 NULL 값을 무시해요. 예를 들어 AVG는 값 1, 5, NULL의 평균을 3으로 계산해요. 다음 공식에 기반합니다:

(1 + 5) / 2 = 3

분자와 분모 모두에서 두 개의 비-NULL 값만 사용돼요. 집계 함수에 전달된 값이 모두 NULL이면 집계 함수는 NULL을 반환해요.

일부 집계 함수는 두 개 이상의 컬럼을 전달받을 수 있어요. 예를 들어:

SELECT COUNT(col1, col2) FROM table1;

이런 경우에 집계 함수는 개별 컬럼 중 하나라도 NULL이면 그 행을 무시해요. 예를 들어 다음 쿼리에서 COUNT는 1을 반환하고 4를 반환하지 않아요. 그 이유는 네 개의 행 중 세 개가 선택된 컬럼에 NULL 값을 하나 이상 포함하기 때문입니다.

CREATE OR REPLACE TABLE test_null_aggregate_functions (x INT, y INT);
INSERT INTO test_null_aggregate_functions (x, y) VALUES
 (1, 2), -- No NULLs.
 (3, NULL), -- One but not all columns are NULL.
 (NULL, 6), -- One but not all columns are NULL.
 (NULL, NULL); -- All columns are NULL.

SELECT COUNT(x, y) FROM test_null_aggregate_functions;

+-------------+
| COUNT(X, Y) |
|-------------|
| 1 |
+-------------+

SUM이 두 개 이상의 컬럼을 참조하는 표현식으로 호출되고, 그 컬럼 중 하나 이상이 NULL이면 표현식이 NULL로 평가되고 행이 무시됩니다:

SELECT SUM(x + y) FROM test_null_aggregate_functions;

+------------+
| SUM(X + Y) |
|------------|
| 3 |
+------------+

이 동작은 일부 컬럼이 NULL일 때 행을 버리지 않는 GROUP BY의 동작과 다릅니다:

SELECT x AS X_COL, y AS Y_COL
 FROM test_null_aggregate_functions
 GROUP BY x, y;

+-------+-------+
| X_COL | Y_COL |
|-------+-------|
| 1 | 2 |
| 3 | NULL |
| NULL | 6 |
| NULL | NULL |
+-------+-------+

더 알아보기 (Learn more)

• 스칼라 함수 (functions)
• 컨텍스트 함수 (functions-context)
• 변환 함수 (functions-conversion)
• 날짜 및 시간 함수 (functions-date-time)