- Advanced guides
- 時系列 - ギャップフィル
時系列データのギャップを埋める
時系列データを扱う際に、データが欠落していたり非アクティブであったりするためにギャップが発生することがあります。
一般的に、データをクエリする際にこれらのギャップを存在させたくありません。この場合、WITH FILL
句が役立ちます。
このガイドでは、WITH FILL
を使用して時系列データのギャップを埋める方法について説明します。
セットアップ
以下のテーブルがあり、GenAI画像サービスによって生成された画像のメタデータを格納していると仮定します。
CREATE TABLE images
(
`id` String,
`timestamp` DateTime64(3),
`height` Int64,
`width` Int64,
`size` Int64
)
ENGINE = MergeTree
ORDER BY (size, height, width);
いくつかのレコードをインポートしてみましょう:
INSERT INTO images VALUES (1088619203512250448, '2023-03-24 00:24:03.684', 1536, 1536, 2207289);
INSERT INTO images VALUES (1088619204040736859, '2023-03-24 00:24:03.810', 1024, 1024, 1928974);
INSERT INTO images VALUES (1088619204749561989, '2023-03-24 00:24:03.979', 1024, 1024, 1275619);
INSERT INTO images VALUES (1088619206431477862, '2023-03-24 00:24:04.380', 2048, 2048, 5985703);
INSERT INTO images VALUES (1088619206905434213, '2023-03-24 00:24:04.493', 1024, 1024, 1558455);
INSERT INTO images VALUES (1088619208524431510, '2023-03-24 00:24:04.879', 1024, 1024, 1494869);
INSERT INTO images VALUES (1088619208425437515, '2023-03-24 00:24:05.160', 1024, 1024, 1538451);
バケットによるクエリ
2023年3月24日の00:24:03
から00:24:04
までに作成された画像を調査するために、その時間ポイントのパラメータを作成します:
SET param_start = '2023-03-24 00:24:03',
param_end = '2023-03-24 00:24:04';
次に、データを100msのバケットにグループ化し、そのバケットで作成された画像のカウントを返すクエリを書きます:
SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
┌──────────────────bucket─┬─count─┐
│ 2023-03-24 00:24:03.600 │ 1 │
│ 2023-03-24 00:24:03.800 │ 1 │
│ 2023-03-24 00:24:03.900 │ 1 │
│ 2023-03-24 00:24:04.300 │ 1 │
│ 2023-03-24 00:24:04.400 │ 1 │
│ 2023-03-24 00:24:04.800 │ 1 │
└─────────────────────────┴───────┘
結果セットには画像が作成されたバケットのみが含まれますが、時系列分析では、エントリがなくても各100msのバケットを返したい場合があります。
WITH FILL
WITH FILL
句を使用してこれらのギャップを埋めることができます。
また、埋めるギャップのサイズを指定するSTEP
も設定します。
これはDateTime
型のデフォルト値が1秒ですが、我々は100msのギャップを埋めたいので、ステップ値として100msの間隔を設定します:
SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
STEP toIntervalMillisecond(100);
┌──────────────────bucket─┬─count─┐
│ 2023-03-24 00:24:03.600 │ 1 │
│ 2023-03-24 00:24:03.700 │ 0 │
│ 2023-03-24 00:24:03.800 │ 1 │
│ 2023-03-24 00:24:03.900 │ 1 │
│ 2023-03-24 00:24:04.000 │ 0 │
│ 2023-03-24 00:24:04.100 │ 0 │
│ 2023-03-24 00:24:04.200 │ 0 │
│ 2023-03-24 00:24:04.300 │ 1 │
│ 2023-03-24 00:24:04.400 │ 1 │
│ 2023-03-24 00:24:04.500 │ 0 │
│ 2023-03-24 00:24:04.600 │ 0 │
│ 2023-03-24 00:24:04.700 │ 0 │
│ 2023-03-24 00:24:04.800 │ 1 │
└─────────────────────────┴───────┘
count
カラムには0値が埋められたことが確認できます。
WITH FILL...FROM
ただし、時間範囲の開始時にまだギャップがあり、これを修正するためにFROM
を指定できます:
SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
FROM toDateTime64({start:String}, 3)
STEP toIntervalMillisecond(100);
┌──────────────────bucket─┬─count─┐
│ 2023-03-24 00:24:03.000 │ 0 │
│ 2023-03-24 00:24:03.100 │ 0 │
│ 2023-03-24 00:24:03.200 │ 0 │
│ 2023-03-24 00:24:03.300 │ 0 │
│ 2023-03-24 00:24:03.400 │ 0 │
│ 2023-03-24 00:24:03.500 │ 0 │
│ 2023-03-24 00:24:03.600 │ 1 │
│ 2023-03-24 00:24:03.700 │ 0 │
│ 2023-03-24 00:24:03.800 │ 1 │
│ 2023-03-24 00:24:03.900 │ 1 │
│ 2023-03-24 00:24:04.000 │ 0 │
│ 2023-03-24 00:24:04.100 │ 0 │
│ 2023-03-24 00:24:04.200 │ 0 │
│ 2023-03-24 00:24:04.300 │ 1 │
│ 2023-03-24 00:24:04.400 │ 1 │
│ 2023-03-24 00:24:04.500 │ 0 │
│ 2023-03-24 00:24:04.600 │ 0 │
│ 2023-03-24 00:24:04.700 │ 0 │
│ 2023-03-24 00:24:04.800 │ 1 │
└─────────────────────────┴───────┘
結果から、00:24:03.000
から00:24:03.500
のバケットがすべて表示されたことがわかります。
WITH FILL...TO
ただし、時間範囲の終わりのバケットがまだ欠落しており、TO
値を提供することで埋めることができます。
TO
は含まれないため、終了時間に少し足して含まれるようにします:
SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
FROM toDateTime64({start:String}, 3)
TO toDateTime64({end:String}, 3) + INTERVAL 1 millisecond
STEP toIntervalMillisecond(100);
┌──────────────────bucket─┬─count─┐
│ 2023-03-24 00:24:03.000 │ 0 │
│ 2023-03-24 00:24:03.100 │ 0 │
│ 2023-03-24 00:24:03.200 │ 0 │
│ 2023-03-24 00:24:03.300 │ 0 │
│ 2023-03-24 00:24:03.400 │ 0 │
│ 2023-03-24 00:24:03.500 │ 0 │
│ 2023-03-24 00:24:03.600 │ 1 │
│ 2023-03-24 00:24:03.700 │ 0 │
│ 2023-03-24 00:24:03.800 │ 1 │
│ 2023-03-24 00:24:03.900 │ 1 │
│ 2023-03-24 00:24:04.000 │ 0 │
│ 2023-03-24 00:24:04.100 │ 0 │
│ 2023-03-24 00:24:04.200 │ 0 │
│ 2023-03-24 00:24:04.300 │ 1 │
│ 2023-03-24 00:24:04.400 │ 1 │
│ 2023-03-24 00:24:04.500 │ 0 │
│ 2023-03-24 00:24:04.600 │ 0 │
│ 2023-03-24 00:24:04.700 │ 0 │
│ 2023-03-24 00:24:04.800 │ 1 │
│ 2023-03-24 00:24:04.900 │ 0 │
│ 2023-03-24 00:24:05.000 │ 0 │
└─────────────────────────┴───────┘
すべてのギャップが埋められ、00:24:03.000
から00:24:05.000
までの各100msについてエントリが得られました。
累積カウント
バケット全体で作成された画像の累積カウントを保持したいとします。
これを、以下に示すようにcumulative
カラムを追加することで実現できます:
SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count,
sum(count) OVER (ORDER BY bucket) AS cumulative
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
FROM toDateTime64({start:String}, 3)
TO toDateTime64({end:String}, 3) + INTERVAL 1 millisecond
STEP toIntervalMillisecond(100);
┌──────────────────bucket─┬─count─┬─cumulative─┐
│ 2023-03-24 00:24:03.000 │ 0 │ 0 │
│ 2023-03-24 00:24:03.100 │ 0 │ 0 │
│ 2023-03-24 00:24:03.200 │ 0 │ 0 │
│ 2023-03-24 00:24:03.300 │ 0 │ 0 │
│ 2023-03-24 00:24:03.400 │ 0 │ 0 │
│ 2023-03-24 00:24:03.500 │ 0 │ 0 │
│ 2023-03-24 00:24:03.600 │ 1 │ 1 │
│ 2023-03-24 00:24:03.700 │ 0 │ 0 │
│ 2023-03-24 00:24:03.800 │ 1 │ 2 │
│ 2023-03-24 00:24:03.900 │ 1 │ 3 │
│ 2023-03-24 00:24:04.000 │ 0 │ 0 │
│ 2023-03-24 00:24:04.100 │ 0 │ 0 │
│ 2023-03-24 00:24:04.200 │ 0 │ 0 │
│ 2023-03-24 00:24:04.300 │ 1 │ 4 │
│ 2023-03-24 00:24:04.400 │ 1 │ 5 │
│ 2023-03-24 00:24:04.500 │ 0 │ 0 │
│ 2023-03-24 00:24:04.600 │ 0 │ 0 │
│ 2023-03-24 00:24:04.700 │ 0 │ 0 │
│ 2023-03-24 00:24:04.800 │ 1 │ 6 │
│ 2023-03-24 00:24:04.900 │ 0 │ 0 │
│ 2023-03-24 00:24:05.000 │ 0 │ 0 │
└─────────────────────────┴───────┴────────────┘
累積カラムの値が我々の望むようには機能していません。
WITH FILL...INTERPOLATE
count
カラムに0
がある行も累積カラムに0
がありますが、我々はむしろ累積カラムの前の値を使用したいと考えています。
これは、以下のようにINTERPOLATE
句を使用することで実現できます:
SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count,
sum(count) OVER (ORDER BY bucket) AS cumulative
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
FROM toDateTime64({start:String}, 3)
TO toDateTime64({end:String}, 3) + INTERVAL 100 millisecond
STEP toIntervalMillisecond(100)
INTERPOLATE (cumulative);
┌──────────────────bucket─┬─count─┬─cumulative─┐
│ 2023-03-24 00:24:03.000 │ 0 │ 0 │
│ 2023-03-24 00:24:03.100 │ 0 │ 0 │
│ 2023-03-24 00:24:03.200 │ 0 │ 0 │
│ 2023-03-24 00:24:03.300 │ 0 │ 0 │
│ 2023-03-24 00:24:03.400 │ 0 │ 0 │
│ 2023-03-24 00:24:03.500 │ 0 │ 0 │
│ 2023-03-24 00:24:03.600 │ 1 │ 1 │
│ 2023-03-24 00:24:03.700 │ 0 │ 1 │
│ 2023-03-24 00:24:03.800 │ 1 │ 2 │
│ 2023-03-24 00:24:03.900 │ 1 │ 3 │
│ 2023-03-24 00:24:04.000 │ 0 │ 3 │
│ 2023-03-24 00:24:04.100 │ 0 │ 3 │
│ 2023-03-24 00:24:04.200 │ 0 │ 3 │
│ 2023-03-24 00:24:04.300 │ 1 │ 4 │
│ 2023-03-24 00:24:04.400 │ 1 │ 5 │
│ 2023-03-24 00:24:04.500 │ 0 │ 5 │
│ 2023-03-24 00:24:04.600 │ 0 │ 5 │
│ 2023-03-24 00:24:04.700 │ 0 │ 5 │
│ 2023-03-24 00:24:04.800 │ 1 │ 6 │
│ 2023-03-24 00:24:04.900 │ 0 │ 6 │
│ 2023-03-24 00:24:05.000 │ 0 │ 6 │
└─────────────────────────┴───────┴────────────┘
見た目がずっと良くなりました。
最後に、INTERPOLATE
句に新しいカラムを追加することを忘れずに、bar
関数を使って棒グラフを追加しましょう。
SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count,
sum(count) OVER (ORDER BY bucket) AS cumulative,
bar(cumulative, 0, 10, 10) AS barChart
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
FROM toDateTime64({start:String}, 3)
TO toDateTime64({end:String}, 3) + INTERVAL 100 millisecond
STEP toIntervalMillisecond(100)
INTERPOLATE (cumulative, barChart);
┌──────────────────bucket─┬─count─┬─cumulative─┬─barChart─┐
│ 2023-03-24 00:24:03.000 │ 0 │ 0 │ │
│ 2023-03-24 00:24:03.100 │ 0 │ 0 │ │
│ 2023-03-24 00:24:03.200 │ 0 │ 0 │ │
│ 2023-03-24 00:24:03.300 │ 0 │ 0 │ │
│ 2023-03-24 00:24:03.400 │ 0 │ 0 │ │
│ 2023-03-24 00:24:03.500 │ 0 │ 0 │ │
│ 2023-03-24 00:24:03.600 │ 1 │ 1 │ █ │
│ 2023-03-24 00:24:03.700 │ 0 │ 1 │ █ │
│ 2023-03-24 00:24:03.800 │ 1 │ 2 │ ██ │
│ 2023-03-24 00:24:03.900 │ 1 │ 3 │ ███ │
│ 2023-03-24 00:24:04.000 │ 0 │ 3 │ ███ │
│ 2023-03-24 00:24:04.100 │ 0 │ 3 │ ███ │
│ 2023-03-24 00:24:04.200 │ 0 │ 3 │ ███ │
│ 2023-03-24 00:24:04.300 │ 1 │ 4 │ ████ │
│ 2023-03-24 00:24:04.400 │ 1 │ 5 │ █████ │
│ 2023-03-24 00:24:04.500 │ 0 │ 5 │ █████ │
│ 2023-03-24 00:24:04.600 │ 0 │ 5 │ █████ │
│ 2023-03-24 00:24:04.700 │ 0 │ 5 │ █████ │
│ 2023-03-24 00:24:04.800 │ 1 │ 6 │ ██████ │
│ 2023-03-24 00:24:04.900 │ 0 │ 6 │ ██████ │
│ 2023-03-24 00:24:05.000 │ 0 │ 6 │ ██████ │
└─────────────────────────┴───────┴────────────┴──────────┘