BigQueryのSQLで、IF文による条件分岐などプログラムのような処理ができたら便利ですよね。
本記事では、IF文やCASE式により条件分岐をする方法と、複数のクエリをトランザクション処理のように1つのクエリで実行する方法を解説します。
BigQueryのSQLチートシートをダウンロード
BigQueryのIF文による条件分岐
IF文の構文は以下のように表されます。
IF(expr, true_result, else_result)
exprがTRUEならば、true_resultが返され、それ以外の場合はelse_resultが返されます。
例えば以下のようなクエリを考えてみましょう。
SELECT sales, IF(sales > 1000, 'high', 'low') AS sales_level
FROM sales_data
このクエリでは、salesが1000を超える場合には’high’、それ以外の場合には’low’という値を返すsales_levelというカラムを作成しています。このように特定のカラムの値に着目して、ラベリングを行うような使い方がIF文の頻出の用途です。
更にIF文は入れ子構造にも対応しているので、以下のような書き方も可能です。
SELECT sales, IF(sales > 1000, 'high', IF(sales > 500, 'middle', 'low')) AS sales_level
FROM sales_data
このクエリではsalesが1000を超える場合には’high’、500を超える場合には’middle’、それ以外の場合には’low’という値を返すsales_levelというカラムを作成しています。このように入れ子構造によって２段階の条件分岐を行うことができます。
BigQueryのCASE式による条件分岐
IF文ともう1つ条件分岐に用いられる構文にCASE式があります。
CASE式の構文は以下のように表されます。
CASE
WHEN condition THEN result
[ … ]
[ ELSE else_result ]
END
例えば以下のようなクエリを考えてみましょう。
SELECT
CASE
WHEN age < 18 THEN 'Child'
WHEN age < 25 THEN 'Young'
ELSE 'Adult'
END AS age_category
FROM
`mydataset.mytable`
このクエリではageという列に着目して、18歳未満の場合には”Child”、18歳以上25歳未満の場合には”Young”、25歳以上の場合には”Adult”という値を返すage_categoryというカラムを作成しています。
このようにCASE式は複数の条件で段階的に細かく分岐を行いたい場合に向いています。
重複行の処理に便利な番号付け関数
条件分岐のほかに有用な関数として、番号付け関数があります。番号付け関数はその名の通りデータに番号や順位を付番する関数で、ROW_NUMBER()、RANK()、DENSE_RANK()などがよく使われます。例えば商品マスタや会員マスタに同じ名前をもつ重複行が存在し、更新日時が一番最新のデータのみを抽出したい場合などにこの番号付け関数は有効です。
RANK()関数はOVER()句と併用して、以下のような構文で使用します。
RANK() OVER (ORDER BY update_date DESC) as rank
このクエリではupdate_dateの降順に1から順位を振っていったrankというカラムを作成しています。つまり更新日時が一番最新のデータのみを抽出したい場合はこのrank=1のデータを抽出すればよい、ということになります。
ROW_NUMBER()、RANK()、DENSE_RANK()はそれぞれ順位付けのロジックが微妙に異なり、具体的には、
同じ値でも別の番号にしたい場合はROW_NUMBER()を、それ以外の場合はRANK()、DENSE_RANK()を使います。
BigQueryでプログラムのようなトランザクション処理を行う方法
さらにBigQueryのSQLでプログラムのような複雑なロジックを実装する方法を解説します。
BigQueryでは手続き型言語と言って、複数のクエリを1つのクエリとして実行する機能がサポートされていて、ここでもIFなどの条件分岐、FORなどのループ処理、変数の宣言、等々を実装可能です。そのためデータの加工・統合などのETL処理をプログラムで行わずに、BigQueryのSQLで完結させることもできてしまう点は大きな魅力です。
変数の宣言、ループ処理、条件分岐
以下のクエリが変数の宣言、ループ処理、条件分岐の内容を含んだクエリです。
BEGIN
DECLARE i INT64 DEFAULT 0;
WHILE i < 10 DO
IF i % 2 = 0 THEN
SELECT i;
END IF;
SET i = i + 1;
END WHILE;
END;
内容は0から9までの値の内、偶数のものを出力するという単純なものです。ポイントは
・トランザクション処理の始まりと終わりをBEGIN ~ ENDで明示している（宣言した変数はこのブロック内で参照可能）
・各ステートメントの区切りを「；」で明示している
という２点になります。
トランザクション処理における条件分岐の使い方として、例えばデータの更新処理で、新規に挿入するデータに対して、既存データ内の存在チェック→存在していれば、DELETEして挿入、存在していなければそのまま挿入というフローを実装したいとします。
BEGIN
［IF文による判定ブロック］
↓ ↓
↓［A:既存データに存在］→［DELETE INSERT］→ END
↓
［B:既存データに存在しない］→［INSERT］→ END
この場合上記のフローでステートメントを記述すれば、実装可能です。
トランザクション処理でテーブルにロックをかけて、途中で失敗したらロールバックする方法
トランザクション処理中はテーブルにロックをかけて、途中で失敗したら自動的にロールバックを行いたい場合は、BEGIN TRANSACTION構文を使います。
BEGIN TRANSACTION;
↓
［処理ブロック］
↓
COMMIT TRANSACTION;
または
ROLLBACK TRANSACTION;
このように書けば、処理がCOMMITやROLLBACKに達する前に終了した場合、自動的にロールバックを行うので、テーブルの整合性を保ちたい場合など、厳密な処理に向いています。
トランザクション処理を行うSQLをスケジュールクエリに登録して定期実行する
ここまでにご紹介したようなプログラム構造に則ったSQLを作成したら、次は日次バッチ処理のような、定期的にSQLを実行する仕組みを作りたくなることと思います。
BigQueryではCloudFunctionsのようなサーバーレスサービスを使わずに、BigQueryのコンソール画面上で定期実行処理を作り込むことができます。SQLのスケジュール実行登録は、クエリエディタ画面上部「スケジュール」 > スケジュールされたクエリを新規作成、より可能です。
まとめ
本記事ではBigQueryの条件分岐、番号付け関数、トランザクション処理を解説しました。
条件分岐、番号付け関数は基本的なSQLに慣れてきた後に、その有効性に気づく方は多いと思います。またトランザクション処理もBigQueryでETL処理を行う場合、必須となるので、実際に使ってみて便利さを実感いただけたら幸いです。
株式会社KUIXではただ導入・開発するだけでなく、導入後の利活用を実現することに着眼した、BIツールの選定・導入からレポート作成、運用、啓蒙・展開までトータルのコンサルテーションなどを行っています。
「BigQuery/BIツールを導入したい」
「すでに導入しているがデータ活用、展開が進んでいない」など
お困りの方は、ぜひお気軽にご連絡ください！お問い合わせはこちらから
BigQueryのSQLチートシートをダウンロード
