スプレッドシートQuery関数を使ってみよう ②クエリ関数の構成

クエリイメージ スプレッドシート
この記事は約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が助けてくれる

クエリ関数の使い方(関数構成)

QUERY関数の構文

QUERY(データ, クエリ, [見出し])

データ    クエリを実行するデータ範囲(別シートも可能)
クエリGoogle Visualization APIで指定されたクエリ。
ここが基本勉強範囲
見出し省略可能。[データ]の上部にある見出し行の番号。
指定しない場合や-1と指定した場合は、[データ]の内容に基づいて推測される。

クエリの部分は””(ダブルクオーテーション)で、”SELECT * ・・・”みたいな形で囲んで表示します。

検索する対象を設定する時に、クエリないに文字列を使う場合”(シングルクオーテーション)で囲んで書きます。

とりあえず、サンプルとして1つくらいみてみるのが早いです。クエリの中身は、どちらでもいいので、上の表と見比べながら、query関数をみてみましょう。アンダーラインの色が対応しています。

=query(sample!A1:H7,"select * where E > date '2005-01-01' AND F >30 ")

赤アンダーラインの部分が基本””で囲われており、その中のdate ‘2005-01-01’がさらにシングルクオーテーションで囲まれているのが分かります。””のなかで、さらに特定文字を打つ時は”で囲うと覚えておいてください。

メインのクエリ(Google Visualization APIについて)

Query関数は一般的なSQLが全て使えるわけではなく、Googleが提供するクエリ構文のみ利用可能です。そのため、MySQLやPostgresのDDL/DMLなどが全て使えるわけではないので、気をつけましょう。

では、どんなクエリが実行できるのか、それは、Google Visualization API documentに記載があります。

document url: https://developers.google.com/chart/interactive/docs/querylanguage

Clause利用
select表示するカラムを選択。表示順序はデータのまま
where条件に一致する行のデータを表示する
group by行全体の値を合計
pivot列の特定の値を新しい列に展開
order by並べ替え
limit表示するデータ(行数)の制限をかける
offset任意の行をスキップ
label列の見出しをつける(上書き)
formatフォーマットパターンを使って、行の値を整形する
options現状no_format, no_valuesの2つ(※2019年11月時点)

という大項目に分かれており、基本的なクエリでの検索は可能な形になっています。サブクエリは直接はサポートされていないので、query関数特有の方法で実装が必要です。join関数もないので、データの結合ができないようにも見えますが、これは実は{範囲1;範囲2 }みたいにデータ保持させればできます。

データの結合関係については、第6回『他のシートからデータを持ってきてみよう〜 ImportRange関数との組み合わせ〜』でもう少し触れていく予定です。

まとめ

初回だったので、簡単にQuery関数の構文についてみていきました。実際にデータを操る部分はクエリで、データ自体はデータ部分で加工するというイメージでしたね。

これから数回に分けて、簡単なクエリ構文をみていきます。その後データの結合などをみて、最後に汚いデータへの対処方法を何個か紹介していく流れを想定しています。

まずは、自分のスプレッドシートに取り扱ってみたいデータを準備してみてください。何が取り出したいか、集計したいかが見つからないと、どんなQuery関数を作るべきかは一生見つかりません。

関数はあくまで集計のサポートであって、とるべきデータの知見は皆さんの中にあるはずですので。

連載目次: 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 クエリ関数エラー対策

コメント

  1. […] […]