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

時系列データを扱っていると、欠損データや非活動によるギャップが生じることがあります。
通常、データをクエリする際に、これらのギャップが存在しないようにしたいと思います。その場合、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句に追加することを忘れないでください。