スプレッドシートQuery関数学習シリーズ⑤GROUP BY解説

読んで欲しい人
シリーズ予定　（あくまで予定）
GROUP BYの基本
1. GROUP BYでできること
  1. 実際の使い方〜１つのカラムでグルーピングする〜
  2. 実際の使い方〜2つのカラムでグルーピングする〜
まとめ

読んで欲しい人

SQLに興味があるが、一から環境やデータ整備するのがめんどくさい人
vlookupとかでデータ抽出しているが、限界を感じている人
データ分析したい人
ノンプログラマーでもクエリぐらいは使いたいって人
クエリって美味しいの？って人（クエリは美味しいですよって意味も込めて）

シリーズ予定　（あくまで予定）

概要と目的
クエリ関数の構成
基本の基本　SELECT句（全部取得、特定カラムだけ取得）
抽出の基本　WHERE句を使ってみる(数値一致、大小、日付一致、大小etc）
GROUP　BY句を使ってみる←今回
他のシートからデータを持ってきてみよう〜 ImportRange関数との組み合わせ〜
並び替えの基本　ORDER BY句
SQLやるなら必須　サブクエリをやってみる
データに改行が入ってるぞ　likeで対応できない時はcontains/starts withが助けてくれる

GROUP BYの基本

さて前回は結構重たいWHEREですが、今回は結構気楽にGROUP BYです。さくっと行ってみましょう。

GROUP BYでできること

その名の通り、列の値をグループ化して処理することができるようにします。要は、カラム単位で一体とみて、ここの情報を取り出すのではなく、その個数や集計・平均などが使えるようになります。

グループ化したものについては、以下の集計処理を利用していきます。

処理	概要	カラムのデータ型	戻り値の型
avg()	数値データの平均値を計算	number	number
count()	データの個数を計算	any type	number
max()	最大値をとる日付型：古い日付の方が大きい（最も古い日付のデータ）文字型：アルファベット順	any type	カラムのデータ型と同じ
min()	最小値をとる日付型：古い日付の方が大きい（最新の日付のデータ）文字型：アルファベット順	any type	カラムのデータ型と同じ
sum()	合計値を計算する	number	number

実際の使い方〜１つのカラムでグルーピングする〜

それでは、実際にGROUP BYを利用してみましょう。

今回は、dept別の給与額を計算してみましょう。これをクエリで意味すると以下のようになります。

=query(sample!A1:H7,"select B ,sum(D) group by B")

カラムB：dept
カラムD：salary

group byする対象（group byの後にいれるカラム）は基本セレクトの対象にします。そして、集計したいデータをsum(カラム）でいれると、B列基準（B列の要素別に）合計して、salary(カラムD）の値を合計してくれます。

dept単位でsalaryが集計されているのが分かりますね。salaryの部分にsumとlabelingされています。salary部分がsumされていることがわかると思います。もちろんavgにしたらavgと表示されます。max/minも同様です。

参考）sum salaryを給与合計に変えてみる（labelの利用法)

sum salaryでも十分分かりやすいのですが、一応この表記の部分を変更することも可能です。それがlabelという要素です。

=query(sample!A1:H7,"select B, sum(D) group by B label sum(D) '合計', B '部署'")

labelはquery関数の引数２つ目の最後に必ずつけます。上のケースだと、sum(D)（sum salary表示)を”合計”に、B（deptと表示されている部分）を”部署”に変更しています。

この変更対象はselectで選択した要素を指定することになります。

汎用イメージ　＝query(データ範囲, "select A, B group by B label A 'えー', B 'びー'"

複数存在する場合は、,(カンマ）区切りで複数設定可能です。

実際の使い方〜2つのカラムでグルーピングする〜

今回は、特に意味のないグルーピングですが、BとCでグルーピングして、BとCでグルーピングされたグループ群の中で、hireDateの最大値、ageの最小値を出してみましょう。

=query(sample!A1:H7,"select B, C, max(E), min(F) group by B ,C")

複数グルーピングする場合は、group by の後に、カンマ区切りで集約させたいカラムを複数並べれば可能です。

まとめ

集計や最大値・最小値の取得などgroup byの用途は多いです。多くの機能は実はcountif/countifsやsumif/sumifsと同類です。

ただし、上記のような関数とquery関数の大きな違いは、データのアップデートが生じた場合の関数変更の必要性や、order_by/where/labelなど他のコマンド郡と組み合わせ易いか否かにあります。

ぜひ慣れていって欲しいなと思います。

【提供講座の紹介】

スプレッドシートで始めるクエリ習得（クエリ関数）
++++++++++++++++++++++++++++++++++++++
https://www.street-academy.com/myclass/121961?conversion_name=direct_message&tracking_code=aca582bad30e1261a3c978717da46284
++++++++++++++++++++++++++++++++++++++

連載目次： spreadsheet_query関数を使いこなそう

SQL勉強もかねて、spreadsheetのquery関数の使い方に慣れていきます。簡単なselect文の使い方から、SQLとの違いも含めて勉強していきます。