메인 기술 탐색 안티스트레스
🇬🇧 English 🇪🇸 Español 🇩🇪 Deutsch 🇫🇷 Français 🇵🇱 Polski 🇨🇿 Česky 🇨🇳 中文 🇰🇷 한국어
홈으로 돌아가기

ClickHouse의 SELECT 쿼리: PostgreSQL과의 차이점, PREWHERE, SAMPLE

PostgreSQL과의 차이점에 중점을 둔 ClickHouse SELECT 쿼리에 대한 상세 가이드. PREWHERE(컬럼 읽기 전 필터링, I/O 절약), SAMPLE(빠른 근사 답변을 위한 확률적 샘플링), FINAL(ReplacingMergeTree에서 중복 제거 — 필요한 경우와 피하는 이유), 서브쿼리와의 IN vs JOIN 비교(ClickHouse에서 IN이 더 빠름), ANY/ALL 수정자, DISTINCT 성능 및 대안(uniq, topK)을 설명합니다. 출력 형식: Pretty, JSON, CSV, JSONEachRow. 베팅 분석을 위한 10가지 준비된 쿼리 제공: 스포츠별 베팅, 매출 상위 플레이어, 배당률별 승률, LTV, 사기 패턴. PostgreSQL에서 ClickHouse로 SQL을 마이그레이션할 때 일반적인 오류와 구체적인 수정 예시를 나열합니다.

By Sudonull Editorial Team • Published • Updated

ClickHouse SELECT: PostgreSQL에서 작동하지 않는 것(그리고 더 잘 작동하는 것)
Advertisement 728x90

ClickHouse의 SELECT 쿼리: 10년간 PostgreSQL을 사용하다가 사고방식을 바꾼 방법

10년 동안 PostgreSQL을 사용하다가 ClickHouse를 처음 접했을 때, 익숙한 SELECT * FROM bets WHERE sport = 'football' ORDER BY created_at DESC LIMIT 10 쿼리를 실행해봤습니다. 작동은 했지만, 의심스러울 정도로 빨랐습니다. 그런 다음 PREWHERE와 SAMPLE에 대해 배웠습니다. 이들은 행 기반 스토리지인 PostgreSQL에는 존재하지도 않고 존재할 수도 없는 구문입니다.

ClickHouse는 단순히 SQL을 실행하는 것이 아니라, 컬럼 기반 아키텍처에 맞게 SQL을 재고합니다. 아래는 제 쿼리(때로는 프로덕션)를 망가뜨린 모든 차이점입니다.

1. 기본 구문: 익숙한 얼굴, 컬럼 기반 특성

기본 SELECT는 익숙해 보입니다:

Google AdInline article slot
-- 간단한 선택
SELECT user_id, amount, odds 
FROM betting.bets 
WHERE created_at >= today() - 7 
ORDER BY amount DESC 
LIMIT 100;

하지만 EXPLAIN을 보면 차이가 드러납니다. PostgreSQL은 Seq Scan, Index Scan, Bitmap Heap Scan으로 계획을 세웁니다. ClickHouse는 읽은 그라뉼과 파티션의 수를 보여줍니다.

핵심 차이점: PostgreSQL에서는 SELECT *가 때로는 괜찮습니다(거의 모든 컬럼이 필요할 때). ClickHouse에서는 SELECT *가 디스크에서 모든 컬럼을 읽습니다. 30개 중 20개 컬럼이 필요 없다면 필요한 컬럼만 나열하세요. 이 방법으로 디스크 I/O를 70% 절약했습니다.

2. PREWHERE — PostgreSQL이 부러워하는 최적화

PREWHERE는 컬럼이 압축 해제되어 읽히기 전에 필터링합니다.

Google AdInline article slot
-- PREWHERE 없음 (느림)
SELECT user_id, amount, odds, sport
FROM betting.bets
WHERE outcome = 'win' AND amount > 1000;

-- PREWHERE 사용 (빠름)
SELECT user_id, amount, odds, sport
FROM betting.bets
PREWHERE outcome = 'win'
WHERE amount > 1000;

작동 방식:

  1. ClickHouse가 먼저 outcome 컬럼(디스크의 하나의 파일)을 읽습니다.
  2. 행을 필터링하여 win만 남깁니다.
  3. 필터링된 행에 대해서만 나머지 컬럼을 읽습니다.
  4. 그런 다음 amount > 1000을 적용합니다.

ClickHouse가 PREWHERE를 자동으로 적용하는 경우: WHERE outcome = 'win'이라고 쓰면 옵티마이저가 가벼운 조건을 자동으로 PREWHERE로 이동시킬 수 있습니다. 하지만 저는 복잡한 조건의 경우 항상 명시적으로 작성합니다.

저를 괴롭힌 점: PREWHERE는 PRIMARY KEY의 컬럼에서는 작동하지 않습니다. ClickHouse는 여전히 인덱스를 먼저 읽습니다. 이미 빠른 것을 최적화하려 하지 마세요.

Google AdInline article slot

3. SAMPLE — 0.1초 만에 근사치 답변

비즈니스에서는 때때로 "시간당 베팅의 대략적인 볼륨을 추정해 주세요, ±5% 정도면 됩니다"와 같은 질문을 받습니다. 절대적인 정확도는 필요하지 않습니다.

-- 10% 무작위 행 (SAMPLE 0.1)
SELECT 
    toHour(created_at) AS hour,
    count() * 10 AS estimated_total_bets
FROM betting.bets
SAMPLE 0.1
WHERE created_at >= now() - INTERVAL 1 HOUR
GROUP BY hour;

SAMPLE의 물리적 작동 방식: ClickHouse는 모든 그라뉼을 완전히 읽지 않고, N번째 그라뉼만 읽습니다. 이는 디스크의 데이터가 섞이지 않고 그라뉼 내에서 ORDER BY로 정렬되어 있기 때문에 가능합니다.

SAMPLE에 대한 제 규칙:

  • 수십억 행의 집계에는 SAMPLE 0.01로 2-3% 정확도면 충분합니다.
  • 정확한 계산(금융, 지급)에는 사용하지 마세요.
  • 테이블이 SAMPLE BY 키(또는 ORDER BY)로 생성된 경우에만 작동합니다.

4. FINAL — 초보자를 위한 지뢰밭

ReplacingMergeTree(중복 제거 엔진)를 사용하면 행에 여러 버전이 있을 수 있습니다. FINAL은 ClickHouse가 즉시 병합하도록 강제합니다.

-- 느림 (하지만 때로는 필요)
SELECT user_id, max(amount)
FROM betting.bets_replacing
FINAL
GROUP BY user_id;

제가 FINAL을 거의 사용하지 않는 이유: 모든 파트를 읽고 메모리에서 병합하도록 강제합니다. 수십억 행이 있으면 쿼리가 메모리 부족으로 실패합니다.

FINAL의 대안:

  • argMax를 사용한 그룹화 (권장)
  • 백그라운드에서 주기적인 OPTIMIZE TABLE ... FINAL
  • ReplacingMergeTree를 아예 사용하지 않기
-- FINAL 대신
SELECT user_id, argMax(amount, version) AS last_amount
FROM betting.bets_replacing
GROUP BY user_id;

5. 서브쿼리와 JOIN의 IN/NOT IN

PostgreSQL에서는 JOIN이 서브쿼리보다 빠른 경우가 많습니다. ClickHouse에서는 반대입니다. 서브쿼리와 함께 IN을 사용하는 것이 더 빠릅니다.

-- ClickHouse에서 빠름
SELECT user_id, sum(amount)
FROM betting.bets
WHERE user_id IN (SELECT user_id FROM betting.fraud_users)
GROUP BY user_id;

-- 느림 (하지만 더 읽기 쉬움)
SELECT b.user_id, sum(b.amount)
FROM betting.bets b
JOIN betting.fraud_users f ON b.user_id = f.user_id
GROUP BY b.user_id;

IN이 더 빠른 이유: ClickHouse는 서브쿼리를 메모리의 상수 집합으로 바꾸고 컬럼 연산을 사용하여 필터링합니다. JOIN은 행 단위 매칭이 필요합니다.

JOIN이 여전히 필요한 경우:

  • 테이블이 두 개 이상인 경우
  • SELECT에서 두 테이블의 컬럼이 모두 필요한 경우
  • 복잡한 조인 조건(단순 동등 조건이 아닌 경우)

6. ANY / ALL 수식어 — 초기 시절의 유물

이 수식어는 다른 DBMS와의 호환성을 위해 존재합니다. 저는 거의 사용하지 않습니다.

-- ANY: 그룹화에서 MIN과 유사
SELECT user_id, ANY(sport) AS any_sport
FROM betting.bets
GROUP BY user_id;

-- ALL: MAX와 유사
SELECT user_id, ALL(amount) AS all_amounts  -- 모든 금액의 배열
FROM betting.bets
GROUP BY user_id;

하지만 저는 명시적인 집계 함수(min(), max(), groupArray())를 선호합니다.

7. DISTINCT와 그 성능

ClickHouse의 SELECT DISTINCT는 PostgreSQL보다 빠르지만, 공짜는 아닙니다.

-- 모든 고유 스포츠
SELECT DISTINCT sport FROM betting.bets;

-- ORDER BY와 함께 DISTINCT
SELECT DISTINCT user_id, created_at
FROM betting.bets
ORDER BY created_at DESC
LIMIT 100;

내부 동작: ClickHouse는 메모리에 해시 테이블을 만듭니다. 수십억 개의 고유 값을 가진 컬럼에 DISTINCT를 실행하면 OOM이 발생합니다.

제 팁:

  • SELECT DISTINCT user_id 대신 GROUP BY user_id 사용 (동일한 결과)
  • 근사 고유 개수: uniq()와 uniqHLL12()
  • 상위 고유 값: topK()

8. FORMAT — 클라이언트가 필요로 하는 출력 형식

ClickHouse는 수십 가지 형식으로 결과를 반환할 수 있습니다. 저는 다섯 가지를 사용합니다:

-- 사람이 읽기 쉬운 (콘솔용)
SELECT * FROM bets LIMIT 3 FORMAT Pretty;

-- 간결 (기본값)
SELECT * FROM bets LIMIT 3 FORMAT PrettyCompact;

-- API용 JSON
SELECT * FROM bets LIMIT 3 FORMAT JSON;

-- 행 단위 JSON (파싱 시 메모리 절약)
SELECT * FROM bets LIMIT 3 FORMAT JSONEachRow;

-- Excel용 CSV
SELECT * FROM bets LIMIT 3 FORMAT CSV;
# 명령줄에서 형식 재정의 가능
clickhouse-client --format=JSON --query="SELECT * FROM bets LIMIT 3"

9. 베팅 분석을 위한 상위 10개 쿼리 (프로덕션 준비 완료)

1. 오늘 스포츠별 베팅

SELECT 
    sport,
    count() AS bets,
    sum(amount) AS total_staked,
    round(avg(odds), 2) AS avg_odds
FROM betting.bets
WHERE created_at >= today()
GROUP BY sport
ORDER BY total_staked DESC;

2. 이번 주 매출 상위 10명의 플레이어

SELECT 
    user_id,
    count() AS bets,
    sum(amount) AS total_staked,
    sumIf(amount * odds, outcome = 'win') AS total_won,
    round(total_won / total_staked, 4) AS roi
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY user_id
ORDER BY total_staked DESC
LIMIT 10;

3. 오늘 시간별 베팅 볼륨

SELECT 
    toHour(created_at) AS hour,
    count() AS bets,
    sum(amount) AS volume
FROM betting.bets
WHERE created_at >= today()
GROUP BY hour
ORDER BY hour;

4. 배당률 구간별 승률

SELECT 
    CASE 
        WHEN odds < 1.5 THEN '1.00-1.49'
        WHEN odds < 2.0 THEN '1.50-1.99'
        WHEN odds < 3.0 THEN '2.00-2.99'
        ELSE '3.00+'
    END AS odds_range,
    count() AS total_bets,
    countIf(outcome = 'win') AS wins,
    round(wins / total_bets, 4) AS win_rate
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY odds_range
ORDER BY odds_range;

5. 요일별 가장 활동적인 시간

SELECT 
    toDayOfWeek(created_at) AS dow,
    toHour(created_at) AS hour,
    count() AS bets
FROM betting.bets
WHERE created_at >= today() - 30
GROUP BY dow, hour
ORDER BY dow, hour;

6. 사용자별 평균 베팅 및 배당률 (LTV)

SELECT 
    user_id,
    avg(amount) AS avg_bet,
    avg(odds) AS avg_odds,
    count() AS total_bets,
    now() - max(created_at) AS hours_since_last_bet
FROM betting.bets
GROUP BY user_id
HAVING total_bets > 100
ORDER BY avg_bet DESC
LIMIT 50;

7. 시간당 대략적인 고유 플레이어 수

SELECT 
    toStartOfHour(created_at) AS hour,
    uniq(user_id) AS unique_users_approx,
    uniqExact(user_id) AS unique_users_exact
FROM betting.bets
WHERE created_at >= today() - 1
GROUP BY hour
ORDER BY hour;

8. 일별 지급 및 환불

SELECT 
    toDate(created_at) AS day,
    sum(amount) AS staked,
    sumIf(amount * odds, outcome = 'win') AS paid,
    sumIf(amount, outcome = 'void') AS refunded,
    round((paid + refunded) / staked, 4) AS net_hold_pct
FROM betting.bets
GROUP BY day
ORDER BY day DESC
LIMIT 30;

9. 팔레이 vs 단폴 베팅

SELECT 
    bet_type,
    count() AS bets,
    avg(amount) AS avg_stake,
    avg(odds) AS avg_odds,
    avgIf(amount * odds, outcome = 'win') AS avg_payout
FROM betting.bets
GROUP BY bet_type;

10. 의심스러운 패턴의 사용자 (사기)

SELECT 
    user_id,
    count() AS bets_5min,
    stddevPop(amount) AS stake_variance,
    stddevPop(odds) AS odds_variance
FROM betting.bets
WHERE created_at >= now() - INTERVAL 5 MINUTE
GROUP BY user_id
HAVING bets_5min > 30 AND stake_variance < 1 AND odds_variance < 0.1;

10. PostgreSQL에서 ClickHouse로 SQL 마이그레이션 시 흔한 실수

실수 1: 서브쿼리에서 SELECT * 사용 PostgreSQL에서는 괜찮습니다. ClickHouse에서는 모든 레벨에서 모든 컬럼을 읽습니다.

실수 2: 서브쿼리의 ORDER BY가 유지될 것이라고 기대 ClickHouse에서 서브쿼리는 ORDER BY가 있어도 순서를 보장하지 않습니다. 최상위 레벨에서만 정렬하세요.

실수 3: 상관 서브쿼리 ClickHouse는 상관 서브쿼리를 제대로 최적화하지 못합니다. JOIN이나 윈도우 함수로 다시 작성하세요.

-- 나쁨 (느림)
SELECT user_id, amount
FROM bets b1
WHERE amount = (SELECT max(amount) FROM bets b2 WHERE b2.user_id = b1.user_id);

-- 좋음 (빠름)
SELECT user_id, max(amount) AS max_amount
FROM bets
GROUP BY user_id;

실수 4: 트랜잭션 무결성 기대 ClickHouse에는 REPEATABLE READ가 없습니다. 쿼리 중에 데이터를 삽입하면 일부만 보일 수 있습니다.

실수 5: ALTER TABLE 없이 UPDATE와 DELETE 사용 ClickHouse에서 이들은 비동기적이고 무거운 뮤테이션입니다. 한 명령으로 백만 행을 업데이트하지 마세요.

다음은?

이제 ClickHouse에서 SELECT를 예상치 못한 문제 없이 작성하는 방법을 알게 되었습니다. 다음 글에서는 고급 집계와 윈도우 함수를 다룹니다.

← 이전 글:
→ 다음 글: ClickHouse 집계 함수: uniqHLL12와 quantileTDigest에 대한 두려움을 극복한 방법

— Editorial Team

Advertisement 728x90

다음 읽기

태그

#clickhouse-select #sql-differences #betting-analytics #query-optimization #clickhouse-syntax

주제

데이터베이스 SQL

FAQ

ClickHouse에서 PREWHERE란 무엇이며 WHERE와 어떻게 다른가요?

PREWHERE는 나머지 컬럼이 디스크에서 언패킹되어 읽히기 전에 데이터를 필터링합니다. 먼저 PREWHERE의 컬럼만 읽고, 행을 필터링한 후에만 나머지 컬럼을 읽습니다. 이로 인해 2~10배의 속도 향상이 있습니다. PostgreSQL은 행 기반 저장 방식으로 인해 이 최적화가 없습니다.

ClickHouse에서 SAMPLE은 언제 사용하나요?

2~5%의 정확도가 허용되는 빠른 근사 답변이 필요할 때: 시간당 총 베팅량 추정, 고유 사용자 수 근사, 백분위 분포. 재무 계산(지급액, 잔액)에는 사용하지 마십시오. SAMPLE 0.1은 무작위 과립의 10%를 읽습니다.

ClickHouse에서 FINAL이 해로운 이유는 무엇인가요?

FINAL은 테이블의 모든 파트를 읽고 메모리에서 실시간으로 병합합니다. 10억 행의 테이블에서 FINAL을 사용한 쿼리는 메모리 부족으로 중단됩니다. 쿼리에서 FINAL을 사용하는 대신 argMax(컬럼, 버전) GROUP BY 또는 백그라운드에서 주기적인 OPTIMIZE TABLE을 사용하는 것이 좋습니다.

ClickHouse에서 IN과 서브쿼리 중 무엇이 더 빠른가요? JOIN과 비교하여?

IN과 서브쿼리가 더 빠릅니다. ClickHouse는 서브쿼리를 메모리의 상수 집합으로 구체화하고 컬럼 필터링을 적용합니다. JOIN은 행 단위 매칭과 임시 테이블이 필요합니다. 두 테이블의 컬럼이나 복잡한 조인 조건이 필요한 경우에만 JOIN을 사용하십시오.