BigQueryでGROUPING SETSが実装されたので早速試してみた

概要

BigQueryがGROUPING SETSをサポートし始めました
GROUPING SETSの基本的な使用方法と、それがデータ分析にどのように役立つかを実際のクエリ例とともに紹介します

こんにちは、データエンジニアの石田です。今月の初め頃にBigQueryにGROUPING SETSの機能がリリースされました。一般的なSQL環境では既にお馴染みの機能ですが、BigQueryでは長らくサポートされていませんでした。いままでもUNIONやUNNESTを駆使すれば同様の集計は可能でしたが、GROUPING SETSを使うことで簡素に書けるようになりましたので、実際の使用例を通じて使い方を紹介します。 cloud.google.com

GROUPING SETSとは何か？

GROUPING SETSは、GROUP BY句の一部で、複数のグループ化基準を一度のクエリで指定できる機能です。簡単に言えば、一つのクエリで複数のGROUP BYを実行する機能だと思ってください。

異なるレベルの集計を同時に行いたい場合に役立ちます。とてもシンプルに書けるので冗長性が減少し、実行効率が向上します。

GROUPING SETSの使い方

それではGROUPING SETSの使用例を見てみましょう。

我々はホワイトヘルスケアでは、医療データの分析をメインで行っています。ここでは、hospital（病院）、patient（患者）、point（点数、患者の医療費のこと）の3つのフィールドを持つ架空の医療データセットを使用します。

hospital	patient	point
ヘルスケア病院	石田さん	200
ヘルスケア病院	山田さん	250
ヘルスケア病院	鈴木さん	550
ホワイトクリニック	石田さん	100
ホワイトクリニック	山田さん	200
ホワイトクリニック	鈴木さん	450

この点数を「病院ごと」、「患者ごと」、そして「病院と患者の組み合わせごと」に集計したいとします。普通に集計するなら、集計したいレベルごと3つの別々のクエリでGROUP BYを実行します。しかし、GROUPING SETSを使用すると、これを一度のクエリで実行できます。

SELECT 
  hospital, 
  patient, 
  SUM(point) as total_points
FROM 
  medical_data
GROUP BY 
  GROUPING SETS (
    (hospital),
    (patient),
    (hospital, patient)
  );

このクエリは、次の3つの集計を一度に行います:

病院ごとの点数合計
患者ごとの点数合計
病院と患者の組み合わせごとの点数合計

結果セットは以下のようになります。

hospital	patient	total_points
ヘルスケア病院	NULL	1000
ホワイトクリニック	NULL	750
NULL	石田さん	300
NULL	山田さん	450
NULL	鈴木さん	1000
ヘルスケア病院	石田さん	200
ヘルスケア病院	山田さん	250
ヘルスケア病院	鈴木さん	550
ホワイトクリニック	石田さん	100
ホワイトクリニック	山田さん	200
ホワイトクリニック	鈴木さん	450

いくつかの行にhospitalまたはpatientがNULLとして表示されていますが、これはその行が特定のレベルの集計（病院全体または患者全体）を表しているためです。例えば、patientがNULLの場合、その行は病院全体の点数を示しています。

GROUPING SETSを使わずにこれを集計しようとするなら、hospitalでGROUP BYしたレコード、patientでGROUP BYしたレコード、hospital, patientでGROUP BYしたレコードを個別に集計した後、それらをUNIONしなくてはなりません。

ROLLUPとCUBEとの関係

GROUPING SETSはROLLUPやCUBEにも対応しており、これらと組み合わせるとさらに複雑な集計を1つのクエリで実現できます。それぞれ使い方を見てみましょう。

ROLLUP

ROLLUPは特定の階層での集約データ（サブトータル）と、すべてのレベルの集計（グランドトータル）を簡単に取得できる機能です。GROUPING SETSと組み合わせると、さまざまなレベルの集計を一度のクエリで取得できます。

以下は、hospital（病院）とdepartment（診療科）の架空のデータセットです。

hospital	patient	point
ヘルスケア病院	循環器科	350
ヘルスケア病院	神経科	650
ホワイトクリニック	循環器科	400
ホワイトクリニック	神経科	350

例えば、病院内の各診療科で行われた診療の点数の合計を計算し、さらにその病院全体での合計も計算したい場合、ROLLUPが役に立ちます。

SELECT 
  hospital, 
  department, 
  SUM(point) as total_points
FROM 
  medical_data
GROUP BY 
  GROUPING SETS (
    ROLLUP (hospital, department)
  );

このクエリは、以下の集計を行います。

各病院と診療科部門の組み合わせごとの点数合計（最も詳細なレベル）
各病院全体の点数合計（診療科をロールアップした結果）
全病院の点数総合計（全てをロールアップした結果）

結果セットは以下のようになります。

hospital	department	total_points
ヘルスケア病院	循環器科	350
ヘルスケア病院	神経科	650
ヘルスケア病院	null	1000
ホワイトクリニック	循環器科	400
ホワイトクリニック	神経科	350
ホワイトクリニック	null	750
null	null	1750

NULLはロールアップされた集計を示しています。たとえば、departmentがNULLの場合、その行は病院全体の小計を示しています。表の一番下、hospitalとdepartmentの両方がNULLのレコードは、全病院の総合計です。

各レベルは、前のレベルのデータを「ロールアップ」して、より集約された値を集計しています。表の下に行けば行くほど、全体に近くなるイメージでしょうか。上の例はカラムが少ないのでピンとこないかもしれませんが、これに患者、地域、疾患、担当医、etc...と様々な階層が加わったときも、このクエリで一撃で全ての粒度の集計が行えるため、詳細からより広い視点の洞察を一度に得ることができます。

CUBE

CUBEはROLLUPと似ていますが、CUBEの方が提供される集計の種類がより多いです。CUBEは、指定された列のすべての可能な組み合わせにわたって集計を行います。

実行すると、データを複数の次元で集計し、各階層のサブトータルと、全階層のグランドトータルを含む、クロス集計（ピボットテーブルのような）結果を得ることができます。

具体的な例を使いながら説明します。以下は、hospital（病院）、department（診療科）、doctor（医者）という3つの階層があるセットです。

hospital	department	doctor	point
ヘルスケア病院	循環器科	山本	150
ヘルスケア病院	循環器科	佐藤	200
ヘルスケア病院	神経科	山本	300
ヘルスケア病院	神経科	佐藤	350
ホワイトクリニック	循環器科	山本	250
ホワイトクリニック	循環器科	佐藤	150
ホワイトクリニック	神経科	山本	200
ホワイトクリニック	神経科	佐藤	150

CUBEを使用すると、これらのすべての可能な組み合わせ（病院、診療科、医者、病院+診療科、病院+医者、診療科+医者、病院+診療科+医者、など）にわたってデータを集計できます。

以下が、GROUPING SETSとCUBEを使用したクエリです。

SELECT 
  hospital, 
  department, 
  doctor,
  SUM(point) as total_points
FROM 
  medical_data
GROUP BY 
  GROUPING SETS (
    CUBE (hospital, department, doctor)
  );

以下がクエリの実行結果になります。

hospital	department	doctor	total_points
ヘルスケア病院	循環器科	山本先生	150
ヘルスケア病院	循環器科	佐藤先生	200
ヘルスケア病院	循環器科	NULL	350
ヘルスケア病院	神経科	山本先生	300
ヘルスケア病院	神経科	佐藤先生	350
ヘルスケア病院	神経科	NULL	650
ヘルスケア病院	NULL	山本先生	450
ヘルスケア病院	NULL	佐藤先生	550
ヘルスケア病院	NULL	NULL	1000
ホワイトクリニック	循環器科	山本先生	250
ホワイトクリニック	循環器科	佐藤先生	150
ホワイトクリニック	循環器科	NULL	400
ホワイトクリニック	神経科	山本先生	200
ホワイトクリニック	神経科	佐藤先生	150
ホワイトクリニック	神経科	NULL	350
ホワイトクリニック	NULL	山本先生	450
ホワイトクリニック	NULL	佐藤先生	300
ホワイトクリニック	NULL	NULL	750
NULL	循環器科	山本先生	400
NULL	循環器科	佐藤先生	350
NULL	循環器科	NULL	750
NULL	神経科	山本先生	500
NULL	神経科	佐藤先生	500
NULL	神経科	NULL	1000
NULL	NULL	山本先生	900
NULL	NULL	佐藤先生	850
NULL	NULL	NULL	1750

NULLを多数含むので一見とっつき難い印象があるかもしれませんが、今までの一連の出力例と同様だと思えば、それほど違和感はないでしょう。

簡潔なクエリで様々な角度からのインサイトを得ることができる一面、すべての可能な組み合わせを含むため計算量が多く、中には分析に必要ではない組み合わせも含まれるため、少し冗長かもしれませんね。

まとめ

GROUPING SETSをうまく使いこなせば、グループ集計のダルさを一発で解消できます。レポートにするまでもないけど、なんとなくデータ全体のイメージをカジュアルに把握したいなんて時に最適です。ただし、やはり予想外に計算量が多くなる可能性があるので、必要なデータだけをしっかりと指定して、スマートにクエリを実行しましょう！

採用情報

ホワイトヘルスケアと医療データの分析に興味がある方は以下をクリック。

hrmos.co

ホワイトヘルスケア - テックブログ

概要

目次

はじめに