迁移到标准 SQL

BigQuery 支持两种 SQL 方言,即标准 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 标准 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
);

此查询会将 -62135596800000000253402300799999999 分别作为 min_unix_microsmax_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 的示例),请参阅表修饰器和通配符函数

英文逗号运算符与表

在旧版 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 与标准 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 和标准 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 都支持在某些数据类型之间进行强制转换(自动转换)。例如,如果查询将 INT64 类型的值传递到需要 FLOAT64 作为输入的函数,则 BigQuery 会将该值强制转换为 FLOAT64。标准 SQL 不支持旧版 SQL 所支持的以下强制转换。 因此,您必须改用显式 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 添加了溢出检查,如果计算溢出则会引发错误。标准 SQL 会对 +-* 运算符以及 SUMAVGSTDDEV 聚合函数等执行溢出检查。
  • 标准 SQL 会在除数为零时引发错误,而旧版 SQL 则返回 NULL。在标准 SQL 中,如需在除数为零时返回 NULL,请使用 SAFE_DIVIDE
  • 标准 SQL 会在输入格式无效或超出目标类型范围时,对 CAST 引发错误,而旧版 SQL 则会返回 NULL。在标准 SQL 中,如需避免因无效的类型转换而引发错误,请使用 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
);

此查询返回 163716 作为计数。或者,使用 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
);

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

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

用户定义的函数主题介绍了如何将 JavaScript 用户定义的函数与标准 SQL 搭配使用。本部分介绍用户定义的函数在旧版 SQL 和标准 SQL 之间的一些主要差异。

查询文本中的函数

使用标准 SQL 时,您可以将 CREATE TEMPORARY FUNCTION 用作查询主体的一部分,而不用单独指定用户定义的函数。单独定义函数的示例包括在 BigQuery 网页界面中使用 UDF 编辑器定义,或在使用 bq 命令行界面时通过 --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 子查询,请参阅使用数组。另请参阅数组结构体对应的参考文档。