Google 視覺化 API Query Query Language 可讓您針對資料來源執行各種資料操控。
目錄
簡介
一般而言,視覺化資料通常會呈現特定形式的資料。例如,圓餅圖呈現的資料可能為兩個資料欄:文字標籤和數值。資料來源中的資料可能不完全符合這個結構。 例如,資料來源可能有兩個以上的資料欄,或者資料欄的順序與圓餅圖預期的順序不符。
查詢語言可讓您傳送資料操控和格式要求至資料來源,並確保傳回的資料結構和內容與預期的結構相符。
查詢語言的語法與 SQL 類似。熟悉 SQL 的開發人員應該能夠快速學習和使用這個查詢語言。網路上有許多 SQL 教學課程。此查詢語言與 SQL 之間有些差異,請參閱 語法一節。
請注意,即使實作查詢語言或執行該語言的所有功能都不需要資料來源。除非您有充分理由,否則不應該仰賴資料來源實作此語言的所有功能。
使用查詢語言
您可以透過下列兩種方式將查詢字串附加至資料來源要求:在 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
請使用下列工具對查詢字串進行編碼或解碼:
注意:如要存取私人試算表資料,您必須使用 OAuth 傳送明確的授權憑證。詳情請參閱 Google 試算表:授權一節。
語言語法
簡介
Google 視覺化 API Query Language 語法與 SQL 語法類似。但是,它是 SQL 的子集,其中需要您學習的某些功能。如果您熟悉 SQL,則應該很難熟悉的。
資料表
本文件使用「資料表」一詞來表示查詢的結果集。資料表是由資料列和資料欄所組成。資料表中的每個資料欄都具有下列屬性:
- ID (或資料欄 ID)。用於參照查詢中的資料欄。請注意,您一律不能在查詢中只依 ID 參照資料欄。提示:請勿使用包含空格的任何 ID;空格難以管理,而且可能會導致程式碼出現小小但難以找出的錯誤。此外,包含空格的 ID 必須以引號括住。
- 標籤:通常向使用者顯示的
string
。例如圓餅圖中的圖例,或是表格中的欄標題。 - 資料類型。支援的資料類型包括
string
、number
、boolean
、date
、datetime
和timeofday
。資料欄的所有值都會有與資料欄類型相符的資料類型,或是null
值。這些類型與 JavaScript 類型類似,但不完全相同。詳情請參閱本頁的程式庫一節。 - 格式設定模式。資料來源可定義部分或全部資料欄的格式模式。您可以加入格式子句來覆寫這個模式。
在本節中,所有查詢範例均列於下表。欄標題為資料欄 ID。
名稱string |
日期:string |
午餐時間timeofday |
薪資 number |
聘僱日期:date |
年齡number |
高級boolean |
SenseiorityStartTimedatetime |
---|---|---|---|---|---|---|---|
John | 軟顎鼻音 | 12:00:00 | 1000 | 2005-03-19 | 35 | true | 2007-12-02 15:56:00 |
Dave | 軟顎鼻音 | 12:00:00 | 500 | 2006-04-19 | 27 | 否 | 空值 |
莎莎 | 軟顎鼻音 | 13:00:00 | 600 | 2005-10-10 | 30 | 否 | 空值 |
Ben | 銷售 | 12:00:00 | 400 | 2002-10-10 | 32 | true | 2005-03-09 12:30:00 |
達納 | 銷售 | 12:00:00 | 350 | 2004-09-08 | 25 | 否 | 空值 |
Mike | 行銷 | 13:00:00 | 800 | 2005-01-10 | 24 | true | 2007-12-30 14:40:00 |
語言子句
查詢語言的語法是由下列子句組成。每個子句的開頭都是一到兩個關鍵字。所有子句都是選用項目。子句以空格分隔。子句的順序如下:
子句 | 用量 |
---|---|
select |
選取要傳回的資料欄,以及資料欄的排序順序。如果省略,資料表的所有資料欄都會依預設順序傳回。 |
where |
只傳回符合條件的資料列。省略時,系統會傳回所有資料列。 |
group by |
匯總多個資料列的值。 |
pivot |
將資料欄中的不同值轉換為新資料欄。 |
order by |
依資料欄的值排序資料列。 |
limit |
限制傳回的資料列數。 |
offset |
略過指定第一列的列數。 |
label |
設定欄標籤。 |
format |
使用特定格式設定模式,為特定資料欄的值設定格式。 |
options |
設定其他選項。 |
from |
from 子句已從語言中移除。 |
選取
select
子句用於指定要傳回的資料欄及其順序。如未指定這個子句,或已使用 select *
,則所有資料來源資料表的資料欄都會以原始順序傳回。資料欄會由 ID (而非標籤) 參照。例如,在 Google 試算表中,欄 ID 是由一或兩個半形字元欄字母 (A、B、C、...),
select
子句中的項目可以是資料欄 ID,或是匯總函式、純量函式或運算子的輸出內容。
例如:
select *
select dept, salary
select max(salary)
在以下範例中,倒引號是用來參照包含空格 (電子郵件地址) 或保留字詞 (日期) 的欄 ID:
select `email address`, name, `date`
在範例資料表中執行下列查詢:
select lunchTime, name
傳回下列回應:
午餐時間 | 名稱 |
---|---|
12:00:00 | John |
12:00:00 | Dave |
13:00:00 | 莎莎 |
12:00:00 | Ben |
12:00:00 | 達納 |
13:00:00 | Mike |
位置
where
子句只會傳回符合特定條件的資料列。
簡易的比較運算子為 <=, <, >, >=, =,
!=, <>
。兩個比較運算子 != <>
都不等於。系統會依詞法值比較字串。請注意,等式以 =
表示等式,而非大部分電腦語言中的 ==
。
null
是使用 is null
或 is not null
來完成。
您可以使用邏輯運算子 and
、or
和 not
加入多個條件。括號可用來定義明確的優先順序。
「地點」子句也支援更複雜的字串比較運算子。這些運算子會使用兩個字串做為引數;任何非字串引數 (例如日期或數字) 會先轉換為字串,然後再比較。字串比對須區分大小寫 (您可以使用 upper()
或 lower()
純量函式來解決這個問題)。
contains
- 子字串比對。wholecontains
part 為 part 的任何位置時,都是 whole。範例:where name contains 'John'
可比對「John」、「John Adams」、「Long John Silver」但不符合「John adams」。starts with
- 前置字串比對。值starts with
前置字串 表示在 value 的開頭。範例:where dept starts with 'engineering'
會比對「工程」和「工程經理」。where dept starts with 'e'
會比對「工程」、「工程」和「e」。ends with
- 字尾比對相符。如果 suffix 位於 value 的結尾,則 value 為 true。範例:where role ends with 'y'
會比對「cowboy」、「boy」和「y」。matches
- (規則運算式) 規則運算式比對。haystackmatches
needle 在 needle 中的規則運算式符合 haystack 時,將為 true。範例:where country matches '.*ia'
符合印度和奈及利亞,但不符合印第安納州。請注意,這不是全球搜尋,因此「where country matches 'an'
」不會比對「加拿大」。like
- 支援兩個萬用字元的文字搜尋:使用 % 可比對任何零個或多個任何字元,以及 _ (底線),符合任何字元。這與 SQL LIKE 運算子類似。範例:where name like fre%
會比對「fre」、「fed」和「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
傳回下列回應:
名稱 |
---|
John |
Mike |
分組依據
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
傳回下列回應:
午餐時間 | 平均薪資 | 年齡 |
---|---|---|
12:00:00 | 425 | 2 |
13:00:00 | 600 | 1 |
12:00:00 | 700 | 2 |
13:00:00 | 800 | 1 |
資料透視
pivot
子句用於將資料欄中的不同值轉換為新的資料欄。例如,依照「year」資料欄建立資料透視表,就會產生一份資料表,其中包含原始資料表中顯示的每個不同年份。舉例來說,如果折線圖呈現個別資料欄,並以視覺化方式呈現各個資料欄,這項功能就能派上用場。如果您想要每年個別繪製一行,且「year」是原始資料表的一個資料欄,則使用資料透視作業來進行必要的資料轉換,是一個不錯的選擇。
注意:如果您使用 pivot
子句,則 select
子句中列出的每個資料欄都必須列在 group by
子句中,或由匯總函式包裝
由於資料透視表中的多個列可能包含相同的資料值,因此資料透視表的匯總作業。請注意,在不使用 group by
的情況下使用 pivot
時,結果資料表只會包含一列。例如,在範例資料表中執行下列查詢:
select sum(salary) pivot dept
傳回下列回應:
英文總薪酬 | 行銷總薪酬 | 銷售總額 |
---|---|---|
2100 | 800 | 750 |
這是因為 2100 是工程部門的薪酬總和,行銷部門的薪酬則是 800 等。
將 pivot
與 group by
搭配使用使用則更加實用,因為建立資料表時,每個儲存格都會包含相關資料列與相關資料欄的匯總結果。例如,在範例資料表中執行下列查詢:
select dept, sum(salary) group by dept pivot lunchTime
傳回下列回應:
時間 | 12:00:00 薪酬 | 13:00:00 薪酬 |
---|---|---|
軟顎鼻音 | 1500 | 600 |
行銷 | 空值 | 800 |
銷售 | 750 | 空值 |
您也可以在 pivot
欄和 group by
資料欄之間切換,藉此「反轉」此表格,藉此切換資料欄與資料列。在範例資料表中執行下列查詢:
select lunchTime, sum(salary) group by lunchTime pivot dept
傳回下列回應:
午餐時間 | 英文總薪酬 | 行銷總薪酬 | 銷售總額 |
---|---|---|---|
12:00:00 | 1500 | 空值 | 750 |
13:00:00 | 600 | 800 | 空值 |
您也可以在 pivot
子句中使用多個資料欄。在這類情況下,回應資料表的資料欄是由原始資料表中不同資料欄的值的唯一組合組成。例如,針對範例資料表執行下列查詢:
select sum(salary) pivot dept, lunchTime
傳回下列回應:
工程,12:00:00 薪酬 | Eng,13:00:00 和薪 | 行銷,13:00:00 薪酬 | 銷售,12:00:00 總薪酬 |
---|---|---|---|
1500 | 600 | 800 | 750 |
請注意,只有原始資料表中顯示的組合會顯示在回應資料表中。這就是為什麼沒有行銷、12:00:00 或銷售 13:00:00 的欄
此外,您也可以使用多個匯總。例如,對範例資料表執行以下查詢:
select sum(salary), max(lunchTime) pivot dept
傳回下列回應:
英文總薪酬 | 行銷總薪酬 | 銷售總額 | 英文 max-lunchTime | 行銷 max-lunchTime | 銷售 max-lunchTime |
---|---|---|---|---|---|
2100 | 800 | 750 | 13:00:00 | 13:00:00 | 12:00:00 |
您可以在 select
子句中結合多個匯總,在 group by
子句中包含多個資料欄,並在 pivot
子句中包含多個資料欄。內部作業的執行方式是由群組內的資料欄串連,以及資料透視子句完成。
pivot
子句中指定的欄可能不會顯示在 select
、group by
或 order by
子句中。使用 pivot
時,order by
子句不得包含任何匯總資料欄。這是因為對 select
子句中指定的每個匯總來說,系統會在結果資料表中產生許多資料欄。不過,您可以在使用 pivot
時設定匯總資料欄的格式。這種格式的結果,是由資料透視作業產生的特定匯總所有新資料欄,依指定模式格式化。在上述範例中,新增 format sum(salary) "some_format_string"
會影響下列資料欄:英文 (總計) 和行銷總薪和銷售總收入。
您可以對匯總資料欄加上標籤。如果 label
子句中未指定標籤,則由資料透視結果產生的資料欄標籤是由資料透視資料欄的值清單、匯總類型 (最小值、最大值、總和...) 和匯總資料欄的標籤組成。例如:「工程,12:00:00 總薪酬」。如果在 select
子句中只指定一個匯總,則系統會從標籤中移除匯總部分,只保留資料透視表中的值清單。例如「Eng,12:00:00」。當 label
子句為匯總資料欄指定標籤時,系統會在 select
子句中只有一個匯總,以及有多個組合時,將要求的標籤附加至值清單。舉例來說,label sum(salary) "sumsal"
會產生「Eng, 12:00:00 總和」、「Eng, 13:00:00 總和」等資料欄標籤。
排序依據
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 資料欄的標籤設為「部門」,名稱欄的標籤設為「Employee Name」,並將地點資料欄的標籤設為「Employee Location」:
label dept 'Department', name "Employee Name", location 'Employee Location'
格式
format
子句用於在一或多個資料欄中指定儲存格的格式值。傳回的資料應包含格式中每個儲存格的實際值和格式化值。許多視覺化資料會使用未格式化的值來計算,但使用格式化值顯示。您在此子句中指定的模式通常是在對應資料欄的 pattern 屬性中傳回。
模式語法:
number
、date
、timeofday
、datetime
- ICU 定義的 date 和 number 模式。
-
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
會從結果中移除基礎值,只保留格式化值。只有在特定的視覺化內容僅使用格式化值來縮減回應時,才能使用這個屬性。
資料操縱函式
您可以利用許多類型的運算子和函式來操控或匯總單一資料欄中的資料,或比較或合併多個資料欄的資料。例如 Google 將 Sum() (加入資料欄中的所有值)、最大值 (用於尋找資料欄中最大值) 和 + (將兩個資料欄的值在同一列相加)。
某些函式可以顯示在任何子句中;有些函式可以出現在部分子句中。詳情請見下文。
範例:
已指定此表格... | 如果我們套用這個查詢... | 我們得到這個結果。 | ||||||||||||||||||||||||
|
select upper(name), year(startDate) |
|
以下資料操控函式由 Google 視覺化 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
子句中。
支援的匯總函式如下:
名稱 | 說明 | 支援的資料欄類型 | 傳回類型 |
---|---|---|---|
avg() |
傳回群組內所有欄的平均值。 | number |
number |
count() |
傳回群組指定資料欄中元素的計數。系統不會計算空值儲存格。 | 不限類型 | number |
max() |
傳回群組資料欄中的最大值。系統會將這個日期與較早的日期比較,依字母順序比較 string ,並以區分大小寫。 |
不限類型 | 與資料欄相同的類型 |
min() |
傳回群組資料欄中的最小值。系統會比較較早的日期和較早的日期,並依據字母順序比較 string ,以及區分大小寫 |
不限類型 | 與資料欄相同的類型 |
sum() |
傳回群組欄中所有值的總和。 | number |
number |
注意:匯總函式只能採用資料欄 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.
純量函式
純量函式會對零個或多個參數運作,以產生其他值。任何經過評估的運算式都可以傳遞至適當類型的參數。請注意,這些類型是本文件的程式庫部分中定義的類型,這可能與名稱相似的 JavaScript 物件略有不同。
請注意,資料欄名稱將會以純量函式包裝。
純量函式可視為任何會評估為單一值的參數:
year(max(startDate)) datediff(now(), todate(1234567890000))
您可以在下列任何子句中使用純量函式:select
、where
、group by
、pivot
、order by
、label,
和
format
。
名稱 | |
---|---|
year() |
從日期或日期時間值傳回年份值。例如: 參數:
date 或 datetime 類型的單一參數傳回類型:
number |
month() |
從日期或日期時間傳回零的月份值。例如: 參數:
date 或 datetime 類型的單一參數傳回類型:
number |
day() |
傳回 參數:
date 或 datetime 類型的單一參數傳回類型:
number |
hour() |
傳回日期時間或 參數:
datetime 或 timeofday 類型的單一參數傳回類型:
number |
minute() |
傳回 參數:
datetime 或 timeofday 類型的單一參數傳回類型:
number |
second() |
從 參數:
datetime 或 timeofday 類型的單一參數傳回類型:
number |
millisecond() |
傳回 參數:
datetime 或 timeofday 類型的單一參數傳回類型:
number |
quarter() |
傳回 參數:
date 或 datetime 類型的單一參數傳回類型:
number |
dayOfWeek() |
傳回 參數:
date 或 datetime 類型的單一參數傳回類型:
number |
now() |
傳回 GMT 時區的目前日期,代表 參數:無
傳回類型:
datetime |
dateDiff() |
傳回兩個 參數:
date 或 datetime 類型的兩個參數 (可以是參數之一)傳回類型:
number |
toDate() |
將指定值轉換成
參數:
date 、datetime 或 number 類型的單一參數傳回類型:
date |
upper() |
以大寫英文字母傳回指定的 參數:
string 類型的一個參數傳回類型:
string |
lower() |
以小寫英文字母傳回指定的 參數:
string 類型的一個參數傳回類型:
string |
算術運算子
您可以使用算術運算子對任何評估為單一數字 (即適當匯總函式、運算子或常數的輸出) 的運算執行數學運算。
例如:
select empSalary - empTax select 2 * (max(empSalary) / max(empTax))
下列運算子的定義:
名稱 | 說明 | 參數 | 傳回類型 |
---|---|---|---|
+ |
傳回兩個 number 值的總和。 |
2 個number |
number |
- |
傳回兩個 number 值之間的差異。 |
2 個number |
number |
* |
傳回兩個 number 的產品。 |
2 個number |
number |
/ |
傳回兩個 number 的商。除以 0 會傳回空值。 |
2 個number |
number |
語言元素
文學
常值是用於比較或指派的值。常值可以是 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
,
重要事項:如果您的識別碼
- 包含空格
- 是保留字詞。
- 含有任何英數字元或底線 ([a-zA-Z0-9_]),或
- 開頭是數字
「必須」以單引號 (而非單引號) 括住。
如果不是,系統不需要加上識別碼。(請注意,並非所有由語法定義的關鍵字都是保留的字詞,因此您可以使用「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