시계열 분석 함수

ClickHouse에서 시계열 분석은 표준 SQL 집계 및 윈도 함수(window function)를 사용하여 수행할 수 있습니다. 시계열 데이터를 다룰 때는 일반적으로 다음과 같은 세 가지 주요 유형의 메트릭이 있습니다.

시간에 따라 단조 증가하는 카운터(counter) 메트릭 (페이지 조회 수나 전체 이벤트 수처럼)
시점별 측정값을 나타내며 값이 올라갔다 내려갔다 할 수 있는 게이지(gauge) 메트릭 (CPU 사용량이나 온도처럼)
관측값을 샘플링하여 버킷에 개수를 집계하는 히스토그램(histogram) (요청 지연 시간이나 응답 크기처럼)

이러한 메트릭에 대한 일반적인 분석 패턴에는 기간 간 값 비교, 누적 합계 계산, 변화율 산출, 분포 분석 등이 있습니다. 이러한 분석은 집계, sum() OVER와 같은 윈도 함수, histogram()와 같은 특수 함수를 조합하여 모두 수행할 수 있습니다.

기간별 변동

시계열 데이터를 분석할 때는 기간 사이에서 값이 어떻게 변하는지를 이해할 필요가 있습니다. 이는 게이지(gauge) 및 카운터(counter) 메트릭 모두에서 중요합니다. lagInFrame 윈도우 함수는 이전 기간의 값에 접근하여 이러한 변화를 계산할 수 있게 해줍니다.

다음 쿼리는 "Weird Al" Yankovic's Wikipedia 페이지의 조회수에 대해 일(日) 단위 전일 대비 변화를 계산하는 예시입니다. trend 컬럼은 이전 날짜와 비교했을 때 트래픽이 증가했는지(양수 값), 감소했는지(음수 값)를 보여 주어, 비정상적인 급증이나 급감을 식별하는 데 도움이 됩니다.

SELECT
    toDate(time) AS day,
    sum(hits) AS h,
    lagInFrame(h) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS p,
    h - p AS trend
FROM wikistat
WHERE path = '"Weird_Al"_Yankovic'
GROUP BY ALL
LIMIT 10;

┌────────day─┬────h─┬────p─┬─trend─┐
│ 2015-05-01 │ 3934 │    0 │  3934 │
│ 2015-05-02 │ 3411 │ 3934 │  -523 │
│ 2015-05-03 │ 3195 │ 3411 │  -216 │
│ 2015-05-04 │ 3076 │ 3195 │  -119 │
│ 2015-05-05 │ 3450 │ 3076 │   374 │
│ 2015-05-06 │ 3053 │ 3450 │  -397 │
│ 2015-05-07 │ 2890 │ 3053 │  -163 │
│ 2015-05-08 │ 3898 │ 2890 │  1008 │
│ 2015-05-09 │ 3092 │ 3898 │  -806 │
│ 2015-05-10 │ 3508 │ 3092 │   416 │
└────────────┴──────┴──────┴───────┘

누적 값

카운터 메트릭은 시간이 지남에 따라 자연스럽게 누적됩니다. 이 누적 증가를 분석하기 위해 윈도 함수(window function)를 사용해 누적 합계를 계산할 수 있습니다.

다음 쿼리는 sum() OVER 절을 사용해 누적 합계를 계산하는 방법을 보여줍니다. bar() 함수는 이러한 누적 증가를 시각적으로 표현합니다.

SELECT
    toDate(time) AS day,
    sum(hits) AS h,
    sum(h) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING) AS c,
    bar(c, 0, 50000, 25) AS b
FROM wikistat
WHERE path = '"Weird_Al"_Yankovic'
GROUP BY ALL
ORDER BY day
LIMIT 10;

┌────────day─┬────h─┬─────c─┬─b─────────────────┐
│ 2015-05-01 │ 3934 │  3934 │ █▉                │
│ 2015-05-02 │ 3411 │  7345 │ ███▋              │
│ 2015-05-03 │ 3195 │ 10540 │ █████▎            │
│ 2015-05-04 │ 3076 │ 13616 │ ██████▊           │
│ 2015-05-05 │ 3450 │ 17066 │ ████████▌         │
│ 2015-05-06 │ 3053 │ 20119 │ ██████████        │
│ 2015-05-07 │ 2890 │ 23009 │ ███████████▌      │
│ 2015-05-08 │ 3898 │ 26907 │ █████████████▍    │
│ 2015-05-09 │ 3092 │ 29999 │ ██████████████▉   │
│ 2015-05-10 │ 3508 │ 33507 │ ████████████████▊ │
└────────────┴──────┴───────┴───────────────────┘

비율 계산

시계열 데이터를 분석할 때는 단위 시간당 이벤트 발생 비율을 파악하는 것이 유용합니다. 이 쿼리는 시간별 합계를 1시간(3600초)의 초 수로 나누어 초당 페이지 조회 수 비율을 계산합니다. 막대 시각화는 활동이 가장 집중되는 시간을 파악하는 데 도움이 됩니다.

SELECT
    toStartOfHour(time) AS time,
    sum(hits) AS hits,
    round(hits / (60 * 60), 2) AS rate,
    bar(rate * 10, 0, max(rate * 10) OVER (), 25) AS b
FROM wikistat
WHERE path = '"Weird_Al"_Yankovic'
GROUP BY time
LIMIT 10;

┌────────────────time─┬───h─┬─rate─┬─b─────┐
│ 2015-07-01 01:00:00 │ 143 │ 0.04 │ █▊    │
│ 2015-07-01 02:00:00 │ 170 │ 0.05 │ ██▏   │
│ 2015-07-01 03:00:00 │ 148 │ 0.04 │ █▊    │
│ 2015-07-01 04:00:00 │ 190 │ 0.05 │ ██▏   │
│ 2015-07-01 05:00:00 │ 253 │ 0.07 │ ███▏  │
│ 2015-07-01 06:00:00 │ 233 │ 0.06 │ ██▋   │
│ 2015-07-01 07:00:00 │ 359 │  0.1 │ ████▍ │
│ 2015-07-01 08:00:00 │ 190 │ 0.05 │ ██▏   │
│ 2015-07-01 09:00:00 │ 121 │ 0.03 │ █▎    │
│ 2015-07-01 10:00:00 │  70 │ 0.02 │ ▉     │
└─────────────────────┴─────┴──────┴───────┘

히스토그램

시계열 데이터의 대표적인 사용 사례 중 하나는 추적된 이벤트를 기반으로 히스토그램을 생성하는 것입니다. 예를 들어, 각 페이지의 총 조회 수를 기준으로 한 분포를 파악하되, 조회 수가 10,000회를 초과하는 페이지만 포함한다고 가정합니다. 이때 histogram() FUNCTION을 사용하여 빈(bin) 개수에 따라 자동으로 적응형 히스토그램을 생성할 수 있습니다:

SELECT
    histogram(10)(hits) AS hist
FROM
(
    SELECT
        path,
        sum(hits) AS hits
    FROM wikistat
    WHERE date(time) = '2015-06-15'
    GROUP BY path
    HAVING hits > 10000
)
FORMAT Vertical;

Row 1:
──────
hist: [(10033,23224.55065359477,60.625),(23224.55065359477,37855.38888888889,15.625),(37855.38888888889,52913.5,3.5),(52913.5,69438,1.25),(69438,83102.16666666666,1.25),(83102.16666666666,94267.66666666666,2.5),(94267.66666666666,116778,1.25),(116778,186175.75,1.125),(186175.75,946963.25,1.75),(946963.25,1655250,1.125)]

그런 다음 arrayJoin()을 사용하여 데이터를 가공하고 bar()를 사용해 시각화할 수 있습니다.

WITH histogram(10)(hits) AS hist
SELECT
    round(arrayJoin(hist).1) AS lowerBound,
    round(arrayJoin(hist).2) AS upperBound,
    arrayJoin(hist).3 AS count,
    bar(count, 0, max(count) OVER (), 20) AS b
FROM
(
    SELECT
        path,
        sum(hits) AS hits
    FROM wikistat
    WHERE date(time) = '2015-06-15'
    GROUP BY path
    HAVING hits > 10000
);

┌─lowerBound─┬─upperBound─┬──count─┬─b────────────────────┐
│      10033 │      19886 │ 53.375 │ ████████████████████ │
│      19886 │      31515 │ 18.625 │ ██████▉              │
│      31515 │      43518 │  6.375 │ ██▍                  │
│      43518 │      55647 │  1.625 │ ▌                    │
│      55647 │      73602 │  1.375 │ ▌                    │
│      73602 │      92880 │   3.25 │ █▏                   │
│      92880 │     116778 │  1.375 │ ▌                    │
│     116778 │     186176 │  1.125 │ ▍                    │
│     186176 │     946963 │   1.75 │ ▋                    │
│     946963 │    1655250 │  1.125 │ ▍                    │
└────────────┴────────────┴────────┴──────────────────────┘

기간별 변동​

누적 값​

비율 계산​

히스토그램​

기간별 변동

누적 값

비율 계산

히스토그램