BigQuery 新機能を試してみた

― GROUP BY STRUCT / ARRAY と SELECT DISTINCT、そして GROUP BY ALL の使いどころ

2025 年 5 月 13 日、BigQuery で 3 つの SQL 拡張が GA（一般提供） になりました (Google Cloud)。

GROUP BY STRUCT と SELECT DISTINCT
GROUP BY ARRAY と SELECT DISTINCT
GROUP BY ALL

本稿では “実際に書いて動かしてみる” 形式で、それぞれの挙動とユースケースを確認します。

検証環境

BigQuery 標準 SQL（GoogleSQL）
公開データセット bigquery-public-data.google_analytics_sample.ga_sessions_*
- ネストされた STRUCT や ARRAY が豊富で、今回の題材に最適
スロットはオンデマンド（課金額はご留意を）

1. GROUP BY STRUCT と SELECT DISTINCT

これまで

従来、STRUCT 全体で集計や重複排除を行うには

-- 旧）全フィールドを列挙する必要があった
GROUP BY ts.source, ts.medium, ts.campaign

のようにフィールドを一つずつ書く必要がありました。

新しい書き方

WITH sample AS (
  SELECT
    trafficSource AS ts   -- STRUCT<source STRING, medium STRING, ...>
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
  LIMIT 1000
)
SELECT
  ts,
  COUNT(*) AS sessions
FROM sample
GROUP BY ts           -- ←STRUCT そのものを指定
ORDER BY sessions DESC;

SELECT DISTINCT ts も同じ感覚で書け、重複しない STRUCT 一覧が一発で取得できます。

使用感メモ

可読性が抜群。特に trafficSource のように 5–6 列ある STRUCT では差が大きい
生成された実行プランは従来と同等で、追加コストは感じられず

2. GROUP BY ARRAY と SELECT DISTINCT

サンプルデータ

次は配列型。今回は簡単に ユーザーが閲覧したページパスの配列 を合成して試します。

WITH page_views AS (
  SELECT
    fullVisitorId,
    ARRAY_AGG(h.page.pagePath ORDER BY h.time) AS pages
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
       UNNEST(hits) AS h
  WHERE `hits`[SAFE_OFFSET(0)].type = 'PAGE'
  GROUP BY fullVisitorId
  LIMIT 1000
)
SELECT
  pages,
  COUNT(*) AS users
FROM page_views
GROUP BY pages        -- ←ARRAY で group 化
ORDER BY users DESC
LIMIT 10;

SELECT DISTINCT pages と書けば、全く同じページ遷移をしたユーザー集合を抽出できます。

ポイント

配列の 要素順序も値に含まれる（['a','b'] と ['b','a'] は別物）
配列に重複要素がある場合、完全一致でしかマッチしない

3. GROUP BY ALL

背景

長い SELECT があると GROUP BY も同じ列を延々とコピペする羽目になります。
GROUP BY ALL は SELECT 式から “自動で” グルーピングキーを推論 してくれる構文です。

具体例

WITH purchases AS (
  SELECT 'JP' AS country, 'Books' AS category, 1200 AS amount UNION ALL
  SELECT 'JP', 'Toys', 800 UNION ALL
  SELECT 'US', 'Books', 1500
)
SELECT
  country,
  category,
  SUM(amount) AS total_amount
FROM purchases
GROUP BY ALL      -- ←country と category を自動推論
ORDER BY total_amount DESC;

便利なところ

SELECT を書き換えても GROUP BY が自動追従
- 例：category を消したら自動で country だけの集計に
動的に列が増減する場合にスクリプトがシンプルになる

まとめ

機能	主な使いどころ
GROUP BY STRUCT	ネストした行動データ、アトリビューション情報など “複数列＝1論理単位” の集計
GROUP BY ARRAY	ページ遷移やタグ配列など順序も含めたパターン分析
SELECT DISTINCT + STRUCT/ARRAY	マスタ生成・パターン辞書作成
GROUP BY ALL	列数が多いレポートテーブル、動的列追加を伴う dbt モデル

実際に手元のクエリを 5 行ほど短縮できるだけでも、可読性と保守性は大幅アップ。
皆さんもぜひ、試してみてください。