はじめに
BigQueryはシンプルなクエリで高速な分析ができるのが魅力ですが、本格的なデータ分析やパフォーマンスチューニングを行うには、以下のようなお作法を理解する必要があります。
- ✅ Window関数
- ✅ 時間を使った条件指定
- ✅ ワイルドカードによる複数テーブル参照
- ✅ パーティション指定
この記事では、個人的に抑えておきたい機能についてサンプルクエリを交えてまとめています。
1. Window関数（分析関数）
Window関数を使うと、集計しながらも元データの粒度を保持できます。
🔹 サンプル：各ユーザーの累積売上を計算
SELECT
user_id,
order_date,
amount,
SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS cum_amount
FROM
`project.dataset.orders`
✅ ポイント
-
PARTITION BY：ユーザー単位に集計 -
ORDER BY：日付順に累積 -
OVER()を使って、集計結果を行単位で出力！
2. 時間指定（TIMESTAMP, DATE関数）
BigQueryでは、TIMESTAMP, DATE, DATETIME を扱う多彩な関数が用意されています。
🔹 サンプル：特定の月のデータを抽出
SELECT *
FROM `project.dataset.orders`
WHERE DATE(order_datetime) BETWEEN '2024-04-01' AND '2024-04-30'
🔹 時間関数の例
| 関数 | 説明 |
|---|---|
CURRENT_DATE() |
今日の日付 |
DATE_SUB(DATE, INTERVAL N DAY) |
N日前 |
FORMAT_TIMESTAMP('%Y-%m', timestamp) |
フォーマット変換 |
3. ワイルドカードテーブル（_TABLE_SUFFIX）
日別・月別などでテーブルが分割されている場合、ワイルドカードを使って一括参照できます。
🔹 サンプル：access_log_* の全テーブルから取得
SELECT
_TABLE_SUFFIX AS table_date,
user_id,
action
FROM
`project.dataset.access_log_*`
WHERE
_TABLE_SUFFIX BETWEEN '20240101' AND '20240430'
✅ 補足
-
_TABLE_SUFFIXはワイルドカード指定時のみ使える疑似カラム。 -
WHEREで対象テーブルを絞ることでパフォーマンス向上。
4. パーティションを意識したクエリ設計
パーティションを正しく指定することで、スキャン量を大幅に削減できます。
🔹 サンプル：日付パーティションでフィルタ
SELECT *
FROM `project.dataset.partitioned_table`
WHERE _PARTITIONTIME BETWEEN '2024-04-01' AND '2024-04-30'
または、パーティション列（例：order_date）がある場合：
SELECT *
FROM `project.dataset.orders_partitioned`
WHERE order_date = '2024-05-01'
まとめ
| 技術 | 使いどころ | メリット |
|---|---|---|
| Window関数 | ユーザー別の累積・ランク付け | 粒度保持した集計 |
| 時間関数 | 日付フィルタや動的クエリ | 柔軟な条件指定 |
| ワイルドカード | 日次/月次テーブルの一括分析 | SQL簡潔＋高速 |
| パーティション | 大量テーブルの最適化 | スキャン量削減＋高速化 |
おわりに
これらの高度な機能を活用することで、BigQueryのパフォーマンスと表現力は格段に向上します。
実務における大量データ分析やコスト削減にも直結するので、しっかり理解したい。