みなさんこんにちは！kirigayaです。
今回はこちらの本のメモをまとめました
WITHの使いどころ
ネストされたSELECTステートメントを外に出して可読性を上げる。
SELECT COUNT(*) FROM (
SELECT
*
FROM
`bigqueryml-packt.03_bigquery_syntax.first_table`
WHERE
id_key=1
);
このクエリは、最初にネストされたSELECTステートメントを作成しています。bigqueryml-packt.03_bigquery_syntax.first_tableからid_keyが1の行を選択し、その数をCOUNTしています。
次に、同じロジックをWITH句を使用して書き直します。
WITH records_with_clause AS (
SELECT *
FROM `bigqueryml-packt.03_bigquery_syntax.first_table`
WHERE id_key=1
)
SELECT COUNT(*) FROM records_with_clause;
このクエリでは、WITH句がASキーワードに続くロジックを括弧で囲んで埋め込んでいます。WITH句の名前がrecords_with_clauseで、その後のSELECT COUNTステートメントでこのクエリのロジックを呼び出すことができます。
注意:WITH句は一時テーブルを作成しません。WITH句を使用すると、特に多くのネストされたSELECTステートメントがある場合にクエリの可読性が向上しますが、クエリのパフォーマンスには影響しません。
BigQueryMLでモデルを作成
--- モデルの作成場所を指定する
CREATE MODEL`<プロジェクト名>.<データセット名>.<ml_モデル名>`
--- 前処理変換のリスト※必須ではない
TRANSFORM (<変換された機能のリスト>
--- model_typeやinput_label_colsなどを指定する
OPTIONS(<オプションのリスト>)
--- 使用するデータセットとカラム
AS <select_statement>;
- CREATE OR REPLACE MODEL を使用して、新しいモデルを作成するか、既存のモデルを同じ名前で置き換えます。
- CREATE MODEL IF NOT EXISTS は、同じ名前のモデルが存在しない場合にのみ新しいモデルをトレーニングします。
モデルの評価
SELECT *
FROM ML.EVALUATE(
MODEL `<プロジェクト名>.<データセット名>.<ml_モデル名>`,
`<プロジェクト名>.<データセット名>.<評価テーブル>`
, STRUCT(<しきい値> AS しきい値));
ML.EVALUATE関数は、機械学習モデルのトレーニングと評価の結果をまとめて返すものです。
- 最初のSELECT *ステートメントは、評価段階で生成されたすべてのデータを取得します。
- ML.EVALUATE関数を呼び出します。
- モデルの識別子（プロジェクト、データセット、モデル名）を指定します。
- 評価に使用されるテーブルを指定します。省略すると、トレーニングデータセットの一部が使われます。
- ロジスティック回帰モデルの場合、オプションで使用する閾値を指定できます。指定しない場合、デフォルトの0.5が使われます。
注意:ML.EVALUATE関数を使う際には、評価セットのフィールド名がモデルのトレーニング時に使用したフィールド名と一致している必要があります。
混同行列関数
SELECT *
FROM ML.CONFUSION_MATRIX(
MODEL `<プロジェクト名>.<データセット名>.<ml_モデル名>`,
`<プロジェクト名>.<データセット名>.<評価テーブル>`
, STRUCT(<しきい値> AS しきい値));
- ML.CONFUSION_MATRIX関数は、偽陽性、偽陰性、真陽性、および真陰性の数を含む2行2列の行列を返します。
ROCカーブ関数
SELECT *
FROM ML.ROC_CURVE(
MODEL `<プロジェクト名>.<データセット名>.<ml_モデル名>`,
`<プロジェクト名>.<データセット名>.<評価テーブル>`
, GENERATE_ARRAY(<しきい値_1>, <しきい値_2>, <しきい値_n> ));
- GENERATE_ARRAY関数は、カンマ文字で区切られ、丸括弧で囲まれたしきい値の値を含む配列を作成します。この関数の出力には、入力で渡されたしきい値、再現率、偽陽性率、真陽性、偽陽性、真陰性、偽陰性の数が含まれます。
予測
SELECT *
FROM ML.PREDICT(
MODEL `<プロジェクト名>.<データセット名>.<ml_モデル名>`,
`<プロジェクト名>.<データセット名>.<機能テーブル>`
、STRUCT(<しきい値> AS しきい値));
ML.PREDICT関数は
- ML.PREDICTキーワードを使用してMLモデルの予測を行います。
-
<ml_model_name>は予測に使用するMLモデルの名前を指定し、 -
<features_table>は予測を行うための特徴を含むテーブルを指定します。
オプションとして、ロジスティック回帰モデルの場合は、<threshold>値を指定して、その後にASキーワードで名前を指定することができます。
モデルの削除
DROP MODEL `<プロジェクト名>.<データセット名>.<ml_モデル名>`;
モデルが存在する時だけ削除する場合
DROP MODEL IF EXISTS `<プロジェクト名>.<データセット名>.<ml_モデル名>`;
Discussion