【BIGQUERY】時系列データの欠損("歯抜け")データの補完

Heroyuki_F

KEYWORD

BigQuery, 時系列データ, 欠損データ, GENERATE_DATE_ARRAY, UNNEST, LAST_LAVUE関数

どんなときに使うか

　購買データから"企業課題"の発見、そして課題解決に向けた仕組みつくりをする際のデータ集計・分析を想定してます。このとき、データベース上の受注系テーブル(order_table)にはレコード自体が存在しない"歯抜け"レコードが存在します。今回はそんな欠損データをBigQuery上で補完する方法を記します。

テーブル構成

　具体的に、受注テーブルは期間2022年1月1日 ~ 2022年1月7日の時系列データを想定します。2022年1月2日と2022年1月6日のレコードが存在しない下記のようなテーブルとします。

order_date(受注日), product_id(商品ID), sales_quantity(受注個数)
2022-01-01, 1, 1
2022-01-03, 1, 1
2022-01-04, 1, 1
2022-01-05, 1, 1
2022-01-07, 1, 1

ここでは2022年1月2日と2022年1月5日のレコードを補完するために
・GENERATE_DATE_ARRAY
・配列のUNNEST
・LAST_LAVUE関数
を利用します。

補完クエリ

DECLARE start_date DATE DEFAULT DATE('2022-01-01');
DECLARE end_date DATE DEFAULT DATE_ADD(DATE_SUB(start_date, INTERVAL 1 DAY), INTERVAL 1 WEEK);

WITH product_master AS(
   --商品マスタテーブル
  SELECT gpi AS product_id FROM(
      SELECT GENERATE_ARRAY(1, 3) AS g_product_id 
  ) AS t, UNNEST (g_product_id) AS gpi
)
, order_table AS(
    -- 想定するrow形式の受注(購買系)テーブル
    SELECT * FROM(
    SELECT
        gd AS order_date, --受注日
        goi AS product_id, --商品ID
        CASE
            WHEN EXTRACT(DAYOFWEEK from gd) = 1 OR EXTRACT(DAYOFWEEK from gd) = 5 THEN NULL
            ELSE 1
        END AS sales_quantity, --受注個数
    FROM(
        SELECT 
            GENERATE_DATE_ARRAY(start_date, end_date) AS g_date,
            GENERATE_ARRAY(1, 3) AS g_product_id
        ) AS t, UNNEST(g_date) AS gd ,UNNEST (g_product_id) AS goi
    ) WHERE sales_quantity IS NOT NULL
)
, complement_table AS(
   -- 欠損値データ補完用のテーブル
   SELECT 
       gd AS order_date,
       t.product_id AS product_id
   FROM(
       SELECT 
           GENERATE_DATE_ARRAY(start_date, end_date) AS g_date,
           pm.product_id AS product_id
       FROM product_master AS pm
   ) AS t, UNNEST(g_date) AS gd
)

-- 欠損値が補完されているかどうかを確認するクエリ
SELECT 
    ct.order_date,
    ct.product_id,
    IFNULL(ot.sales_quantity,0) AS sales_quantity -- 受注がついてないため受注個数=0で補完する。
FROM complement_table AS ct
LEFT OUTER JOIN order_table AS ot 
ON ct.order_date = ot.order_date  AND ct.product_id = ot.product_id
ORDER BY product_id, order_date

クエリの必要なところを順序立てて説明すると以下の3ステップのみです。
1. complement_tableという補完したい日付と商品IDのみの仮想テーブルを作成します。
2. 歯抜けテーブル(order_table)とOUTER JOINします。
3. OUTER JOINにより受注個数にNULLレコードが生まれるのでNULLの場合はゼロとします。
※ product_masterとorder_tableは例題用にテキトーに作成しています。

レコードの前後の値で欠損データを補完する場合

　先ほどの補完クエリではNULL値を"ゼロ"に補完しました。しかし、在庫などのデータだと時系列対象時点より前の値を参照して欠損値を補完したいときがあります。この時にはナビゲーション関数のLAST_VALUEが使えます。Python言語のpandasのffill(ないしbfill)に相当する機能です。抜粋したクエリを下に記します。

# 対象時点より過去の1番近い値 = ffill機能
LAST_VALUE(ot.quantity IGNORE NULLS) OVER(
    PARTITION BY ct.product_id 
    ORDER BY ct.date
) AS quantity_ffill,
# 対象時点より未来の1番近い値 = bfill機能
LAST_VALUE(ot.quantity IGNORE NULLS) OVER(
    PARTITION BY ct.product_id 
    ORDER BY ct.date DESC
) AS quantity_bfill


　この他にも有料課金サービスですが、BigQuery MLやVertex AIに加入すればデータ補完を自動で行ってくれるようです。そちらもチェックすると良いかもしれません。ただ、公式のドキュメントにはlocal linear interpolation method(≒線形補完法)を補完法として採用しているとあるので、本稿の内容のように自前で補完処理を実装した方が現実に適したモデリングになるかと私は思います。

参考


この記事が気に入ったらサポートをしてみませんか？