時系列データのギャップを埋める

時系列データを扱うとき、データの欠落や非活動によりギャップが発生することがあります。 通常、データをクエリするときにこれらのギャップが存在しないことを望みます。このような場合に、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 句に新しい列を追加することを忘れないようにしましょう。