（1）GROUPING SETS（複数ディメンションでの集計）

• GROUPING SETSを使用すると、UNION ALLを使用しなくても複数パターンの集計が可能になります。
• 下記のSQLを実行すると①都市×州×ブラウザごとの集計②都市×州ごとの集計③州×ブラウザごとの集計④全体集計（総合計）の集計を1つの結果セットとして返します。
• GROUPING()関数と組み合わせることで、GROUPINGカラムが０の場合は集計対象と判断でき、どのレベルの集計かを判別しやすくなります。

SELECT

city,

state,

browser,

GROUPING(city)   AS city_grouped,

GROUPING(state)  AS state_grouped,

GROUPING(browser) AS browser_grouped,

COUNT(DISTINCT user_id) AS unique_users,

COUNT(*) AS total_events

FROM `bigquery-public-data.thelook_ecommerce.events`

GROUP BY GROUPING SETS (

(city, state, browser),

(city, state),

(state, browser),

()

);

上記を実行すると画像のような出力結果が得られます。

左から、’city’, ‘state’, ‘browser’が各カラムの各カラムの集計対象の内容を表し、その隣の’city_grouped’, ‘state_grouped’, ‘browser_grouped’で集計に含まれているか判断ができます。

‘unique_users’が重複を除いたユーザー数の合計、’total_events’がイベントの合計数を表しています。

従来は、各集計を合わせて出力したい場合、ほぼ同じ処理であるにも関わらず、複数の SELECT 文を作成して UNION ALL で繋げる必要があったため、GROUPING SETS は非常に便利です。

（2）CUBE（多次元集計）

• GROUP BY CUBEはGROUPING SETSの短縮構文です。
• 下記のSQLを実行することで、city×browserのすべての組み合わせを集計することができます。

SELECT

  city,

  browser,

  COUNT(DISTINCT user_id) AS unique_users,

  COUNT(*) AS total_events

FROM `bigquery-public-data.thelook_ecommerce.events`

GROUP BY CUBE(city, browser)

ORDER BY city, browser;

上記を実行すると画像のような出力結果が得られます。

非常にシンプルな SQL で、複数組み合わせの集計を簡単に行うことができます。

（3）GROUP BY STRUCT, ARRAY

• STRUCTデータとARRAYデータに対しても直接GROUP BYとSELECT DISTINCTを利用できるようになりました。

（4）GROUP BY ALL

• SELECT句で集計を行う場合、指定した集計列を除外して、非集計列を自動でGROUP BYしてくれる機能です。
• 集計に利用しているSQLに列を追加してもGROUP BYの部分の修正が不要になります。

• ユーザー定義の集計関数（UDAF：User-Defined Aggregate Function）とは文字通りユーザー自身が定義・作成した集計関数を指します。
• イベントテーブルから各セッションごとのイベント範囲（最大と最小の差：そのセッション内で最初から最後までにどれだけイベントがあったか）を計算するSQLをユーザー定義の集計関数を利用して作成したいと思います。

CREATE TEMP FUNCTION session_event_range(arr ARRAY)

RETURNS INT64

LANGUAGE js AS """

  if (arr.length == 0) return 0;

  return Math.max(...arr) - Math.min(...arr);

""";



SELECT

  session_id,

  session_event_range(ARRAY_AGG(sequence_number)) AS event_range

FROM `bigquery-public-data.thelook_ecommerce.events`

GROUP BY session_id;

SQL の処理内容は下記のとおりです。

• CREATE TEMP FUNCTION：一時的に使う関数を定義（TEMPを利用しない場合、永続的な関数として複数のクエリで再利用できます。）
• 引数に数値の配列を指定、戻り値で数値を指定します。
• LANGUAGE js：関数の中身をJavaScriptで記載するために必要です。
• 処理内容：配列が空なら0を返却、0出ない場合最大値-最小値を計算して値を返却します。
• SELECT文で

①各セッション内のsequence_numberを配列にまとめます。

②さきほど作成した関数session_event_rangeを呼び出し、配列内のデータを計算させます。

③GROUP BY session_idでセッション単位で集計します。

作成したSQLを実行すると、次のような出力結果が得られます。

ユーザー定義の集計関数を利用することで、非常に自由度の高い集計が行えることがわかります。

ユーザー定義の集計関数には様々なルールがありますので、さらに詳しく知りたい方は下記のページを参照してください。

ユーザー定義の集計関数

最後に、近似集計関数について説明します。

ユーザー行動や取引データなど、日々増えていく数百万行、数十億行のデータに対して分析を行う場合、通常の集計では計算コストや処理時間が大きくなってしまいます。

「正確な値でなくても、だいたいの中央値や分位数が分かれば十分」というケースも多くあります。そこで活躍するのが近似集計関数です。

• 近似集計関数は スケッチ（Sketch） と呼ばれる小さなデータ構造を使います。

• スケッチを作成すると、膨大なデータを1回だけ処理するだけで、分位数やヒストグラム、カウントなどを近似的に推定できます。

• メモリや計算のオーバーヘッドが少なく、高速な処理が可能です。

近似集計関数の KLL quantile 関数（プレビュー機能）を利用すると、分位数（中央値や四分位数など）を高速に計算できます。

ステップ1：日ごとにスケッチを作成（sequence_numberを対象にスケッチを作成）

CREATE TABLE `your_project.your_dataset.sketch_table` AS

SELECT

  DATE(created_at, "UTC") AS day,

  KLL_QUANTILES.INIT_INT64(sequence_number) AS sequence_number_sketch

FROM

  `bigquery-public-data.thelook_ecommerce.events`

GROUP BY day;

上記で作成したテーブルの中身です。
スケッチは分位数やヒストグラムなどを近似計算するためのデータであり、人が中身を読めるようなデータではありません。

ステップ2：特定日の中央値を取得
先程作成したsketchテーブルを利用して、2025年1月16日のsequence_numberの中央値を取得してみます。

SELECT

  KLL_QUANTILES.EXTRACT_POINT_INT64(sequence_number_sketch, 0.5) AS sequence_number_median

FROM

  `your_project.your_dataset.sketch_table`

WHERE

  day = '2025-01-16';

出力結果は3でした。

ステップ3：月単位の中央値を推定
同じく先程作成したsketchテーブルを利用して、2025年1月全体のsequence_numberの中央値を取得してみます。

SELECT

  KLL_QUANTILES.MERGE_POINT_INT64(sequence_number_sketch, 0.5) AS sequence_number_median

FROM

  `your_project.your_dataset.sketch_table`

WHERE

  day >= '2025-01-01' AND day <= '2025-01-31';

出力結果は3でした。

いかがだったでしょうか。

他にも様々な機能が用意されていますので、ご興味のあるかたは下記のページを参照してください。

KLL分位関数

本記事では、BigQuery の新しい高度な集計関数として、GROUPING SETS、CUBE、STRUCT/ARRAY に対する GROUP BY、GROUP BY ALL、ユーザー定義集計関数（UDAF）、近似集計関数（KLL quantile 関数）をご紹介しました。

これらの機能を活用することで、複雑な集計処理をより簡潔に記述できるだけでなく、クエリの可読性とパフォーマンス向上も期待できます。

特に、近似集計関数は、大規模データセットにおける高速な分析に役立ちます。

BigQuery はアップデートのサイクルが非常に速いため、私も新しい情報のキャッチアップに努め、新しい機能を最大限に活用することで、データ活用に貢献していきたいと考えています。

最後までお読みいただき、ありがとうございました。