時系列データ同士（複数可）の波形の類似度を BigQuery のクエリ一発で計算する方法

複数の時系列データの波形の類似度を BigQuery のクエリ一発で調べる方法をご紹介します。
だって、ググると CCF やら DTW やらが出てきますが、わざわざ Python で書くのは正直しんどい時とか、あるじゃないですか。

出典：http://www.thothchildren.czzzzzzzom/chapter/59b495f775704408bd43002d

CCF（相互相関関数）：2 つの時系列データの各サンプル値間の相関を取る、時間のズレも計算に入れられる
DTW（動的時間伸縮法）：CCFに加え周期が伸縮するような時系列データの類似度も計算できる

タイミングのズレはなく、周期の伸縮も考慮しないなら BigQuery の CORR 関数だけで類似度を調べることができます。さらに今回は、複数の比較対象を一発で見比べたい、という（私の）要望に応えるため BigQuery Scripting でループさせました。

時系列データの波形が似ている＝相関が高い

いろいろあってビールの家計支出について東京と似た波形（平行トレンド）の地域ってどこ？　を知りたくなった、というのが今回の調査の経緯です。その辺は別記事にしたのでよかったら覗いてってください！

まずは結果から。

東京都の波形と相関の高いもの、低いものそれぞれ２つずつピックアップしてみました。

東京と最も相関が高いのは大阪（堺市）というのはなんとなく想像通りでした。

BigQuery のクエリ一発で時系列データの相関係数を一覧出力する方法

BigQuery で FOR/WHILEループや IF文 が書けるのはご存じでしょうか。BigQuery Scripting と言って、クエリ内にスクリプトが書けるという便利機能です。

今回、時系列データの相関係数を出したいということで、日毎の金額について、東京との差が小さい（＝相関が高い）かどうかを CORR 関数（ピアソン相関）で調べます。

つまり、一度のクエリで一つの地域しかできません。

なので、クエリ一発で全地域の相関係数を一覧で出力するために、ループ処理の力を借りることにしました。以下がクエリになります。

クエリ全体

# 変数の宣言
DECLARE areas ARRAY<STRING>; # areas という配列を作ってループを回します
DECLARE x INT64 DEFAULT 1; # areas で使う引数
DECLARE t STRING DEFAULT ”; # 比較のareas名を格納
DECLARE cor FLOAT64 DEFAULT 0; # 相関係数


# 変数への代入
# ARRAYの値を自動的に作りたかったので、ARRAY_AGGで作成
SET areas = (
SELECT ARRAY_AGG(area_code) as list
FROM (SELECT area_code FROM `プロジェクトID.データセット名.テーブル名` GROUP BY area_code ORDER BY area_code)
);

# 結果格納用の一時テーブル
CREATE OR REPLACE TEMP TABLE _SESSION.tmp(
  t STRING,
  cor FLOAT64
);

# ループ処理
# areasの長さまで繰り返し
WHILE x <= array_length(areas) DO

# 比較対象の都道府県コードを格納
SET t = areas [ORDINAL(x)];

# 相関係数を格納
SET cor = (
WITH area1 as (
  SELECT
      area_code, area_name,
      CAST(CONCAT(
        NORMALIZE(SUBSTR(year_month,0,INSTR(year_month,’年’) – 1),NFKC),
        ‘-‘,
        NORMALIZE(LPAD(TRANSLATE(SUBSTR(year_month,INSTR(year_month,’年’) + 1, 2),’月’,”),2,’0′),NFKC),
        ‘-01’) as DATE) AS year_month,
      CAST(value AS FLOAT64) AS value
    FROM `プロジェクトID.データセット名.テーブル名`
    WHERE cat01_code = ‘011100030’ –ビール
    AND cat02_code = ’03’ — 二人以上の世帯（2000年〜）
    AND area_code = ‘13003’ –東京都区部
), area2 as (
  SELECT
      area_code, area_name,
      CAST(CONCAT(
        NORMALIZE(SUBSTR(year_month,0,INSTR(year_month,’年’) – 1),NFKC),
        ‘-‘,
        NORMALIZE(LPAD(TRANSLATE(SUBSTR(year_month,INSTR(year_month,’年’) + 1, 2),’月’,”),2,’0′),NFKC),
        ‘-01’) as DATE) AS year_month,
      CAST(value AS FLOAT64) AS value
    FROM `プロジェクトID.データセット名.テーブル名`
    WHERE cat01_code = ‘011100030’ –ビール
    AND cat02_code = ’03’ — 二人以上の世帯（2000年〜）
    AND area_code = areas [ORDINAL(x)] # areasのX番目の値を取り出す
)
SELECT
  CORR(value1,value2) AS correlation
FROM
　(SELECT
    area1.value AS value1,
    area2.value AS value2,
    FROM area1 LEFT JOIN area2 ON area1.year_month = area2.year_month
  )
);

# 対象の地域コードと、東京との相関係数を一時テーブルにINSERT
INSERT _SESSION.tmp (
SELECT
  t,
  cor
);
SET x = x + 1;
END WHILE;
# ループ処理終了

# 時系列データの相関係数一覧で出力
SELECT
c.t,
m.area_name,
c.cor
FROM _SESSION.tmp LEFT JOIN (
SELECT area_code,area_name
FROM  `プロジェクトID.データセット名.テーブル名`
GROUP BY area_code,area_name
ORDER BY area_code
) AS m ON c.t = m.area_code
ORDER BY c.cor

出力結果（2019年の月別ビールへの支出額に関する、東京との相関係数）

東京自身は相関が1、次に相関が高いのは全国ですが、全国には東京のデータも含まれているので除外し、最も相関の高い地域は堺市（大阪）となりました。

BigQuery Scripting 使ってみた感想

実は、BigQuery Scripting を書いてみたのは今回が初めてでしたが、使ってみてわかりました。めちゃくちゃ便利ですね！

これまでは BigQuery 一発でできない処理は、Vertex AI のワークベンチでノートブック（jupyter notebook的なやつ）を立ち上げて、Python で BigQuery からデータロードしてから処理する、ということをやっていました。

でも、今回のようなちょっとした検証やデータ確認だったら、BigQuery 単体でできる方が素敵ですね。もっと早くに出会いたかった。

まとめ

• 類似度＝相関が高い、ということで CORR 関数が使える
• ただし、ズレや伸縮は考慮しない
• 複数の比較対象がある場合は BigQuery Scripting でループさせると便利