よく使うBigQueryのSQLクエリ集

個人用ですが、BigQueryでデータを触る時に、
よく使うものをピックアップしました。
コピペすることで効率化したいと思います。

1. データ抽出・集計

重複除外

特定のカラムで重複を除外したデータを取得します。

SELECT DISTINCT column FROM dataset.table;

集計

特定のカラムをグループ化してデータを集計（カウント）します。

SELECT column, COUNT(*) FROM dataset.table GROUP BY column;

• COUNT以外の集約関数
  • SUM(column): 数値の総和
  • AVG(column): 数値の平均値
  • MIN(column): 最小値
  • MAX(column): 最大値
  • COUNT(DISTINCT column): 重複を除外した値の数

2. データの追加・挿入

新しいレコードの追加（重複除外）

既存のデータと重複しない新しいレコードを追加します。

INSERT INTO dataset.target_table
SELECT *
FROM dataset.source_table AS src
WHERE NOT EXISTS (
  SELECT 1
  FROM dataset.target_table AS dest
  WHERE dest.column = src.column
);

3. データの結合

全カラムを結合

データセット内の複数のテーブルを全カラムで結合し、新しいテーブルを作成します。

CREATE OR REPLACE TABLE dataset.target_table AS
SELECT * FROM (
  SELECT * FROM dataset.table1
  UNION ALL
  SELECT * FROM dataset.table2
  UNION ALL
  SELECT * FROM dataset.table3  -- 必要に応じて追加
);

注意: この方法は、結合する全てのテーブルが同じカラム構造を持っている場合に有効です。

指定カラムを結合

特定のカラムのみを結合する場合のクエリです。

CREATE OR REPLACE TABLE dataset.target_table AS
SELECT column1, column2
FROM (
  SELECT column1, column2 FROM dataset.table1
  UNION ALL
  SELECT column1, column2 FROM dataset.table2
  UNION ALL
  SELECT column1, column2 FROM dataset.table3  -- 必要に応じて追加
);

注意点: この方法では、結合に含めるカラムを特定する必要があるため、カラム名が異なるテーブルを結合することが可能です。

4. データの削除

これらの操作により、テーブル内のデータが削除されます。
一度削除すると基本的に元に戻せないため、十分注意して操作を行ってください。

レコードの全件削除

テーブル内のすべてのデータを削除します。

DELETE FROM dataset.table WHERE TRUE;

レコードの条件付き削除

特定の条件を満たすデータを削除します。

DELETE FROM dataset.table
WHERE column = 'value';

テーブル自体の削除

レコードだけでなく、テーブル自体も削除します。

DROP TABLE dataset.table;

5. 特定の条件に基づく新しいカラムの追加

データの日時情報を元に、新しいカラムとして曜日を追加するクエリの例です。
曜日は日本語で表現され、BIなどで昇順・降順ができるよう、
数字の接頭詞が付加されています。

CREATE OR REPLACE TABLE dataset.new_table AS
SELECT
  id,
  CAST(date_time AS DATE) AS record_date,
  column1,
  column2,
  CASE
    WHEN EXTRACT(DAYOFWEEK FROM date_time) = 1 THEN '0.日曜'
    WHEN EXTRACT(DAYOFWEEK FROM date_time) = 2 THEN '1.月曜'
    WHEN EXTRACT(DAYOFWEEK FROM date_time) = 3 THEN '2.火曜'
    WHEN EXTRACT(DAYOFWEEK FROM date_time) = 4 THEN '3.水曜'
    WHEN EXTRACT(DAYOFWEEK FROM date_time) = 5 THEN '4.木曜'
    WHEN EXTRACT(DAYOFWEEK FROM date_time) = 6 THEN '5.金曜'
    WHEN EXTRACT(DAYOFWEEK FROM date_time) = 7 THEN '6.土曜'
  END AS weekday_label
FROM
  `project.dataset.source_table`;

date_time カラムから DAYOFWEEK 関数を用いて曜日を取得します。
CASE 文で数値の曜日インデックスを日本語の曜日名に変換し、新しいカラム weekday_label に追加します。
このクエリは、元データを元に新しいテーブル new_table を作成します。

注意点

バッククォートの利用

BigQueryでは、テーブル名や列名に特殊文字が含まれている場合、
バッククオート（`）で囲む必要があります。

例:

SELECT DISTINCT column FROM `project.dataset.table-name`;

スケジュールの注意点

BigQueryでスケジュールに設定したクエリは、
登録時のクエリがそのまま使用されるため、
修正が必要になった場合は、元のスケジュール設定を削除し、
新しくスケジュールを設定する必要があります。