借助 Google 可视化 API 的查询语言,您可以对对数据源的查询执行各种数据操作。
目录
简介
通常情况下,可视化图表会希望获得某种特定形式的数据。例如,饼图可能需要数据作为两列:文本标签和数值。数据源中的数据可能与此结构不完全一致。 例如,数据源可能具有两列以上的列,或者列的顺序可能与饼图中期望的顺序不符。
查询语言支持向数据源发送数据操作和格式请求,并确保返回的数据结构和内容与预期结构一致。
查询语言的语法与 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 查询语言的语法与 SQL 的语法类似。但是,它是 SQL 的子集,具有它自己的几项功能,您需要学习。如果您熟悉 SQL,应该不会太难学习。
数据表
本文档使用术语数据表来指代查询结果集。数据表由行和列组成。数据表中的每一列都有以下属性:
- 标识符(或列 ID)。用于引用查询中的列。请注意,切勿尝试在查询中按标签引用列,而只能尝试按标识符引用列。提示:尽量不要使用任何包含空格的 ID;空格很难管理,且可能会导致您在编码中犯错,但可能会导致错误。此外,包含空格的 ID 必须用反引号括起来。
- Label(标签)。通常向最终用户显示的
string
。例如,饼图中的图例或表格中的列标题。 - 数据类型。支持的数据类型包括
string
、number
、boolean
、date
、datetime
和timeofday
。 列的所有值的数据类型均与列类型匹配,或具有null
值。这些类型与本文中的字面量部分所述(但并不完全相同)的 JavaScript 类型类似。 - 格式设置。数据源可以为其部分或全部列定义格式设置模式。您可以通过添加格式子句来替换此模式。
在本节中,所有查询示例都引用了下表。列标题是列标识符。
名称string |
部门string |
午餐时间timeofday |
薪资 number |
HireDatedate |
年龄:number |
老年人boolean |
资历开始时间datetime |
---|---|---|---|---|---|---|---|
John | 拉丁文小写字母 eng | 12:00:00 | 1000 | 2005-03-19 | 35 | true | 2007-12-02 15:56:00 |
Dave | 拉丁文小写字母 eng | 12:00:00 | 500 | 2006-04-19 | 27 | false | null |
莎莉 | 拉丁文小写字母 eng | 13:00:00 | 600 | 2005-10-10 | 30 | false | null |
Ben | 销售 | 12:00:00 | 400 | 2002-10-10 | 32 | true | 2005-03-09 12:30:00 |
达纳语 | 销售 | 12:00:00 | 350 | 2004-09-08 | 25 | false | null |
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 *
,系统会按原始顺序返回数据源表的所有列。列通过标识符(而不是标签)引用。例如,在 Google 电子表格中,列标识符是一到两个字符的列字母(A、B、C、...)。
select
子句中的项可以是列标识符,也可以是聚合函数、标量函数或运算符的输出。
示例:
select *
select dept, salary
select max(salary)
在以下示例中,反引号用于引用包含空格(电子邮件地址)或预留字词(日期)的列 ID:
select `email address`, name, `date`
对示例表运行以下查询:
select lunchTime, name
返回以下响应:
午餐时间 | 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
联接多个条件。圆括号可用于定义明确的优先级。
where 子句还支持一些更复杂的字符串比较运算符。
这些运算符采用两个字符串作为参数;任何非字符串参数(例如,日期或数字)都将在比较之前转换为字符串。字符串匹配区分大小写(您可以使用 upper()
或 lower()
标量函数来解决此问题)。
contains
- 子字符串匹配。如果部分为整个中的任意位置,则contains
整个部分为contains
。示例:where name contains 'John'
会匹配“John”、“John Adams”、“Long John Silver”而非“john adams”。starts with
- 前缀匹配。如果前缀位于 value 的开头,则starts with
前缀 true 为 true。示例:where dept starts with 'engineering'
与“工程”和“工程经理”匹配。where dept starts with 'e'
与“engineering”(工程)、“eng”和“e”匹配。ends with
- 后缀匹配。如果后缀位于 value 的末尾,则ends with
ends with
后缀为 true。示例:where role ends with 'y'
匹配“cowboy”、“boy”和“y”。matches
- 一个(预设)正则表达式匹配。haystackmatches
如果 needle 中的正则表达式与 haystack 匹配,则 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
返回以下响应:
name |
---|
John |
Mike |
分组依据
group by
子句用于跨行聚合值。系统会为 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
返回以下响应:
午餐时间 | 平均工资 | count-age |
---|---|---|
12:00:00 | 425 | 2 |
13:00:00 | 600 | 1 |
12:00:00 | 700 | 2 |
13:00:00 | 800 | 1 |
数据透视
pivot
子句用于将列中的不同值转换为新列。例如,按“年”列的数据透视将生成一个表,该表针对原始表中出现的每个不同年份显示一列。例如,如果折线图直观呈现功能将每列绘制为单独的线条,这可能会很有用。如果要为每年单独绘制一条线,并且“年”是原始表中的列之一,那么较好的做法是使用数据透视操作进行必要的数据转换。
注意:如果您使用 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 薪资 |
---|---|---|
拉丁文小写字母 eng | 1500 | 600 |
营销 | null | 800 |
销售 | 750 | null |
您还可以通过在 pivot
列和 group by
列之间切换的情况下与此表“反向”切换列和行。对示例表运行以下查询:
select lunchTime, sum(salary) group by lunchTime pivot dept
返回以下响应:
午餐时间 | 工程工资 | 营销工资 | 销售工资(工资) |
---|---|---|---|
12:00:00 | 1500 | null | 750 |
13:00:00 | 600 | 800 | null |
您还可以在 pivot
子句中使用多个列。在这种情况下,响应表的列由原始表中存在的所有值的唯一组合组成。例如,对示例表运行以下查询:
select sum(salary) pivot dept, lunchTime
返回以下响应:
工程师 12:00:00 薪资 | 工程师 13:00:00 薪资 | 营销,13:00:00 薪资 | Sales,12:00:00 总工资 |
---|---|---|---|
1500 | 600 | 800 | 750 |
请注意,在响应表中,系统只会为原始表格中显示的组合提供列。因此,“12:00:00”或“13:00:00”列中没有对应的列。
也可以使用多种汇总方法。例如,对示例表运行以下查询:
select sum(salary), max(lunchTime) pivot dept
返回以下响应:
工程工资 | 营销工资 | 销售工资(工资) | 互动型午餐时间上限 | 营销 - 最长午餐时间 | 最长销售时间(销售额) |
---|---|---|---|---|---|
2100 | 800 | 750 | 13:00:00 | 13:00:00 | 12:00:00 |
您可以将 select
子句中的多个聚合、group by
子句中的多个列以及 pivot
子句中的多个列组合在一起。在内部,通过将组中的列和 pivot 子句进行串联来执行聚合。
pivot
子句中指定的列可能不会显示在 select
、group by
或 order by
子句中。使用 pivot
时,order by
子句不能包含任何聚合列。这是因为,对于 select
子句中指定的每次汇总,结果表中都会生成许多列。不过,您可以在使用 pivot
时设置聚合列的格式。这种格式的结果是,数据透视操作生成的与特定汇总相关的所有新列均采用指定的模式设置。在上面的示例中,添加 format sum(salary) "some_format_string"
将影响以下列:Eng sum-salary, Marketing sum-salary and
Sales sum-salary。
您可以为汇总列添加标签。如果 label
子句中未指定标签,则透视后生成的列的标签将由数据透视表列中的值列表、汇总类型(最小值、最大值、总和...)和汇总列的标签组成。例如“Eng,12:00:00 sum Salary”。如果在 select
子句中仅指定了一种聚合,则聚合部分会从标签中移除,并且只保留数据透视列中值的列表。例如“Eng,12:00:00”。当 label
子句为聚合列指定标签时,当 select
子句中只有一个聚合时以及当存在多个汇总时,请求的标签会附加到值列表中。例如,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
子句用于为一列或多列设置标签。请注意,在查询中,您不能使用标签值来代替 ID。
label
子句中的项可以是列标识符,也可以是聚合函数、标量函数或运算符的输出。
语法:
label column_id label_string [,column_id label_string]
column_id
- :获得标签的列的标识符。
label_string
- 要分配给该列的标签。许多可视化图表会将列标签作为文本显示给最终用户,例如饼图中的图例标签。标签是字符串字面量,并遵循这些语法规则。
示例:
以下示例将 dept 列的标签设置为“部门”,将名称列的标签设置为“员工姓名”,将位置列的标签设置为“员工位置”:
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
:从结果中移除底层值,仅保留格式化值。可在特定可视化图表仅使用格式化值来缩减响应的大小时使用。
数据操纵函数
您可以使用多种运算符和函数在单个列中操作或汇总数据,或者比较或合并列中的数据。示例包括 sum()(用于添加列中的所有值)、max(以查找列中最大的值)和 +(将两列的值添加到同一行中)。
有些函数可以出现在任何子句中;有些可以出现在子句的子集中。具体如下所示。
示例:
在此表格中... | 如果我们应用此查询... | 我们获得了这一结果。 | ||||||||||||||||||||||||
|
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
子句中。
以下是受支持的聚合函数:
名称 | 说明 | 支持的列类型 | 返回值类型 |
---|---|---|---|
avg() |
返回列中某一列中所有值的平均值。 | number |
number |
count() |
返回组的指定列中元素的数量。系统不会统计 null 单元格。 | 任何类型 | number |
max() |
返回列中某一组的最大值。日期与前面的较小日期进行比较,string 按字母顺序比较,且区分大小写。 |
任何类型 | 与列的类型相同 |
min() |
返回列中某一组的最小值。日期与前面的较小日期进行比较,string 按字母顺序比较,区分大小写 |
任何类型 | 与列的类型相同 |
sum() |
返回列中所有值的总和。 | number |
number |
注意:聚合函数只能将列标识符作为参数:
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 值的总和。 |
两个 number |
number |
- |
返回两个 number 值之间的差异。 |
两个 number |
number |
* |
返回两个 number 的乘积。 |
两个 number |
number |
/ |
返回两个 number 的商。除数为 0 时返回 null。 |
两个 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
,后跟yyyy-MM-dd
格式的string
字面量。 示例:date "2008-03-18"
。 -
timeofday
- 使用关键字
timeofday
,后跟string
格式,格式为HH:mm:ss[.SSS]
示例:timeofday "12:30:45"
。 -
datetime
- 日期和时间,使用关键字
datetime
或关键字timestamp
,后跟yyyy-MM-dd HH:mm:ss[.sss]
格式的string
字面量。示例:datetime '2008-03-18 12:30:34.123'
标识符
标识符是用于标识列的文本 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