時系列データのギャップを埋める
時系列データを扱うとき、データの欠落や非活動によりギャップが発生することがあります。
通常、データをクエリするときにこれらのギャップが存在しないことを望みます。このような場合に、WITH FILL
句が役立ちます。
このガイドでは、時系列データのギャップを埋めるための WITH FILL
の使い方について説明します。
セットアップ
次のようなテーブルがあり、GenAI画像サービスによって生成された画像のメタデータを格納しているとしましょう。
次に、いくつかのレコードをインポートします。
バケット別にクエリする
2023年3月24日の 00:24:03
と 00:24:04
の間に作成された画像を探索するので、その時間点のパラメータを作成しましょう。
次に、データを100msのバケットにグループ分けし、バケット内に作成された画像の数を返すクエリを書きます。
結果セットには画像が作成されたバケットのみが含まれていますが、時系列分析のためには、エントリーがない場合でも各100msバケットを返すことを望むかもしれません。
WITH FILL
WITH FILL
句を使用してこれらのギャップを埋めることができます。
ギャップを埋めるための STEP
も指定します。これは DateTime
型の場合、デフォルトで1秒ですが、100msの間隔を埋めたいので、ステップ値として100msの間隔を設定します。
ギャップが count
列の0の値で埋められたことが確認できます。
WITH FILL...FROM
しかし、時間範囲の最初にもギャップが残っています。これを FROM
を指定することで修正できます。
結果から、00:24:03.000
から00:24:03.500
までのバケットが全て表示されることが確認できます。
WITH FILL...TO
しかし、時間範囲の終わりにもいくつかのバケットが欠けています。これを TO
値を提供することで埋めることができます。
TO
は含まれないので、終了時間に少し追加してそれが含まれるようにします。
ギャップがすべて埋まり、00:24:03.000
から00:24:05.000
までの各100msにエントリーがあることが確認できます。
累積カウント
次に、バケット内で作成された画像の数を累積カウントで保持したいとします。
以下のように cumulative
列を追加することでこれを実現できます。
累積列の値は、私たちが望むようには動作していません。
WITH FILL...INTERPOLATE
count
列に 0
がある行は、累積列にも 0
があり、むしろ累積列の前の値を使用してほしいです。
これを INTERPOLATE
句を使用することで実現できます。
これでずっと良くなりました。
最後に、bar
関数を使ってバーチャートを追加し、INTERPOLATE
句に新しい列を追加することを忘れないようにしましょう。