기본 작업 - 시계열

ClickHouse의 기본 시계열 작업입니다.

ClickHouse는 시계열 데이터를 다루기 위한 여러 가지 방법을 제공하며, 이를 통해 서로 다른 시간 구간에 걸친 데이터 포인트를 집계하고 그룹화하며 분석할 수 있습니다. 이 섹션에서는 시간 기반 데이터를 다룰 때 일반적으로 사용하는 기본 작업을 설명합니다.

일반적인 작업으로는 데이터를 시간 인터벌별로 그룹화하고, 시계열 데이터의 누락 구간을 처리하며, 시간 구간 간 변화를 계산하는 작업이 있습니다. 이러한 작업은 표준 SQL 구문과 ClickHouse의 내장 시간 함수를 함께 사용하여 수행할 수 있습니다.

다음에서는 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') 
LIMIT 1e9;

​

시간 버킷별 집계

가장 일반적인 요구 사항은 시간 주기를 기준으로 데이터를 집계하는 것입니다. 예를 들어, 각 날짜별 hits의 총합을 구할 수 있습니다:

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() 함수를 사용했습니다. 또는 1시간 단위로 그룹화한 뒤 특정 날짜를 필터링할 수도 있습니다.

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 │ <- 누락된 값
│ 2015-07-01 02:00:00 │         1 │ <- 누락된 값
│ 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 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 │ <- 새 값
│ 2015-07-01 02:00:00 │         1 │
│ 2015-07-01 03:00:00 │         0 │ <- 새 값
│ 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 │ <- 새 값
│ 2015-07-01 11:00:00 │         0 │ <- 새 값
│ 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 │
└─────────────────────┴───────────┘

​

롤링 시간 윈도우

때로는 인터벌의 시작점(예: 하루나 한 시간의 시작)이 아니라 윈도우 인터벌을 기준으로 다뤄야 할 때가 있습니다. 예를 들어, 일(day) 기준이 아니라 오후 6시를 기준으로 하는 24시간 구간의 총 hits를 파악하려는 경우가 있습니다.

기준 시각과 각 레코드의 시각 간 차이를 계산하기 위해 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 │
└─────┴───────────┘