GROUP BY のその先へ: BigQuery の新しい高度な集計関数
Candice Chen
BigQuery Product Manager
Jian He
BigQuery Engineer Manager
※この投稿は米国時間 2025 年 7 月 10 日に、Google Cloud blog に投稿されたものの抄訳です。
大規模なデータ分析は BigQuery が強みを発揮するところです。SUM、AVG、COUNT などの基本的な集計関数を駆使して、あらゆる種類のワークロードから有意義な分析情報を抽出できます。そして本日発表する一連の高度な集計機能により、さらに複雑かつ高度なユースケースが可能になり、BigQuery のデータ分析能力は新たな次元へと引き上げられました。これらの集計関数は、次の 3 つのカテゴリに分類されます。
-
Group by の拡張機能（grouping sets / cube、group by struct、array、group by all）
-
ユーザー定義の集計関数 （JavaScript / SQL UDAF）
-
近似集計関数（KLL quantiles, Apache DataSketches）
これらの関数は、BigQuery の Customer Council グループから特に機能リクエストの多かったものです。New York Times は次のように述べています。
「GROUP BY ROLLUP をリリースしてくれた BigQuery チームに本当に感謝しています。これまで毎日 2 時間以上かかっていたクエリが 10 分で実行できるようになりました。他の多くのチームもこの拡張機能を使いたがっています。時間の短縮だけでなく、スロットの消費量も約 96% 減少しました。」- The New York Times、コンサルタント、Edward Podojil 氏
これらの新しい集計関数と、データ分析ワークフローでの使用方法について詳しく見ていきましょう。
Group by の拡張機能
Grouping sets を使用すると、UNION ALL を使用しなくても、1 つのステートメントで複数のディメンションの集計を柔軟に計算できます。Group by Struct と Group by Array により、BigQuery で一般的に使用されるデータ型で簡単にグループ化できます。Group by を使用すると、各列を 2 回繰り返し処理することなく、Select ステートメント内のすべての非集計列でグループ化できます。
GROUP BY GROUPING SETS、CUBE（一般提供）
データを分析するために、複数のディメンションに細分化しなければならないことがよくあります。これまでは UNION ALL / CROSS JOIN を繰り返してデータをグループ化するしか方法がなく、そのためクエリが煩雑になり理解しづらくなることがありました。GROUP BY GROUPING SETS を使用すると、1 つのステートメントで複数のディメンションをグループ化できます。たとえば、次のクエリは、さまざまな組み合わせでグループ化された売上高の合計を返します。
-
Date: 1 日あたりの総売上高
-
Region: 地域ごとの総売上高
-
Product: 商品ごとの総売上高
GROUP BY CUBE(x, y) は GROUP BY GROUPING SETS ((x,y), x, y, ()) の短縮構文で、さまざまなディメンションのすべての組み合わせでグループ化できます。
GROUP BY STRUCT、ARRAY（一般提供）STRUCT と ARRAY は、現在 BigQuery で最もよく使用されているデータ型です。この STRUCT データと ARRAY データの操作が BigQuery でさらに簡単になり、これらの半構造化データ型に対して GROUP BY と SELECT DISTINCT を直接使用できるようになりました。つまり、STRUCT / ARRAY を JSON 文字列に変換するなど、時間のかかる回避策から解放され、クエリが簡素化され、パフォーマンスが向上し、複雑な分析がより効率的になります（ドキュメント）。
GROUP BY ALL（一般提供）GROUP BY ALL を使用すると SELECT 句から集計を除くすべての列を導出してグループ化するため、SELECT と GROUP BY で同じ列を 2 回リストする必要がなくなります。ディメンションが多く、集計が少ないクエリでよく使用されますが、すべての列を 2 回記述するのは、クエリが長くなるうえ面倒です。たとえば、簡単な GROUP BY ALL クエリを使用して、シカゴのタクシーの乗車状況を company、payment_type、taxi_id でグループ化してみましょう。
ユーザー定義の集計関数（JavaScript / SQL UDAF）
ユーザー定義の集計関数（UDAF）を使用すると、カスタム集計を一度定義するだけで、プロジェクトやチーム全体で再利用できます。加重平均、JSON データのマージ、さらには地理空間関数のシミュレーションなど、高度な機能を利用するために、同じロジックを何度も記述する必要はもうありません。
JavaScript UDAF（一般提供）
JavaScript のユーザー定義集計関数（JS UDAF）を使用すると、組み込み関数では対応できないカスタム集計ロジックを作成できるため、ニーズを正確に反映した指標を計算できます。たとえば、加重平均や特殊な統計を計算したり、データスケッチを構築したりする UDAF を作成できます。JavaScript UDAF の例を次に示します。これは、mode() 関数をシミュレートし、グループ内で最も頻度の高い値を返します。
SQL UDAF（一般提供）
SQL のユーザー定義集計関数（SQL UDAF）を使用すると、複雑な集計式をコンポーズ可能、再利用可能な UDF にカプセル化できるため、同じコードを何度も記述する必要がなくなります。たとえば、構造体コンストラクタを使用して、複数の集計関数呼び出しを UDAF にラップしてみましょう。
近似集計関数
広告テクノロジー、小売、フィンテックなどの業界の企業は、通常、膨大な量の多次元データを保有しています。しかし、数百万行または数十億行のユーザー行動データから分析情報を抽出するには、多大な費用と時間がかかる可能性があります。これは、所定の誤差範囲内で計算する近似集計の結果で構わないから、早く回答が欲しいという多くの企業のために用意した関数です。スケッチを使用すると、メモリと計算のオーバーヘッドを最小限に抑えつつ、膨大なデータでも 1 回処理するだけで、個別のカウント、分位数、ヒストグラム、その他の統計量を近似推定できます。
KLL quantile 関数（プレビュー）
BigQuery では、ネイティブの KLL quantile 関数を使用して分位数を計算できます。たとえば、特定の日または月のすべてのタクシー乗車について、乗車時間と乗車距離の中央値を推定してみましょう。
まず、`chicago_taxi_trips` データテーブルから、日ごとの trip_seconds と trip_miles の KLL quantile スケッチを作成します。
次に、EXTRACT_POINT_INT64 / FLOAT64 関数を使用すると、特定の日におけるすべての乗車の trip_seconds と trip_miles の中央値を取得できます。
また、MERGE_POINT_INT64 / FLOAT64 関数を使用して、1 か月間のすべての乗車について、trip_seconds と trip_miles の中央値を推定することもできます。このクエリは、まず日ごとのスケッチをマージし、次に 1 か月間の分位値を計算します。
Apache DataSketches（一般提供）BigQuery は、スケッチのネイティブ サポートに加えて Apache DataSketches もサポートしています。確率的なストリーミング アルゴリズム（スケッチ）のオープンソースの高性能ライブラリである Apache DataSketches は、Yahoo が最初に開発したもので、JS UDAF を活用することで、UDF の公開レポジトリ bigquery-utils を介して直接使用できるようになっています。以下に例をいくつか挙げます（詳しくは、こちらのブログ投稿をご覧ください）。
-
Theta Sketch: カーディナリティの推定と集合演算（和集合、積集合、差集合）に特化した設計
-
KLL Sketch: 分位数の推定用に設計
-
Tuple Sketch: Theta Sketch の拡張で、一意のアイテムに値を関連付けられるようにする
次の例では、UDAF スケッチ ライブラリの威力を示すために、一般公開データセットであるシカゴのタクシー乗車のデータセットに対して Theta Sketch を使用しています。
まず theta_sketch_agg_string を使用して、1 日に運行するタクシーのユニーク数を推定する日ごとの Theta Sketch を作成します。
次に theta_sketch_intersection を使用すると、特定の 2 つの日付に運行したタクシーのユニーク数を推定できます。
theta_sketch_union を使用すると、この 2 日間のいずれかに運行したタクシーのユニーク数を推定できます。
高度な集計機能でもっと便利に
BigQuery の高度な集計機能を使用すると、より複雑な分析を効率的に実行できます。Grouping sets を使用すると、複数のディメンションで集計を柔軟に計算できます。UDAF を使用すると、カスタム集計を定義できます。近似結果でよい場合は、速度、スケーラビリティ、パフォーマンスを提供する近似集計関数を利用できます。これらの機能を活用することで、データからより深い分析情報を引き出してチームに提供し、ビジネス上の意思決定をタイムリーに行えるよう支援できます。皆様のユースケースや、日々の分析でどのように活用されるのか、ぜひお聞かせください。
ー BigQuery プロダクト マネージャー Candice Chen
ー BigQuery エンジニア マネージャー Jian He
