データ分析の現場で使用するSQLまとめ②（Bigquery） #GoogleCloud

開発現場で使用するクエリ一覧

＜開発＆改修⇒テスト⇒保守＞

開発＆改修

1, IF分岐
2, 正規表現
3, 関数＆変数定義＆ストアドプロシージャ
4, トランザクション処理
5, 分析関数

1~6(6は次回)をすべて使用してSQLを作成してみた

1,IF分岐

CASE WHENとの差分

記載方法の差分は下記です。
縦もち(列としてレコード表示)しているデータを
横もち(列名や他列のレコード)にすることが可能です。

SQL

CASE
WHEN age < 10 THEN 'A'
WHEN age < 20 THEN 'B'
ELSE 'C'
END AS カラム名

IF文の構文は以下のように表されます。

SQL

①IF(A, true, else)
②IF(A >10, 'high', IF(B > 50, 'low', 'other'))

AがTRUEならば、truetが返され、それ以外の場合はelseが返されます。
②のように入れ子が可能です。

2,正規表現

1, 正規表現一覧
GA4データを扱う際にURLデータを扱います！
その時に使用すると便利な正規表現について記載します。

メタ文字	説明
\	メタ文字の直前につけることでメタ文字の効果を打ち消します。
.	改行文字以外の任意の1文字
*	直前の文字の0個以上の連続
+	直前の文字の1個以上の連続
?	直前の文字はあってもなくてもいいよ〜という意味。
^	行頭になります。
[^ ]	〜以外の任意の文字を表す。
()	キャプチャグループ。

引用
【BigQuery】正規表現を使ってURLを分解する - ドメイン名｜パス(path)｜utmパラメータ

<正規表現関数まとめ>

関数	使いどころ	戻り値の型
REGEXP_CONTAINS	文字列が正規表現と一致するかを判定したい	BOOL
REGEXP_EXTRACT	正規表現と一致する最初の部分文字列を抽出したい	STRING または BYTES
REGEXP_EXTRACT_ALL	正規表現と一致するすべての部分文字列を抽出したい	ARRAY または ARRAY
REGEXP_INSTR	正規表現と一致する部分が初めて出現する位置を取得したい	INT64
REGEXP_REPLACE	文字列内の正規表現と一致する部分を置き換えたい	STRING または BYTES
REGEXP_SUBSTR	（REGEXP_EXTRACTと同じ）正規表現と一致する最初の部分文字列を抽出したい	STRING または BYTES

引用
BigQuery でトランザクション処理がサポート開始！！

その他構文一覧はこちらから
Google cloud 公式 | github

3, 関数＆変数定義＆ストアドプロシージャ

◆変数

「DECLARE」で変数名とデータ型を宣言
　⇒　SETコマンドで値を代入

DECLARE {変数名} [{変数のデータ型}] [DEFAULT {変数のデフォルト値}];

--SETコマンドで値を代入
SET 変数名 = "A";

次にルーティン(後から呼び出せるようにしておくためのコンポーネント)です。

◆UDF関数

UDFとはユーザー自身で定義する関数
create temporary function getNum() as ();

SQL

visitStartTimeを日本時間の日付に変更する関数
CREATE TEMPORARY FUNCTION DATE_BY_VISITSTARTTIME(visitStartTime INT64)
AS (DATE(TIMESTAMP_SECONDS(visitStartTime), >"Asia/Tokyo")));

引用
ZOZO TECH BLOG | BigQueryでユーザー定義関数（UDF）は武器になるという話

・関数には二つ存在します。

CREATE TEMP FUNCTION

一時的
クエリ実行が完了するまで作成されます！

CREATE FUNCTION

クエリ実行が完了しても
永続的
にその関数が作られたままになります。

なんとJavascriptも使用可能です！

UDFBigQuery内でJavascriptやSQLの文法を書いて、データ整形処理を行うことのできる機能

js＆sql

CREATE TEMP FUNCTION getCountry(country STRING)
 RETURNS STRING
 LANGUAGE js AS 
 """
   function convert(country) {
     if(country == "Japan") return "日本";
      return country;
   }
   return convert(country);
 """;
select getCountry("Japan")    // 日本

配列にして渡す処理については、次のARRAY_AGG関数で扱います。

js＆sql

CREATE TEMP FUNCTION getPrefecture(pref STRING)
 RETURNS STRING
 LANGUAGE js AS 
 """
   function convert(pref) {
     if(pref == "Tokyo") return "東京";
      return pref;
   }
   return convert(pref);
 """;
With pref_eng as (
 SELECT "Tokyo" as pref_name UNION ALL SELECT >"Saitama"
)
SELECT getPrefecture(pref_name) as pref_name FROM >pref_eng

引用
【GCP】BigQueryの定数やユーザー定義関数（UDF）

・最後に簡単な使用方法の説明

sql

CREATE TEMP FUNCTION 関数名(変数名 型名(Integerなど)){
   return 計算式を記載
}
select 関数名(引数)

最後に

◆ストアドプロシージャ

上記UDFとの差分
より複雑な処理を記載できます
。
ストアドプロシージャ（STORED PROCEDURE）とは
⇒複数のSQL文を１つにまとめてプログラムの関数のように利用することができる仕組みです。

変数の宣言や変数に式の結果を格納することができます。

SQL

--入力パラメータを使用して値を渡
CREATE OR REPLACE PROCEDURE mydataset.create_customer(name STRING)
BEGIN
DECLARE id STRING;
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id, name)
  VALUES(id, name);
SELECT FORMAT("Created customer %s (%s)", id, name);
END

--呼び出し方法 Create a new customer record.
DECLARE id STRING;
CALL mydataset.create_customer("alice",id);

GCP公式ドキュメント

4, トランザクション

マルチステートメントのトランザクション
ロールバック

トランザクションとはデータの整合性とリカバリができる機能です。
同時の実行を制御できる機能と失敗した際に更新がされないロールバックが可能です。

トランザクション処理における条件分岐の使い方として、例えばデータの更新処理で、新規に挿入するデータに対して、既存データ内の存在チェック→存在していれば、DELETEして挿入、存在していなければそのまま挿入というフローとなります。


BEGIN

［IF文による判定ブロック］

　↓　↓

　↓［A:既存データに存在］→［DELETE INSERT］→ END

　↓

［B:既存データに存在しない］→［INSERT］→ END

参考文献＆引用
BigQueryのIF文による条件分岐やトランザクション処理を解説！
Transaction｜ BigQuery ドキュメント

SQL

BEGIN
    CREATE TABLE ds_test.table1_bk AS SELECT * FROM ds_test.table1;
    BEGIN TRANSACTION;
    DELETE FROM ds_test.table1 WHERE True;
--    INSERT INTO ds_test.table1 (SELECT * FROM ds_test.table1_bk WHERE x = 1);
    INSERT INTO ds_test.table1 (SELECT * FROM ds_test.table1_tmp WHERE x = 1);
    COMMIT TRANSACTION;
    DROP TABLE ds_test.table1_bk;
EXCEPTION WHEN ERROR THEN
    ROLLBACK TRANSACTION;
    DROP TABLE ds_test.table1_bk;
END;
>途中でエラーが発生したためトランザクション外のバックアップテーブル削除が実行されずに終わってしまったものの、元テーブルデータは期待通り削除前の状態にロールバックされました。 ROLLBACK TRANSACTION ステートメントを明示的に実行しなくても、トランザクション内の処理は自動でロールバックされることが確認できました。
なお、トランザクション外のクエリも含めてエラー発生時の処理を考慮する必要がある場合は、EXCEPTION 句を追加する必要があります。

引用:
BigQuery でトランザクション処理がサポート開始！！

5, 分析関数

OVER句との組み合わせ

SQL

OVER ( -- OVER句を伴うことで分析関数が明示的になる
PARTITION BY category 
-- PARTITON BY句: 指定したカラム名でパーティションを作>成する
ORDER BY id
-- ORDER BY句: パーティションの中で並び替えを行う
ROWS BETWEEN unbounded preceding AND current row 
-- WINDOW FRAME句: パーティションの中でどのレコードを>計算対象のフレームとするか宣言する
 ) AS cumulative_sales

引用
BigQueryの分析関数についてまとめたをまとめる

SQL

関数名　OVER ( PARTITION BY　グループ仕切り列名
ORDER BY 並べ替え列名
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW　ウィンドウフレーム
  )

UNBOUNDED PRECEDING :
パーティション内）の一番上の行から
CURRENT ROW :
現在の行

LAG関数、LEAD関数

・LAG関数：前の行を持ってくる関数

LAG (対象カラム, nレコード分下にずらす) OVER( [PARTITION BY ～] ORDER BY ～ )

・LEAD関数：後ろの行を持ってくる関数

LEAD (対象カラム, nレコード分だけ上にずらす) OVER( [PARTITION BY ～] ORDER BY ～ )

前回
データ分析の現場で使用するSQLまとめ①（Bigquery）

日付関数＆テスト＆日々の保守＆運用編は次へ

引用
GCP公式

参考文献
リーダブルコード ―より良いコードを書くためのシンプルで実践的なテクニック (Theory in practice)