SQLだけで機械学習を実装する方法

BigQueryMLを用いたAI分析

概要

GCPのBigQuey機能でSQLクエリを作成する機能があります。
あれの応用で、SQLクエリを作成するだけでAIの実装ができるようになるBigQueryMLという機能があります。
この機能を用いることでビットコインのAI分析をしてみようという内容です。
実装も結構楽に実装できる便利機能なのでぜひ皆さんも作成してみてください。

そもそもBigQueryとは？

BigQueryはデータの蓄積と分析の両方を担うことができるGCPのデータウェアハウスプロダクトになります。
今回これはあまり主題ではないため、詳細な解説を省きますが、
要はデータを入れてSQLなどで分析できるものなんだなくらいの認識で大丈夫です。

BigQueryMLでできること

BigQueryMLで利用可能なモデルは主に教師あり学習の分類、回帰と、教師なし学習のクラスタリングのようなものに限定されています。
そのため、強化学習やTransformerのようなものまではできないので注意してください。（まぁ普通それだけあれば十分ですが...）
また、これはVertexAIなどと連携ができるため、そのまま機械翻訳などLLM向けのタスクに昇華することも可能です。
詳細は以下公式ドキュメントを確認して下さい
BigQueryのAIとMLの概要

実際の分析

今回は例としてBTCUSDの15分足のOHLCVデータを与えて、
ブーステッドツリーモデルを用いた分析を行おうと思います。

※本来時系列解析ではARIMAモデルなど時系列解析向けのモデルを使用しますが、SELECT句で指定できるカラム数がTIMESTAMPと予測対象だけっぽいので、より実践的な方をと思いこちらで作成しています。

前提条件

どのような方法でも構いませんが、BigQueryにBTCUSDのデータが挿入されていることを前提とします。
今回はBTCUSDの15分足のOHLCVの値を1万件ほど与えています。

モデルの構築

今回は以下手順でモデルの構築と学習を行います。

• モデルに渡すデータの選定
• モデルの選定と学習

1つ目です。
新規SQLクエリにて以下クエリを作成&実行して下さい。

SQL

CREATE OR REPLACE TABLE `yourTable.btc_15min_features_for_tree` AS (
  WITH base_data AS (
    SELECT
      timestamp,
      close,
      open,
      high,
      low,
      volume,

      -- 直前closeデータ
      LAG(close, 1) OVER (ORDER BY timestamp) AS close_lag_1,
      -- 直是volumeデータ
      LAG(volume, 1) OVER (ORDER BY timestamp) AS volume_lag_1,

      -- 1時間足closeのSMAデータ
      AVG(close) OVER (ORDER BY timestamp ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS close_ma_4,
      -- 1時間足volumeのSMAデータ
      AVG(volume) OVER (ORDER BY timestamp ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS volume_ma_4,

      -- 直後データ
      LEAD(close, 1) OVER (ORDER BY timestamp) AS target_close

    FROM
      `TradeLearning.BTCUSD15min`
  )
  -- 計算外のデータを除去
  SELECT * FROM base_data WHERE target_close IS NOT NULL AND close_lag_1 IS NOT NULL AND volume_ma_4 IS NOT NULL
);

上記では2つの内容を実装することで、モデルの構築を行っています。

• モデルに渡すTableの作成
  CREATE句ではAIモデルに渡すデータについての説明をしています。
  ここではyourTableにbtc_15min_features_for_treeという名前でテーブルを作成しました。
  また、一応エラーハンドリングとして、すでに同様の名前でモデルを作成済みの場合は上書きして作成してねという内容で作成しています。
• 特徴量
  base_dataではどのようなデータを与えて学習させるかを選定しています。
  今回はTIMESTAMP、OHLCV、直前のデータ、MA、正解データを与えています。
  また、最初の行ではカラム名を、最後の行では直後のデータを特徴量として与えることができないため、
  最後の行で省いています。

先ほど作成したデータをもとに学習をさせてみます。
そのために、以下クエリを新規で作成&実行してください。

SQL

CREATE OR REPLACE MODEL `yourTable.BTCUSD_boosted_tree_model`
OPTIONS(
  MODEL_TYPE='BOOSTED_TREE_REGRESSOR',
  INPUT_LABEL_COLS=['target_close']
) AS
SELECT
  * EXCEPT (timestamp)
FROM
  `yourTable.btc_15min_features_for_tree`;

ここでモデルの選択と、その学習を行っています。

モデルの評価

モデルの評価はおおよそ2パターンあります。

• 平均絶対誤差などの指標でそのモデルの性能評価をする方法
• 実際にそのモデルを実行してみて、どのような結果になるかを確認する方法

これらを順に実行方法などを踏まえて解説します。

統計評価

SQL

SELECT
  *
FROM
  ML.EVALUATE(MODEL `TradeLearning.BTCUSD_boosted_tree_model`);

これで平均二乗誤差などの性能評価全部を含めて計算させることができます。
以下が私の性能評価の内容です。

この性能評価指標の詳細についてはググってください
また、これらをもとにより精度を上げたいなどある場合は、モデルの構築部分で特徴量やモデルの変更などで対応してみて下さい。

実際の予測

SQL

SELECT
  predicted_target_close
FROM
  ML.PREDICT(MODEL `yourTable.BTCUSD_boosted_tree_model`,
    (
      WITH latest_features AS (
        SELECT
          timestamp,
          close,
          open,
          high,
          low,
          volume,
          LAG(close, 1) OVER (ORDER BY timestamp) AS close_lag_1,
          LAG(volume, 1) OVER (ORDER BY timestamp) AS volume_lag_1,
          AVG(close) OVER (ORDER BY timestamp ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS close_ma_4,
          AVG(volume) OVER (ORDER BY timestamp ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS volume_ma_4,
          ROW_NUMBER() OVER (ORDER BY timestamp DESC) as rn
        FROM
          `TradeLearning.BTCUSD15min`
        QUALIFY rn = 1
      )
      SELECT * EXCEPT(timestamp, rn) FROM latest_features
    )
  );

これは、以下のような流れで予測を行うようになっています。
1: 最新データ1行分のデータを取得
2:そのデータをもとに学習時と同じ特徴量を計算
3:その結果をモデルに渡す
4:予測

以下のクエリがその1～3を実装している内容です。
※5行目以降の内容

SQL

(
  WITH latest_features AS (
    SELECT
      timestamp,
      close,
      open,
      high,
      low,
      volume,
      LAG(close, 1) OVER (ORDER BY timestamp) AS close_lag_1,
      LAG(volume, 1) OVER (ORDER BY timestamp) AS volume_lag_1,
      AVG(close) OVER (ORDER BY timestamp ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS close_ma_4,
      AVG(volume) OVER (ORDER BY timestamp ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS volume_ma_4,
      ROW_NUMBER() OVER (ORDER BY timestamp DESC) as rn
    FROM
      `TradeLearning.BTCUSD15min`
    QUALIFY rn = 1
  )
  SELECT * EXCEPT(timestamp, rn) FROM latest_features
)

4の予測はこれをそもそもどのモデルに渡すかを選択するようになっています。
以下が対象部分です。

SQL

SELECT
  predicted_target_close
FROM
  ML.PREDICT(MODEL `yourTable.BTCUSD_boosted_tree_model`,
    ( -- ここにデータの選択部分が挿入
      ...
    )
  );

これで以下のように予測ができました

おわりに

本記事では、BigQueryMLという機能を活用し、SQLクエリだけでビットコインの価格予測AIを構築する全手順を解説しました。

①特徴量の準備 → ②モデルの学習 → ③性能評価 → ④未来予測 という一連のワークフローを通じて、
BigQueryMLで実践的な分析が可能であるかを感じていただけたかと思います。

この記事が、SQLの知識を活かしてAI分析の世界へ第一歩を踏み出すきっかけとなれば幸いです。ぜひ、皆様もご自身の興味のあるデータで、AIモデルの構築に挑戦してみてください。