SQL トレーニング BigQuery

トレーニングの目的 ● BigQuery のスキルを、一通り学ぶ為のトレーニングです。 ● SQLの基礎と共に、業務でよく使用する関数を学んでいただきます。

トレーニングの全体像 STEP 1 STEP 2 STEP 3 STEP 4 udemy：BigQuery で学ぶ非エンジニアのための SQL データ分析入門 当スライド p* ~ 当スライド p* ~ 当スライド p* ~ ● ● ● ● ● 初級 中級 上級 ・初級レベルまでの関数を使い、正しい数 字を出すまで自走できるようになる ・難易度の低い機能改修タスクの実装を 独力で遂行する知識を身につける ・アドホック分析 /抽出要件を満たす為に 必要なデータを、必要なデータ形式で抽 出/検証まで自走できる。 ・保守性（パイプライン組込時のサーバー負荷 や実行速度等）を考え、要件に沿って（場合によ り提案し）データマート・ SQLの設計、開発、運 用保守ができる レベル感 トレーニング内容 オンライン教材（SQLデータ分析）🔗

進め方 ご自身のスキルレベルに合わせて、STEP順に学んでください。

STEP1 初級 ~

STEP1 udemy：BigQuery で学ぶ非エンジニアのための SQL データ分析入門 を受講してください。 オンライン教材（SQLデータ分析）🔗

STEP2 初級

STEP2 目次 ● テーブル操作（CREATE、DROP文） ● データ操作（INSERT、UPDATE、DELETE、TRUNCATE文）

テーブル操作 CREATE文 CREATE文は、新しいテーブルを作成する際に使用されます。 ex) 顧客情報を格納する新しいテーブル「customers」を作成する場合 CREATE TABLE dataset_name.customers ( customer_id INT64, customer_name STRING, email STRING, phone_number STRING ); ● この例では、dataset_nameというデータセットに customersというテーブルを作成しています。 ● テーブルのスキーマは、 customer_id（整数型）、customer_name（文字列型）、email（文字列型）、 phone_number（文字列型）の4つのカラムから構成されています。 ● 必要に応じてデータ型やカラム名を変更し、自分のデータセットに合わせた CREATE文を作成できます。

テーブル操作 CREATE文 （SELECT結果使用） 既存テーブルからSELECT結果を、そのまま新しいテーブルとして作成することが出来ます。 ex) あるテーブルから特定のカラムを抽出して新しいテーブルを作成する場合 CREATE TABLE dataset_name.new_table_name AS SELECT column1, column2 FROM dataset_name.existing_table WHERE hogehoge = ‘aaa’; ● この例では、dataset_nameデータセット内のexisting_tableからcolumn1とcolumn2のカラムを抽出し、 dataset_nameデータセット内にnew_table_nameという新しいテーブルを作成しています。さらに、条件に従っ て特定の行だけを選択して新しいテーブルを作成することも可能です。 ● 前スライドは空っぽのテーブルが作成され、こちらではデータ入りのテーブルが作成されます。

テーブル操作 DROP文 DROP文は、既存のテーブルを削除する際に使用されます。 ex) 「customers」テーブルを削除する場合 DROP TABLE dataset_name.customers ; ● この例では、dataset_nameデータセット内のcustomersテーブルが削除されます。テーブルのデータやスキー マも含め、完全に削除されるので、慎重に使用してください。 ● DROP文を実行すると、削除されたテーブルは元に戻すことができないので、必ず確認してから実施しましょう。

データ操作 INSERT文 INSERT文は、新しい行を既存のテーブルに挿入する際に使用されます。 INSERT INTO dataset_name.table_name (column1, column2, column3) VALUES (value1, value2, value3); ● この例では、dataset_nameデータセット内のtable_nameテーブルに新しい行を挿入しています。カラム名とそ れに対応する値を指定して、指定したカラムに対して値を挿入します。

データ操作 DELETE文 DELETE文は、テーブル内の特定の行やすべての行を削除する際に使用されます。 DELETE FROM dataset_name.table_name WHERE hogehoge=1; ● この例では、dataset_nameデータセット内のtable_nameテーブルから特定の行を削除する条件を指定してい ます。 ● DELETE文では、WHERE句の指定は必須となります。

データ操作 TRUNCATE文 TRUNCATE文は、テーブル内のすべての行を削除する際に使用されます。 TRUNCATE TABLE dataset_name.table_name; ● この例では、dataset_nameデータセット内のtable_nameテーブルのデータを全て削除しています。 ● TUNCATE文では、WHERE句は指定できません。

参考資料 テーブル操作やデータ操作に関する参考記事 ● CREATE文 ● DROP、DELETE、TRUNCATEの違い ● INSERT/UPDATE/DELETE文 ● UPDATE文 ● データ定義言語（DDL）Google公式サイト ● データ操作言語（DML）Google公式サイト

STEP2 演習問題 1. 新しいテーブルを作成するSQL文を書いてください。テーブル名は ordersとし、カラムは order_id（INTEGER）、order_date（DATE）、customer_id（INTEGER）、total_amount （FLOAT）を含むものとします。 2. 1に、order_date が今日の日付と昨日の日付のデータを 2行追加してください。order_date 以 外のカラムのデータは、任意で入れてください。 3. UPDATE文を使用し、今日の日付のtotal_amountに100を足してください。 4. DELETE文を使用し、2のうち、昨日の情報だけ削除してください。 5. orders テーブルを全件SELECTし、意図通りになっているか確認してください。 6. DROP文を使用して、1で作成したテーブルを削除するSQLを書いてください。 仮

STEP3 中級

STEP3 目次 ● 構造データの取り扱い（JSON / ARRAY / STRUCT） ● パーティション ● 分かりやすいSQL（コメント、コーディングルール） ● SQL コードレビュー、

構造データ JSON BigQueryでは、JSON形式データを取り扱う事が出来ます。 -- JSONデータを含むテーブルを作成 CREATE TABLE dataset.json_data AS SELECT '{"name": "Alice", "age": 30}' AS json_column; ● 上記の例では、json_dataテーブルからjson_columnカラムに含まれるJSONデータからnameを抽出しています。 -- JSONデータを抽出してnameを取得 SELECT JSON_EXTRACT(json_column, '$.name') AS name FROM dataset.json_data; JSON_EXTRACT関数を使用して、JSONデータから必要な情報を抽出出来ます。

構造データ ARRAY ARRAY関数とSTRUCT関数を使用することで、配列や構造体（ Nested Data）を操作することができます。 SELECT ARRAY[1, 2, 3, 4, 5] AS numbers_array; SELECT numbers_array[OFFSET(0)] AS first_element, numbers_array[OFFSET(2)] AS third_element FROM (SELECT ARRAY[1, 2, 3, 4, 5] AS numbers_array); 1.配列を生成する方法（ARRAY） 2.配列内の要素を取得する方法（numbers_array） 3.配列内の要素を展開して取得する方法（UNNEST） SELECT number FROM UNNEST([1, 2, 3, 4, 5]) AS number;

構造データ STRUCT ARRAY関数とSTRUCT関数を使用することで、配列や構造体（ Nested Data）を操作することができます。 SELECT STRUCT('Alice' AS name, 30 AS age) AS person; SELECT person.name AS name, person.age AS age FROM (SELECT STRUCT('Alice' AS name, 30 AS age) AS person); 1.構造体を生成する方法（STRUCT） 2.構造体内のフィールドを取得する方法 3.構造体を含む配列を生成する方法 SELECT [ STRUCT('Alice' AS name, 30 AS age), STRUCT('Bob' AS name, 25 AS age) ] AS people;

パーティション ● パーティションは、特定の列の値を基準にして内部的に複数パートに分割する機能です。 ● これによりクエリ時にスキャンする範囲を狭め、パフォーマンス向上とスキャン料金の節約ができます。 ● 主に日付や時刻などの時間ベースのカラムを使用してデータをパーティション化することが一般的です。 ● パーティショニングを使用することで、クエリのパフォーマンスを向上させることができるため、大規模な データセットの処理に適しています。 参考URL ● パーティションについて　 Google公式サイト ● GUI上でのパーティション分割テーブル作成

パーティション　テーブルの作成 CREATE TABLE dataset.partitioned_table PARTITION BY DATE(timestamp_column) OPTIONS( partition_expiration_days=365 ) AS SELECT * FROM dataset.source_table; パーティショニングを適用したいカラムを指定して、 CREATE TABLE 文を使用してパーティション化されたテーブルを作成します。

パーティション　パーティションを活用したクエリ実行 SELECT * FROM dataset.partitioned_table WHERE DATE(_PARTITIONTIME) = '2022-01-01'; パーティション化されたテーブルに対してクエリを実行する際、 WHERE 句や _PARTITIONTIME 等を使用して 特定のパーティションのデータのみを対象にすることで、パフォーマンスを向上させることができます。

分かりやすいSQL 可読性の高いSQLを書くことは非常に重要です。 可読性が高いSQLは、他の開発者やチームメンバーがコードを理解しやすくし、メンテナンスや デバッグ作業を効率的に行うことができます。 以下に、可読性を高めるためのいくつかの例を挙げます 1. 適切なインデントと改行の使用 ：SQL文を階層ごとにインデントし、適切な箇所で改行することで、コードの構造を明確に示 すことができます。 2. 意味のあるテーブル・カラムの別名 ：長すぎる名前や、意味を連想できない名前を避ける事で、SQL文が簡潔になったり、理 解しやすくなります。 3. 適切なコメントの追加 ：コードの意図や処理内容を説明するコメントを適宜追加することで、他の人がコードを追いやすくなり ます。 4. 不要な冗長性の排除 ：不要なクエリや不要な条件式を削除し、SQL文をシンプルに保つことで、理解しやすくなります。 5. サブクエリの多用を避ける ：サブクエリを多用しすぎると、クエリ全体が複雑になり、理解やメンテナンスが難しくなる可能性 があります

分かりやすいSQL　コメントアウト BigQueryでは、コメントを挿入する方法として以下の方法があります。 -- コメントアウト # コメントアウト 1. 行コメント（単一行）方法は以下 2つです。 2.ブロックコメント（複数行） -- testdatasetデータセットのテーブル一覧を取得する SELECT * FROM testdataset.INFORMATION_SCHEMA.TABLES; /* コメントアウト コメントアウト */ 例）行コメント

分かりやすいSQL サンプル -- NG SELECT name FROM dataset.table WHERE create_dt = ‘2020-10-10’ 適切なインデントと改行の例 -- OK SELECT name FROM dataset.table WHERE create_dt = ‘2020-10-10’

SQL コードレビュー SQLコードをレビューする際には、以下を考慮することが重要です。 ● 全体像を把握する ：まず最初に、SQLコード全体の構造や目的を理解します。どのようなデータを取得・ 操作しようとしているのかを把握しましょう。 ● 意図した動作の確認 ：コードが意図した通りに動作しているかを確認します。データの取得や操作が正し いかを検証します。 ● パフォーマンス ：クエリが効率的に実行されているか、不要なリソースを消費していないかを評価します。 ● 可読性・保守性の確認 ：コードの可読性が高く、保守性が確保されているかを評価します。 ● エラーハンドリング ：適切なエラーハンドリングが行われているかを確認し、エラーが発生した際の挙動を 確認します。

SQL コードレビュー　 レビューする際にチェックするポイントをいくつか例にあげます。 ● NULLの取り扱い ：NULLの扱い方によって、クエリの結果が異なる場合があります。 NULLを許容する列か否か、操作や条件式が正しく行われているかを確認し、データの完全性を保つために注意を払いま す。必要に応じてCOALESCEやIS NULLを使用して適切に処理しましょう。 ● 主キー（PK）の取り扱い ：主キーが正しく設定されていることを確認することで、データの一貫性を保つことができます。重複 や不整合を防ぐために主キーの取り扱いを慎重に行います。特に条件が複雑なクエリやJOINを含む場合は、主キーや外部 キーの設定誤りで意図しない結果セットが返ってくるので注意が必要です。 ● タイムゾーンの取り扱い ：データベースやサーバーの設定に依存することがあります。日時データを取得・保存する際には、 常にタイムゾーンを明示的に指定し、一貫性を保つようにしましょう。 ● Case文の取り扱い： Case文では条件式が上から順に評価され、最初に一致した条件の結果が返されます。条件の優先順 位や条件の重複を適切に考慮して、意図通りの結果が得られるようにしましょう。 ● 予約語の取り扱い ：予約語（Reserved Keywords）呼ばれる特定のキーワードを適切に取り扱うことが重要です。予約語は SQLの構文や機能を定義するために予約されているため、予約語をカラム名に使用するなど、その機能以外での安易な利 用は避けましょう。 予約語リスト

SQL コードレビュー　 SQLでは、注意すべき観点が数多くあります。作成、修正内容に応じて適切に確認してください。 以下、参考記事です。 ● SQLをレビューするポイント ● テーブル設計時のチェックリスト ● クエリ計算 最適化 Google公式サイト ● SQLと浮動小数点演算の誤差の話

STEP3 演習問題 1. ※ 仮

No content

END