googlesheetsquery
1.0.0
Google のクエリ言語は、SQL で使用されるパターンの多くを借用して、Google シート内のデータを分析するための高速かつ強力な方法です。このチュートリアルでは、クエリを使用してサンプル データ セットから特定の質問に答える方法の基本を学びます。
まず、再生するサンプル データが必要です。 import 関数を使用して、Wikipedia から世界の人口データの表を取得します。
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)", "table", 3)この関数は、上記の URL で 3 番目のテーブルを検索し、各セルからデータを取得し、Google シートにインポートします。いいね！ただし、このデータのクエリをもう少し簡単にするために、もう 1 つやってみましょう。
データが含まれるセルをクリックして強調表示し、CMD + A を押して表全体を強調表示します。テーブル全体を強調表示した状態で、[データ] > [名前付き範囲] をクリックして [名前付き範囲] パネルを開きます。右側からパネルが表示されます。
名前付き範囲は、セルのグループに付けることができるニックネームです。私たちの国を「国」と名付けましょう。
素晴らしい！これで、クエリを開始する準備が整いました。
セル J1 を強調表示します。このセルを使用して、以下のすべてのクエリを入力します。
データセット全体のコピーを見せてください:
=QUERY(countries, "select *",1)
国と地域の列のみを表示します。
=QUERY(countries, "select B, C",1)
WHERE 句は、データセットからより具体的な質問を開始するための優れた方法です。
人口が 1 億以上の国を表示してください:
=QUERY(countries, "SELECT B, E where E >= 100000000")
名前に「es」が含まれている国を表示してください:
=QUERY(countries, "SELECT B where B contains 'es'")
名前に「es」が含まれていない国を表示してください:
=QUERY(countries, "SELECT B where not B contains 'es'")
「S」で始まる国を表示してください:
=QUERY(countries, "SELECT B where B STARTS WITH 'S'")
「s」で終わる国を表示してください (大文字と小文字を区別することを忘れないでください:
=QUERY(countries, "SELECT B where B ENDS WITH 's'")
「United」を含む国を表示:
=QUERY(countries, "SELECT B where B CONTAINS 'United'")
AND 句でさらに具体的な加算をしてみましょう。
アメリカ大陸の人口が 100,000 人未満の国をすべて表示してください。
=QUERY(countries, "select B, F WHERE C = 'Americas' AND F <= 100000", 1)
AND 句は無限に連結できます。
このクエリは、人口が 100,000 人未満および 10,000 人を超えるアメリカ大陸のすべての国を返します。
=QUERY(countries, "select B, F WHERE C = 'Americas' AND F <= 100000 AND F >= 10000", 1)
おそらく、データセットを探索するときに最初に行いたいことの 1 つは、特定のフィールドの最大値、最小値、平均値などの単純な集計を確認することです。データセット内の国の 2017 年の最大人口、最小人口、平均人口を見つけてみましょう。
=QUERY(countries, "select max(F), min(F), avg(F)", 1)
かっこいい、
少し変更を加えると、各大陸の最大、最小、平均の人口を確認できます。
=QUERY(countries, "select C, max(F), min (F), avg(F) GROUP by C", 1)
「カウント」と「グループ化」を組み合わせると、データ セットを探索するもう 1 つの優れた方法になります。これらを組み合わせると、1 つ以上の列の項目をカウントアップし、ビューを表示および集計することができます。
これら 2 つの句を使用して、リスト内の各地域にいくつの国が含まれているかを確認してみましょう。
=QUERY(countries, "SELECT C, count(B) GROUP by C", 1)
「ORDER BY」句を追加して、すべてのリージョンのリストを最高から最低の昇順で表示しましょう。
=QUERY(countries, "SELECT C, count(B) GROUP by C ORDER by C asc", 1)
「PIVOT」句は値を 1 つの行に集計します。
たとえば、2017 年のすべての人口の合計を地域ごとに表示したい場合は、次のように使用できます。
=QUERY(countries, "SELECT sum(F) pivot C", 1)
クエリの一部として数学演算を実行してデータを分析することもできます。
次に、世界総人口に対する 2017 年の人口の割合を示す国のリストを表示してみましょう。
=QUERY(countries, "select B, C, (F / 7550262101) * 100", 1)
パーセンテージ データはクールに見えますが、列ヘッダーは非常に読みやすいです。セルをクリックして名前を変更することもできますが、「label」パラメータを追加してクエリの一部として名前を設定することもできます。
=QUERY(countries, "select B, C, (F / 7550262101) * 100 Label (F / 7550262101) * 100 'Percentage'", 1)
Google クエリ言語のドキュメント: https://developers.google.com/chart/interactive/docs/query language
Google スプレッドシートのクエリ関数の概要 (Coding Is For Losers による): https://codingisforlosers.com/google-sheets-query-function/#why
