BigQueryよく使うクエリ10選

よく使うクエリ10選

BigQueryで使用頻度の高い標準SQLクエリを10個紹介します。

前提: 以下のクエリ例では、your_project_id.your_dataset_id.your_table_id を実際のプロジェクトID、データセットID、テーブルIDに置き換えてください。

基本的なデータ選択と件数取得:

-- テーブルからすべてのカラムを100件取得
SELECT *
FROM `your_project_id.your_dataset_id.your_table_id`
LIMIT 100;

-- テーブルの全件数を取得
SELECT COUNT(*)
FROM `your_project_id.your_dataset_id.your_table_id`;

特定のカラムの選択とフィルタリング（WHERE句）:

-- 特定のカラムを選択し、条件に合致する行のみ取得
SELECT column1, column2, column3
FROM `your_project_id.your_dataset_id.your_table_id`
WHERE column1 = '値' AND column2 > 100;

データの集計（GROUP BY, COUNT, SUM, AVG, MIN, MAX）:

-- column1ごとのレコード数とcolumn2の合計を計算
SELECT
    column1,
    COUNT(*) AS record_count,
    SUM(column2) AS total_value,
    AVG(column2) AS average_value
FROM `your_project_id.your_dataset_id.your_table_id`
GROUP BY column1
ORDER BY record_count DESC;

期間によるフィルタリング（日付/タイムスタンプ型）:

-- 特定の期間内のデータを取得（例: 2024年1月1日以降のデータ）
SELECT *
FROM `your_project_id.your_dataset_id.your_table_id`
WHERE event_timestamp >= '2024-01-01 00:00:00'
  AND event_timestamp < '2024-02-01 00:00:00'; -- 月全体を取得する場合

-- または、DATE関数を使用して日付のみで比較
SELECT *
FROM `your_project_id.your_dataset_id.your_table_id`
WHERE DATE(event_timestamp) = '2024-07-13'; -- 特定の日付のデータ

重複行の除外（DISTINCT）:

-- column1とcolumn2の組み合わせで重複しないユニークな値を取得
SELECT DISTINCT column1, column2
FROM `your_project_id.your_dataset_id.your_table_id`;

テーブルの結合（JOIN）:

-- 2つのテーブルを結合し、関連する情報を取得
SELECT
    t1.id,
    t1.name,
    t2.order_date,
    t2.amount
FROM `your_project_id.your_dataset_id.table1` AS t1
JOIN `your_project_id.your_dataset_id.table2` AS t2
  ON t1.id = t2.customer_id
WHERE t2.order_date >= '2025-01-01';

サブクエリの使用:

-- サブクエリで集計した結果を元にフィルタリング
SELECT
    column1,
    column2
FROM `your_project_id.your_dataset_id.your_table_id`
WHERE column2 IN (SELECT column2 FROM `your_project_id.your_dataset_id.another_table` WHERE status = 'active');

ウィンドウ関数（ROW_NUMBER(), RANK(), LEAD(), LAG(), SUM() OVER()など）:

-- 各グループ（column1）内でcolumn2の値に基づいて行に番号を付与
SELECT
    column1,
    column2,
    column3,
    ROW_NUMBER() OVER(PARTITION BY column1 ORDER BY column2 DESC) AS rn
FROM `your_project_id.your_dataset_id.your_table_id`
QUALIFY ROW_NUMBER() OVER(PARTITION BY column1 ORDER BY column2 DESC) = 1; -- 各グループの最初の行のみ取得

QUALIFY はBigQueryの強力な機能で、ウィンドウ関数の結果をフィルタリングするために使用します。

NULL値の扱い（IS NULL, IS NOT NULL, COALESCE）:

-- column1がNULLのレコードを取得
SELECT *
FROM `your_project_id.your_dataset_id.your_table_id`
WHERE column1 IS NULL;

-- column1がNULLでないレコードを取得
SELECT *
FROM `your_project_id.your_dataset_id.your_table_id`
WHERE column1 IS NOT NULL;

-- column1がNULLの場合、'N/A' を表示
SELECT
    COALESCE(column1, 'N/A') AS display_column1,
    column2
FROM `your_project_id.your_dataset_id.your_table_id`;

配列（ARRAY）と構造体（STRUCT）の操作: BigQueryはネストされたデータを扱うのに長けています。

-- 配列から要素を展開 (UNNEST)
SELECT
    t.user_id,
    item.item_name,
    item.quantity
FROM `your_project_id.your_dataset_id.orders_table` AS t,
UNNEST(t.items_array) AS item;

-- 配列を作成 (ARRAY_AGG)
SELECT
    user_id,
    ARRAY_AGG(STRUCT(product_name, quantity)) AS purchased_items
FROM `your_project_id.your_dataset_id.sales_data`
GROUP BY user_id;

これらの情報で、BigQueryの基本的な理解と操作に役立つかと思います。 BigQueryは非常に奥深いサービスなので、公式ドキュメントも併せて参照されることをお勧めします。