Google Visualization API のクエリ言語を使用すると、データソースへのクエリを使用してさまざまなデータ操作を行うことができます。

目次

はじめに

一般的に、ビジュアリゼーションは特定の形式のデータを想定しています。たとえば、円グラフでは、テキストラベルと数値の 2 つの列にデータが存在すると想定できます。データソース内のデータが、この構造と完全には一致しない場合があります。たとえば、データソースに 3 つ以上の列が含まれている場合や、列の順序が円グラフの期待値と一致していない場合があります。

クエリ言語を使用すると、データ操作とフォーマットのリクエストをデータソースに送信し、返されるデータ構造とコンテンツが想定どおりのものであることを確認できます。

クエリ言語の構文は SQL に似ています。SQL を使い慣れているデベロッパーは、このクエリ言語をすばやく学習し、使用できます。ウェブ上に多数ある SQL チュートリアルをご覧ください。このクエリ言語と SQL にはいくつか違いがあります。詳しくは、構文のセクションをご覧ください。

データソースでクエリ言語を実装する必要はなく、存在する場合は言語のすべての機能を実装できます。特に理由がない限り、この言語のすべての機能を実装する際にデータソースに依存しないようにしてください。

クエリ言語の使用

クエリ文字列は、JavaScript コード内から、またはデータソース URL 内のパラメータとして設定することにより、2 つの方法でデータソース リクエストに追加できます。リクエストにクエリ文字列が含まれていない場合、データソースのデフォルトの動作では、デフォルトの行/列の順序と形式を使用して、すべての行と列が返されます。これを変更するには、データソースへのリクエストにクエリ文字列を含めます。

JavaScript からのクエリの設定

JavaScript コード内からクエリ文字列を設定するには、google.visualization.Query クラスの setQuery メソッドを呼び出します。

var query = new google.visualization.Query(DATA_SOURCE_URL);
query.setQuery('select dept, sum(salary) group by dept');
query.send(handleQueryResponse);

データソース URL でクエリを設定する

クエリ文字列は、tq パラメータを使用してデータソース URL に追加できます。JavaScript ではなく URL パラメータでクエリを設定すると、他のデベロッパーが作成したビジュアル表示を簡単に使用できるほか、クエリをカスタマイズすることもできます。

クエリ文字列は、URL パラメータとして適切にエンコードする必要があります。URL をエンコードするには、JavaScript の encodeURIComponent 関数を使用するか、このセクションの最後にあるエンコード ツールを使用して手動でエンコードします。

例:

Google スプレッドシートの次のクエリ文字列について考えてみましょう。（スプレッドシートの列 ID は常に文字になります。公開済みのスプレッドシートに表示される列見出しテキストは ID ではなくラベルです。クエリ文字列で、ラベルではなく ID を使用する必要があります）。

select A, sum(B) group by A

エンコードすると、このクエリは次のようになります。

select%20A%2C%20sum(B)%20group%20by%20A

スプレッドシートの URL が次のようになっているとします。

https://docs.google.com/a/google.com/spreadsheets/d/1r8_mfnZAvTFmT02JHi1XgOwn_-sLCR9XgmR8wEQ4uW4

スプレッドシートの URL に /gviz/tq?tq=YOUR_QUERY_STRING を追加して、最終的なクエリ文字列を取得します。

https://docs.google.com/a/google.com/spreadsheets/d/1r8_mfnZAvTFmT02JHi1XgOwn_-sLCR9XgmR8wEQ4uW4/gviz/tq?tq=select%A%2C%20sum(B)%20group%20by%20A

以下のツールを使用して、クエリ文字列をエンコードまたはデコードします。

言語の構文

概要

Google Visualization API のクエリ言語の構文は、SQL の構文に似ています。これは SQL のサブセットですが、学習が必要な独自の機能がいくつかあります。SQL に詳しくなれば、それほど難しくなることはありません。

すべての例で使用されている表:

このセクション全体を通して、すべてのクエリの例を次の表に示します。列見出しは列識別子で、

言語句

クエリ言語の構文は、次の句で構成されます。各句は 1 つまたは 2 つのキーワードで始まります。すべての句は省略可能です。句はスペースで区切ります。句の順序は、次の順序で指定する必要があります。

選択

select 句は、返される列とその順序を指定するために使用されます。 この句を指定しない場合、または select * を使用した場合は、データソース テーブルのすべての列が元の順序で返されます。列は、ラベルではなく識別子によって参照されます。たとえば、Google スプレッドシートでは、列識別子は 1 ～ 2 文字の列文字（A、B、C、...）です。

select 句のアイテムは、列識別子、または集計関数、スカラー関数、演算子の出力です。

例:

select *

select dept, salary

select max(salary)

次の例では、スペースを含むメールアドレス（メールアドレス）や予約語（日付）を参照する場合、バッククォートを使用します。

select `email address`, name, `date`

サンプル テーブルに対して次のクエリを実行します。

select lunchTime, name

次のレスポンスを返します。

場所

where 句は、指定した条件に一致する行のみを返すために使用されます。

シンプルな比較演算子は <=, <, >, >=, =, !=, <> です。両方の比較演算子 != <> は等しくありません。文字列は辞書式値によって比較されます。等価性は、ほとんどのコンピュータ言語における == ではなく、= で示されます。 null との比較は is null または is not null を使用して行います。

複数の条件は、論理演算子 and、or、not を使用して結合できます。かっこを使用して明示的な優先度を定義できます。

where 句は、より複雑な文字列比較演算子もサポートしています。 これらの演算子は 2 つの文字列を引数として受け取ります。文字列以外の引数（日付や数字など）は、比較前に文字列に変換されます。文字列一致では大文字と小文字が区別されます（この問題に対処するには、upper() または lower() のスカラー関数を使用できます）。

• contains - 部分文字列の一致。contains全体part が whole 内のどこかにある場合は true です。例: where name contains 'John' は「John」、「John Adams」、「Long John Silver」に一致しますが、「john adams」は一致しません。
• starts with - 接頭辞を一致させます。value starts with接頭辞は、接頭辞が値の先頭にある場合は true です。例: where dept starts with 'engineering' は「engineering」と「engineering manager」に一致します。where dept starts with 'e' は「engineering」、「eng」、「e」に一致します。
• ends with - 接尾辞と一致します。value ends with 接尾辞が value の末尾にある場合は true です。例: where role ends with 'y' は「cowboy」、「boy」、「y」に一致します。
• matches - （正規表現の）正規表現に一致します。haystack と needle は、needle と haystack が一致する場合に true となります。例: where country matches '.*ia' はインドとナイジェリアを照合しますが、インディアナ州は一致しません。これはグローバル検索ではないため、where country matches 'an' は「カナダ」とは一致しません。
• like - 2 つのワイルドカードをサポートするテキスト検索。0 以上の文字に一致する % と、任意の 1 文字に一致する _（アンダースコア）です。これは SQL の LIKE 演算子に似ています。例: where name like fre% は「fre」、「fred」、「freddy」に一致します。

例:

where salary >= 600
where dept != 'Eng' and date '2005-01-21' < hireDate
where (dept<>'Eng' and isSenior=true) or (dept='Sales') or seniorityStartTime is null

サンプル テーブルに対して次のクエリを実行します。

select name where salary > 700

次のレスポンスを返します。

グループ条件

group by 句は、行全体で値を集計するために使用されます。 group-by 句内の値の個別の組み合わせごとに、1 つの行が作成されます。order by 句で特に指定されていない限り、データはグループ列によって自動的に並べ替えられます。

注: group by 句を使用する場合は、select 句にリストされているすべての列が group by 句にリストされているか、または集計関数でラップされている必要があります。

例:

select dept, max(salary) group by dept

サンプル テーブルに対して次のクエリを実行します。

select lunchTime, avg(salary), count(age) group by isSenior,lunchTime

次のレスポンスを返します。

ピボット

pivot 句は、列内の個別の値を新しい列に変換するために使用されます。たとえば、「year」列でピボットすると、元のテーブルに表示される一意の年ごとに列を持つテーブルが生成されます。これは、たとえば折れ線グラフで可視化する際に、各列を別々の線として描画する場合に便利です。年ごとに別々の線を引く場合や、「year」が元のテーブルの列の 1 つである場合、必要なデータ変換にはピボット オペレーションを使用すると便利です。

注: pivot 句を使用する場合は、select 句にリストされているすべての列が group by 句にリストされているか、または集計関数でラップされている必要があります。

複数の列にピボット列の値が同じである場合があるため、ピボットは集計を意味します。group by を使用せずに pivot を使用すると、結果テーブルには行が 1 つだけ含まれます。たとえば、サンプル テーブルで次のクエリを実行します。

select sum(salary) pivot dept

次のレスポンスを返します。

これは、2100 は Eng 部門の給与を合計し、800 はマーケティング部門の給与を合計したものです。

pivot を group by と一緒に使用すると、さらに便利な場合があります。これは、各セルに関連する行と関連する列の集計結果が含まれるテーブルを作成するためです。たとえば、サンプル テーブルで次のクエリを実行します。

select dept, sum(salary)
  group by dept
  pivot lunchTime

次のレスポンスを返します。

このテーブルを「反転」するには、列と行を切り替えて、pivot 列と group by 列を切り替えます。サンプル テーブルに対して次のクエリを実行します。

select lunchTime, sum(salary)
  group by lunchTime
  pivot dept

次のレスポンスを返します。

pivot 句で複数の列を使用することもできます。この場合、レスポンス テーブルの列は、元のテーブルに存在する列の値の一意の組み合わせをすべて構成します。たとえば、サンプル テーブルで次のクエリを実行します。

select sum(salary)
  pivot dept, lunchTime

次のレスポンスを返します。

元のテーブルに表示されている組み合わせだけが、レスポンス テーブル内の列に表示されます。そのため、マーケティング、12:00:00、または販売: 13:00:00 の列はありません。

複数の集計を使用することもできます。たとえば、サンプル テーブルで次のクエリを実行します。

select sum(salary), max(lunchTime)
  pivot dept

次のレスポンスを返します。

select 句では複数の集計を、group by 句では複数の列、pivot 句では複数の列を組み合わせることができます。内部的には、集計はグループ内の列を連結し、ピボット句で列を連結して行われます。

pivot 句で指定された列は、select、group by、order by 句で指定することはできません。pivot を使用する場合、order by 句に集計列を含めることはできません。これは、select 句で指定された集計ごとに、結果テーブルに多くの列が生成されるためです。ただし、pivot が使用されている場合は集計列を書式設定できます。このような形式の結果、ピボット オペレーションによって生成された、特定の集計に関連する新しい列はすべて、指定されたパターンでフォーマットされます。上記の例では、format sum(salary) "some_format_string" を追加すると、Eng の salary 列、Marketing salal-salary、Sales salal-salary 列に影響します。

集計列にラベルを付けることができます。label 句でラベルが指定されていない場合、ピボットの結果として生成される列のラベルは、ピボット列の値のリスト、集計タイプ（min、max、sum、...）と、集計された列のラベルで構成されます。例: 「Eng,12:00:00 sum Salary」select 句で 1 つの集計のみを指定した場合は、集計部分がラベルから削除され、ピボット列の値のリストのみが保持されます。例: 「Eng,12:00:00」label 句で集計列にラベルを指定すると、select 句に集計が 1 つしかない場合と、複数の集計がある場合の両方で、リクエストされたラベルが値のリストに追加されます。たとえば、label sum(salary) "sumsal" の場合、列ラベルは「Eng, 12:00:00 sumsal」、「Eng, 13:00:00 sumsal」などになります。

並べ替え条件

order by 句は、指定された列の値によって行を並べ替えるために使用されます。

order by 句内の項目は、列識別子、または集計関数、スカラー関数、演算子の出力です。

例:

order by dept, salary desc

select dept, max(salary) group by dept order by max(salary)

上限

limit 句は、返される行数を制限するために使用されます。

例:

limit 100

オフセット

offset 句は、指定した数の先頭行をスキップするために使用します。 limit 句を使用する場合、offset が最初に適用されます。たとえば、limit 15 offset 30 は 31 行目から 45 行目までを返します。

例:

offset 10
limit 30 offset 210

ラベル

label 句は、1 つ以上の列にラベルを設定するために使用されます。 クエリ内の ID の代わりにラベル値を使用することはできません。

label 句内の項目は、列識別子、または集計関数、スカラー関数、演算子の出力です。

構文:

label column_id label_string [,column_id label_string]

column_id

ラベルが割り当てられる列の識別子。

label_string

その列に割り当てるラベル。多くのビジュアル表示では、エンドユーザーに表示されるテキストとして列ラベルが使用されます（円グラフの凡例ラベルなど）。ラベルは文字列リテラルであり、その構文ルールに従います。

例:

次の例では、dept 列のラベルを「Department」に、名前列のラベルを「Employee Name」に、ロケーション列のラベルを「Employee Location」に設定しています。

label dept 'Department', name "Employee Name", location 'Employee Location'

形式

format 句は、1 つ以上の列のセルに書式設定された値を指定するために使用されます。返されるデータには、実際の値と、セルごとに書式設定された値の両方を書式設定された列に含める必要があります。多くのビジュアリゼーションでは、書式設定に値が使用されますが、書式設定された値も表示されます。この句で指定するパターンは通常、対応する列の pattern プロパティで返されます。

パターン構文:

number、date、timeofday、datetime

ICU で定義された日付パターンと番号パターン。

boolean

パターンは、「value-if-true:value-if-false」形式の string です。

例:

format salary '#,##0.00', hireDate 'dd-MMM-yyyy', isSenior 'Yes!:Not yet'

オプション

options 句は、クエリ実行の追加オプションを制御するために使用されます。options 句に続く可能なキーワードは次のとおりです。

• no_format: 結果から書式付き値を削除し、元の値のみを残します。特定のビジュアリゼーションで書式設定された値を使用していない場合に、レスポンスのサイズを小さくできます。
• no_values: 結果から基盤となる値を削除し、フォーマットされた値のみを残します。特定のビジュアリゼーションで書式設定された値のみを使用して、レスポンスのサイズを削減する場合に使用できます。

データ操作関数

1 つの列のデータを操作または集計したり、列間でデータを比較または組み合わせたりできる演算子と関数にはいくつかあります。例には、sum()（列にすべての値を追加する）、max（列に最大値を表示する）、+（2 つの列の値を同じ行に追加する）などがあります。

関数には、どの句でも指定できます。また、一部の句のサブセットに出現する関数もあります。これについては以下で説明します。

例:

select upper(name), year(startDate)

次のデータ操作関数は、Google Visualization API のクエリ言語によって定義されています。

• 集計関数
• スカラー関数
• 算術演算子

集計関数

集計関数には単一の列識別子が渡され、各グループのすべての値に対して処理が実行されます（これらの句を使用しない場合は、group by または pivot 句で指定するか、すべての行を指定します）。

例:

select max(salary)               // Returns a table with one column and one row.
select max(salary) group by dept // Returns a table with the max salary for each dept column value.
select max(salary) pivot dept    // Returns a one-row table with a column for each dept,
                                 //   and the max salary for that department.

集計関数は、select、order by、label、format 句で使用できます。where、group by、pivot、limit、offset、options 句では使用できません。

サポートされている集計関数は次のとおりです。

注: 集計関数は、列 ID のみを引数として使用できます。

max(startDate)                      // OK
min(firstScore) + min(secondScore)  // OK
max(year(startDate))                // INVALID. max requires column ID
sum(salary + perks)                 // INVALID. sum requires column ID.

スカラー関数

スカラー関数は、0 個以上のパラメータを介して作用し、別の値を生成します。スカラー関数には、適切な型のパラメータに評価される式を渡すことができます。これらの型は、このドキュメントのリテラル セクションで定義されている型です。この名前は、同様の名前の JavaScript オブジェクトとは若干異なる場合があります。

列名は、スカラー関数でラップすることで変更されます。

スカラー関数は、単一の値に評価されるものをパラメータとして受け取ることができます。

year(max(startDate))
datediff(now(), todate(1234567890000))

スカラー関数は、select、where、group by、pivot、order by、label,、 format のいずれかの句で使用できます。

算術演算子

算術演算子を使用して、1 つの数値（適切な集計関数、演算子、定数の出力）で評価されるものに対して数学演算を実行できます。

例:

select empSalary - empTax
select 2 * (max(empSalary) / max(empTax))

定義されている演算子は次のとおりです。

言語要素

リテラル

リテラルは比較や割り当てに使用される値です。リテラルには、string、数値、ブール値、さまざまな日時型を指定できます。クエリ構文で使用されるリテラルの例を次に示します。

where startDate < date "2008-03-18"  // date "2008-03-18" is a date literal
limit 30                             // 30 is a numeric literal
format salary '#,##0.00', isSenior 'not yet:of course!'  // '#,##0.00' and 'not yet:of course!' are both string literals

各リテラルの形式は次のとおりです。

string

string のリテラルは、単一引用符または二重引用符で囲みます。例: "fourteen" 'hello world' "It's raining"

number

数値リテラルは 10 進数で指定されます。例: 3  3.0  3.14  -71  -7.2  .6

boolean

ブール値のリテラルは、true または false です。

date

キーワード date に続けて、yyyy-MM-dd 形式の string リテラルを使用します。例: date "2008-03-18"

timeofday

キーワード timeofday に続けて、HH:mm:ss[.SSS] 形式の string リテラルを使用します。例: timeofday "12:30:45"。

datetime

日付と時刻。キーワード datetime またはキーワード timestamp に続けて、yyyy-MM-dd HH:mm:ss[.sss] 形式の string リテラルを使用します。例: datetime '2008-03-18 12:30:34.123'

識別子

識別子（または ID）は、列を識別するテキスト string である。

重要: 識別子が

• スペースあり。
• 予約語。
• 英数字またはアンダースコア以外（[a-zA-Z0-9_]）
• 先頭が数字

一重引用符ではなく、二重引用符で囲む必要があります。

それ以外の場合は、引用符で囲む必要はありません。（構文によって定義されたすべてのキーワードが予約済みの単語ではないため、たとえば、「max」をバッククォートで囲まなくても識別子として使用できます）。

例: col1   employee_table   `start date`   `7 days traffic`   `select`

バッククォートを必要とする識別子を選択することはおすすめしません。バッククォートを使用することを忘れがちな場合や、バッククォートではなく誤って「単一クォート」を使用する可能性があるからです。このようなミスはよくある間違いで、多くの場合、デバッグは困難です。

大文字と小文字の区別

識別子と文字列リテラルは、大文字と小文字が区別されます。他の言語要素はすべて、大文字と小文字が区別されません。

予約語

次の予約済みの単語を識別子として使用する場合は、バッククォートで囲む必要があります。

and
asc
by
date
datetime
desc
false
format
group
label
limit
not
offset
options
or
order
pivot
select
timeofday
timestamp
true
where