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

## はじめに

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

すると結果はこんな感じ。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/54904/876fdb22-2a45-e5d5-e61a-c1a7dd47e698.png)

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

期待するのはこういう感じのグラフです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/54904/29ed56c2-2245-8b4c-88bc-a11c7ae7dda7.png)
フィルタとか弄ってみたんですが、データポータルのグラフの機能で上手くやる方法が分からなかったのでBigQueryの元データの方を弄ることにしました。

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

## 日付マスタとの結合

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

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

```sql
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でちゃんと埋められました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/54904/bc392dff-86ca-4acd-760c-dc5d2064dbac.png)
しかしながらデータポータルにこれを食わせても結果は変わらず。。。という分けで前営業日のデータで埋める事にします。

## 案１: Window関数のLAGを使う

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

```sql
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は手続き的ではないのでまあ予想はしてました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/54904/56f722a2-39f7-da3c-903d-2ec66c6b95d7.png)

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

```sql
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が連続する場合も前営業日のバッチり埋まりました！

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/54904/f7931f8e-138f-edcd-d898-3361abfe99ce.png)

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

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

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

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

```sql
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
)
```

こちらも実行結果は案１と同様になります。これならNULLが何個連続していても対応できるので良いですね。

## Window関数とUDFのパフォーマンス比較

BigQueryの超並列処理のパワーを手軽に利用できるUDFですがSQLネイティブの関数に比べると遅いという欠点もあります。まあ、そりゃそうですよね。

なのでWindow関数版とUDF版の実行時間を比較してみました。

**Window関数版：**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/54904/57008371-26a1-92b6-4f04-502ceeaebd25.png)

**UDF版：**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/54904/684275fb-4acc-5b77-b068-0c2ade704317.png)

1.5秒と9.5秒なので予想通りですがずいぶん性能が違う結果になりました。どっちを使うのが良いかは要件次第でしょうが、今回は外部テーブルを読み込むELT処理で最終的にBigQueryネイティブのテーブルにするのでUDF版を使おうと思います。

## まとめ

今回はデータポータルでグラフが期待と違う形になったので、土日など休日のため欠けてるデータを営業日ベースのデータで上書きする方法を記載しました。それによって無事に期待値と同じグラフになりました。

UDFやWindow関数の便利さが分かった副次的なメリットはあったのですが、グラフコンポーネント側でこの辺はよしなにして欲しい所ですね。てか、普通に良くあるユースケースだと思うので自分が無駄に面倒な方法を取ってしまってる気がしてならない。。。

もし、そのやり方より「そもそもこうするべき」とか「もっと効率が良い方法が。。」とかあればぜひコメント欄等で教えていただければと思います。

それではHappy Hacking!

## 参考

- [BigQuery - 標準 SQL クエリ構文: With](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ja#with_clause)
- [BigQuery - 標準 SQL のナビゲーション関数](https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions?hl=ja)
- [BigQuery - 標準 SQL ユーザー定義関数](https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions?hl=ja)
- [にほんごのれんしゅう - bigqueryでUDFとwindow関数を使う](https://catindog.hatenablog.com/entry/2018/07/21/132919)
- [Qiita - 【Bigquery】指定した期間の日付のテーブルをさくっとだす](https://qiita.com/chatrate/items/b5e441c3d7dc0dfbe971)