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 | WHERE句で条件に一致する行を抽出する | ★★★★★ |
GROUP BY | 列のデータを行方向に集約(グルーピング)する | ★★★★★ |
PIVOT | 列のデータを列方向に集約(グルーピング)する | ★★★★☆ |
ORDER BY | 出力されるデータを並び替える | ★★★★☆ |
SKIPPING | 出力されるデータの先頭行から任意の行数ずつスキップする | ★☆☆☆☆ |
LIMIT | 出力されるデータの行数を制限する | ★★★☆☆ |
OFFSET | 出力されるデータの先頭行から任意の行数スキップする | ★★☆☆☆ |
LABEL | 出力される列名を変更する | ★★★★☆ |
FORMAT | 出力される列のデータの書式を変更する | ★★★☆☆ |
OPTIONS | 追加オプションを設定する | ★☆☆☆☆ |
この記事では、列のデータを行方向に集約するGROUP BY句について、じっくり解説していきます。
目次
データサイエンティスト / ブロガー
たぬ
tanu
福岡在住のデータサイエンティスト。Pythonでルーチンワークを瞬殺する仕組みを作ることやデータを視覚化することが得意です。
GROUP BY句の基本
=QUERY(データ, "SELECT 列1, 集約関数(列2) GROUP BY 列1", -1)GROUP BY句の基本的な使い方は、『定性データの列をユニークな値でグルーピングし、定量データの列に集約関数を適用する』という流れになります。
集約関数とは、要約統計量を求める関数であり、以下の5種類がサポートされています。
| 集約関数 | 説明 | 使用できる列のデータ型 | 戻り値のデータ型 |
|---|---|---|---|
AVG | 平均値を返す | number | number |
COUNT | 要素数を返す(欠損値は数えない) | All type. | number |
MAX | 最大値を返す | All type. | Same type as column. |
MIN | 最小値を返す | All type. | Same type as column. |
SUM | 合計値を返す | number | number |
GROUP BY句の使い方
それでは、冒頭に紹介したように所属(dept)ごとの平均年齢(age)を知りたい場合にどのようにクエリ文を組み立てていくのか見てみましょう。
"GROUP BY C"とクエリ文を書きます。
=QUERY(B2:I8, "GROUP BY C", -1)"SELECT C GROUP BY C"とクエリ文を追加します。このとき、SELECT句とGROUP BY句の順番を逆にしないように注意しましょう。
=QUERY(B2:I8, "SELECT C GROUP BY C", -1)"SELECT C, AVG(G) GROUP BY C"とクエリ文を追加します。
=QUERY(B2:I8, "SELECT C, AVG(G) GROUP BY C", -1)複数の集約関数を使う
=QUERY(データ, "SELECT 列1, 集約関数1(列2), 集約関数2(列2) GROUP BY 列1", -1)複数の集約関数を使う場合は、集約関数と集約関数の間にカンマを入力して、"SELECT 列1, 集約関数1(列2), 集約関数2(列2)"のように指定します。
それでは、複数の集約関数を使う例として、『所属』ごとの『年齢』の最小値、最大値を確認してみましょう。
"GROUP BY C"とクエリ文を書きます。
=QUERY(B2:I8, "GROUP BY C", -1)"SELECT C GROUP BY C"とクエリ文を追加します。このとき、SELECT句とGROUP BY句の順番を逆にしないように注意しましょう。
=QUERY(B2:I8, "SELECT C GROUP BY C", -1)"SELECT C, MIN(G), MAX(G) GROUP BY C"とクエリ文を追加します。
=QUERY(B2:I8, "SELECT C, MIN(G), MAX(G) GROUP BY C", -1)
複数の列をグルーピングする
=QUERY(データ, "SELECT 列1, 列2, 集約関数(列3) GROUP BY 列1, 列2", -1)複数の列をグルーピングする場合は、列と列の間にカンマを入力して、"GROUP BY 列1, 列2"のように指定します。複数の列を選択した場合、先に記述した列の方から優先的にグルーピングされます。
それでは、複数の列をグルーピングする例として、『所属』ごとの『役職持ちか否か』の平均年齢を確認してみましょう。
"GROUP BY C, H"とクエリ文を書きます。
=QUERY(B2:I8, "GROUP BY C, H", -1)"SELECT C, H GROUP BY C, H"とクエリ文を追加します。
=QUERY(B2:I8, "SELECT C, H GROUP BY C, H", -1)"SELECT C, H, AVG(G) GROUP BY C, H"とクエリ文を追加します。
=QUERY(B2:I8, "SELECT C, H, AVG(G) GROUP BY C, H", -1)
GROUP BY句×スカラー関数
GROUP BY句とスカラー関数を組み合わせると、スカラー関数に適用させた値でグルーピングすることができます。
| スカラー関数 | 説明 | 使用できる列のデータ型 | 戻り値のデータ型 |
|---|---|---|---|
YEAR | 年の値を返す | date、datetime | number |
MONTH | ゼロベースの月の値を返す(0〜11) | date、datetime | number |
DAY | 日の値を返す | date、datetime | number |
HOUR | 時間の値を返す | datetime、timeofday | number |
MINUTE | 分の値を返す | datetime、timeofday | number |
SECOND | 秒の値を返す | datetime、timeofday | number |
MILLISECOND | ミリ秒の値を返す | datetime、timeofday | number |
QUARTER | 四半期の値を返す(1〜4) | date、datetime | number |
DAYOFWEEK | 曜日の値を返す、{1:日, … , 7:土} | date、datetime | number |
NOW | 現在の日時を返す | None | None |
DATEDIFF | 2つの日付の日数差を返す(時間は切り捨て) | date、datetime | number |
UPPER | 大文字を返す | string | string |
LOWER | 小文字を返す | string | string |
スカラー関数はこれだけありますが、グルーピングで使うのは日付や時刻を抜き出すYEAR・MONTH・DAY・HOUR・DAYOFWEEKくらいですね。
それでは、スカラー関数YEARを使って、日付型の『雇われた年(hireDate)』をグルーピングして、平均年齢を求めてみましょう。
YEARを適用させた値をグルーピングしたいので、"GROUP BY YEAR(F)"とクエリ文を書きます。
=QUERY(B2:I8, "GROUP BY YEAR(F)", -1)"SELECT YEAR(F) GROUP BY YEAR(F)"とクエリ文を追加します。
=QUERY(B2:I8, "SELECT YEAR(F) GROUP BY YEAR(F)", -1)"SELECT YEAR(F), AVG(G) GROUP BY YEAR(F)"とクエリ文を追加します。
=QUERY(B2:I8, "SELECT YEAR(F), AVG(G) GROUP BY YEAR(F)", -1)
定量データをグルーピングする
GROUP BY句でグルーピングするデータは、基本的には定性データを用いますが、ある処理を施すことで、定量データもグルーピングすることができます。
その処理とは、『定量データを定性データへと変換する』ことです。専門用語を使うと、階級値を定めて定量データをビニングします。
それでは、定量データをグルーピングする例として、『年齢』を10代ごとに分割した『世代』ごとの平均月収を確認してみましょう。
=ROUNDDOWN(G3/10)*10
=ARRAY_CONSTRAIN(ARRAYFORMULA(ROUNDDOWN(G3:G/10)*10), COUNTA(G3:G), 1)
"GROUP BY H"とクエリ文を書きます。
=QUERY(B2:J8, "GROUP BY H", -1)B2:I8からB2:J8に広がったことに注意しましょう。
"SELECT H GROUP BY H"とクエリ文を追加します。
=QUERY(B2:J8, "SELECT H GROUP BY H", -1)"SELECT H, AVG(E) GROUP BY H"とクエリ文を追加します。
=QUERY(B2:J8, "SELECT H, AVG(E) GROUP BY H", -1)
連載：QUERY関数完全攻略
英語で書かれた公式ドキュメントをベースに、マーケッターの誰もがQUERY関数を完全に使いこなすための連載記事を作成しました。分析するときの辞書代わりとして利用してください。
- データ分析関数『QUERY関数』の使い方をひと通り
- SELECT句でデータから列を抽出する
- WHERE句で条件に一致する行を抽出する
- GROUP BY句で行方向にグループ集計する
- PIVOT句で列方向にグループ集計、クロス集計する
- ORDER BY句で出力されるデータを並び替える
- SKIPPING句で出力されるデータの先頭行から任意の行数ずつスキップする
- LIMIT句で出力されるデータの行数を制限する
- OFFSET句で出力されるデータの先頭行から任意の行数スキップする
- LABEL句で出力される列名を変更する
- FORMAT句で出力される列のデータの書式を変更する
- ARRAYFORMULA関数とVLOOKUP関数で『OUTER JOIN』を実現する
- QUERY関数100本ノック
それと、筆者がQUERY関数についてまとめたスプレッドシートをこちらで公開しています。QUERY関数の理解がより深まること間違いなしです！
