기본 연산

기본 연산 (Basic operations)

ClickHouse에서 시계열 데이터를 시간 버킷으로 집계·그룹화하고 빈 그룹을 채우는 기본 연산을 살펴봐요.

출처: 문서

본문

ClickHouse는 시계열 데이터 작업을 위한 여러 방법을 제공해요. 다양한 시간 기간에 걸쳐 데이터 포인트를 집계, 그룹화, 분석할 수 있게 해줘요. 이 섹션은 시간 기반 데이터로 작업할 때 흔히 사용되는 기본 연산을 다뤄요. 일반적인 연산에는 시간 간격별 데이터 그룹화, 시계열 데이터의 갭 처리, 시간 기간 사이의 변화 계산이 있어요. 이러한 연산은 ClickHouse의 내장 시간 함수와 결합된 표준 SQL 구문으로 수행할 수 있어요. Wikistat(Wikipedia 조회수 데이터) 데이터셋으로 ClickHouse 시계열 조회 능력을 살펴볼게요:

CREATE TABLE wikistat
(
    `time` DateTime,
    `project` String,
    `subproject` String,
    `path` String,
    `hits` UInt64
)
ENGINE = MergeTree
ORDER BY (time);

이 테이블을 10억 개의 레코드로 채울게요:

INSERT INTO wikistat
SELECT *
FROM s3('https://ClickHouse-public-datasets.s3.amazonaws.com/wikistat/partitioned/wikistat*.native.zst', NOSIGN)
LIMIT 1e9;

시간 버킷별 집계

가장 인기 있는 요구 사항은 기간을 기준으로 데이터를 집계하는 거예요. 예를 들어 하루의 총 조회수를 얻는 거죠:

SELECT
    toDate(time) AS date,
    sum(hits) AS hits
FROM wikistat
GROUP BY ALL
ORDER BY date ASC
LIMIT 5;

┌───────date─┬─────hits─┐
│ 2015-05-01 │ 25524369 │
│ 2015-05-02 │ 25608105 │
│ 2015-05-03 │ 28567101 │
│ 2015-05-04 │ 29229944 │
│ 2015-05-05 │ 29383573 │
└────────────┴──────────┘

여기서 지정된 시간을 날짜 타입으로 변환하는 toDate() 함수를 사용했어요. 대신 시간 단위로 배치하고 특정 날짜로 필터링할 수 있어요:

SELECT
    toStartOfHour(time) AS hour,
    sum(hits) AS hits
FROM wikistat
WHERE date(time) = '2015-07-01'
GROUP BY ALL
ORDER BY hour ASC
LIMIT 5;

┌────────────────hour─┬───hits─┐
│ 2015-07-01 00:00:00 │ 656676 │
│ 2015-07-01 01:00:00 │ 768837 │
│ 2015-07-01 02:00:00 │ 862311 │
│ 2015-07-01 03:00:00 │ 829261 │
│ 2015-07-01 04:00:00 │ 749365 │
└─────────────────────┴────────┘

여기서 사용된 toStartOfHour() 함수는 주어진 시간을 시간의 시작으로 변환해요. 연, 분기, 월, 일로도 그룹화할 수 있어요.

커스텀 그룹화 간격

toStartOfInterval() 함수로 5분처럼 임의의 간격으로도 그룹화할 수 있어요. 4시간 간격으로 그룹화하고 싶다고 가정해요. INTERVAL 절로 그룹화 간격을 지정할 수 있어요:

SELECT
    toStartOfInterval(time, INTERVAL 4 HOUR) AS interval,
    sum(hits) AS hits
FROM wikistat
WHERE date(time) = '2015-07-01'
GROUP BY ALL
ORDER BY interval ASC
LIMIT 6;

또는 toIntervalHour() 함수를 사용할 수 있어요:

SELECT
    toStartOfInterval(time, toIntervalHour(4)) AS interval,
    sum(hits) AS hits
FROM wikistat
WHERE date(time) = '2015-07-01'
GROUP BY ALL
ORDER BY interval ASC
LIMIT 6;

어느 쪽이든 다음 결과를 얻어요:

┌────────────interval─┬────hits─┐
│ 2015-07-01 00:00:00 │ 3117085 │
│ 2015-07-01 04:00:00 │ 2928396 │
│ 2015-07-01 08:00:00 │ 2679775 │
│ 2015-07-01 12:00:00 │ 2461324 │
│ 2015-07-01 16:00:00 │ 2823199 │
│ 2015-07-01 20:00:00 │ 2984758 │
└─────────────────────┴─────────┘

빈 그룹 채우기

많은 경우 일부 간격이 없는 희소 데이터를 다루게 돼요. 이로 인해 빈 버킷이 생겨요. 데이터를 1시간 간격으로 그룹화하는 다음 예시를 살펴볼게요. 일부 시간에 값이 없는 다음 통계가 출력될 거예요:

SELECT
    toStartOfHour(time) AS hour,
    sum(hits)
FROM wikistat
WHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01')
GROUP BY ALL
ORDER BY hour ASC;

┌────────────────hour─┬─sum(hits)─┐
│ 2015-07-01 00:00:00 │         3 │ <- missing values
│ 2015-07-01 02:00:00 │         1 │ <- missing values
│ 2015-07-01 04:00:00 │         1 │
│ 2015-07-01 05:00:00 │         2 │
│ 2015-07-01 06:00:00 │         1 │
│ 2015-07-01 07:00:00 │         1 │
│ 2015-07-01 08:00:00 │         3 │
│ 2015-07-01 09:00:00 │         2 │ <- missing values
│ 2015-07-01 12:00:00 │         2 │
│ 2015-07-01 13:00:00 │         4 │
│ 2015-07-01 14:00:00 │         2 │
│ 2015-07-01 15:00:00 │         2 │
│ 2015-07-01 16:00:00 │         2 │
│ 2015-07-01 17:00:00 │         1 │
│ 2015-07-01 18:00:00 │         5 │
│ 2015-07-01 19:00:00 │         5 │
│ 2015-07-01 20:00:00 │         4 │
│ 2015-07-01 21:00:00 │         4 │
│ 2015-07-01 22:00:00 │         2 │
│ 2015-07-01 23:00:00 │         2 │
└─────────────────────┴───────────┘

ClickHouse는 이를 해결하기 위한 WITH FILL 한정자를 제공해요. 이는 모든 빈 시간을 0으로 채워 시간에 따른 분포를 더 잘 이해할 수 있게 해줘요:

SELECT
    toStartOfHour(time) AS hour,
    sum(hits)
FROM wikistat
WHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01')
GROUP BY ALL
ORDER BY hour ASC WITH FILL STEP toIntervalHour(1);

┌────────────────hour─┬─sum(hits)─┐
│ 2015-07-01 00:00:00 │         3 │
│ 2015-07-01 01:00:00 │         0 │ <- new value
│ 2015-07-01 02:00:00 │         1 │
│ 2015-07-01 03:00:00 │         0 │ <- new value
│ 2015-07-01 04:00:00 │         1 │
│ 2015-07-01 05:00:00 │         2 │
│ 2015-07-01 06:00:00 │         1 │
│ 2015-07-01 07:00:00 │         1 │
│ 2015-07-01 08:00:00 │         3 │
│ 2015-07-01 09:00:00 │         2 │
│ 2015-07-01 10:00:00 │         0 │ <- new value
│ 2015-07-01 11:00:00 │         0 │ <- new value
│ 2015-07-01 12:00:00 │         2 │
│ 2015-07-01 13:00:00 │         4 │
│ 2015-07-01 14:00:00 │         2 │
│ 2015-07-01 15:00:00 │         2 │
│ 2015-07-01 16:00:00 │         2 │
│ 2015-07-01 17:00:00 │         1 │
│ 2015-07-01 18:00:00 │         5 │
│ 2015-07-01 19:00:00 │         5 │
│ 2015-07-01 20:00:00 │         4 │
│ 2015-07-01 21:00:00 │         4 │
│ 2015-07-01 22:00:00 │         2 │
│ 2015-07-01 23:00:00 │         2 │
└─────────────────────┴───────────┘

롤링 시간 윈도우

때로는 간격의 시작(하루나 한 시간의 시작처럼)이 아니라 윈도우 간격을 다루고 싶을 수 있어요. 하루가 아니라 오후 6시에서 시작하는 24시간 기간을 기준으로 윈도우의 총 조회수를 이해하고 싶다고 가정해요. date_diff() 함수를 사용해 참조 시간과 각 레코드의 시간 사이의 차이를 계산할 수 있어요. 이 경우 day 컬럼은 일 단위 차이(예: 1일 전, 2일 전 등)를 나타낼 거예요:

SELECT
    dateDiff('day', toDateTime('2015-05-01 18:00:00'), time) AS day,
    sum(hits),
FROM wikistat
GROUP BY ALL
ORDER BY day ASC
LIMIT 5;

┌─day─┬─sum(hits)─┐
│   0 │  25524369 │
│   1 │  25608105 │
│   2 │  28567101 │
│   3 │  29229944 │
│   4 │  29383573 │
└─────┴───────────┘

더 알아보기 (Learn more)

• 시계열 갭 채우기 (Time series filling gaps)
• 분석 함수 (Analysis functions)
• 날짜 시간 데이터 타입 (Date time data types)