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

目次

はじめに

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

クエリ言語を使用すると、データ操作とフォーマットのリクエストをデータソースに送信し、返されたデータ構造とコンテンツが想定される構造と一致するようにできます。

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

クエリ言語を実装するために、または実装する場合、データソース言語のすべての機能を実装するために、データソースは必要ありません。他に信じられない理由がない限り、この言語の全機能の実装をデータソースに頼ることはしないでください。

クエリ言語の使用

クエリ文字列をデータソース リクエストに添付するには、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

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

言語の構文

概要

GoogleVisualization API のクエリ言語の構文は、SQL 構文に類似したように設計されています。ただし、これは SQL のサブセットであり、学習が必要な独自の機能がいくつかあります。SQL の知識があれば、それほど難しくないでしょう。

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

このセクション全体を通して、すべてのクエリの例は次の表を参照します。列ヘッダーは列識別子です。

言語句

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

選択

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

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

例:

select *

select dept, salary

select max(salary)

次の例では、スペース（メールアドレス）を含む列 ID または予約語（日付）を含む列 ID を参照するためにバッククォートが使用されています。

select `email address`, name, `date`

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

select lunchTime, name

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

場所

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

単純な比較演算子は <=, <, >, >=, =, !=, <> です。比較演算子 != <> はどちらも、等しくないことを意味します。文字列は辞書順の値によって比較されます。ほとんどのコンピュータ言語では、等式は == ではなく = で示されます。null との比較を行うには、is null または is not null を使用します。

論理演算子 and、or、not を使用すると、複数の条件を結合できます。括弧を使用して明示的な優先順位を定義できます。

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

• contains - 部分文字列の一致。whole contains part は、part が全体内のいずれかの場合、true。例: where name contains 'John' は「John」、「John Adams」、「Long John Silver」には一致しますが、「john adams」には一致しません。
• starts with - 接頭辞の一致。value starts with prefix は、prefix が value の先頭にある場合に true になります。例: where dept starts with 'engineering' は「engineering」と「engineering manager」に一致します。where dept starts with 'e' は、「engineering」、「eng」、「e」に一致します。
• ends with - 接尾辞の一致。値 ends with 接尾辞が value の最後にある場合、suffix は true です。例: where role ends with 'y' は、「cowboy」、「boy」、「y」に一致します。
• matches -（preg）正規表現の一致。haystack matches 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

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

これは、2,100 が 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

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

元のテーブルに表示される組み合わせにのみ、レスポンス テーブルの列が提供されます。Marketing,12:00:00 または Sales,13:00:00 の列がないのはそのためです。

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

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

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

select 句の複数の集計、group by 句の複数の列、pivot 句の複数の列を組み合わせることができます。内部的には、集計は group by 句と pivot 句で列を連結することによって実行されます。

pivot 句で指定された列は、select、group by、order by 句の中では使用できない場合があります。 pivot を使用する場合、order by 句に集計列を含めることはできません。これは、select 句で指定された集計ごとに、結果テーブルに多くの列が生成されるためです。ただし、pivot を使用すると、集計列をフォーマットできます。このような形式を使用すると、ピボット オペレーションによって生成される、特定の集計に関連するすべての新しい列が、指定されたパターンで整形されます。上記の例では、format sum(salary) "some_format_string" を追加すると、Eng sum-salary、Marketing sum-salary、Sales sum-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」に、name 列のラベルを「Employee Name」に、location 列のラベルを「Employee Location」に設定しています。

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

形式

format 句は、1 つ以上の列のセルに書式設定された値を指定するために使用されます。返されるデータには、書式設定された列の各セルの実際の値と書式設定された値の両方が含まれている必要があります。多くの可視化では、計算には書式設定されていない値が使用されますが、表示には書式設定された値が使用されます。この句で指定するパターンは通常、対応する列の pattern プロパティで返されます。

パターンの構文:

number さん、date さん、timeofday さん、datetime さん

ICU で定義された日付と数値のパターン。

boolean

パターンは、true の場合の値: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 ビジュアリゼーション 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 句には指定できません 。

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

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

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 のいずれかの句で使用できます。

算術演算子

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

例:

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

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

重要: ID が

• スペースがあり
• 予約語である
• 英数字とアンダースコア以外の文字が含まれている（[a-zA-Z0-9_]）
• 数字で始まる

必ずバッククォート（一重引用符ではない）で囲む必要があります。

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

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

バッククォートが必要な ID は選択しないことをおすすめします。バッククォートの使用を忘れたり、誤って「バッククォート」の代わりに「一重引用符」を使用したりするおそれがあるためです。このような誤りはよくある間違いであり、デバッグが困難になることもあります。

大文字と小文字の区別

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

予約済みの単語

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

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