スプレッドシートのQUERY関数のGROUP BY句を使用して、グループ集計する方法まとめ

QUERY関数のGROUP BY句を使用すると、定性データをユニークな値(一意な値、固有値)ごとにグルーピングし、それぞれの固有値単位で集計することができます。(=グループ集計)

例えば、このデータから所属(dept)ごとの平均年齢(age)を知りたい場合、次のようにクエリ文を書くことで目的のデータを抽出できます。

=QUERY(データ, クエリ, [見出し])
=QUERY(B2:I8, "SELECT C, AVG(G) GROUP BY C", -1)

これで所属ごとの年齢の平均値をそれぞれ求めることが出来ました。

QUERY関数は、第1引数に『データ』、第2引数に『目的に沿ったSQL風なクエリ文』を書くという流れになるので、この第2引数クエリを思い通りに操れるかどうか、が目的のデータを正しく抜き出すための必要条件になります。

2020年12月現在、11種類ものクエリ句が存在し、

クエリ句役割重要度(5段階)
SELECTデータから列を抽出する★★★★★
WHERE条件に一致する行を抽出する★★★★★
GROUP BY列のデータを行方向に集約(グルーピング)する★★★★★
PIVOT列のデータを列方向に集約(グルーピング)する★★★★☆
ORDER BY出力されるデータを並び替える★★★★☆
SKIPPING出力されるデータの先頭行から任意の行数ずつスキップする★☆☆☆☆
LIMIT出力されるデータの行数を制限する★★★☆☆
OFFSET出力されるデータの先頭行から任意の行数スキップする★★☆☆☆
LABEL出力される列名を変更する★★★★☆
FORMAT出力される列のデータの書式を変更する★★★☆☆
OPTIONS追加オプションを設定する★☆☆☆☆

この記事では、列のデータを行方向に集約するGROUP BY句について、じっくり解説していきます。

GROUP BY句の基本

GROUP BY句の基本構文
=QUERY(データ, "SELECT 列1, 集約関数(列2) GROUP BY 列1", -1)

GROUP BY句の基本的な使い方は、『定性データの列をユニークな値でグルーピングし、定量データの列に集約関数を適用する』という流れになります。

集約関数とは、要約統計量を求める関数であり、以下の5種類がサポートされています。

集約関数説明使用できる列のデータ型戻り値のデータ型
AVG平均値を返すnumbernumber
COUNT要素数を返す(欠損値は数えない)All type.number
MAX最大値を返すAll type.Same type as column.
MIN最小値を返すAll type.Same type as column.
SUM合計値を返すnumbernumber

GROUP BY句の使い方

それでは、冒頭に紹介したように所属(dept)ごとの平均年齢(age)を知りたい場合にどのようにクエリ文を組み立てていくのか見てみましょう。

STEP.1
グルーピングする列を選ぶ
所属(C列)ごとにグルーピングしたいので、"GROUP BY C"とクエリ文を書きます。
C列をGROUP BY句でグルーピングする
=QUERY(B2:I8, "GROUP BY C", -1)
STEP.2
グルーピングした列をSELECT句で選ぶ
STEP.1でC列をグルーピングしたので、"SELECT C GROUP BY C"とクエリ文を追加します。このとき、SELECT句とGROUP BY句の順番を逆にしないように注意しましょう。
C列をSELECT句で抽出する
=QUERY(B2:I8, "SELECT C GROUP BY C", -1)
STEP.3
集計したい列に集約関数を適用する
年齢(G列)の平均値を知りたいので、"SELECT C, AVG(G) GROUP BY C"とクエリ文を追加します。
G列に集約関数AVGを適用する(=完成)
=QUERY(B2:I8, "SELECT C, AVG(G) GROUP BY C", -1)
END

複数の集約関数を使う

GROUP BY句で複数の集約関数を使う
=QUERY(データ, "SELECT 列1, 集約関数1(列2), 集約関数2(列2) GROUP BY 列1", -1)

複数の集約関数を使う場合は、集約関数と集約関数の間にカンマを入力して、"SELECT 列1, 集約関数1(列2), 集約関数2(列2)"のように指定します。

それでは、複数の集約関数を使う例として、『所属』ごとの『年齢』の最小値最大値を確認してみましょう。

STEP.1
グルーピングする列を選ぶ
所属(C列)ごとにグルーピングしたいので、"GROUP BY C"とクエリ文を書きます。
C列をGROUP BY句でグルーピングする
=QUERY(B2:I8, "GROUP BY C", -1)
STEP.2
グルーピングした列をSELECT句で選ぶ
STEP.1でC列をグルーピングしたので、"SELECT C GROUP BY C"とクエリ文を追加します。このとき、SELECT句とGROUP BY句の順番を逆にしないように注意しましょう。
C列をSELECT句で抽出する
=QUERY(B2:I8, "SELECT C GROUP BY C", -1)
STEP.3
集計したい列に集約関数を適用する
年齢(G列)の最小値、最大値を知りたいので、"SELECT C, MIN(G), MAX(G) GROUP BY C"とクエリ文を追加します。
G列に集約関数MIN、MAXを適用する(=完成)
=QUERY(B2:I8, "SELECT C, MIN(G), MAX(G) GROUP BY C", -1)
END

複数の列をグルーピングする

GROUP BY句で複数の列をグルーピングする
=QUERY(データ, "SELECT 列1, 列2, 集約関数(列3) GROUP BY 列1, 列2", -1)

複数の列をグルーピングする場合は、列と列の間にカンマを入力して、"GROUP BY 列1, 列2"のように指定します。複数の列を選択した場合、先に記述した列の方から優先的にグルーピングされます。

それでは、複数の列をグルーピングする例として、『所属』ごとの『役職持ちか否か』の平均年齢を確認してみましょう。

STEP.1
グルーピングする列を選ぶ
所属(C列)ごとの役職もちか否か(H列)をグルーピングしたいので、"GROUP BY C, H"とクエリ文を書きます。
C列とH列をGROUP BY句でグルーピングする
=QUERY(B2:I8, "GROUP BY C, H", -1)
STEP.2
グルーピングした列をSELECT句で選ぶ
STEP.1でC列とH列をグルーピングしたので、"SELECT C, H GROUP BY C, H"とクエリ文を追加します。
C列とH列をSELECT句で抽出する
=QUERY(B2:I8, "SELECT C, H GROUP BY C, H", -1)
STEP.3
集計したい列に集約関数を適用する
年齢(G列)の平均値を知りたいので、"SELECT C, H, AVG(G) GROUP BY C, H"とクエリ文を追加します。
G列に集約関数AVGを適用する(=完成)
=QUERY(B2:I8, "SELECT C, H, AVG(G) GROUP BY C, H", -1)
END

GROUP BY句×スカラー関数

GROUP BY句とスカラー関数を組み合わせると、スカラー関数に適用させた値でグルーピングすることができます。

スカラー関数説明使用できる列のデータ型戻り値のデータ型
YEAR年の値を返すdatedatetimenumber
MONTHゼロベースの月の値を返す(0〜11)datedatetimenumber
DAY日の値を返すdatedatetimenumber
HOUR時間の値を返すdatetimetimeofdaynumber
MINUTE分の値を返すdatetimetimeofdaynumber
SECOND秒の値を返すdatetimetimeofdaynumber
MILLISECONDミリ秒の値を返すdatetimetimeofdaynumber
QUARTER四半期の値を返す(1〜4)datedatetimenumber
DAYOFWEEK曜日の値を返す、{1:日, … , 7:土}datedatetimenumber
NOW現在の日時を返すNoneNone
DATEDIFF2つの日付の日数差を返す(時間は切り捨て)datedatetimenumber
UPPER大文字を返すstringstring
LOWER小文字を返すstringstring

スカラー関数はこれだけありますが、グルーピングで使うのは日付や時刻を抜き出すYEARMONTHDAYHOURDAYOFWEEKくらいですね。

それでは、スカラー関数YEARを使って、日付型の『雇われた年(hireDate)』をグルーピングして、平均年齢を求めてみましょう。

STEP.1
グルーピングする列を選ぶ
雇われた年(F列)にスカラー関数YEARを適用させた値をグルーピングしたいので、"GROUP BY YEAR(F)"とクエリ文を書きます。
F列にスカラー関数を適用させた値をGROUP BY句でグルーピングする
=QUERY(B2:I8, "GROUP BY YEAR(F)", -1)
STEP.2
グルーピングした列をSELECT句で選ぶ
STEP.1でF列にスカラー関数を適用させた値をグルーピングしたので、"SELECT YEAR(F) GROUP BY YEAR(F)"とクエリ文を追加します。
F列にスカラー関数を適用させた値をSELECT句で抽出する
=QUERY(B2:I8, "SELECT YEAR(F) GROUP BY YEAR(F)", -1)
STEP.3
集計したい列に集約関数を適用する
年齢(G列)の平均値を知りたいので、"SELECT YEAR(F), AVG(G) GROUP BY YEAR(F)"とクエリ文を追加します。
G列に集約関数AVGを適用する(=完成)
=QUERY(B2:I8, "SELECT YEAR(F), AVG(G) GROUP BY YEAR(F)", -1)
END

定量データをグルーピングする

GROUP BY句でグルーピングするデータは、基本的には定性データを用いますが、ある処理を施すことで、定量データもグルーピングすることができます。

その処理とは、『定量データを定性データへと変換する』ことです。専門用語を使うと、階級値を定めて定量データをビニングします。

MEMO
例えば、年齢という定量データを10代、20代のようなカテゴリに分類するということです。

それでは、定量データをグルーピングする例として、『年齢』を10代ごとに分割した『世代』ごとの平均月収を確認してみましょう。

STEP.1
新しい列を作成する
年齢(G列)を定性データに変換したいので、G列とH列の間にgeneration列を新しく作成します。
STEP.2
ビニングする数式を作成
H3セルに、年齢(G列)を10歳ごとに分割する数式を設置します。
H3セル
=ROUNDDOWN(G3/10)*10
この数式を一番下まで反映させたいので、ARRAYFORMULA関数を使って拡張します。
H3セル
=ARRAY_CONSTRAIN(ARRAYFORMULA(ROUNDDOWN(G3:G/10)*10), COUNTA(G3:G), 1)
これで、ビニング処理は完了です。
STEP.3
グルーピングする列を選ぶ
世代(H列)をグルーピングしたいので、"GROUP BY H"とクエリ文を書きます。
H列をGROUP BY句でグルーピングする
=QUERY(B2:J8, "GROUP BY H", -1)
世代の列を新たに追加したので、QUERY関数の第一引数のデータ範囲がB2:I8からB2:J8に広がったことに注意しましょう。
STEP.4
グルーピングした列をSELECT句で選ぶ
STEP.3でH列をグルーピングしたので、"SELECT H GROUP BY H"とクエリ文を追加します。
H列をSELECT句で抽出する
=QUERY(B2:J8, "SELECT H GROUP BY H", -1)
STEP.5
集計したい列に集約関数を適用する
月収(E列)の平均値を知りたいので、"SELECT H, AVG(E) GROUP BY H"とクエリ文を追加します。
E列に集約関数AVGを適用する(=完成)
=QUERY(B2:J8, "SELECT H, AVG(E) GROUP BY H", -1)
END

連載:QUERY関数完全攻略

英語で書かれた公式ドキュメントをベースに、マーケッターの誰もがQUERY関数を完全に使いこなすための連載記事を作成しました。分析するときの辞書代わりとして利用してください。

  • データ分析関数『QUERY関数』の使い方をひと通り
  • SELECT句でデータから列を抽出する
  • WHERE句で条件に一致する行を抽出する
  • GROUP BY句で行方向にグループ集計する
  • PIVOT句で列方向にグループ集計、クロス集計する
  • ORDER BY句で出力されるデータを並び替える
  • SKIPPING句で出力されるデータの先頭行から任意の行数ずつスキップする
  • LIMIT句で出力されるデータの行数を制限する
  • OFFSET句で出力されるデータの先頭行から任意の行数スキップする
  • LABEL句で出力される列名を変更する
  • FORMAT句で出力される列のデータの書式を変更する
  • ARRAYFORMULA関数とVLOOKUP関数で『OUTER JOIN』を実現する

それと、筆者がQUERY関数についてまとめたスプレッドシートをこちらで公開しています。QUERY関数の理解がより深まること間違いなしです!