분석 함수

분석 함수 (Analysis functions)

ClickHouse에서 시계열 데이터를 표준 SQL 집계와 윈도우 함수로 분석하는 방법을 살펴봐요.

출처: 문서

본문

ClickHouse의 시계열 분석은 표준 SQL 집계와 윈도우 함수로 수행할 수 있어요. 시계열 데이터로 작업할 때 보통 세 가지 주요 메트릭 유형을 만나게 돼요:

  • 시간이 지나며 단조 증가하는 카운터 메트릭(페이지 조회수나 총 이벤트처럼)
  • 특정 시점의 측정값을 나타내며 오르내릴 수 있는 게이지 메트릭(CPU 사용량이나 온도처럼)
  • 관측치를 샘플링해 버킷으로 세는 히스토그램(요청 지속 시간이나 응답 크기처럼)

이 메트릭의 일반적인 분석 패턴은 기간 간 값 비교, 누적 합계 계산, 변화율 결정, 분포 분석을 포함해요. 이 모두 집계, sum() OVER 같은 윈도우 함수, histogram() 같은 전용 함수의 조합으로 달성할 수 있어요.

기간 대 기간 변화 (Period-over-period changes)

시계열 데이터를 분석할 때 시간 기간 사이의 값이 어떻게 변하는지 이해해야 하는 경우가 많아요. 이는 게이지와 카운터 메트릭 모두에 필수적이에요. lagInFrame 윈도우 함수를 사용하면 이전 기간의 값에 접근해 이러한 변화를 계산할 수 있어요. 다음 쿼리는 "Weird Al" Yankovic의 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 │
└────────────┴──────┴──────┴───────┘

누적 값 (Cumulative values)

카운터 메트릭은 자연스럽게 시간이 지나며 누적돼요. 이 누적 성장을 분석하려면 윈도우 함수로 누적 합계(running total)를 계산할 수 있어요. 다음 쿼리는 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 │ ████████████████▊ │
└────────────┴──────┴───────┴───────────────────┘

비율 계산 (Rate calculations)

시계열 데이터를 분석할 때 단위 시간당 이벤트 비율을 이해하는 것이 유용한 경우가 많아요. 이 쿼리는 시간별 합계를 시간의 초 수(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 │ ▉     │
└─────────────────────┴─────┴──────┴───────┘

히스토그램 (Histograms)

시계열 데이터의 인기 있는 사용 사례는 추적된 이벤트를 기반으로 히스토그램을 구축하는 거예요. 총 조회수가 10,000 이상인 페이지만 포함해 페이지 수의 분포를 이해하고 싶다고 가정해요. histogram() 함수를 사용해 버킷 수에 따라 적응형 히스토그램을 자동으로 생성할 수 있어요:

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 │ ▍                    │
└────────────┴────────────┴────────┴──────────────────────┘

더 알아보기 (Learn more)