BigQuery 新機能（GROUP BY STRUCT, GROUP BY ARRAY, GROUP BY ALL）を試してみた

# BigQuery 新機能を試してみた

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

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

* **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` 全体で集計や重複排除を行うには

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

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

### 新しい書き方

```sql
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

### サンプルデータ

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

```sql
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 式から “自動で” グルーピングキーを推論** してくれる構文です。

### 具体例

```sql
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 行ほど短縮できるだけでも、可読性と保守性は大幅アップ**。
皆さんもぜひ、試してみてください。

[1]: https://cloud.google.com/bigquery/docs/release-notes#May_13_2025 "BigQuery release notes  |  Google Cloud"