집계 함수

집계 함수 (Aggregate functions)

이 문서는 Trino의 집계 함수를 설명합니다. 값 집합에 대해 단일 결과를 계산하는 다양한 함수와, 정렬/필터링하며 집계하는 방법을 배울 수 있어요.

출처: 문서

본문

집계 함수는 값 집합에 대해 연산해 단일 결과를 계산합니다.

count(), count_if(), max_by(), min_by(), approx_distinct()을 제외한 모든 집계 함수는 null 값을 무시하고, 입력 행이 없거나 모든 값이 null이면 null을 반환합니다. 예를 들어 sum()은 0이 아니라 null을 반환하고 avg()는 카운트에 null 값을 포함하지 않습니다. coalesce 함수를 사용해 null을 0으로 변환할 수 있습니다.

집계 중 정렬 (Ordering during aggregation)

array_agg() 같은 일부 집계 함수는 입력 값의 순서에 따라 다른 결과를 만듭니다. 이 순서는 집계 함수 안에 ORDER BY 절을 작성해 지정할 수 있습니다:

array_agg(x ORDER BY y DESC)
array_agg(x ORDER BY x, y, z)

집계 중 필터링 (Filtering during aggregation)

FILTER 키워드를 사용하면 WHERE 절로 표현한 조건으로 집계 처리에서 행을 제거할 수 있습니다. 이것은 집계에 사용되기 전에 각 행에 대해 평가되며 모든 집계 함수에서 지원됩니다.

aggregate_function(...) FILTER (WHERE <condition>)

흔하고 매우 유용한 예로, array_agg를 사용할 때 FILTER로 null을 고려에서 제거하는 것입니다:

SELECT array_agg(name) FILTER (WHERE name IS NOT NULL)
FROM region;

또 다른 예로, Iris 꽃의 count에 조건을 추가하고 싶다면 다음 쿼리를 수정한다고 해봅시다:

SELECT species,
       count(*) AS count
FROM iris
GROUP BY species;

species    | count
-----------+-------
setosa     |   50
virginica  |   50
versicolor |   50

일반 WHERE 문만 사용하면 정보를 잃습니다:

SELECT species,
    count(*) AS count
FROM iris
WHERE petal_length_cm > 4
GROUP BY species;

species    | count
-----------+-------
virginica  |   50
versicolor |   34

필터를 사용하면 모든 정보를 유지합니다:

SELECT species,
       count(*) FILTER (where petal_length_cm > 4) AS count
FROM iris
GROUP BY species;

species    | count
-----------+-------
virginica  |   50
setosa     |    0
versicolor |   34

일반 집계 함수 (General aggregate functions)

any_value(x) → [입력과 동일]

존재하면 임의의 null이 아닌 값 x를 반환합니다. x는 어떤 유효한 표현식이든 될 수 있습니다. 이 함수를 사용하면 집계에 직접 속하지 않는 컬럼(이 컬럼을 사용하는 표현식 포함)의 값을 쿼리에서 반환할 수 있습니다.

예를 들어 다음 쿼리는 name 컬럼의 고객 이름을 반환하고 총 가격의 합을 고객 지출로 반환합니다. 다만 집계는 고객 식별자 custkey로 그룹화된 행을 사용하는데, 그 컬럼만 고유함이 보장되기 때문입니다:

SELECT sum(o.totalprice) as spend,
    any_value(c.name)
FROM tpch.tiny.orders o
JOIN tpch.tiny.customer c
ON o.custkey  = c.custkey
GROUP BY c.custkey;
ORDER BY spend;

arbitrary(x) → [입력과 동일]

존재하면 x의 임의의 null이 아닌 값을 반환합니다. any_value()와 동일합니다.

array_agg(x) → array<[입력과 동일]>

입력 x 요소로 만든 배열을 반환합니다.

avg(x) → double

모든 입력 값의 평균(산술 평균)을 반환합니다.

avg(real) → real

모든 입력 값의 평균(산술 평균)을 반환합니다.

avg(decimal) → decimal

모든 입력 값의 평균(산술 평균)을 반환합니다.

avg(number) → number

모든 입력 값의 평균(산술 평균)을 반환합니다.

avg(time interval type) → time interval type

모든 입력 값의 평균 간격 길이를 반환합니다.

bool_and(boolean) → boolean

모든 입력 값이 TRUE이면 TRUE, 그렇지 않으면 FALSE를 반환합니다.

bool_or(boolean) → boolean

어떤 입력 값이 TRUE이면 TRUE, 그렇지 않으면 FALSE를 반환합니다.

checksum(x) → varbinary

주어진 값의 순서에 민감하지 않은 체크섬을 반환합니다.

count(*) → bigint

입력 행의 수를 반환합니다.

count(x) → bigint

null이 아닌 입력 값의 수를 반환합니다.

count_if(x) → bigint

TRUE 입력 값의 수를 반환합니다. 이 함수는 count(CASE WHEN x THEN 1 END)와 동일합니다.

every(boolean) → boolean

bool_and()의 별칭입니다.

geometric_mean(x) → double

모든 입력 값의 기하 평균을 반환합니다.

listagg(x, separator) → varchar

separator 문자열로 구분해 입력 값을 연결해 반환합니다.

시그니처:

LISTAGG( expression [, separator] [ON OVERFLOW overflow_behaviour])
    WITHIN GROUP (ORDER BY sort_item, [...]) [FILTER (WHERE condition)]

expression 값은 문자열 데이터 유형(varchar)으로 평가되어야 합니다. 비문자열 데이터 유형은 listagg와 함께 사용하기 전에 CAST(expression AS VARCHAR)로 명시적으로 varchar로 캐스팅해야 합니다.

separator를 지정하지 않으면 빈 문자열이 separator로 사용됩니다.

가장 단순한 형태의 함수:

SELECT listagg(value, ',') WITHIN GROUP (ORDER BY value) csv_value
FROM (VALUES 'a', 'c', 'b') t(value);

결과는:

csv_value
-----------
'a,b,c'

다음 예제는 v 컬럼을 varchar로 캐스팅합니다:

SELECT listagg(CAST(v AS VARCHAR), ',') WITHIN GROUP (ORDER BY v) csv_value
FROM (VALUES 1, 3, 2) t(v);

결과는:

csv_value
-----------
'1,2,3'

오버플로우 동작은 기본적으로 함수 출력 길이가 1048576 바이트를 초과하면 오류를 던지는 것입니다:

SELECT listagg(value, ',' ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY value) csv_value
FROM (VALUES 'a', 'b', 'c') t(value);

또한 함수 출력 길이가 1048576 바이트를 초과할 때 생략된 null이 아닌 값의 개수를 WITH COUNT 또는 WITHOUT COUNT로 함께 출력을 잘라낼 수도 있습니다:

SELECT listagg(value, ',' ON OVERFLOW TRUNCATE '.....' WITH COUNT) WITHIN GROUP (ORDER BY value)
FROM (VALUES 'a', 'b', 'c') t(value);

지정하지 않으면 잘림 채움 문자열은 기본적으로 '...'입니다.

이 집계 함수는 그룹화가 포함된 시나리오에서도 사용할 수 있습니다:

SELECT id, listagg(value, ',') WITHIN GROUP (ORDER BY o) csv_value
FROM (VALUES
    (100, 1, 'a'),
    (200, 3, 'c'),
    (200, 2, 'b')
) t(id, o, value)
GROUP BY id
ORDER BY id;

결과는:

 id  | csv_value
-----+-----------
 100 | a
 200 | b,c

이 집계 함수는 필터 조건과 일치하지 않는 데이터의 집계가 여전히 출력에 표시되어야 하는 시나리오를 위한 집계 중 필터링을 지원합니다:

SELECT 
    country,
    listagg(city, ',')
        WITHIN GROUP (ORDER BY population DESC)
        FILTER (WHERE population >= 10_000_000) megacities
FROM (VALUES 
    ('India', 'Bangalore', 13_700_000),
    ('India', 'Chennai', 12_200_000),
    ('India', 'Ranchi', 1_547_000),
    ('Austria', 'Vienna', 1_897_000),
    ('Poland', 'Warsaw', 1_765_000)
) t(country, city, population)
GROUP BY country
ORDER BY country;

결과는:

 country |    megacities     
---------+-------------------
 Austria | NULL              
 India   | Bangalore,Chennai 
 Poland  | NULL

listagg 함수의 현재 구현은 윈도우 프레임을 지원하지 않습니다.

max(x) → [입력과 동일]

모든 입력 값의 최댓값을 반환합니다.

max(x, n) → array<[x와 동일]>

x의 모든 입력 값 중 가장 큰 n개의 값을 반환합니다.

max_by(x, y) → [x와 동일]

모든 입력 값 중 y의 최댓값과 연관된 x 값을 반환합니다.

max_by(x, y, n) → array<[x와 동일]>

y의 모든 입력 값 중 가장 큰 n개의 값과 연관된 x 값을 y의 내림차순으로 반환합니다.

min(x) → [입력과 동일]

모든 입력 값의 최솟값을 반환합니다.

min(x, n) → array<[x와 동일]>

x의 모든 입력 값 중 가장 작은 n개의 값을 반환합니다.

min_by(x, y) → [x와 동일]

모든 입력 값 중 y의 최솟값과 연관된 x 값을 반환합니다.

min_by(x, y, n) → array<[x와 동일]>

y의 모든 입력 값 중 가장 작은 n개의 값과 연관된 x 값을 y의 오름차순으로 반환합니다.

sum(x) → [입력과 동일]

모든 입력 값의 합을 반환합니다.

비트 연산 집계 함수 (Bitwise aggregate functions)

bitwise_and_agg(x) → bigint

2의 보수 표현에서 모든 입력 null이 아닌 값의 비트 AND를 반환합니다. 그룹 안의 모든 레코드가 NULL이거나 그룹이 비어 있으면 NULL을 반환합니다.

bitwise_or_agg(x) → bigint

2의 보수 표현에서 모든 입력 null이 아닌 값의 비트 OR을 반환합니다. 그룹 안의 모든 레코드가 NULL이거나 그룹이 비어 있으면 NULL을 반환합니다.

bitwise_xor_agg(x) → bigint

2의 보수 표현에서 모든 입력 null이 아닌 값의 비트 XOR을 반환합니다. 그룹 안의 모든 레코드가 NULL이거나 그룹이 비어 있으면 NULL을 반환합니다.

맵 집계 함수 (Map aggregate functions)

histogram(x) → map<K, bigint>

각 입력 값이 나타나는 횟수의 카운트를 담은 맵을 반환합니다.

map_agg(key, value) → map<K, V>

입력 key/value 쌍으로 만든 맵을 반환합니다.

map_union(x(K, V)) → map<K, V>

모든 입력 맵의 합집합을 반환합니다. 키가 여러 입력 맵에서 발견되면 결과 맵의 해당 키 값은 임의의 입력 맵에서 옵니다.

예를 들어 Iris 데이터셋으로 여러 맵을 만드는 다음 히스토그램 함수를 살펴봅시다:

SELECT histogram(floor(petal_length_cm)) petal_data
FROM memory.default.iris
GROUP BY species;

        petal_data
-- {4.0=6, 5.0=33, 6.0=11}
-- {4.0=37, 5.0=2, 3.0=11}
-- {1.0=50}

map_union으로 이 맵들을 결합할 수 있습니다:

SELECT map_union(petal_data) petal_data_union
FROM (
       SELECT histogram(floor(petal_length_cm)) petal_data
       FROM memory.default.iris
       GROUP BY species
       );

             petal_data_union
--{4.0=6, 5.0=2, 6.0=11, 1.0=50, 3.0=11}

multimap_agg(key, value) → map<K, array(V)>

입력 key/value 쌍으로 만든 멀티맵을 반환합니다. 각 키는 여러 값과 연관될 수 있습니다.

근사 집계 함수 (Approximate aggregate functions)

approx_distinct(x) → bigint

고유 입력 값의 근사 개수를 반환합니다. 이 함수는 count(DISTINCT x)의 근사치를 제공합니다. 모든 입력 값이 null이면 0을 반환합니다.

이 함수는 2.3%의 표준 오차를 생성해야 하며, 이는 모든 가능한 집합에 대한 (대략 정규적인) 오차 분포의 표준편차입니다. 특정 입력 집합에 대한 오차의 상한을 보장하지는 않습니다.

approx_distinct(x, e) → bigint

고유 입력 값의 근사 개수를 반환합니다. 이 함수는 count(DISTINCT x)의 근사치를 제공합니다. 모든 입력 값이 null이면 0을 반환합니다.

이 함수는 e 이하의 표준 오차를 생성해야 하며, 이는 모든 가능한 집합에 대한 (대략 정규적인) 오차 분포의 표준편차입니다. 특정 입력 집합에 대한 오차의 상한을 보장하지는 않습니다. 이 함수의 현재 구현은 e가 [0.0040625, 0.26000] 범위에 있어야 합니다.

approx_most_frequent(buckets, value, capacity) → map<[value와 동일], bigint>

최대 buckets개 요소까지 상위 빈도 값을 대략 계산합니다. 함수의 근사 추정으로 더 적은 메모리로 빈도 값을 뽑을 수 있습니다. 더 큰 capacity는 메모리를 희생하며 기반 알고리즘의 정확도를 높입니다. 반환값은 상위 요소와 해당 추정 빈도를 담은 맵입니다.

함수의 오차는 값의 순열과 카디널리티에 따라 달라집니다. 최소 오차를 얻으려면 capacity를 기반 데이터의 카디널리티와 동일하게 설정할 수 있습니다.

buckets와 capacity는 bigint여야 합니다. value는 숫자 또는 문자열 유형일 수 있습니다.

이 함수는 A. Metwalley, D. Agrawl, A. Abbadi의 "Efficient Computation of Frequent and Top-k Elements in Data Streams" 논문에 제안된 스트림 요약 데이터 구조를 사용합니다.

approx_percentile(x, percentage) → [x와 동일]

주어진 percentage에서 x의 모든 입력 값에 대한 근사 백분위수를 반환합니다. percentage 값은 0과 1 사이여야 하며 모든 입력 행에 대해 상수여야 합니다.

approx_percentile(x, percentages) → array<[x와 동일]>

지정된 각 백분위수에서 x의 모든 입력 값에 대한 근사 백분위수를 반환합니다. percentages 배열의 각 요소는 0과 1 사이여야 하며 배열은 모든 입력 행에 대해 상수여야 합니다.

approx_percentile(x, w, percentage) → [x와 동일]

개별 항목 가중치 w를 사용해 percentage에서 x의 모든 입력 값에 대한 근사 가중 백분위수를 반환합니다. 가중치는 1보다 크거나 같아야 합니다. 정수 값 가중치는 백분위수 집합에서 x 값의 복제 횟수로 생각할 수 있습니다. percentage 값은 0과 1 사이여야 하며 모든 입력 행에 대해 상수여야 합니다.

approx_percentile(x, w, percentages) → array<[x와 동일]>

개별 항목 가중치 w를 사용해 배열에 지정된 각 백분위수에서 x의 모든 입력 값에 대한 근사 가중 백분위수를 반환합니다. 가중치는 1보다 크거나 같아야 합니다. 정수 값 가중치는 백분위수 집합에서 x 값의 복제 횟수로 생각할 수 있습니다. percentages 배열의 각 요소는 0과 1 사이여야 하며 배열은 모든 입력 행에 대해 상수여야 합니다.

approx_set(x) → HyperLogLog

HyperLogLog 함수 문서를 참고하세요.

merge(x) → HyperLogLog

HyperLogLog 함수 문서를 참고하세요.

merge(qdigest(T)) → qdigest(T)

Quantile digest 함수 문서를 참고하세요.

merge(tdigest) → tdigest

T-Digest 함수 문서를 참고하세요.

numeric_histogram(buckets, value) → map<double, double>

모든 value에 대해 최대 buckets개의 버킷으로 대략적인 히스토그램을 계산합니다. 이 함수는 weight를 받는 numeric_histogram() 변형에서 개별 항목 가중치가 1인 것과 동일합니다.

numeric_histogram(buckets, value, weight) → map<double, double>

모든 value에 대해 개별 항목 가중치 weight로 최대 buckets개의 버킷으로 대략적인 히스토그램을 계산합니다. 알고리즘은 대략 다음에 기반합니다:

Yael Ben-Haim and Elad Tom-Tov, "A streaming parallel decision tree algorithm",
J. Machine Learning Research 11 (2010), pp. 849--872.

buckets는 bigint여야 합니다. value와 weight는 숫자여야 합니다.

qdigest_agg(x) → qdigest([x와 동일])

Quantile digest 함수 문서를 참고하세요.

qdigest_agg(x, w) → qdigest([x와 동일])

Quantile digest 함수 문서를 참고하세요.

qdigest_agg(x, w, accuracy) → qdigest([x와 동일])

Quantile digest 함수 문서를 참고하세요.

tdigest_agg(x) → tdigest

T-Digest 함수 문서를 참고하세요.

tdigest_agg(x, w) → tdigest

T-Digest 함수 문서를 참고하세요.

통계 집계 함수 (Statistical aggregate functions)

corr(y, x) → double

입력 값의 상관 계수를 반환합니다.

covar_pop(y, x) → double

입력 값의 모집단 공분산을 반환합니다.

covar_samp(y, x) → double

입력 값의 표본 공분산을 반환합니다.

kurtosis(x) → double

모든 입력 값의 초과 첨도(excess kurtosis)를 반환합니다. 다음 표현식을 사용한 편향 없는 추정값입니다:

kurtosis(x) = n(n+1)/((n-1)(n-2)(n-3))sum[(x_i-mean)^4]/stddev(x)^4-3(n-1)^2/((n-2)(n-3))

regr_intercept(y, x) → double

입력 값의 선형 회귀 절편을 반환합니다. y는 종속 값이고 x는 독립 값입니다.

regr_slope(y, x) → double

입력 값의 선형 회귀 기울기를 반환합니다. y는 종속 값이고 x는 독립 값입니다.

skewness(x) → double

모든 입력 값의 피셔 모멘트 왜도 계수를 반환합니다.

stddev(x) → double

stddev_samp()의 별칭입니다.

stddev_pop(x) → double

모든 입력 값의 모집단 표준편차를 반환합니다.

stddev_samp(x) → double

모든 입력 값의 표본 표준편차를 반환합니다.

variance(x) → double

var_samp()의 별칭입니다.

var_pop(x) → double

모든 입력 값의 모집단 분산을 반환합니다.

var_samp(x) → double

모든 입력 값의 표본 분산을 반환합니다.

람다 집계 함수 (Lambda aggregate functions)

reduce_agg(inputValue T, initialState S, inputFunction(S, T, S), combineFunction(S, S, S)) → S

모든 입력 값을 단일 값으로 축소합니다. inputFunction은 각 null이 아닌 입력 값에 대해 호출됩니다. 입력 값을 받는 것에 더해 inputFunction은 현재 상태(초기에는 initialState)를 받아 새 상태를 반환합니다. combineFunction은 두 상태를 새 상태로 결합하는 데 호출됩니다. 최종 상태가 반환됩니다:

SELECT id, reduce_agg(value, 0, (a, b) -> a + b, (a, b) -> a + b)
FROM (
    VALUES
        (1, 3),
        (1, 4),
        (1, 5),
        (2, 6),
        (2, 7)
) AS t(id, value)
GROUP BY id;
-- (1, 12)
-- (2, 13)

SELECT id, reduce_agg(value, 1, (a, b) -> a * b, (a, b) -> a * b)
FROM (
    VALUES
        (1, 3),
        (1, 4),
        (1, 5),
        (2, 6),
        (2, 7)
) AS t(id, value)
GROUP BY id;
-- (1, 60)
-- (2, 42)

상태 유형은 부울, 정수, 부동 소수점, char, varchar 또는 date/time/interval이어야 합니다.

더 알아보기 (Learn more)

집계 함수와 함께 자주 쓰이는 그룹화, 윈도우 함수도 살펴보세요. FILTER와 ORDER BY를 집계 함수 안에서 활용하면 더 정밀한 결과를 얻을 수 있어요.