More than 3 years have passed since last update.

日付が歯抜けのデータに前営業日のデータを入れて補完する

Last updated at 2020-09-08Posted at 2020-09-08

はじめに

ふと思い立ってデータポータル(旧DataStuiod)の練習がてらドル円相場を表示しようとおもってBigQueryにデータを突っ込んで時系列グラフに突っ込んでみました。

すると結果はこんな感じ。

圧倒的これじゃない感。明らかに変。で、なんでかと考えたらこれはドル円相場なので土日のデータが無いんですね。だからそこが0としてカウントされてしまってこんな役に立たないグラフになっている。

期待するのはこういう感じのグラフです。

フィルタとか弄ってみたんですが、データポータルのグラフの機能で上手くやる方法が分からなかったのでBigQueryの元データの方を弄ることにしました。

ちなみにRAW_JPY_USDはGCSに配置してあるCSVの外部テーブルです。ロードと変換を同時に出来るので外部テーブルは便利ですね。

日付マスタとの結合

まずは欠けてる日付のレコードを作り出す必要があります。軽く調べてみると「日付マスタを作ってLeft JoinすればOK」とありました。なるほど賢い。
というわけで以下のように書きました。

WITHで一時テーブルdate_master を作っていますが別にViewでも良いかと思っています。

WITH
    date_master AS (
    SELECT
        publish_date
    FROM
        UNNEST(GENERATE_DATE_ARRAY( DATE('2015-01-01'), CURRENT_DATE())) AS publish_date
    ) 
SELECT
    A.publish_date date, 
    B.open, 
    B.high, 
    B.avg, 
    B.low, 
    B.close
FROM date_master as A 
LEFT JOIN (
    SELECT 
        Date, 
        CAST(Open as NUMERIC) as open,
        CAST(High as NUMERIC) as high, 
        (CAST(High as NUMERIC) + CAST(Low as NUMERIC))/2 as avg,
        CAST(Low as NUMERIC) as low,
        CAST(CLOSE as NUMERIC) as close   
    FROM `myprj.mydataset.RAW_JPY_USD` WHERE Date >= "2015-01-01" and Open <> "null"
) as B
ON A.publish_date = B.date

実行結果はこんな感じ。足りないレコードがNullでちゃんと埋められました。

しかしながらデータポータルにこれを食わせても結果は変わらず。。。という分けで前営業日のデータで埋める事にします。

案１: Window関数のLAGを使う

色々調べたりTwitterで教えてもらった結果としてWindow関数使えば良さそうです。すごいぞ標準SQL！
COALESCEがNULL出ない最初の値を返すので第一引数がNULLならLAGで日付順に並べたときの一つ前の値を返します。

WITH
    date_master AS (
    SELECT
        publish_date
    FROM
        UNNEST(GENERATE_DATE_ARRAY( DATE('2015-01-01'), CURRENT_DATE())) AS publish_date
    )
SELECT
    date,
    COALESCE(open,  LAG(open, 1) OVER (ORDER BY date)) AS open,
    COALESCE(high,  LAG(high, 1) OVER (ORDER BY date)) AS high,
    COALESCE(avg,  LAG(avg, 1) OVER (ORDER BY date)) AS avg,
    COALESCE(low,  LAG(low, 1) OVER (ORDER BY date)) AS low,
    COALESCE(close,  LAG(close, 1) OVER (ORDER BY date)) AS close,
FROM(
    SELECT
        A.publish_date date, 
        B.open, 
        B.high, 
        B.avg, 
        B.low, 
        B.close
    FROM date_master as A 
    LEFT JOIN (
        SELECT 
            Date, 
            CAST(Open as NUMERIC) as open,
            CAST(High as NUMERIC) as high, 
            (CAST(High as NUMERIC) + CAST(Low as NUMERIC))/2 as avg,
            CAST(Low as NUMERIC) as low,
            CAST(CLOSE as NUMERIC) as close   
        FROM `myprj.mydataset.RAW_JPY_USD` WHERE Date >= "2015-01-01" and Open <> "null"
    ) as B
    ON A.publish_date = B.date
)

結果は以下の通り。ちゃんと前の行が取れてるのですが残念ながらこの方法では連続するNULL値には対応できません。SQLは手続き的ではないのでまあ予想はしてました。

なので、とりあえずドル円相場は土日のデータが無いというドメイン知識を使って1行前がNULLの時は2行前までを読むという力技で解決します。

WITH
    date_master AS (
    SELECT
        publish_date
    FROM
        UNNEST(GENERATE_DATE_ARRAY( DATE('2015-01-01'), CURRENT_DATE())) AS publish_date
    )
SELECT
    date,
    COALESCE(open,  LAG(open, 1) OVER (ORDER BY date), LAG(open, 2) OVER (ORDER BY date)) AS open,
    COALESCE(high,  LAG(high, 1) OVER (ORDER BY date), LAG(high, 2) OVER (ORDER BY date)) AS high,
    COALESCE(avg,  LAG(avg, 1) OVER (ORDER BY date), LAG(avg, 2) OVER (ORDER BY date)) AS avg,
    COALESCE(low,  LAG(low, 1) OVER (ORDER BY date), LAG(low, 2) OVER (ORDER BY date)) AS low,
    COALESCE(close,  LAG(close, 1) OVER (ORDER BY date),  LAG(close, 2) OVER (ORDER BY date)) AS close,
FROM(
    SELECT
        A.publish_date date, 
        B.open, 
        B.high, 
        B.avg, 
        B.low, 
        B.close
    FROM date_master as A 
    LEFT JOIN (
        SELECT 
            Date, 
            CAST(Open as NUMERIC) as open,
            CAST(High as NUMERIC) as high, 
            (CAST(High as NUMERIC) + CAST(Low as NUMERIC))/2 as avg,
            CAST(Low as NUMERIC) as low,
            CAST(CLOSE as NUMERIC) as close   
        FROM `myprj.mydataset.RAW_JPY_USD` WHERE Date >= "2015-01-01" and Open <> "null"
    ) as B
    ON A.publish_date = B.date
)

実行結果はこんな感じ。期待値通りNULLが連続する場合も前営業日のバッチり埋まりました！

案２: UDFとARRAY_AGGを使ってレコードを探索する関数を定義する

さて、一応上記で上手くいったのですが3個の連続NULLに対応できないので汎用性はありません。ドル円相場は問題無い気もしますが一応汎用的に解きたいですよね？

という分けで禁断のユーザ定義関数（UDF）を使います。UDF自体は標準SQLに定義されている機能です。これはストアドプロシージャみたいなもので必ず関数として戻り値を定義する必要があります。また、実装依存ですがPL/SQLでは無くCとかPythonとか馴染みの言語が使えるのもポイントです。BigQueryではJavaScriptが利用できます。

レコードを配列として返すARRAY_AGG、自分の位置を返すrow_numberを使って関数に値を渡し単純にNULLじゃない最後の値を取るようにループさせます。
なお、一時関数の作成にCREATE TEMPORARY FUNCTION を使います。

CREATE TEMPORARY FUNCTION prev(xs ARRAY<NUMERIC>, index INT64)
RETURNS NUMERIC
LANGUAGE js AS """
  for(var i=1;i<=index;i++){
     if(xs[index-i] != null) return xs[index-i];
  }
  return null;
""";

WITH
    date_master AS (
    SELECT
        publish_date
    FROM
        UNNEST(GENERATE_DATE_ARRAY( DATE('2015-01-01'), CURRENT_DATE())) AS publish_date
    )
    
SELECT
    date,
    prev(ARRAY_AGG(open) over(order by date), row_number() over(order by date)) AS open,
    prev(ARRAY_AGG(high) over(order by date), row_number() over(order by date)) AS high,
    prev(ARRAY_AGG(avg) over(order by date), row_number() over(order by date)) AS avg,
    prev(ARRAY_AGG(low) over(order by date), row_number() over(order by date)) AS low,
    prev(ARRAY_AGG(close) over(order by date), row_number() over(order by date)) AS close
FROM(
    SELECT
        A.publish_date date, 
        B.open, 
        B.high, 
        B.avg, 
        B.low, 
        B.close
    FROM date_master as A 
    LEFT JOIN (
        SELECT 
            Date, 
            CAST(Open as NUMERIC) as open,
            CAST(High as NUMERIC) as high, 
            (CAST(High as NUMERIC) + CAST(Low as NUMERIC))/2 as avg,
            CAST(Low as NUMERIC) as low,
            CAST(CLOSE as NUMERIC) as close   
        FROM `myprj.mydataset.RAW_JPY_USD` WHERE Date >= "2015-01-01" and Open <> "null"
    ) as B
    ON A.publish_date = B.date
)