查询语言参考(版本 0.7)

借助 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。例如,饼图中的图例或表格中的列标题。
  • 数据类型。支持的数据类型包括 stringnumberbooleandatedatetimetimeofday。 列的所有值的数据类型均与列类型匹配,或具有 null 值。这些类型与本文中的字面量部分所述(但并不完全相同)的 JavaScript 类型类似。
  • 格式设置。数据源可以为其部分或全部列定义格式设置模式。您可以通过添加格式子句来替换此模式。

所有示例中使用的表格

在本节中,所有查询示例都引用了下表。列标题是列标识符。

名称
string
部门
string
午餐时间
timeofday
薪资
number
HireDate
date
年龄:
number
老年人
boolean
资历开始时间
datetime
John拉丁文小写字母 eng12:00:00
1000
2005-03-1935true2007-12-02 15:56:00
Dave拉丁文小写字母 eng12:00:00
500
2006-04-1927falsenull
莎莉拉丁文小写字母 eng13:00:00
600
2005-10-1030falsenull
Ben销售12:00:00
400
2002-10-1032true2005-03-09 12:30:00
达纳语销售12:00:00
350
2004-09-0825falsenull
Mike营销13:00:00
800
2005-01-1024true2007-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:00John
12:00:00Dave
13:00:00莎莉
12:00:00Ben
12:00:00达纳语
13:00:00Mike

在哪里

where 子句仅用于返回与指定条件匹配的行。

简单的比较运算符为 <=, <, >, >=, =, !=, <>。这两个比较运算符 != <> 表示不等于。系统会按字典顺序比较字符串。请注意,等式用 = 表示,与大多数计算机语言中的 == 不同。null 的比较是使用 is nullis not null 完成的。

您可以使用逻辑运算符 andornot 联接多个条件。圆括号可用于定义明确的优先级。

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 - 一个(预设)正则表达式匹配。haystack matches如果 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 的营销部门工资的总和,以此类推。

pivotgroup 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 子句中指定的列可能不会显示在 selectgroup byorder 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 属性中返回。

模式语法

numberdatetimeofdaydatetime
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(以查找列中最大的值)和 +(将两列的值添加到同一行中)。

有些函数可以出现在任何子句中;有些可以出现在子句的子集中。具体如下所示。

示例

在此表格中... 如果我们应用此查询... 我们获得了这一结果。
名称 Salary 税费 开始日期
Sharon 1000 100 2009-1-1
生命体征 2000 200 2008 年 1 月 21 日
Moran 3000 300 2008-2-12
select upper(name), year(startDate)
名称 年(开始日期)
虚拟 2008
莫兰 2008
莎伦 2009

 

以下数据操纵函数由 Google 可视化 API 查询语言定义:

 

聚合函数

系统会向聚合函数传递单列标识符,并对每个组中的所有值执行操作(组由 group bypivot 子句指定,如果未使用这些子句,则由所有行指定)。

示例

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.

聚合函数可以在 selectorder bylabelformat 子句中使用。它们不能出现在 wheregroup bypivotlimitoffsetoptions 子句中。

以下是受支持的聚合函数:

名称 说明 支持的列类型 返回值类型
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))

标量函数可用于以下任何子句:selectwheregroup bypivotorder bylabel, format

名称
year()

返回日期或日期时间值的年份值。例如: year(date "2009-02-05") 会返回 2009。

参数:一个类型为 datedatetime 的参数
返回值类型number
month()

从日期或日期时间值返回从零开始的月份值。例如:month(date "2009-02-05") 会返回 1。注意:月份从 0 开始,因此该函数针对 1 月返回 0,针对 2 月返回 1,依此类推。

参数:一个类型为 datedatetime 的参数
返回值类型number
day()

date 或日期时间值返回一个月中的某天。例如:day(date "2009-02-05") 会返回 5。

参数:一个类型为 datedatetime 的参数
返回值类型number
hour()

从日期时间或 timeofday 值返回小时值。例如:hour(timeofday "12:03:17") 会返回 12。

参数:一个类型为 datetimetimeofday 的参数
返回值类型number
minute()

datetimetimeofday 值返回分钟值。例如:minute(timeofday "12:03:17") 会返回 3。

参数:一个类型为 datetimetimeofday 的参数
返回值类型number
second()

返回 datetimetimeofday 值中的第二个值。例如:second(timeofday "12:03:17") 会返回 17。

参数:一个类型为 datetimetimeofday 的参数
返回值类型number
millisecond()

返回 datetimetimeofday 值的毫秒部分。例如:millisecond(timeofday "12:03:17.123") 会返回 123。

参数:一个类型为 datetimetimeofday 的参数
返回值类型number
quarter()

datedatetime 值返回季度。例如:quarter(date "2009-02-05") 会返回 1。请注意,季度从 1 开始,因此该函数针对第一个季度返回 1,针对第二个季度返回 2,依此类推。

参数:一个类型为 datedatetime 的参数
返回值类型number
dayOfWeek()

datedatetime 值返回星期几。例如:dayOfWeek(date "2009-02-26") 会返回 5。请注意,天数从 1 开始,因此该函数针对星期日返回 1,针对星期一返回 2。依此类推。

参数:一个类型为 datedatetime 的参数
返回值类型number
now()

返回表示 GMT 时区中当前 datetime 的日期时间值。

参数:无
返回值类型datetime
dateDiff()

返回两个 datedatetime 值之间的天数差。 注意:计算中只使用值的 date 部分,因此函数始终返回一个整数值。例如:dateDiff(date "2008-03-13", date "2008-02-12") 返回 29;dateDiff(date "2009-02-13", date "2009-03-13") 返回 -29。 在比较之前,时间值会被截断。

参数:两个类型为 datedatetime 的参数(可以是每个参数中的一个)
返回值类型number
toDate()

将指定的值转换为 date 值。

  • 对于 date,它会返回相同的值。
  • 对于 datetime,它会返回 date 部分。例如:toDate(dateTime "2009-01-01 12:00:00") 会返回“2009-01-01”。
  • 如果为 number N,则在 Epoch 后返回 date N 毫秒。该周期定义为 1970 年 1 月 1 日 00:00:00 GMT。例如:toDate(1234567890000) 会返回“2009-02-13”。
形参:一个类型为 datedatetimenumber 的形参
返回值类型date
upper()

返回指定的 string(采用大写字母)。例如:upper("foo") 会返回“FOO”。

参数:一个类型为 string 的参数
返回值类型string
lower()

以小写字母返回指定 string。例如:lower("Bar") 会返回“bar”。

参数:一个类型为 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
布尔值字面量为 truefalse
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