時系列分析関数

ClickHouse における時系列分析は、標準的な SQL の集約関数およびウィンドウ関数を用いて実行できます。
時系列データを扱う際には、一般的に次の 3 種類のメトリクスを扱います。

時間とともに単調に増加するカウンターメトリクス（ページビューやイベント総数など）
ある時点の状態を表し、増減しうるゲージメトリクス（CPU 使用率や温度など）
観測値をサンプリングし、バケット単位でカウントするヒストグラム（リクエストの処理時間やレスポンスサイズなど）

これらのメトリクスに対する一般的な分析パターンには、期間をまたいだ値の比較、累積合計の計算、変化率の算出、分布の分析などがあります。
これらはすべて、集約関数の組み合わせ、sum() OVER のようなウィンドウ関数、および histogram() のような専用関数を用いることで実現できます。

期間ごとの変化

時系列データを分析する際には、多くの場合、ある期間から次の期間へ値がどのように変化したかを把握する必要があります。これは、ゲージメトリクスとカウンターメトリクスの両方において重要です。 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 │
└────────────┴──────┴──────┴───────┘

累積値

カウンター型メトリクスは、時間の経過とともに自然に値が増加していきます。この累積的な増加を分析するには、ウィンドウ関数を使って累積合計を計算します。

次のクエリは、累積合計を作成する 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 時間あたりの合計値を 1 時間の秒数（3600）で割ることで、1 秒あたりのページビューのレートを計算します。バーグラフの可視化により、アクティビティがピークとなる時間帯を特定しやすくなります。

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() 関数を使用すると、ビンの数に基づいて自動的に適応的なヒストグラムを生成できます。

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

期間ごとの変化​

累積値​

レート計算​

ヒストグラム​

期間ごとの変化

累積値

レート計算

ヒストグラム