ClickHouse 집계 함수: uniqHLL12, quantileTDigest, topK

ClickHouse 집계 함수: uniqHLL12와 quantileTDigest에 대한 두려움을 극복한 방법

베팅 분석에서 시간당 고유 플레이어 수를 계산해야 했습니다. PostgreSQL에서는 COUNT(DISTINCT user_id)를 작성하고 커피를 마시러 갔을 것입니다. ClickHouse에서는 5억 행에서 동일한 쿼리가 30초 만에 실행되었습니다. 하지만 비즈니스에서는 5초마다 새로고침되는 대시보드가 필요했습니다.

그때 uniqHLL12()를 발견했습니다 — 1-2% 오차의 근사 카운팅이지만 0.2초 만에 완료됩니다. 이 함수로 전환했고 대시보드는 즉시 빨라졌습니다. 디렉터는 숫자의 차이를 눈치채지 못했지만 속도는 분명히 알아차렸습니다.

ClickHouse는 표준 수학 함수뿐만 아니라 PostgreSQL이 꿈도 꾸지 못한 수십 가지 특수 집계 함수를 제공합니다. 아래는 실제 프로젝트에서 사용하는 모든 것입니다.

1. 표준 집계: 익숙하지만 더 빠름

-- 모든 베팅의 전체 통계
SELECT 
    count() AS total_bets,                    -- 베팅 수
    sum(amount) AS total_staked,              -- 총 베팅 금액
    avg(amount) AS avg_bet,                   -- 평균 베팅
    min(created_at) AS first_bet_time,        -- 첫 베팅
    max(created_at) AS last_bet_time,         -- 마지막 베팅
    max(amount) - min(amount) AS range        -- 범위
FROM betting.bets
WHERE created_at >= today() - 7;

PostgreSQL과의 차이점: count(*)와 count()는 동일하게 작동합니다. 하지만 count(DISTINCT user_id)는 느리므로 전용 함수를 사용하세요.

2. 고유 사용자: 정확성 vs 속도

ClickHouse는 고유 값 계산을 위한 세 가지 접근 방식을 제공합니다:

-- 정확하지만 느림 (5억 행에서 30초)
SELECT count(DISTINCT user_id) FROM betting.bets;

-- 정확하지만 문법적 설탕 없음
SELECT uniqExact(user_id) FROM betting.bets;

-- 근사, 빠름 (0.2초, 1-2% 오차)
SELECT uniq(user_id) FROM betting.bets;

-- 제어 가능한 오차의 HyperLogLog (우리가 사용)
SELECT uniqHLL12(user_id) FROM betting.bets;

-- 더 빠르지만 오차가 더 큼
SELECT uniqCombined(user_id) FROM betting.bets;

언제 무엇을 사용할까:

함수	오차	속도	적용처
uniqExact()	0%	느림	세금 보고서, 정확한 지급액
uniqHLL12()	1-2%	매우 빠름	대시보드, 트렌드, KPI
uniq()	2-4%	빠름	실시간 분석
uniqCombined()	5-8%	즉시	탐색적 분석, 추정

실제 프로덕션 예시: 라이브 대시보드에서 시간당 고유 플레이어 수를 계산하기 위해 uniqHLL12(user_id)를 사용합니다. 99% 정확도는 허용 가능하며 대시보드는 30초 대신 3초마다 새로고침됩니다.

3. 분위수: 히스토그램 없이 베팅 분포 파악

"100루블 미만으로 베팅하는 플레이어는 몇 명이고, 1000루블 초과는 몇 명인가?"와 같은 질문은 분위수에 관한 것입니다.

-- 50번째 백분위수 (중앙값)
SELECT quantile(0.5)(amount) FROM betting.bets;

-- 90번째 백분위수 (베팅의 90%가 이 금액 이하)
SELECT quantile(0.9)(amount) FROM betting.bets;

-- 여러 분위수를 한 번에
SELECT quantiles(0.5, 0.75, 0.9, 0.95, 0.99)(amount) FROM betting.bets;

-- 근사 분위수 (10배 빠름)
SELECT quantileTDigest(0.9)(amount) FROM betting.bets;

-- 정확하지만 느림 (메모리 내 정렬)
SELECT quantileExact(0.9)(amount) FROM betting.bets;

문제를 겪었던 부분: 10억 행에서 quantileExact()는 모든 메모리를 소모합니다. quantileTDigest()로 전환했습니다 — 0.5% 오차, 8GB 대신 100MB 메모리 사용.

실제 사용 사례: 사기 탐지를 위한 임계값 결정. 베팅의 99%가 50,000루블 미만인데 플레이어가 500,000루블을 베팅하면 검토 대상으로 보냅니다.

4. groupArray: 값을 배열로 수집

때로는 집계하지 않고 모든 값을 보존해야 할 때가 있습니다.

-- 사용자의 하루 베팅을 배열로
SELECT 
    user_id,
    toDate(created_at) AS day,
    groupArray(amount) AS amounts,
    groupArray(odds) AS odds_list,
    arrayMap(x -> x * 2, amounts) AS doubled  -- 배열 작업
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY user_id, day
LIMIT 10;

고급 사례: 이동 합계 및 평균.

-- 사용자별 마지막 3개 베팅의 이동 합계
SELECT 
    user_id,
    created_at,
    amount,
    groupArrayMovingSum(3)(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS moving_sum
FROM betting.bets
WHERE user_id = 1001
ORDER BY created_at;

이것이 정말 필요할 때: 베팅 시퀀스 분석 — 봇은 연속으로 동일한 금액을 배치하지만 실제 플레이어는 다양합니다.

5. topK: 정확한 값을 셀 필요 없음

질문: "가장 인기 있는 스포츠 5개는?" GROUP BY sport ORDER BY count() DESC LIMIT 5도 작동하지만, 10억 행에서는 백만 개의 고유 값에 대한 해시 테이블을 구축합니다.

-- 근사 상위 (빠름)
SELECT topK(5)(sport) FROM betting.bets;

-- 결과: ['football', 'basketball', 'tennis', 'hockey', 'mma']

-- 정확하지만 느림
SELECT sport, count() AS cnt 
FROM betting.bets 
GROUP BY sport 
ORDER BY cnt DESC 
LIMIT 5;

속도 차이: 100억 행에서 topK는 0.5초, 정확한 GROUP BY는 15초. 자동 새로고침 대시보드에서는 선택이 명확합니다.

6. -If 결합자: 서브쿼리 없이 조건부 집계

SUM(CASE WHEN ...) 대신 sumIf()를 작성하세요 — 가독성이 좋고 실행 속도도 빠릅니다.

-- 승리 및 패배 베팅을 한 행에
SELECT 
    user_id,
    countIf(outcome = 'win') AS wins,
    countIf(outcome = 'loss') AS losses,
    sumIf(amount, outcome = 'win') AS winning_stake,
    sumIf(amount, outcome = 'loss') AS losing_stake,
    avgIf(odds, outcome = 'win') AS avg_win_odds,
    -- 조건을 통한 승률
    round(wins / (wins + losses), 4) AS win_rate
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY user_id
HAVING wins + losses > 50
ORDER BY win_rate DESC
LIMIT 20;

기타 -If 함수: avgIf(), minIf(), maxIf(), anyIf(), uniqIf(), quantileIf().

7. AggregateFunction 타입과 -State/-Merge 결합자: 구체화된 뷰용

ClickHouse의 가장 강력한 도구: 데이터가 아닌 중간 집계 상태를 저장할 수 있습니다.

-- 집계 상태를 저장하는 테이블
CREATE TABLE betting.daily_agg
(
    day Date,
    user_id UInt64,
    total_bets AggregateFunction(count, UInt64),
    total_amount AggregateFunction(sum, Decimal(18,2)),
    unique_sports AggregateFunction(uniq, String)
)
ENGINE = AggregatingMergeTree()
ORDER BY (day, user_id);

-- -State로 삽입
INSERT INTO betting.daily_agg
SELECT 
    toDate(created_at) AS day,
    user_id,
    countState() AS total_bets,
    sumState(amount) AS total_amount,
    uniqState(sport) AS unique_sports
FROM betting.bets
GROUP BY day, user_id;

-- -Merge로 결과 조회
SELECT 
    day,
    user_id,
    countMerge(total_bets) AS bets,
    sumMerge(total_amount) AS total_staked,
    uniqMerge(unique_sports) AS unique_sports_count
FROM betting.daily_agg
GROUP BY day, user_id;

이것을 사용하는 곳: 시간별 집계를 위한 구체화된 뷰. 매번 20억 행을 다시 계산하는 대신 상태를 저장하고 병합만 수행합니다.

8. 기타 결합자: -OrDefault, -OrNull, -Array

-- -OrDefault: NULL 대신 기본값 반환
SELECT avgOrDefault(amount, 0) FROM betting.bets WHERE 1=0;  -- 0, NULL 아님

-- -OrNull: 행이 없으면 NULL 반환
SELECT avgOrNull(amount) FROM betting.bets WHERE 1=0;  -- NULL

-- -Array: 배열 요소에 대한 집계
SELECT groupArrayArray([[1,2], [3,4], [5,6]]) AS flattened;
-- 평탄화: [1,2,3,4,5,6]

9. runningAccumulate: 누적 합계 (강화된 윈도우 함수)

ClickHouse는 윈도우 함수를 지원하지만 runningAccumulate는 더 오래된 (때로는 더 빠른) 형태입니다.

-- 일별 베팅 누적 합계
SELECT 
    toDate(created_at) AS day,
    sum(amount) AS daily_amount,
    runningAccumulate(sum(amount)) OVER (ORDER BY day) AS cumulative_amount
FROM betting.bets
WHERE created_at >= today() - 30
GROUP BY day
ORDER BY day;

때때로 윈도우 함수를 선호하는 이유: runningAccumulate는 엄격한 순서가 필요하고 PARTITION BY를 지원하지 않습니다. 이제는 sum(amount) OVER (ORDER BY day)를 작성합니다.

10. 실제 예제: GGR, 코호트, 롤링 리텐션

예제 1: 일일 GGR (총 게임 수익)

GGR = 총 베팅 - 총 지급액.

SELECT 
    toDate(created_at) AS day,
    sum(amount) AS total_staked,
    sumIf(amount * odds, outcome = 'win') AS total_paid,
    total_staked - total_paid AS ggr,
    round(ggr / total_staked, 4) AS hold_percentage
FROM betting.bets
GROUP BY day
ORDER BY day DESC
LIMIT 30;

예제 2: 코호트 분석 (일별 플레이어 리텐션)

WITH cohorts AS (
    SELECT 
        user_id,
        toDate(min(created_at)) AS cohort_day
    FROM betting.bets
    GROUP BY user_id
),
user_activity AS (
    SELECT 
        b.user_id,
        c.cohort_day,
        toDate(b.created_at) AS activity_day,
        datediff('day', c.cohort_day, activity_day) AS day_number
    FROM betting.bets b
    JOIN cohorts c ON b.user_id = c.user_id
    WHERE day_number <= 30
)
SELECT 
    cohort_day,
    day_number,
    uniqHLL12(user_id) AS active_users
FROM user_activity
GROUP BY cohort_day, day_number
ORDER BY cohort_day DESC, day_number;

예제 3: 7일 롤링 리텐션

SELECT 
    toDate(created_at) AS day,
    uniqHLL12(user_id) AS dau,
    -- 7일 전에도 활동했던 사용자
    uniqHLL12If(user_id, 
        created_at >= today() - 7 AND created_at < today() - 6
    ) AS retained_users,
    round(retained_users / uniqHLL12If(user_id, 
        created_at >= today() - 14 AND created_at < today() - 13
    ), 4) AS retention_7d
FROM betting.bets
GROUP BY day
ORDER BY day DESC;

집계 작업 시 흔한 실수

실수 1: 수십억 행에서 COUNT(DISTINCT col) 사용.
해결책: uniqHLL12(col) 또는 정확성이 필요하면 uniqExact(col).

실수 2: 필터 없이 고유성 높은 열(user_id)에 GROUP BY 사용.
해결책: 항상 WHERE 또는 제한이 있는 HAVING 추가.

실수 3: 대규모 데이터에서 quantileExact() 사용.
해결책: quantileTDigest() 또는 Exact 없는 quantile(0.9).

실수 4: 이해 없이 집계 내에서 arrayJoin 사용.
해결책: arrayJoin은 행을 곱한다는 것을 기억하세요. 데이터를 비정규화하는 것이 좋습니다.

다음은?

집계는 분석의 핵심입니다. 다음 글은 ClickHouse의 윈도우 함수와 통계 테스트에 관한 것입니다.

---

← 이전 글: ClickHouse의 SELECT 쿼리: 10년간 PostgreSQL을 사용하다가 사고방식을 바꾼 방법
→ 다음 글: ClickHouse 설정: 운영 환경을 구성하면서 실수하지 않는 방법

— Editorial Team