Google Visualization API 查詢語言可讓您透過查詢對資料來源執行各種資料操作。

目錄

簡介

一般而言，圖表預期資料會以特定形式呈現。舉例來說，圓餅圖可能會要求提供兩個資料欄：文字標籤和數值。資料來源中的資料可能不會與這個結構完全一致。 舉例來說，資料來源可能有兩個以上的資料欄，或者資料欄的順序可能會與圓餅圖預期的順序不符。

查詢語言可以將資料操縱及格式化要求傳送至資料來源，並確保傳回的資料結構和內容符合預期結構。

查詢語言的語法與 SQL 類似。熟悉 SQL 的開發人員應能快速學習並使用這個查詢語言。網路上有許多 SQL 教學課程可供參考。這個查詢語言和 SQL 有些許差異，詳情請參閱syntax一節。

請注意，無須資料來源即可實作查詢語言；如有提供，也可以實作該語言的所有功能。除非您有特別相信，否則不應依賴資料來源實作該語言的所有功能。

使用查詢語言

您可以透過下列兩種方式將查詢字串附加至資料來源要求：在 JavaScript 程式碼中設定查詢字串，或將查詢字串設為資料來源網址的參數。如果您的要求未包含查詢字串，則資料來源的預設行為是使用預設的資料列/資料欄順序和格式傳回所有資料列和資料欄。如要變更這項設定，請在對資料來源的要求中加入查詢字串。

透過 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);

在資料來源網址中設定查詢

您可使用 tq 參數在資料來源網址中加入查詢字串。在網址參數中設定查詢 (而非在 JavaScript 中) 可讓您輕鬆使用由其他開發人員編寫的圖表，並且還能自訂查詢。

查詢字串必須正確編碼為網址參數。 您可以使用 JavaScript encodeURIComponent 函式編碼網址，也可以使用本節結尾的編碼工具手動編碼。

範例：

假設 Google 試算表使用下列查詢字串，(請注意，試算表中的欄 ID 一律為英文字母；在發布的試算表中，欄標題文字僅是標籤，而非 ID。請務必在查詢字串中使用 ID，而非標籤)。

select A, sum(B) group by A

編碼後，這個查詢會變為：

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

假設您的試算表網址如下：

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

將 /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，就不應該很難理解。

所有範例中使用的表格：

在本節中，所有查詢範例皆參照下表。欄標題則是資料欄的 ID。

語言子句

查詢語言的語法是由下列子句組成。每個子句以一或兩個關鍵字開頭。所有子句皆為選用。子句之間以空格分隔。子句的順序必須如下所示：

選取

select 子句是用來指定要傳回的資料欄和資料欄的順序。如未指定這個子句，或是使用的是 select *，系統會按原始順序傳回資料來源資料表中的所有資料欄。資料欄可由 ID (而非標籤) 參照。例如，在 Google 試算表中，資料欄 ID 是一或兩個字元的資料欄字母 (A、B、C、...)。

select 子句中的項目可以是資料欄 ID，或是匯總函式、純量函式或運算子的輸出內容。

例如：

select *

select dept, salary

select max(salary)

在下例中，引號是用來參照包含空格 (電子郵件地址) 或保留字詞 (date) 的資料欄 ID：

select `email address`, name, `date`

在範例資料表上執行以下查詢：

select lunchTime, name

傳回以下回應：

哪裡

where 子句是用來只傳回符合指定條件的資料列。

簡單的比較運算子是 <=, <, >, >=, =, !=, <>。兩個比較運算子 != <> 都代表不等於。系統會按字典式值比較字串。請注意，與大多數電腦語言中一樣，相等是由 = 表示，而非 ==。使用 is null 或 is not null 即可與 null 進行比較。

您可以使用邏輯運算子 and、or 和 not 彙整多個條件。括號可用來定義明確優先順序。

where 子句也支援一些較複雜的字串比較運算子。這些運算子使用兩個字串做為引數；任何非字串引數 (例如日期或數字) 都會在比較之前轉換為字串。字串比對會區分大小寫 (您可以使用 upper() 或 lower() 純量函式解決這個問題)。

• contains - 子字串比對。如果 part 位於完整內的任何位置，則「完整」contains 部分的值為 true。範例： where name contains 'John' 比對「John」、「John Adams」、「Long John Silver」，而非「john adams」。
• starts with - 前置字串比對。如果前置字串是值的開頭，則 value starts with prefix 為 true。範例： where dept starts with 'engineering' 與「engineering」和「 Engineeringing manager」相符。where dept starts with 'e' 與「 Engineeringing」、「eng」和「e」達成比對。
• ends with - 後置字串比對。如果後置字串在值的結尾，則 value ends with 後置字串為 true。範例： where role ends with 'y' 會比對「cowboy」、「boy」和「y」。
• matches - A (preg) 規則運算式相符。如果 needle 中的規則運算式符合 haystack，則 haystack matches needle 為 true。 範例： where country matches '.*ia' 會比對印度和奈及利亞，但不包括印第安納州。請注意，這不是全域搜尋，因此 where country matches 'an' 不會與「Canada」。
• like - 支援兩個萬用字元的文字搜尋：% (用於比對任何種類的零或多個字元) 和 _ (底線)，與任一字元相符。這與 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 子句是用來匯總不同資料列的值。 系統會針對分組依據子句中每個不重複值組合建立單一資料列。除非已透過 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」是原始資料表的一個資料欄，建議您使用資料透視作業執行必要的資料轉換作業。

注意：如果您使用 pivot 子句，則 select 子句中列出的「每欄」必須列在 group by 子句中，或要由匯總函式納入。

由於多個資料列可能包含相同的資料透視欄值，因此資料透視表示匯總。請注意，如果在不使用 group by 的情況下使用 pivot，結果資料表只會包含一個資料列。舉例來說，在範例資料表中執行下列查詢：

select sum(salary) pivot dept

傳回以下回應：

這是因為 2100 是工程部門的薪酬總和，行銷部門的薪酬總和是 2100，以此類推。

將 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) 或「Sales,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 total-alary」、「行銷總薪酬」和「銷售總薪」。

您可以替匯總資料欄加上標籤。如未在 label 子句中指定標籤，則資料透視後產生的資料欄標籤是由資料透視資料欄的值清單、匯總類型 (最小值、最大值、總和...) 和匯總資料欄的標籤組成。例如「Eng,12:00:00 total Salary」。如果僅在 select 子句中指定一項匯總作業，系統會將匯總部分從標籤中移除，且只會保留資料透視表資料欄中的值清單。例如「Eng,12:00:00」。當 label 子句指定匯總資料欄的標籤時，如果 select 子句中只有一項匯總，以及同時有多個匯總，則要求的標籤會附加至值清單。舉例來說，label sum(salary) "sumsal" 可讓資料欄標籤「Eng,12:00:00 totalsal」、「Eng,13:00:00 totalsal」等。

排序依據

order by 子句是用來依照指定資料欄中的值排序資料列。

order by 子句中的項目可以是資料欄 ID，或是匯總函式、純量函式或運算子的輸出內容。

例如：

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 子句是用來設定一或多個資料欄的標籤。請注意，您無法在查詢中使用標籤值來取代 ID。

label 子句中的項目可以是資料欄 ID，或是匯總函式、純量函式或運算子的輸出內容。

語法：

label column_id label_string [,column_id label_string]

column_id

要指派標籤的資料欄 ID。

label_string

要指派給該欄的標籤。許多圖表都會使用欄標籤向使用者顯示文字，例如在圓餅圖中的圖例標籤。標籤是「字串常值」，並且遵循這些語法規則。

範例：

以下範例會將 dept 欄的標籤設為「Department」(部門)，並將「name」(名稱) 欄的標籤設為「Employee Name」(員工名稱)，並將位置欄的標籤設為「Employee Location」(員工地點)：

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

格式

format 子句用於指定一或多個資料欄中儲存格的格式化值。傳回的資料應包含格式化資料欄中的每個儲存格的實際值與格式化值。許多圖表會使用未格式化的值進行計算，但會使用經過格式化的值來顯示。您在這個子句中指定的模式通常會在對應資料欄的 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 會從結果中移除基礎值，只保留格式化值。只有在特定視覺化呈現僅使用格式化值縮減回應大小時，才能使用此選項。

資料操縱函式

有多種運算子和函式可讓您在單一資料欄中處理或匯總資料，或是比較或合併不同資料欄的資料。範例包括 total() (用於新增資料欄中的所有值)、max (用於尋找資料欄中最大的值) 和 + (將兩個資料欄的值合併在相同的資料列中)。

有些函式可以出現在任何子句中；有些函式可以出現在子句的子集中。詳情請見下方。

範例：

select upper(name), year(startDate)

下列資料操縱函式是由 Google Visualization API 查詢語言定義：

• 匯總函式
• 純量函式
• 算術運算子

匯總函式

匯總函式會傳遞單一資料欄 ID，並針對每個群組中的所有值執行動作 (群組是透過 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.

純量函式

純量函式會對零或多個參數進行運算，以產生其他值。您可以將任何可評估為適當類型的參數的運算式傳遞給純量函式。請注意，這些類型為本文「Literals」一節中定義的類型，這些類型可能與名稱類似的 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

數字常值是以十進位標記法指定。示例： 3  3.0  3.14  -71  -7.2  .6

boolean

布林常值為 true 或 false。

date

使用關鍵字 date，後面加上 string 常值，格式為 yyyy-MM-dd。範例： date "2008-03-18"。

timeofday

使用關鍵字 timeofday，後面加上 string 常值，格式為 HH:mm:ss[.SSS] 範例： timeofday "12:30:45"。

datetime

日期和時間：使用關鍵字 datetime 或關鍵字 timestamp，並在後面加上 string 常值 (格式為 yyyy-MM-dd HH:mm:ss[.sss])。範例： datetime '2008-03-18 12:30:34.123'

ID

ID (或 ID) 是用以識別資料欄的文字 string，

重要事項：如果您的 ID 適用

• 有聊天室
• 是保留字詞，
• 包含英數字元或底線以外的任何內容 ([a-zA-Z0-9_])，或
• 以數字開頭

「必須」前後加上引號 (而非單引號)。

否則，您不需要在 ID 前後加上引號。(請注意，語法定義的部分關鍵字並非「保留」字詞，因此您可以使用「max」做為 ID，不必加上反引號)。

範例： col1   employee_table   `start date`   `7 days traffic`   `select`

建議您不要選擇需要回傳引號的 ID，因為這樣很容易忘記使用反引號，或是不小心用「單引號」取代「倒引號」。這些都是常見錯誤，且往往難以偵錯。

區分大小寫

ID 和字串常值會區分大小寫。所有其他語言元素不區分大小寫。

保留字詞

下列保留字詞如果用做 ID，必須以倒引號括住：

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