時系列データの補完に使えるRESAMPLE句と補完関数を試してみた #SnowflakeDB

2025.10.03
 はじめに2025年9月のアップデートで、時系列データの欠損値を埋めるために使用できる RESAMPLE 句と補間関数（INTERPOLATE_FFILL, INTERPOLATE_BFILL, INTERPOLATE_LINEAR）がパブリックプレビューとなりました。
https://docs.snowflake.com/en/release-notes/2025/9_26#filling-gaps-in-time-series-data-preview
こちらの機能を試してみた内容を、本記事でまとめます。
 アップデートの概要時系列データは、機器の故障など様々な理由でデータが欠落する可能性があります。

そのため、各種分析や機械学習モデルの構築時は、このデータの欠損を埋める作業が行われる場面があります。
今回のアップデートにより、この前処理を Snowflake の SQL ベースで簡単に行えるようになります。
具体的には、以下の機能が追加されました。
 RESAMPLE 句FROM 句で使用でき、時系列データのアップサンプリング、またはダウンサンプリングを行えます。
アップサンプリング
時系列データの時間粒度をより細かく揃える
例：15分間隔で記録されたデータを5分間隔に揃える など

この際 RESAMPLE 句は、レコードに不足している時間間隔を自動で検出し、新しい行を生成して NULL で埋める処理を行います

ダウンサンプリング
時系列データの時間粒度をより粗く揃える
例：1分間隔で記録されたデータを10分間隔に集計したい など

RESAMPLE 句と集計関数を組み合わせることで、指定した間隔ごとにデータを集約できます

https://docs.snowflake.com/en/sql-reference/constructs/resample
 補完関数RESAMPLE 句で補完されるレコードの各観測値はデフォルトで NULL となります。
あわせてプレビューとなった補完関数を組み合わせることで、時系列の前後のデータの既存の値から、この NULL の値を推定して埋めることができます。これにより、欠損値がある不完全なデータセットを、分析や機械学習モデルの入力として、より完全な状態で利用できます。
これらはウィンドウ関数として提供されるため、PARTITION BY 句と組み合わせることで、特定のグループごとに指定のロジックを適用できます。補完のロジックごとに以下の関数を使用できます。
INTERPOLATE_FFILL
FFILL（Forward Fill）として、直前の観測値を使用して欠損値を埋めます
データの値が次の観測まで一定であると仮定できるような場合に使用できます

INTERPOLATE_BFILL
BFILL（Backward Fill）として、直後の観測値を使用して欠損値を埋めます

INTERPOLATE_LINEAR
直前と直後の値の間を線形に補間して欠損値を埋めます
データの変化が線形と仮定できる場合に使用できます

https://docs.snowflake.com/en/sql-reference/functions/interpolate_bfill
 試してみるRESAMPLE 句によるサンプリング、サンプリングされたレコードの観測値の補完を試してみます。
 RESAMPLE 句サンプルデータとして、公式ドキュメント記載の内容を参考に以下のデータを用意しました。

					
			CREATE OR REPLACE TABLE sample_data
 (observed TIMESTAMP, temperature INT, city VARCHAR(20), county VARCHAR(20));

INSERT INTO sample_data VALUES
  ('2025-03-15 09:50:15.000', 44, 'South Lake Tahoe', 'El Dorado'),
  ('2025-03-15 09:55:30.000', 46, 'South Lake Tahoe', 'El Dorado'),
  ('2025-03-15 10:00:05.000', 49, 'South Lake Tahoe', 'El Dorado'),
  ('2025-03-15 10:05:45.000', 52, 'South Lake Tahoe', 'El Dorado'),  
  ('2025-03-15 09:49:00.000', 48, 'Big Bear City', 'San Bernardino'),
  ('2025-03-15 09:52:15.000', 45, 'Big Bear City', 'San Bernardino'),
  ('2025-03-15 09:55:00.000', 49, 'Big Bear City', 'San Bernardino'),
  ('2025-03-15 10:07:30.000', 50, 'Big Bear City', 'San Bernardino'), 
  ('2025-03-15 10:10:00.000', 51, 'Big Bear City', 'San Bernardino'),
  ('2025-03-15 10:18:00.000', 54, 'Big Bear City', 'San Bernardino'),
  ('2025-03-15 10:04:00.000', 60, 'Los Angeles', 'Los Angeles');

		
		
基本的な RESAMPLE の実行クエリを試してみます。
詳細の構文はこちらをご参照ください。

以下ではアップサンプリングを試しています。

					
			SELECT *
FROM sample_data
  RESAMPLE (
    USING observed
    INCREMENT BY INTERVAL '5 minutes'
    PARTITION BY city, county)
ORDER BY city, county, observed;

		
		
上記のクエリのポイントは以下です。
USING：
日付と時刻のデータ型または UNIX 時間向けに数値データ型のカラムを指定

INCREMENT BY INTERVAL：
USING 句で日付と時刻のデータ型を指定した場合は、 INTERVAL 定数
USING 句で数値型を指定した場合は、数値

出力は以下のようになります。

					
			+-------------------------+-------------+------------------+----------------+
| OBSERVED                | TEMPERATURE | CITY             | COUNTY         |
|-------------------------+-------------+------------------+----------------|
| 2025-03-15 09:45:00.000 |        NULL | Big Bear City    | San Bernardino |
| 2025-03-15 09:49:00.000 |          48 | Big Bear City    | San Bernardino |
| 2025-03-15 09:50:00.000 |        NULL | Big Bear City    | San Bernardino |
| 2025-03-15 09:52:15.000 |          45 | Big Bear City    | San Bernardino |
| 2025-03-15 09:55:00.000 |          49 | Big Bear City    | San Bernardino |
| 2025-03-15 10:00:00.000 |        NULL | Big Bear City    | San Bernardino |
| 2025-03-15 10:05:00.000 |        NULL | Big Bear City    | San Bernardino |
| 2025-03-15 10:07:30.000 |          50 | Big Bear City    | San Bernardino |
| 2025-03-15 10:10:00.000 |          51 | Big Bear City    | San Bernardino |
| 2025-03-15 10:15:00.000 |        NULL | Big Bear City    | San Bernardino |
| 2025-03-15 10:18:00.000 |          54 | Big Bear City    | San Bernardino |
| 2025-03-15 10:00:00.000 |        NULL | Los Angeles      | Los Angeles    |
| 2025-03-15 10:04:00.000 |          60 | Los Angeles      | Los Angeles    |
| 2025-03-15 09:50:00.000 |        NULL | South Lake Tahoe | El Dorado      |
| 2025-03-15 09:50:15.000 |          44 | South Lake Tahoe | El Dorado      |
| 2025-03-15 09:55:00.000 |        NULL | South Lake Tahoe | El Dorado      |
| 2025-03-15 09:55:30.000 |          46 | South Lake Tahoe | El Dorado      |
| 2025-03-15 10:00:00.000 |        NULL | South Lake Tahoe | El Dorado      |
| 2025-03-15 10:00:05.000 |          49 | South Lake Tahoe | El Dorado      |
| 2025-03-15 10:05:00.000 |        NULL | South Lake Tahoe | El Dorado      |
| 2025-03-15 10:05:45.000 |          52 | South Lake Tahoe | El Dorado      |
+-------------------------+-------------+------------------+----------------+

		
		
前提として、PARTITION BYでcity,countryカラムを指定しているので、この値のグループごとに、データが5分間隔で再サンプリングされます。
具体的なサンプリングの方式は以下の通りです。
指定した間隔（5 minutes）で、元データを再サンプリングし、元レコードに存在しないギャップを埋める新しいレコードが生成されています（例: 09:45:00, 09:50:00）
生成されたレコードの観測値（temperature）にはNULLが挿入されます
09:52:15や10:07:30のように、秒がずれたり、5分間隔から外れたりしている元のレコードも保持されます
Los Angelesのような元のレコードが1つしかない場合でも、その単一のレコードが属する時間範囲を基準に、ギャップを埋める処理が行われます
この際、元のデータが存在する時間範囲内のギャップを埋めることを目的としているため、元のデータの時間範囲を自動的に拡張することはありません
例：Los Angelesのレコード（2025-03-15 10:04:00.000）は、2025-03-15 10:00:00.000 の時間範囲に属するので、このレコードは生成されますが、10:05:00といった、元のデータの時間範囲外のレコードは生成しない

また、メタデータ列を付与して出力することも可能です。

					
			>SELECT *
FROM sample_data
  RESAMPLE (
    USING observed
    INCREMENT BY INTERVAL '5 minutes'
    PARTITION BY city, county
    METADATA_COLUMNS IS_GENERATED() AS generated_row, BUCKET_START()
    )
ORDER BY city, county, observed;
+-------------------------+-------------+------------------+----------------+---------------+-------------------------+
| OBSERVED                | TEMPERATURE | CITY             | COUNTY         | GENERATED_ROW | BUCKET_START            |
|-------------------------+-------------+------------------+----------------+---------------+-------------------------|
| 2025-03-15 09:45:00.000 |        NULL | Big Bear City    | San Bernardino | True          | 2025-03-15 09:45:00.000 |
| 2025-03-15 09:49:00.000 |          48 | Big Bear City    | San Bernardino | False         | 2025-03-15 09:45:00.000 |
| 2025-03-15 09:50:00.000 |        NULL | Big Bear City    | San Bernardino | True          | 2025-03-15 09:50:00.000 |
| 2025-03-15 09:52:15.000 |          45 | Big Bear City    | San Bernardino | False         | 2025-03-15 09:50:00.000 |
| 2025-03-15 09:55:00.000 |          49 | Big Bear City    | San Bernardino | False         | 2025-03-15 09:55:00.000 |
| 2025-03-15 10:00:00.000 |        NULL | Big Bear City    | San Bernardino | True          | 2025-03-15 10:00:00.000 |
| 2025-03-15 10:05:00.000 |        NULL | Big Bear City    | San Bernardino | True          | 2025-03-15 10:05:00.000 |
| 2025-03-15 10:07:30.000 |          50 | Big Bear City    | San Bernardino | False         | 2025-03-15 10:05:00.000 |
| 2025-03-15 10:10:00.000 |          51 | Big Bear City    | San Bernardino | False         | 2025-03-15 10:10:00.000 |
| 2025-03-15 10:15:00.000 |        NULL | Big Bear City    | San Bernardino | True          | 2025-03-15 10:15:00.000 |
| 2025-03-15 10:18:00.000 |          54 | Big Bear City    | San Bernardino | False         | 2025-03-15 10:15:00.000 |
| 2025-03-15 10:00:00.000 |        NULL | Los Angeles      | Los Angeles    | True          | 2025-03-15 10:00:00.000 |
| 2025-03-15 10:04:00.000 |          60 | Los Angeles      | Los Angeles    | False         | 2025-03-15 10:00:00.000 |
| 2025-03-15 09:50:00.000 |        NULL | South Lake Tahoe | El Dorado      | True          | 2025-03-15 09:50:00.000 |
| 2025-03-15 09:50:15.000 |          44 | South Lake Tahoe | El Dorado      | False         | 2025-03-15 09:50:00.000 |
| 2025-03-15 09:55:00.000 |        NULL | South Lake Tahoe | El Dorado      | True          | 2025-03-15 09:55:00.000 |
| 2025-03-15 09:55:30.000 |          46 | South Lake Tahoe | El Dorado      | False         | 2025-03-15 09:55:00.000 |
| 2025-03-15 10:00:00.000 |        NULL | South Lake Tahoe | El Dorado      | True          | 2025-03-15 10:00:00.000 |
| 2025-03-15 10:00:05.000 |          49 | South Lake Tahoe | El Dorado      | False         | 2025-03-15 10:00:00.000 |
| 2025-03-15 10:05:00.000 |        NULL | South Lake Tahoe | El Dorado      | True          | 2025-03-15 10:05:00.000 |
| 2025-03-15 10:05:45.000 |          52 | South Lake Tahoe | El Dorado      | False         | 2025-03-15 10:05:00.000 |
+-------------------------+-------------+------------------+----------------+---------------+-------------------------+

		
		
これまではアップサンプリングの例でしたが、以下のコマンドのようにより粗い時間間隔と集約関数を使用し、ダウンサンプリングも可能です。

					
			--ダウンサンプリング
>SELECT
  bucket_start,
  county,
  city,
  AVG(temperature) AS avg_temperature,
FROM sample_data
  RESAMPLE (
    USING observed
    INCREMENT BY INTERVAL '1 hour'
    PARTITION BY city, county
    METADATA_COLUMNS BUCKET_START()
    )
GROUP BY 1, 2, 3
ORDER BY 2, 3, 1;
+-------------------------+----------------+------------------+-----------------+
| BUCKET_START            | COUNTY         | CITY             | AVG_TEMPERATURE |
|-------------------------+----------------+------------------+-----------------|
| 2025-03-15 09:00:00.000 | El Dorado      | South Lake Tahoe |       45.000000 |
| 2025-03-15 10:00:00.000 | El Dorado      | South Lake Tahoe |       50.500000 |
| 2025-03-15 10:00:00.000 | Los Angeles    | Los Angeles      |       60.000000 |
| 2025-03-15 09:00:00.000 | San Bernardino | Big Bear City    |       47.333333 |
| 2025-03-15 10:00:00.000 | San Bernardino | Big Bear City    |       51.666667 |
+-------------------------+----------------+------------------+-----------------+

		
		
https://docs.snowflake.com/en/sql-reference/constructs/resample#resample-example-that-uses-bucket-start-to-aggregate-resampled-rows
 補完関数続けて補完関数を試してみます。先の RESAMPLE 句で補完されるレコードの各観測値はデフォルトで NULL となるので、補完関数を使用することで、特定のロジックで値を埋めることが可能です。

事前準備として、先の手順で RESAMPLE 句を適用したアップサンプリングの結果を別テーブルとして作成しておきました。

					
			--テーブルを作成
CREATE OR REPLACE TABLE resampled_table AS
SELECT *
FROM sample_data
  RESAMPLE (
    USING observed
    INCREMENT BY INTERVAL '5 minutes'
    PARTITION BY city, county)
ORDER BY city, county, observed;

		
		
補完関数は3種類あるので、それぞれ試してみます。
INTERPOLATE_FFILL：前方補完
INTERPOLATE_FFILL関数は、以前に観測された直近の値を使って、欠損値を埋めるために使用できます。この関数はウィンドウ関数として提供されるため、PARTITION BY句でカテゴリを指定することで、グループごとに補間処理を実行できます。
以下のコマンドは、各パーティション（city, country）内でobservedを昇順に並べた際の直前のtemperature値を使い、欠損値を補完しています。

					
			--前方補完
SELECT
  observed,
  temperature,
  INTERPOLATE_FFILL(temperature) OVER (PARTITION BY city, county ORDER BY observed) AS ffill_out,
  city,
  county
FROM resampled_table
ORDER BY city, county, observed;
+-------------------------+-------------+-----------+------------------+----------------+
| OBSERVED                | TEMPERATURE | FFILL_OUT | CITY             | COUNTY         |
|-------------------------+-------------+-----------+------------------+----------------|
| 2025-03-15 09:45:00.000 |        NULL |      NULL | Big Bear City    | San Bernardino |
| 2025-03-15 09:49:00.000 |          48 |        48 | Big Bear City    | San Bernardino |
| 2025-03-15 09:50:00.000 |        NULL |        48 | Big Bear City    | San Bernardino |
| 2025-03-15 09:52:15.000 |          45 |        45 | Big Bear City    | San Bernardino |
| 2025-03-15 09:55:00.000 |          49 |        49 | Big Bear City    | San Bernardino |
| 2025-03-15 10:00:00.000 |        NULL |        49 | Big Bear City    | San Bernardino |
| 2025-03-15 10:05:00.000 |        NULL |        49 | Big Bear City    | San Bernardino |
| 2025-03-15 10:07:30.000 |          50 |        50 | Big Bear City    | San Bernardino |
| 2025-03-15 10:10:00.000 |          51 |        51 | Big Bear City    | San Bernardino |
| 2025-03-15 10:15:00.000 |        NULL |        51 | Big Bear City    | San Bernardino |
| 2025-03-15 10:18:00.000 |          54 |        54 | Big Bear City    | San Bernardino |
| 2025-03-15 10:00:00.000 |        NULL |      NULL | Los Angeles      | Los Angeles    |
| 2025-03-15 10:04:00.000 |          60 |        60 | Los Angeles      | Los Angeles    |
| 2025-03-15 09:50:00.000 |        NULL |      NULL | South Lake Tahoe | El Dorado      |
| 2025-03-15 09:50:15.000 |          44 |        44 | South Lake Tahoe | El Dorado      |
| 2025-03-15 09:55:00.000 |        NULL |        44 | South Lake Tahoe | El Dorado      |
| 2025-03-15 09:55:30.000 |          46 |        46 | South Lake Tahoe | El Dorado      |
| 2025-03-15 10:00:00.000 |        NULL |        46 | South Lake Tahoe | El Dorado      |
| 2025-03-15 10:00:05.000 |          49 |        49 | South Lake Tahoe | El Dorado      |
| 2025-03-15 10:05:00.000 |        NULL |        49 | South Lake Tahoe | El Dorado      |
| 2025-03-15 10:05:45.000 |          52 |        52 | South Lake Tahoe | El Dorado      |
+-------------------------+-------------+-----------+------------------+----------------+

		
		
出力から、temperatureがNULLになっているレコード（例：Big Bear Cityの09:50:00）が、その直前の観測値（09:49:00の48）で補完されています。Big Bear Cityの最初のレコード（09:45:00）は、直前に観測値がないため、ffill_out列はNULLのままになっています。
INTERPOLATE_BFILL ：後方補完
INTERPOLATE_BFILL関数は、直後の観測値を使って、欠損値を埋める処理を行います。同じくウィンドウ関数として提供されるため、PARTITION BY句でカテゴリを指定することで、グループごとに補間処理を実行できます。
以下のコマンドは、先の例と同じで関数をINTERPOLATE_BFILLに変更しています。

					
			--後方補完
>SELECT
  observed,
  temperature,
  INTERPOLATE_BFILL(temperature) OVER (PARTITION BY city, county ORDER BY observed) AS bfill_out,
  city,
  county
FROM resampled_table
ORDER BY city, county, observed;
+-------------------------+-------------+------------+------------------+----------------+
| OBSERVED                | TEMPERATURE | BFILL_OUT  | CITY             | COUNTY         |
|-------------------------+-------------+------------+------------------+----------------|
| 2025-03-15 09:45:00.000 |        NULL |         48 | Big Bear City    | San Bernardino |
| 2025-03-15 09:49:00.000 |          48 |         48 | Big Bear City    | San Bernardino |
| 2025-03-15 09:50:00.000 |        NULL |         45 | Big Bear City    | San Bernardino |
| 2025-03-15 09:52:15.000 |          45 |         45 | Big Bear City    | San Bernardino |
| 2025-03-15 09:55:00.000 |          49 |         49 | Big Bear City    | San Bernardino |
| 2025-03-15 10:00:00.000 |        NULL |         50 | Big Bear City    | San Bernardino |
| 2025-03-15 10:05:00.000 |        NULL |         50 | Big Bear City    | San Bernardino |
| 2025-03-15 10:07:30.000 |          50 |         50 | Big Bear City    | San Bernardino |
| 2025-03-15 10:10:00.000 |          51 |         51 | Big Bear City    | San Bernardino |
| 2025-03-15 10:15:00.000 |        NULL |         54 | Big Bear City    | San Bernardino |
| 2025-03-15 10:18:00.000 |          54 |         54 | Big Bear City    | San Bernardino |
| 2025-03-15 10:00:00.000 |        NULL |         60 | Los Angeles      | Los Angeles    |
| 2025-03-15 10:04:00.000 |          60 |         60 | Los Angeles      | Los Angeles    |
| 2025-03-15 09:50:00.000 |        NULL |         44 | South Lake Tahoe | El Dorado      |
| 2025-03-15 09:50:15.000 |          44 |         44 | South Lake Tahoe | El Dorado      |
| 2025-03-15 09:55:00.000 |        NULL |         46 | South Lake Tahoe | El Dorado      |
| 2025-03-15 09:55:30.000 |          46 |         46 | South Lake Tahoe | El Dorado      |
| 2025-03-15 10:00:00.000 |        NULL |         49 | South Lake Tahoe | El Dorado      |
| 2025-03-15 10:00:05.000 |          49 |         49 | South Lake Tahoe | El Dorado      |
| 2025-03-15 10:05:00.000 |        NULL |         52 | South Lake Tahoe | El Dorado      |
| 2025-03-15 10:05:45.000 |          52 |         52 | South Lake Tahoe | El Dorado      |
+-------------------------+-------------+------------+------------------+----------------+

		
		
出力結果から、temperatureがNULLになっているレコードが、その直後に観測された値で補完されていることが分かります。例えば、Big Bear Cityの09:45:00のレコードは、直後の09:49:00の観測値である48で補完されています。
INTERPOLATE_LINEAR：線形補間
INTERPOLATE_LINEAR関数は、直前と直後の観測値の間を線形に補間し、欠損値を埋めるため、データの変化が線形であると仮定できる場合に使用できます。
以下のコマンドでは、これまでと同様の手順でINTERPOLATE_LINEAR関数を適用しています。

					
			--線形
>SELECT
  observed,
  temperature,
  INTERPOLATE_LINEAR(temperature) OVER (PARTITION BY city, county ORDER BY observed) AS linear_out,
  city,
  county
FROM resampled_table
ORDER BY city, county, observed;
+-------------------------+-------------+-------------+------------------+----------------+
| OBSERVED                | TEMPERATURE | LINEAR_OUT  | CITY             | COUNTY         |
|-------------------------+-------------+-------------+------------------+----------------|
| 2025-03-15 09:45:00.000 |        NULL |        NULL | Big Bear City    | San Bernardino |
| 2025-03-15 09:49:00.000 |          48 |          48 | Big Bear City    | San Bernardino |
| 2025-03-15 09:50:00.000 |        NULL |          47 | Big Bear City    | San Bernardino |
| 2025-03-15 09:52:15.000 |          45 |          45 | Big Bear City    | San Bernardino |
| 2025-03-15 09:55:00.000 |          49 |          49 | Big Bear City    | San Bernardino |
| 2025-03-15 10:00:00.000 |        NULL |          49 | Big Bear City    | San Bernardino |
| 2025-03-15 10:05:00.000 |        NULL |          50 | Big Bear City    | San Bernardino |
| 2025-03-15 10:07:30.000 |          50 |          50 | Big Bear City    | San Bernardino |
| 2025-03-15 10:10:00.000 |          51 |          51 | Big Bear City    | San Bernardino |
| 2025-03-15 10:15:00.000 |        NULL |          53 | Big Bear City    | San Bernardino |
| 2025-03-15 10:18:00.000 |          54 |          54 | Big Bear City    | San Bernardino |
| 2025-03-15 10:00:00.000 |        NULL |        NULL | Los Angeles      | Los Angeles    |
| 2025-03-15 10:04:00.000 |          60 |          60 | Los Angeles      | Los Angeles    |
| 2025-03-15 09:50:00.000 |        NULL |        NULL | South Lake Tahoe | El Dorado      |
| 2025-03-15 09:50:15.000 |          44 |          44 | South Lake Tahoe | El Dorado      |
| 2025-03-15 09:55:00.000 |        NULL |          46 | South Lake Tahoe | El Dorado      |
| 2025-03-15 09:55:30.000 |          46 |          46 | South Lake Tahoe | El Dorado      |
| 2025-03-15 10:00:00.000 |        NULL |          49 | South Lake Tahoe | El Dorado      |
| 2025-03-15 10:00:05.000 |          49 |          49 | South Lake Tahoe | El Dorado      |
| 2025-03-15 10:05:00.000 |        NULL |          52 | South Lake Tahoe | El Dorado      |
| 2025-03-15 10:05:45.000 |          52 |          52 | South Lake Tahoe | El Dorado      |
+-------------------------+-------------+-------------+------------------+----------------+

		
		
出力結果を見ると、NULLがその前後にあるtemperature値から計算された値で埋められています。

例えば、South Lake Tahoeの09:50:00のレコードは、この行の直前に観測値が存在しないためNULLのままでした。一方で09:55:00のNULL値は、直前の観測値（09:50:15の44）と直後の観測値（09:55:30の46）から計算され、46という値で補完されています。
 さいごに時系列データの欠損値を埋めるために使用できる RESAMPLE 句と補間関数（INTERPOLATE_FFILL, INTERPOLATE_BFILL, INTERPOLATE_LINEAR）を試してみました。

SQL ベースで簡単に欠損値を自動で補完するデータパイプラインを実装する際にも使用でき、機械学習のデータ前処理の効率化が期待できる機能と思います。

こちらの内容が何かの参考になれば幸いです。