집계 함수와 컴비네이터 (-If, -Array, -State, -Merge, uniq, quantile)

왜 컴비네이터부터 배우나

ClickHouse 집계 함수의 특징은 함수 이름 뒤에 접미사(컴비네이터) 를 붙여 동작을 바꾸는 점입니다. countIf, sumArray, uniqState처럼요. 다른 DB에서 SUM(CASE WHEN ... THEN 1 ELSE 0 END)로 쓰던 것이 countIf(...) 하나로 끝나고, 배열 컬럼을 풀지 않고 집계할 수 있고, 중간 상태를 저장해 뒤에 합칠 수 있습니다.

SELECT
    toDate(event_time) AS d,
    count() AS pv,
    countIf(status >= 500) AS err,
    uniq(user_id) AS uv,
    uniqIf(user_id, is_new = 1) AS new_uv,
    quantile(0.95)(duration_ms) AS p95,
    argMax(url, duration_ms) AS slowest_url
FROM web_logs
WHERE event_time >= today() - 7
GROUP BY d
ORDER BY d;

자주 쓰는 기본 집계 함수

팁 "각 사용자의 마지막 로그인 시각과 그때의 IP"는 argMax(ip, login_at)과 max(login_at)을 같은 GROUP BY에서 꺼내면 끝입니다. 윈도우 함수나 자기 조인이 필요하지 않습니다.

컴비네이터 한눈에

컴비네이터는 겹쳐 쓸 수 있습니다. uniqArrayIf(tags, status = 200)은 조건에 맞는 행의 배열을 펼쳐 고유 수를 셉니다. countIfState, sumMapMerge처럼 상태 컴비네이터와도 섞입니다. 접미사 순서는 안쪽 동작이 먼저 오도록, 보통 -Array, -If, 그다음 -State 순으로 붙입니다.

-- 조건별 집계를 CASE 없이
SELECT
    device,
    countIf(status < 400) AS ok,
    countIf(status BETWEEN 400 AND 499) AS client_err,
    countIf(status >= 500) AS server_err,
    avgIf(duration_ms, status < 400) AS ok_avg_ms
FROM web_logs
GROUP BY device;

-- 배열 컬럼을 ARRAY JOIN 없이
SELECT uniqArray(tags) AS distinct_tags, sumArray(item_prices) AS gmv
FROM orders
WHERE order_date = today();

근사 함수의 오차를 알고 고르기

uniq 가족

count(DISTINCT x)는 설정 count_distinct_implementation이 가리키는 함수(기본값 uniqExact)로 바뀝니다. 대시보드에서 count(DISTINCT user_id)를 쓰면 메모리를 많이 쓰니 uniq로 바꿀 것인지 먼저 정합니다.

quantile 가족

여러 분위수를 한 번에 구할 때는 quantiles(0.5, 0.9, 0.99)(x)처럼 복수형을 쓰면 데이터를 한 번만 읽습니다. 함수마다 quantilesTDigest, quantilesTiming이 있습니다.

SELECT
    service,
    quantilesTiming(0.5, 0.95, 0.99)(duration_ms) AS p,
    p[1] AS p50, p[2] AS p95, p[3] AS p99
FROM requests
WHERE event_time >= now() - INTERVAL 1 HOUR
GROUP BY service
ORDER BY p99 DESC;

참고 분위수 함수에 넘기는 값이 정렬 키의 일부라면 optimize_aggregation_in_order가 켜졌을 때 메모리를 덜 씁니다. 그래도 근사 함수 선택이 메모리에 가장 큰 영향을 줍니다.

-State와 -Merge: 미리 집계해 두기

-State는 집계의 중간 결과를 AggregateFunction(함수, 타입) 값으로 돌려줍니다. 이 값은 사람이 읽을 수 없지만 합칠 수 있습니다. 시간별 롤업 테이블에 상태를 저장해 두면, 일별이나 월별 고유 사용자 수를 다시 원본을 읽지 않고 -Merge로 구합니다. 머티리얼라이즈드 뷰와 AggregatingMergeTree의 핵심입니다.

CREATE TABLE daily_uv
(
    d       Date,
    country LowCardinality(String),
    uv      AggregateFunction(uniqCombined, UInt64),
    pv      SimpleAggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (d, country);

CREATE MATERIALIZED VIEW daily_uv_mv TO daily_uv AS
SELECT toDate(event_time) AS d, country,
       uniqCombinedState(user_id) AS uv, count() AS pv
FROM web_logs
GROUP BY d, country;

-- 조회: 상태를 합쳐 최종값으로
SELECT d, uniqCombinedMerge(uv) AS uv, sum(pv) AS pv
FROM daily_uv
WHERE d >= today() - 30
GROUP BY d ORDER BY d;

• uniqCombinedMerge처럼 같은 함수 이름으로 합쳐야 합니다. uniqState를 uniqCombinedMerge로 합칠 수 없습니다.
• sum, max, min, any, groupBitOr처럼 상태가 값 자체인 함수는 SimpleAggregateFunction을 쓰면 저장과 조회가 단순해집니다.
• -MergeState는 상태를 합쳐 다시 상태로 돌려주므로 일별 롤업에서 월별 롤업을 만들 때 씁니다.
• 상태 컬럼은 finalizeAggregation(state)로 바로 최종값을 볼 수 있습니다.

전체 설계는 머티리얼라이즈드 뷰와 MergeTree 가족에서 이어집니다.

GROUP BY 메모리와 설정

큰 GROUP BY에서 메모리 초과가 나면 다음 순서로 손봅니다.

1. 근사 함수로 바꾸기 (uniqExact를 uniq로, quantileExact를 quantileTDigest로).
2. 키 컬럼을 LowCardinality(String)으로, 고카디널리티 문자열 키는 해시나 숫자 ID로.
3. max_bytes_before_external_group_by를 두어 디스크로 흘리기. 보통 max_memory_usage의 절반 정도로 잡습니다.
4. 정렬 키 접두어로 집계한다면 optimize_aggregation_in_order = 1.
5. max_rows_to_group_by와 group_by_overflow_mode = 'any'로 상위 그룹만 보고 멈추기 (탐색용).

SELECT url, uniq(user_id) AS uv
FROM web_logs
GROUP BY url
ORDER BY uv DESC
LIMIT 100
SETTINGS max_bytes_before_external_group_by = 10000000000,
         max_memory_usage = 20000000000;

메모리 오류의 원인 분류는 메모리 초과 트러블슈팅에 있습니다.

WITH TOTALS, ROLLUP, CUBE, GROUPING SETS

SELECT country, device, count() AS c
FROM web_logs
WHERE event_date = today()
GROUP BY country, device WITH ROLLUP
ORDER BY country, device;

• WITH TOTALS는 결과 뒤에 전체 합계 행을 따로 붙입니다. totals_mode로 HAVING에 걸린 행을 합계에 넣을지 정합니다.
• ROLLUP은 (country, device), (country), () 순서로 상위 합계를 만듭니다.
• GROUPING SETS로 필요한 조합만 고르고, grouping(country)로 어느 수준의 행인지 구분합니다.

집계 쿼리를 쓸 때 체크리스트

• 조건 집계는 -If, 배열은 -Array, 두 개 이상이면 겹쳐 쓰기.
• 최신 상태는 argMax, 대표값은 any.
• 고유 수는 목적에 맞는 uniq*. 정산은 uniqExact.
• 분위수는 quantiles*(...)로 한 번에. 지연시간은 quantileTiming 또는 quantileTDigest.
• 반복되는 집계는 -State로 롤업 테이블에 저장하고 -Merge로 읽기.
• 메모리 초과는 근사 함수, LowCardinality, 외부 집계 순으로.

공식 문서

• 집계 함수 컴비네이터
• 집계 함수 목록
• uniq와 uniqCombined
• quantile과 quantiles
• AggregateFunction 타입
• GROUP BY 절