スプレッドシートQuery関数を使ってみよう　⑤GROUP BYを使ってみる

クエリイメージ スプレッドシート
2019.12.02
この記事は約4分で読めます。
Advertisements

読んで欲しい人

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

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

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

GROUP BYの基本

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

GROUP BYでできること

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

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

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

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

それでは、実際に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など他のコマンド郡と組み合わせ易いか否かにあります。

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

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

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

  1. スプレッドシートQuery関数を使ってみよう　①概要と目的
  2. スプレッドシートQuery関数を使ってみよう　②クエリ関数の構成
  3. スプレッドシートQuery関数を使ってみよう　③SELECT句
  4. スプレッドシートQuery関数を使ってみよう　④WHERET句
  5. スプレッドシートQuery関数を使ってみよう　⑤GROUP BYを使ってみる
  6. スプレッドシートQuery関数を使ってみよう　⑥他のシートからデータを持ってきてみよう〜 ImportRange関数との組み合わせ〜
  7. スプレッドシートQuery関数を使ってみよう　⑦並び替えの基本　ORDER BY句
  8. スプレッドシートQuery関数を使ってみよう　⑧SQLやるなら必須　Query関数でもサブクエリをやってみる
  9. スプレッドシートQuery関数を使ってみよう　⑨データに改行が入ってるぞ etc クエリ関数エラー対策
Advertisements

コメント