読んで欲しい人
- 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 |
実際の使い方〜1つのカラムでグルーピングする〜
それでは、実際に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関数の引数2つ目の最後に必ずつけます。上のケースだと、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など他のコマンド郡と組み合わせ易いか否かにあります。
ぜひ慣れていって欲しいなと思います。
【レクチャー関係紹介】
ストアカ
ストアカにおいて、私も1.5H程度で簡単にGASやQuery関数のとっかかりを解説する講座を実施しています。1Hくらいの講座と、30分くらいの相談時間(自由時間)を設けて、これからGASやQueryを始める人向けに実際のドキュメントや簡単なスプレッドシートでのGAS・Queryの使い方などを解説させていただきます。
個人的には普及活動の一環だと思って、他の業務のお客さんには申し訳ないものの、それなりにお求めやすく設定しています。必要に応じて別途継続サポートさせていただくことも可能です。
私の講座以外にもストアカには同じような金額帯でQuery講座が複数準備されていました。受講したことがないので、評価は出来ない立場にありますが、相性の良さそうな先生を探すのも手かもしれません。
MENTA
メンターを探すサイトも存在します。あまりGASやQuery関数を教えますってメンターはそこまで多くはないのが実情です。ノンプロエンジニア向けではなく、エンジニア向けor エンジニア志望者向けなので、Query関数はあまり対象にならないかもしれません。他方で純粋SQLを教えてほしいというリクエストであれば、多くのメンターが集まると思います。
ちなみに、私のレクチャーページも紹介しておきます。
【Query関数】 相談PLAN連載目次: spreadsheet_query関数を使いこなそう
SQL勉強もかねて、spreadsheetのquery関数の使い方に慣れていきます。簡単なselect文の使い方から、SQLとの違いも含めて勉強していきます。
- スプレッドシートQuery関数を使ってみよう ①概要と目的
- スプレッドシートQuery関数を使ってみよう ②クエリ関数の構成
- スプレッドシートQuery関数を使ってみよう ③SELECT句
- スプレッドシートQuery関数を使ってみよう ④WHERET句
- スプレッドシートQuery関数を使ってみよう ⑤GROUP BYを使ってみる
- スプレッドシートQuery関数を使ってみよう ⑥他のシートからデータを持ってきてみよう〜 ImportRange関数との組み合わせ〜
- スプレッドシートQuery関数を使ってみよう ⑦並び替えの基本 ORDER BY句
- スプレッドシートQuery関数を使ってみよう ⑧SQLやるなら必須 Query関数でもサブクエリをやってみる
- スプレッドシートQuery関数を使ってみよう ⑨データに改行が入ってるぞ etc クエリ関数エラー対策
- スプレッドシートQuery関数番外編 〜Query関数のエラー原因ランキング〜