迁移到标准 SQL

BigQuery 支持两种 SQL 语言:标准 SQL旧版 SQL。本主题说明了这两种语言之间的差异,包括语法函数语义,并提供了某些标准 SQL 要点的示例。

旧版和标准 SQL 的比较

BigQuery 以前使用称为 BigQuery SQL 的非标准 SQL 语言执行查询。随着 BigQuery 2.0 的推出,BigQuery 发布了对标准 SQL 的支持,并将 BigQuery SQL 重命名为旧版 SQL。标准 SQL 是用于查询存储在 BigQuery 中的数据的首选 SQL 语言。

是否必须迁移到标准 SQL?

我们建议从旧版 SQL 迁移到标准 SQL,但没有强制要求。例如,假设您执行许多使用旧版 SQL 的查询,但是想要利用标准 SQL 功能来执行新查询。您可以使用标准 SQL 创建新查询,这些新查询可与使用旧版 SQL 的查询一起运行。

启用标准 SQL

在运行查询时,您可以选择是使用旧版 SQL 还是标准 SQL。如需了解如何在各种 SQL 语言之间进行切换,请参阅切换 SQL 语言

标准 SQL 的优势

标准 SQL 符合 SQL 2011 标准,并且具有支持查询嵌套和重复数据的扩展程序。与旧版 SQL 相比,它具有一些优势,包括:

有关对上述某些功能进行说明的示例,请参阅标准 SQL 要点

类型差异

标准 SQL 中具有与旧版 SQL 类型等效的类型,反之亦然。在某些情况下,类型具有不同的名称。下表列出了各种旧版 SQL 数据类型及其标准 SQL 的等效数据类型。

旧版 SQL 标准 SQL 备注
BOOL BOOL
INTEGER INT64
FLOAT FLOAT64
STRING STRING
BYTES BYTES
RECORD STRUCT
REPEATED ARRAY
TIMESTAMP TIMESTAMP 请参阅 TIMESTAMP 差异
DATE DATE 旧版 SQL 对 DATE 提供有限支持
TIME TIME 旧版 SQL 对 TIME 提供有限支持
DATETIME DATETIME 旧版 SQL 对 DATETIME 提供有限支持

要详细了解标准 SQL 类型系统,请参阅标准 SQL 数据类型参考。要详细了解 BigQuery 中的数据类型,请参阅 BigQuery 数据类型参考

TIMESTAMP 差异

与旧版 SQL 相比,标准 SQL 具有更严格的有效 TIMESTAMP 值范围。在标准 SQL 中,有效 TIMESTAMP 值介于 0001-01-01 00:00:00.0000009999-12-31 23:59:59.999999 的范围内。例如,您可以使用标准 SQL 选择最小和最大 TIMESTAMP 值:

#standardSQL
SELECT
  min_timestamp,
  max_timestamp,
  UNIX_MICROS(min_timestamp) AS min_unix_micros,
  UNIX_MICROS(max_timestamp) AS max_unix_micros
FROM (
  SELECT
    TIMESTAMP '0001-01-01 00:00:00.000000' AS min_timestamp,
    TIMESTAMP '9999-12-31 23:59:59.999999' AS max_timestamp
);

该查询返回 -62135596800000000 作为 min_unix_micros,返回 253402300799999999 作为 max_unix_micros

如果您选择的列包含超出此范围的时间戳值,您将会收到一条错误消息:

#standardSQL
SELECT timestamp_column_with_invalid_values
FROM MyTableWithInvalidTimestamps;

此查询返回以下错误:

Cannot return an invalid timestamp value of -8446744073709551617
microseconds relative to the Unix epoch. The range of valid
timestamp values is [0001-01-1 00:00:00, 9999-12-31 23:59:59.999999]

要修正错误,一种选择是定义和使用用户定义的函数过滤无效的时间戳:

#standardSQL
CREATE TEMP FUNCTION TimestampIsValid(t TIMESTAMP) AS (
  t >= TIMESTAMP('0001-01-01 00:00:00') AND
  t <= TIMESTAMP('9999-12-31 23:59:59.999999')
);

SELECT timestamp_column_with_invalid_values
FROM MyTableWithInvalidTimestamps
WHERE TimestampIsValid(timestamp_column_with_invalid_values);

修正错误的另一种方式是使用具有时间戳列的 SAFE_CAST 函数。例如:

#standardSQL
SELECT SAFE_CAST(timestamp_column_with_invalid_values AS STRING) AS timestamp_string
FROM MyTableWithInvalidTimestamps;

此查询返回 NULL,而不是无效时间戳值的时间戳字符串。

语法差异

转义保留关键字和无效标识符

在旧版 SQL 中,使用方括号 [] 来转义包含无效字符(如空格 或连字符 -)的保留关键字和标识符。在标准 SQL 中,使用反引号 ` 来转义此类关键字和标识符。例如:

#standardSQL
SELECT
  word,
  SUM(word_count) AS word_count
FROM
  `bigquery-public-data.samples.shakespeare`
WHERE word IN ('me', 'I', 'you')
GROUP BY word;

旧版 SQL 允许在某些地方使用保留关键字,而标准 SQL 不允许。例如,在使用标准 SQL 时,Syntax error 会导致以下查询失败:

#standardSQL
SELECT
  COUNT(*) AS rows
FROM
  `bigquery-public-data.samples.shakespeare`;

要更正该错误,请使用反引号转义别名 rows

#standardSQL
SELECT
  COUNT(*) AS `rows`
FROM
  `bigquery-public-data.samples.shakespeare`;

如需保留关键字列表以及有效标识符的构成,请参阅词汇结构

项目限定的表名称

在旧版 SQL 中,要查询具有项目限定名称的表,请使用冒号 : 作为分隔符。例如:

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare]
LIMIT 1;

在标准 SQL 中,请改为使用句点 .。例如:

#standardSQL
SELECT
  word
FROM
  `bigquery-public-data.samples.shakespeare`
LIMIT 1;

如果项目名称包含域(例如 example.com:myproject),请将 example.com:myproject 用作项目名称,其中包括 :

表修饰器和通配符函数

标准 SQL 不支持 TABLE_DATE_RANGETABLE_DATE_RANGE_STRICTTABLE_QUERY 函数。

您可以使用 _TABLE_SUFFIX 伪列上的过滤器实现 TABLE_DATE_RANGETABLE_QUERY 相同的语义。例如,请参考以下旧版 SQL 查询,该查询计算了美国国家海洋和大气管理局 GSOD(全球每日总结)表中 2010 和 2011 年间的行数:

#legacySQL
SELECT COUNT(*)
FROM TABLE_QUERY([bigquery-public-data:noaa_gsod],
                 'table_id IN ("gsod2010", "gsod2011")');

使用标准 SQL 的等效查询是:

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.noaa_gsod.*`
WHERE _TABLE_SUFFIX IN ("gsod2010", "gsod2011");

如需了解详情(包括使用 TABLE_DATE_RANGE 的示例),请参阅表修饰器和通配符函数

SELECT 列表中的尾随逗号

与旧版 SQL 不同,标准 SQL 不允许在 FROM 子句之前使用尾随逗号。例如,以下查询无效:

#standardSQL
SELECT
  word,
  corpus,  -- Error due to trailing comma
FROM
  `bigquery-public-data.samples.shakespeare`
LIMIT 1;

要修正该错误,请删除 corpus 后的逗号:

#standardSQL
SELECT
  word,
  corpus
FROM
  `bigquery-public-data.samples.shakespeare`
LIMIT 1;

逗号运算符与表

在旧版 SQL 中,逗号运算符 , 在应用于表时具有 UNION ALL 的非标准含义。在标准 SQL 中,逗号运算符具有 JOIN 的标准含义。例如,请参考以下旧版 SQL 查询:

#legacySQL
SELECT
  x,
  y
FROM
  (SELECT 1 AS x, "foo" AS y),
  (SELECT 2 AS x, "bar" AS y);

这相当于标准 SQL 查询:

#standardSQL
SELECT
  x,
  y
FROM
  (SELECT 1 AS x, "foo" AS y UNION ALL
   SELECT 2 AS x, "bar" AS y);

另请注意,在标准 SQL 中,UNION ALL 是按位置而不是按名称来关联列。上述查询等同于:

#standardSQL
SELECT
  x,
  y
FROM
  (SELECT 1 AS x, "foo" AS y UNION ALL
   SELECT 2, "bar");

逗号运算符在标准 SQL 中的常见用法是与数组连接 (JOIN)。例如:

#standardSQL
WITH T AS (
  SELECT 0 AS x, [1, 2, 3] AS arr UNION ALL
  SELECT 1, [4, 5])
SELECT
  x,
  y
FROM
  T,
  UNNEST(arr) AS y;

这将返回表 Tarr 元素的叉积。您还可以将标准 SQL 中的查询表示为:

#standardSQL
WITH T AS (
  SELECT 0 AS x, [1, 2, 3] AS arr UNION ALL
  SELECT 1, [4, 5])
SELECT
  x,
  y
FROM
  T
JOIN
  UNNEST(arr) AS y;

在此查询中,JOIN 与以上示例中 , 逗号运算分隔的 TUNNEST(arr) AS y 的含义相同。

逻辑视图

您无法使用标准 SQL 查询使用旧版 SQL 定义的逻辑视图,反之亦然,因为这两种方言之间存在语法和语义上的差异。您需要使用标准 SQL 创建新视图(可能使用不同的名称)来代替使用旧版 SQL 的视图。

例如,假设视图 V 使用旧版 SQL 定义,如下所示:

#legacySQL
SELECT *, UTC_USEC_TO_DAY(timestamp_col) AS day
FROM MyTable;

假设视图 W 使用旧版 SQL 定义,如下所示:

#legacySQL
SELECT user, action, day
FROM V;

假设您每天执行以下旧版 SQL 查询,但您希望迁移它,以改为使用标准 SQL:

#legacySQL
SELECT EXACT_COUNT_DISTINCT(user), action, day
FROM W
GROUP BY action, day;

一种可能的迁移路径是使用不同的名称创建新视图。相关步骤包括:

使用标准 SQL 创建名为 V2 的视图,其中包含以下内容:

#standardSQL
SELECT *, EXTRACT(DAY FROM timestamp_col) AS day
FROM MyTable;

使用标准 SQL 创建名为 W2 的视图,其中包含以下内容:

#standardSQL
SELECT user, action, day
FROM V2;

将每天执行的查询更改为使用标准 SQL 并引用 W2

#standardSQL
SELECT COUNT(DISTINCT user), action, day
FROM W2
GROUP BY action, day;

另一种选择是删除视图 VW,然后使用标准 SQL 重新创建相同名称的视图。但若使用此选项,您需要迁移所有引用 VW 的查询,以同时使用标准 SQL。

函数比较

以下是旧版 SQL 函数及其标准 SQL 等效项的部分列表。

旧版 SQL 标准 SQL 备注
INTEGER(x) SAFE_CAST(x AS INT64)
CAST(x AS INTEGER) SAFE_CAST(x AS INT64)
DATEDIFF(t1, t2) TIMESTAMP_DIFF(t1, t2, DAY)
NOW CURRENT_TIMESTAMP
STRFTIME_UTC_USEC(t, fmt) FORMAT_TIMESTAMP(fmt, t)
UTC_USEC_TO_DAY(t) TIMESTAMP_TRUNC(t, DAY)
REGEXP_MATCH(s, pattern) REGEXP_CONTAINS(s, pattern)
IS_NULL(x) x IS NULL
LEFT(s, len) SUBSTR(s, 0, len)
RIGHT(s, len) SUBSTR(s, -len)
s CONTAINS "foo" STRPOS(s, "foo") > 0s LIKE '%foo%'
x % y MOD(x, y)
NEST(x) ARRAY_AGG(x)
ANY(x) ANY_VALUE(x)
GROUP_CONCAT_UNQUOTED(s, sep) STRING_AGG(s, sep)
SOME(x) IFNULL(LOGICAL_OR(x), false)
EVERY(x) IFNULL(LOGICAL_AND(x), true)
COUNT(DISTINCT x) APPROX_COUNT_DISTINCT(x) 请参阅以下备注
EXACT_COUNT_DISTINCT(x) COUNT(DISTINCT x) 请参阅以下备注
QUANTILES(x, buckets + 1) APPROX_QUANTILES(x, buckets)
TOP(x, num), COUNT(*) APPROX_TOP_COUNT(x, num)
NTH(index, arr) WITHIN RECORD arr[SAFE_ORDINAL(index)]
COUNT(arr) WITHIN RECORD ARRAY_LENGTH(arr)
HOST(url) NET.HOST(url) 请参阅以下差异
TLD(url) NET.PUBLIC_SUFFIX(url) 请参阅以下差异
DOMAIN(url) NET.REG_DOMAIN(url) 请参阅以下差异
PARSE_IP(addr_string) NET.IPV4_TO_INT64(NET.IP_FROM_STRING(addr_string))
FORMAT_IP(addr_int64) NET.IP_TO_STRING(NET.IPV4_FROM_INT64(addr_int64 & 0xFFFFFFFF))
PARSE_PACKED_IP(addr_string) NET.IP_FROM_STRING(addr_string)
FORMAT_PACKED_IP(addr_bytes) NET.IP_TO_STRING(addr_bytes)

如需详细了解标准 SQL 函数,请参阅函数和运算符主题。

COUNT 函数比较

旧版 SQL 和标准 SQL 都包含 COUNT 函数。但是,每个函数的行为有所不同,具体取决于您使用的 SQL 方言。

在旧版 SQL 中,COUNT(DISTINCT x) 返回近似计数。在标准 SQL 中,它返回精确计数。如需运行速度更快且需要更少资源的不同值的近似计数,请使用 APPROX_COUNT_DISTINCT

网址函数比较

旧版 SQL 和标准 SQL 都包含用于解析网址的函数。在旧版 SQL 中,这些函数是 HOST(url)TLD(url)DOMAIN(url)。在标准 SQL 中,这些函数是 NET.HOST(url)NET.PUBLIC_SUFFIX(url)NET.REG_DOMAIN(url)

对旧版 SQL 函数的改进

  • 标准 SQL 网址函数可以分析以"//"开头的网址。
  • 当输入不符合 RFC 3986 或不是网址(例如,"mailto:?to=&subject=&body=")时,将应用不同的规则来解析输入。尤其,标准 SQL 网址函数可以解析不带“//”的非标准输入,如“www.google.com”。为获得最佳结果,建议您确保输入为网址并符合 RFC 3986。
  • NET.PUBLIC_SUFFIX 返回没有前导点的结果。例如,它返回“com”,而不是“.com”。这符合公共后缀列表中的格式。
  • NET.PUBLIC_SUFFIXNET.REG_DOMAIN 支持大写字母和国际化域名。TLDDOMAIN 则不支持它们(可能会返回意外的结果)。

边缘情况中的细微差异

  • 如果输入不包含公共后缀列表中的任何后缀,则 NET.PUBLIC_SUFFIXNET.REG_DOMAIN 返回 NULL,而 TLDDOMAIN 返回尽力猜测的非 NULL 值。
  • 如果输入仅包含没有前面标签的公共后缀(例如,“http://com”),则 NET.PUBLIC_SUFFIX 返回该公共后缀,而 TLD 返回空字符串。同样,NET.REG_DOMAIN 返回 NULL,而 DOMAIN 返回该公共后缀。
  • 对于使用 IPv6 主机的输入,NET.HOST 不会从结果中移除英文括号,正如 RFC 3986 的规定。
  • 对于使用 IPv4 主机的输入,NET.REG_DOMAIN 返回 NULL,而 DOMAIN 返回前 3 个八位字节。

示例

在下表中,灰色文本颜色表示旧版和标准 SQL 中的相同结果。

网址(说明) 主机 NET.HOST TLD NET.PUBLIC _SUFFIX 网域 NET.REG_DOMAIN
"//google.com"
(以“//”开头)
NULL "google.com" NULL "com" NULL "google.com"
"google.com"
(非标准;没有“//”)
NULL "google.com" NULL "com" NULL "google.com"
"http://user:pass@word@x.com"
(非标准,有多个“@”)
"word@x.com" "x.com" ".com" "com" "word@x.com" "x.com"
"http://foo.com:1:2"
(非标准,有多个":")
"foo.com:1" "foo.com" ".com:1" "com" "foo.com" "foo.com"
"http://x.Co.uk"
(大写字母)
"x.Co.uk" "x.Co.uk" ".uk" "Co.uk" "Co.uk" "x.Co.uk"
"http://a.b"
(未找到公共后缀)
"a.b" "a.b" ".b" NULL "a.b" NULL
"http://com"
(主机只包含公共后缀)
"com" "com" "" "com" "com" NULL
"http://[::1]"
(IPv6 主机;没有公共后缀)
"::1" "[::1]" "" NULL "::1" NULL
"http://1.2.3.4"
(IPv4 主机;没有公共后缀)
"1.2.3.4" "1.2.3.4" "" NULL "1.2.3" NULL

重复字段处理中的差异

旧版 SQL 中的 REPEATED 类型等效于标准 SQL 中那种类型的 ARRAY。例如,REPEATED INTEGER 等效于标准 SQL 中的 ARRAY<INT64>。以下部分讨论旧版和标准 SQL 在重复字段操作上的一些差异。

NULL 元素和 NULL 数组

标准 SQL 支持 NULL 数组元素,但如果查询结果中有 NULL 数组元素,则会引发错误。如果查询结果中有 NULL 数组列,则标准 SQL 会将它存储为空数组。

选择嵌套的重复叶字段

使用旧版 SQL 时,您可以将“点”添加到嵌套的重复字段中,而无需考虑重复发生的位置。在标准 SQL 中,尝试将“点”添加到嵌套的重复字段会导致错误。例如:

#standardSQL
SELECT
  repository.url,
  payload.pages.page_name
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

尝试执行此查询将返回:

Cannot access field page_name on a value with type
ARRAY<STRUCT<action STRING, html_url STRING, page_name STRING, ...>>

要修正该错误并在结果中返回 page_name 数组,请改用 ARRAY 子查询。例如:

#standardSQL
SELECT
  repository.url,
  ARRAY(SELECT page_name FROM UNNEST(payload.pages)) AS page_names
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

如需详细了解数组和 ARRAY 子查询,请参阅使用数组主题。

过滤重复字段

使用旧版 SQL 时,您可以使用 WHERE 子句直接过滤重复字段。在标准 SQL 中,您可以通过在 JOIN 逗号运算符后添加过滤条件来表示类似逻辑。例如,请参考以下旧版 SQL 查询:

#legacySQL
SELECT
  payload.pages.title
FROM
  [bigquery-public-data:samples.github_nested]
WHERE payload.pages.page_name IN ('db_jobskill', 'Profession');

该查询将返回 page_namedb_jobskillProfession 的所有页面 title。您可以在标准 SQL 中表示类似查询,如下所示:

#standardSQL
SELECT
  page.title
FROM
  `bigquery-public-data.samples.github_nested`,
  UNNEST(payload.pages) AS page
WHERE page.page_name IN ('db_jobskill', 'Profession');

前面的旧版 SQL 和标准 SQL 查询之间的一个差异是,如果未设置展平结果 (Flatten Results) 选项并执行旧版 SQL 查询,则在查询结果中 payload.pages.title 将是 REPEATED。要在标准 SQL 中实现相同的语义并返回 title 列的数组,请改为使用 ARRAY 子查询:

#standardSQL
SELECT
  title
FROM (
  SELECT
    ARRAY(SELECT title FROM UNNEST(payload.pages)
          WHERE page_name IN ('db_jobskill', 'Profession')) AS title
  FROM
    `bigquery-public-data.samples.github_nested`)
WHERE ARRAY_LENGTH(title) > 0;

此查询将创建一个 page_name'db_jobskill''Profession'title 数组,然后使用 ARRAY_LENGTH(title) > 0 过滤数组不符合该条件的所有行。

要详细了解数组,请参阅使用数组主题。

选定的嵌套叶字段的结构

未设置展平结果 (Flatten Results) 选项时,旧版 SQL 会保留 SELECT 列表中嵌套叶级字段的结构,而标准 SQL 则不会。例如,请参考以下旧版 SQL 查询:

#legacySQL
SELECT
  repository.url,
  repository.has_downloads
FROM
  [bigquery-public-data.samples.github_nested]
LIMIT 5;

未设置展平结果 (Flatten Results) 时,此查询将在名为 repository 的记录中返回 urlhas_downloads。现请参考以下标准 SQL 查询:

#standardSQL
SELECT
  repository.url,
  repository.has_downloads
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

此查询将返回 urlhas_downloads 作为顶级列;它们不属于 repository 记录或结构。要将它们作为结构的一部分返回,请使用 STRUCT 运算符:

#standardSQL
SELECT
  STRUCT(
    repository.url,
    repository.has_downloads) AS repository
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

使用 FLATTEN 删除重复

标准 SQL 没有旧版 SQL 中的 FLATTEN 函数,但您可以使用 JOIN(逗号)运算符实现类似语义。例如,请参考以下旧版 SQL 查询:

#legacySQL
SELECT
  repository.url,
  payload.pages.page_name
FROM
  FLATTEN([bigquery-public-data:samples.github_nested], payload.pages.page_name)
LIMIT 5;

您可以在标准 SQL 中表示类似查询,如下所示:

#standardSQL
SELECT
  repository.url,
  page.page_name
FROM
  `bigquery-public-data.samples.github_nested`,
  UNNEST(payload.pages) AS page
LIMIT 5;

或者,使用等效的 JOIN 而不是逗号 , 运算符:

#standardSQL
SELECT
  repository.url,
  page.page_name
FROM
  `bigquery-public-data.samples.github_nested`
JOIN
  UNNEST(payload.pages) AS page
LIMIT 5;

一个重要的差异在于,如果 payload.pages 为空,则旧版 SQL 查询返回 payload.pages.page_nameNULL 的行。但是,如果 payload.pages 为空,则标准 SQL 查询不会返回行。要实现完全相同的语义,请使用 LEFT JOINLEFT OUTER JOIN。例如:

#standardSQL
SELECT
  repository.url,
  page.page_name
FROM
  `bigquery-public-data.samples.github_nested`
LEFT JOIN
  UNNEST(payload.pages) AS page
LIMIT 5;

要详细了解数组,请参阅使用数组主题。如需详细了解 UNNEST,请参阅 UNNEST 主题。

使用 OMIT RECORD IF 过滤行

通过旧版 SQL 的 OMIT IF 子句,您可以根据可应用于重复字段的条件来过滤行。在标准 SQL 中,可以使用 EXISTS 子句、IN 子句或简单过滤条件对 OMIT IF 子句建模。例如,请参考以下旧版 SQL 查询:

#legacySQL
SELECT
  repository.url,
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF
  EVERY(payload.pages.page_name != 'db_jobskill'
        AND payload.pages.page_name != 'Profession');

类似的标准 SQL 查询为:

#standardSQL
SELECT
  repository.url
FROM
  `bigquery-public-data.samples.github_nested`
WHERE EXISTS (
  SELECT 1 FROM UNNEST(payload.pages)
  WHERE page_name = 'db_jobskill'
    OR page_name = 'Profession');

此处,如果页面名称为 'db_jobskill''Profession'payload.pages 的元素至少有一个,则 EXISTS 子句的计算结果为 true

或者,假设旧版 SQL 查询使用 IN

#legacySQL
SELECT
  repository.url,
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF NOT
  SOME(payload.pages.page_name IN ('db_jobskill', 'Profession'));

在标准 SQL 中,您可以使用带有 INEXISTS 子句来表示该查询:

#standardSQL
SELECT
  repository.url
FROM
  `bigquery-public-data.samples.github_nested`
WHERE EXISTS (
  SELECT 1 FROM UNNEST(payload.pages)
  WHERE page_name IN ('db_jobskill', 'Profession'));

请参考以下旧版 SQL 查询,该查询过滤包含 80 个或更少数量页面的记录:

#legacySQL
SELECT
  repository.url,
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF
  COUNT(payload.pages.page_name) <= 80;

在此情况下,您可以在标准 SQL 中使用具有 ARRAY_LENGTH 的过滤条件:

#standardSQL
SELECT
  repository.url
FROM
  `bigquery-public-data.samples.github_nested`
WHERE
  ARRAY_LENGTH(payload.pages) > 80;

请注意,ARRAY_LENGTH 函数会直接应用于重复的 payload.pages 字段,而不是像在旧版 SQL 查询中那样应用于嵌套字段 payload.pages.page_name

如需详细了解数组和 ARRAY 子查询,请参阅使用数组主题。

语义差异

旧版 SQL 和标准 SQL 之间,某些操作的语义会有所不同。

自动数据类型 Coercion 转换

旧版 SQL 和标准 SQL 都支持在某些数据类型之间进行 Coercion 转换(自动转换)。例如,如果查询将 INT64 类型的值传送到需要 FLOAT64 作为输入的函数,则 BigQuery 会将该值 Coercion 转换为 FLOAT64。标准 SQL 不支持旧版 SQL 支持的以下 Coercion 转换。因此必须改为使用显式 CAST 转换。

  • INT64 字面量转换为 TIMESTAMP。改用 TIMESTAMP_MICROS(micros_value)
  • STRING 字面量转换为 INT64FLOAT64BOOL。改用 CAST(str AS INT64)CAST(str AS FLOAT64)CAST(str AS BOOL)
  • STRING 转换为 BYTES。改用 CAST(str AS BYTES)

运行时错误

旧版 SQL 中的某些函数对于无效输入返回 NULL,这可能会掩盖查询或数据中的问题。标准 SQL 通常更严格,如果输入无效则会引发错误。

  • 旧版 SQL 对所有数学函数和运算符都不会检查溢出。标准 SQL 添加了溢出检查,如果计算溢出则会引发错误。上述函数和运算符包括 +-* 运算符以及 SUMAVGSTDDEV 聚合函数等等。
  • 标准 SQL 会在除数为零时引发错误,而旧版 SQL 则返回 NULL。在标准 SQL 中,如需在除数为零时返回 NULL,请使用 SAFE_DIVIDE
  • 标准 SQL 会在输入格式无效或超出目标类型范围时,对 CAST 引发错误,而旧版 SQL 则返回 NULL。在标准 SQL 中,如需避免因无效的 Cast 转换而引发错误,请使用 SAFE_CAST

嵌套的重复结果

使用标准 SQL 执行的查询会保留结果中列的任何嵌套和重复,并且展平结果 (Flatten Results) 选项无效。要返回嵌套字段的顶级列,请对结构列使用 .* 运算符。例如:

#standardSQL
SELECT
  repository.*
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

要返回重复嵌套字段的顶级列(STRUCTARRAY),请使用 JOIN 来获取表的行与重复嵌套字段的元素的叉积。例如:

#standardSQL
SELECT
  repository.url,
  page.*
FROM
  `bigquery-public-data.samples.github_nested`
JOIN
  UNNEST(payload.pages) AS page
LIMIT 5;

如需详细了解数组和 ARRAY 子查询,请参阅使用数组主题。

NOT IN 条件和 NULL

旧版 SQL 在 NOT IN 条件下处理 NULL 时不符合 SQL 标准,而标准 SQL 符合。请参考以下旧版 SQL 查询,它可以查找在 GitHub 示例表中未显示为位置的字词数:

#legacySQL
SELECT COUNT(*)
FROM [bigquery-public-data.samples.shakespeare]
WHERE word NOT IN (
  SELECT actor_attributes.location
  FROM [bigquery-public-data.samples.github_nested]
);

此查询返回 163,716 作为计数,表示 GitHub 表中有 163,716 个字词未显示为位置。现请参考以下标准 SQL 查询:

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare`
WHERE word NOT IN (
  SELECT actor_attributes.location
  FROM `bigquery-public-data.samples.github_nested`
);

此查询返回 0 作为计数。该差异由标准 SQL 中 NOT IN 的语义引起,如果右侧的任何值为 NULL,则该语义返回 NULL。要获得与使用旧版 SQL 查询相同的结果,请使用 WHERE 子句排除 NULL 值:

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare`
WHERE word NOT IN (
  SELECT actor_attributes.location
  FROM `bigquery-public-data.samples.github_nested`
  WHERE actor_attributes.location IS NOT NULL
);

此查询返回 163,716 作为计数。或者,使用 NOT EXISTS 条件:

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare` AS t
WHERE NOT EXISTS (
  SELECT 1
  FROM `bigquery-public-data.samples.github_nested`
  WHERE t.word = actor_attributes.location
);

此查询也返回 163,716 作为计数。如需深入阅读,请参阅文档的比较运算符部分,其中说明了 INNOT INEXISTS 和其他比较运算符的语义。

用户定义的 JavaScript 函数中的差异

用户定义的函数主题介绍了如何将 JavaScript 用户定义的函数与标准 SQL 一起使用。本节介绍用户定义的函数在旧版和标准 SQL 中的一些主要差异。

查询文本中的函数

使用标准 SQL 时,您可以将 CREATE TEMPORARY FUNCTION 用作查询主体的一部分,而不用单独指定用户定义的函数。单独定义函数的示例包括在 BigQuery 网页界面中使用 UDF 编辑器定义,或在使用 bq CLI 时通过 --udf_resource 标志定义。

请参考以下标准 SQL 查询:

#standardSQL
-- Computes the harmonic mean of the elements in 'arr'.
-- The harmonic mean of x_1, x_2, ..., x_n can be expressed as:
--   n / ((1 / x_1) + (1 / x_2) + ... + (1 / x_n))
CREATE TEMPORARY FUNCTION HarmonicMean(arr ARRAY<FLOAT64>)
  RETURNS FLOAT64 LANGUAGE js AS """
var sum_of_reciprocals = 0;
for (var i = 0; i < arr.length; ++i) {
  sum_of_reciprocals += 1 / arr[i];
}
return arr.length / sum_of_reciprocals;
""";

WITH T AS (
  SELECT GENERATE_ARRAY(1.0, x * 4, x) AS arr
  FROM UNNEST([1, 2, 3, 4, 5]) AS x
)
SELECT arr, HarmonicMean(arr) AS h_mean
FROM T;

此查询定义名为 HarmonicMean 的 JavaScript 函数,然后将其应用于 T 中的数组列 arr

要详细了解用户定义的函数,请参阅用户定义的函数主题。

函数作用于值而非行

在旧版 SQL 中,JavaScript 函数作用于表中的行。如上例所示,在标准 SQL 中,JavaScript 函数作用于值。要使用标准 SQL 将行值传递给 JavaScript 函数,请定义一个函数,该函数采用行类型与该表相同的结构。例如:

#standardSQL
-- Takes a struct of x, y, and z and returns a struct with a new field foo.
CREATE TEMPORARY FUNCTION AddField(s STRUCT<x FLOAT64, y BOOL, z STRING>)
  RETURNS STRUCT<x FLOAT64, y BOOL, z STRING, foo STRING> LANGUAGE js AS """
var new_struct = new Object();
new_struct.x = s.x;
new_struct.y = s.y;
new_struct.z = s.z;
if (s.y) {
  new_struct.foo = 'bar';
} else {
  new_struct.foo = 'baz';
}

return new_struct;
""";

WITH T AS (
  SELECT x, MOD(off, 2) = 0 AS y, CAST(x AS STRING) AS z
  FROM UNNEST([5.0, 4.0, 3.0, 2.0, 1.0]) AS x WITH OFFSET off
)
SELECT AddField(t).*
FROM T AS t;

此查询定义了一个 JavaScript 函数,该函数采用行类型与 T 相同的结构,并使用名为 foo 的附加字段创建新结构。SELECT 语句将行 t 作为输入传递给该函数,并使用 .* 返回输出中生成结构的字段。

标准 SQL 的亮点

本节讨论标准 SQL 与旧版 SQL 相比的一些亮点。

使用 WITH 子句的可组合性

本页面上的一些标准 SQL 示例使用 WITH 子句,该子句允许提取或重用已命名的子查询。例如:

#standardSQL
WITH T AS (
  SELECT x FROM UNNEST([1, 2, 3, 4]) AS x
)
SELECT x / (SELECT SUM(x) FROM T) AS weighted_x
FROM T;

该查询定义了一个名称子查询 T,其中包含的 x 值为 1、2、3 和 4。它从 T 中选择 x 值,并将它们除以 T 中所有 x 值的总和。此查询等效于 T 的内容为内嵌的查询:

#standardSQL
SELECT
  x / (SELECT SUM(x)
       FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x)) AS weighted_x
FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x);

请参考下方另一个示例查询,该查询使用多个名称子查询:

#standardSQL
WITH T AS (
  SELECT x FROM UNNEST([1, 2, 3, 4]) AS x
),
TPlusOne AS (
  SELECT x + 1 AS y
  FROM T
),
TPlusOneTimesTwo AS (
  SELECT y * 2 AS z
  FROM TPlusOne
)
SELECT z
FROM TPlusOneTimesTwo;

该查询定义了原始数据的一系列转换,然后在 TPlusOneTimesTwo 中定义了 SELECT 语句。此查询等效于以下内嵌计算的查询:

#standardSQL
SELECT (x + 1) * 2 AS z
FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x);

如需了解详情,请参阅文档中的 WITH 子句主题。

使用 SQL 函数的可组合性

标准 SQL 支持用户定义的 SQL 函数。您可以使用用户定义的 SQL 函数定义公共表达式,然后从该查询中引用它们。例如:

#standardSQL
-- Computes the harmonic mean of the elements in 'arr'.
-- The harmonic mean of x_1, x_2, ..., x_n can be expressed as:
--   n / ((1 / x_1) + (1 / x_2) + ... + (1 / x_n))
CREATE TEMPORARY FUNCTION HarmonicMean(arr ARRAY<FLOAT64>) AS
(
  ARRAY_LENGTH(arr) / (SELECT SUM(1 / x) FROM UNNEST(arr) AS x)
);

WITH T AS (
  SELECT GENERATE_ARRAY(1.0, x * 4, x) AS arr
  FROM UNNEST([1, 2, 3, 4, 5]) AS x
)
SELECT arr, HarmonicMean(arr) AS h_mean
FROM T;

此查询定义名为 HarmonicMean 的 SQL 函数,然后将其应用于 T 中的数组列 arr

更多地方的子查询

标准 SQL 支持 SELECT 列表中、WHERE 子句中以及在查询中需要表达式的任何其他位置处的子查询。例如,请参考以下标准 SQL 查询,该查询计算 2015 年西雅图的暖日分数:

#standardSQL
WITH SeattleWeather AS (
  SELECT *
  FROM `bigquery-public-data.noaa_gsod.gsod2015`
  WHERE stn = '994014'
)
SELECT
  COUNTIF(max >= 70) /
    (SELECT COUNT(*) FROM SeattleWeather) AS warm_days_fraction
FROM SeattleWeather;

西雅图气象站的 ID 为 '994014'。该查询根据温度达到 70 华氏度或大约 21 摄氏度的天数计算暖日天数,然后除以 2015 年该站的记录天数总数。

相关子查询

在标准 SQL 中,子查询可以引用相关列;即源自外部查询的列。例如,请参考以下标准 SQL 查询:

#standardSQL
WITH WashingtonStations AS (
  SELECT weather.stn AS station_id, ANY_VALUE(station.name) AS name
  FROM `bigquery-public-data.noaa_gsod.stations` AS station
  INNER JOIN `bigquery-public-data.noaa_gsod.gsod2015` AS weather
  ON station.usaf = weather.stn
  WHERE station.state = 'WA' AND station.usaf != '999999'
  GROUP BY station_id
)
SELECT washington_stations.name,
  (SELECT COUNT(*)
   FROM `bigquery-public-data.noaa_gsod.gsod2015` AS weather
   WHERE washington_stations.station_id = weather.stn
   AND max >= 70) AS warm_days
FROM WashingtonStations AS washington_stations
ORDER BY warm_days DESC;

该查询获取华盛顿州气象站的名称,并计算 2015 年气温达到 70 华氏度(约 21 摄氏度)的天数。请注意,SELECT 列表中有一个子查询,并且该子查询从外部范围(即 FROM WashingtonStations AS washington_stations)引用 washington_stations.station_id

数组和结构体

ARRAYSTRUCT 是标准 SQL 中非常强大的概念。有关这两者的用法示例,请参考以下查询,它会计算出 HackerNews 数据集中每天最热门的两篇文章:

#standardSQL
WITH TitlesAndScores AS (
  SELECT
    ARRAY_AGG(STRUCT(title, score)) AS titles,
    EXTRACT(DATE FROM time_ts) AS date
  FROM `bigquery-public-data.hacker_news.stories`
  WHERE score IS NOT NULL AND title IS NOT NULL
  GROUP BY date)
SELECT date,
  ARRAY(SELECT AS STRUCT title, score
        FROM UNNEST(titles)
        ORDER BY score DESC
        LIMIT 2)
  AS top_articles
FROM TitlesAndScores
ORDER BY date DESC;

WITH 子句定义了包含两列的 TitlesAndScores。第一列是结构体数组(其中一个字段是文章标题),第二列是分数。ARRAY_AGG 表达式会每天返回这些结构体的数组。

WITH 子句后面的 SELECT 语句使用 ARRAY 子查询,按照 score 排序并返回每个数组中最热门的两篇文章,然后按日期降序返回结果。

如需详细了解数组和 ARRAY 子查询,请参阅使用数组主题。另请参阅数组结构体的参考文档。

此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面