使用排序规则

使用集合让一切井井有条 根据您的偏好保存内容并对其进行分类。

排序规则简介

排序规则确定如何在排序规则支持的运算中对字符串进行排序和比较。如果要为排序规则支持的运算自定义排序规则,则必须为运算中的至少一个字符串分配排序规则规范。某些运算不能使用排序规则,但可以通过排序规则传递

受排序规则影响的运算

当运算受排序规则影响时,意味着运算在执行期间考虑排序规则。对字符串进行排序和比较时,以下查询运算受排序规则的影响:

运维 备注
排序规则支持的比较运算
联接运算
ORDER BY WINDOW 子句中,排序规则不支持 ORDER BY
GROUP BY
WINDOW(适用于窗口函数) WINDOW 子句中,排序规则不支持 ORDER BYPARTITION BY
排序规则支持的标量函数
排序规则支持的聚合函数
SET 运算

传播排序规则的运算

排序规则可以通过一些查询运算传递到查询的其他部分。排序规则在查询中经过运算时,这称为传播。在传播期间:

  • 如果输入不包含排序规则规范或空排序规则规范,而另一个输入包含显式定义的排序规则,则所有输入都使用显式定义的排序规则。
  • 所有使用非空的明确排序规则规范的输入都必须具有相同的排序规则类型,否则会抛出错误。

BigQuery 有几个可以传播排序规则的函数运算符表达式

以下示例中的 'und:ci' 排序规则从 character 列传播到 ORDER BY 运算。

-- With collation
SELECT *
FROM UNNEST([
  COLLATE('B', 'und:ci'),
  'b',
  'a'
]) AS character
ORDER BY character

+-----------+
| character |
+-----------+
| a         |
| B         |
| b         |
+-----------+
-- Without collation
SELECT *
FROM UNNEST([
  'B',
  'b',
  'a'
]) AS character
ORDER BY character

+-----------+
| character |
+-----------+
| B         |
| a         |
| b         |
+-----------+

函数

以下函数允许排序规则在它们之间传播:

函数 备注
AEAD.DECRYPT_STRING
ANY_VALUE
ARRAY_AGG 输入参数的排序规则将作为数组元素上的排序规则传播。
ARRAY_TO_STRING 数组元素的排序规则会传播到输出。
COLLATE
CONCAT
FORMAT format_string 到返回的字符串的排序规则会传播。
FORMAT_DATE format_string 到返回的字符串的排序规则会传播。
FORMAT_DATETIME format_string 到返回的字符串的排序规则会传播。
FORMAT_TIME format_string 到返回的字符串的排序规则会传播。
FORMAT_TIMESTAMP format_string 到返回的字符串的排序规则会传播。
GREATEST
LAG
LEAD
LEAST
LEFT
LOWER
LPAD
MAX
MIN
NET.HOST
NET.PUBLIC_SUFFIX
NET.REG_DOMAIN
NORMALIZE
NORMALIZE_AND_CASEFOLD
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
SOUNDEX
SPLIT 输入参数的排序规则将作为数组元素上的排序规则传播。
STRING_AGG
SUBSTR
UPPER

运算符

以下运算符允许排序规则通过它们传播:

运算符 备注
|| 串联运算符
数组下标运算符 已传播到输出。
集合运算符 输出列的排序规则由位于同一位置的输入列的排序规则决定。
STRUCT 字段访问运算符 获取 STRUCT 时,STRUCT 字段上的排序规则会作为输出排序规则传播。
UNNEST 输入数组元素的排序规则会传播到输出。

表达式

这些表达式让排序规则通过它们传播:

表达式 备注
ARRAY 构建 ARRAY 时,输入参数的排序规则会传播到 ARRAY 中的元素。
CASE
CASE 表达式
COALESCE
IF
IFNULL
NULLIF
STRUCT 构建 STRUCT 时,输入参数的排序规则会传播到 STRUCT 中的字段。

在什么位置可以分配排序规则规范

排序规则规范可以分配给以下排序规则支持的类型:

  • STRING
  • STRUCT 中的 STRING 字段
  • ARRAY 中的 STRING 元素

此外:

  • 您可以在创建或更改架构时,为架构分配默认排序规则规范。如果这些表没有自己的默认排序规则规范,这会将默认排序规则规范分配给添加到架构中的所有未来表。
  • 您可以在创建或更改表时为其分配默认排序规则规范。如果列没有排序规则规范,这将为添加到表中的所有未来支持排序规则的列分配排序规则规范。这将替换架构上的默认排序规则规范。
  • 您可以在列中为排序规则支持的类型分配排序规则规范。在其列架构中包含支持排序规则的类型的列是支持排序规则的列。这将替换表上的默认排序规则规范。
  • 您可以为排序规则支持的查询运算分配排序规则规范。
  • 您可以使用 COLLATE 函数将排序规则规范分配给排序规则支持的表达式。这将替换之前设置的所有排序规则规范。

总结:

您可以为架构定义默认排序规则规范。例如:

CREATE SCHEMA (...)
DEFAULT COLLATE 'und:ci'

您可以为表定义默认的排序规则规范。例如:

CREATE TABLE (...)
DEFAULT COLLATE 'und:ci'

您可以为排序规则支持的列定义排序规则规范。例如:

CREATE TABLE (
  case_insensitive_column STRING COLLATE 'und:ci'
)

您可以使用 COLLATE 函数为排序规则支持的表达式指定排序规则规范。例如:

SELECT COLLATE('a', 'und:ci') AS character

DDL 语句

位置 支持 备注
架构 CREATE SCHEMA 创建架构,并视需要向架构添加默认排序规则规范。
架构 ALTER SCHEMA 更新架构的默认排序规则规范。
CREATE TABLE 创建表并视需要向表添加默认排序规则规范,或向列中排序规则支持的类型添加排序规则规范。

您不能对使用 CLUSTERING 的列执行排序规则。

创建表时,您可以将排序支持的函数与 AS SELECT 配合使用。
ALTER TABLE 更新表中支持排序规则的类型的默认排序规则规范。
ADD COLUMN 将排序规则规范添加到现有表的新列中支持排序规则的类型。

数据类型

类型 支持 备注
数据类型 STRING 您可以直接将排序规则规范应用于此数据类型。
数据类型 STRUCT 您可以将排序规则规范应用于 STRUCT 中的 STRING 字段。STRUCTSTRING 字段可以具有不同的排序规则规范。
数据类型 ARRAY 您可以将排序规则规范应用于 ARRAY 中的 STRING 元素。ARRAYSTRING 元素可以具有不同的排序规则规范。

使用 COLLATE 函数将排序规则规范应用于排序规则支持的表达式。

函数、运算符和条件表达式

函数

类型 支持 备注
标量 COLLATE
标量 ENDS_WITH
标量 GREATEST
标量 INSTR
标量 LEAST
标量 REPLACE
标量 SPLIT
标量 STARTS_WITH
标量 STRPOS
汇总 COUNT 仅当输入包含 DISTINCT 参数时,此运算符才会受到排序规则的影响。
汇总 MAX
汇总 MIN

运算符

支持 备注
<
<=
>
>=
=
!=
[NOT] BETWEEN
[NOT] IN 此运算符通常支持排序规则,但不支持 [NOT] IN UNNEST。如果与列表一起使用,则至少需要列表中的一项。
[NOT] IN

条件表达式

支持
CASE
CASE 表达式
NULLIF

上述排序规则支持的运算(函数、运算符和条件表达式)可包含针对排序规则支持的类型明确定义的排序规则规范的输入。在排序规则支持的运算中:

  • 具有非空、明确定义的排序规则规范的所有输入都必须相同,否则将抛出错误。
  • 如果一个输入不包含显式定义的排序规则,而另一个输入包含显式定义的排序规则,则显式定义的排序规则用于两者。

例如:

-- Assume there is a table with this column declaration:
CREATE TABLE table_a
(
    col_a STRING COLLATE 'und:ci',
    col_b STRING COLLATE '',
    col_c STRING,
    col_d STRING COLLATE 'und:ci'
);

-- This runs. Column 'b' has a collation specification and the
-- column 'c' does not.
SELECT STARTS_WITH(col_b_expression, col_c_expression)
FROM table_a;

-- This runs. Column 'a' and 'd' have the same collation specification.
SELECT STARTS_WITH(col_a_expression, col_d_expression)
FROM table_a;

-- This runs. Even though column 'a' and 'b' have different
-- collation specifications, column 'b' is considered the default collation
-- because it's assigned to an empty collation specification.
SELECT STARTS_WITH(col_a_expression, col_b_expression)
FROM table_a;

-- This works. Even though column 'a' and 'b' have different
-- collation specifications, column 'b' is updated to use the same
-- collation specification as column 'a'.
SELECT STARTS_WITH(col_a_expression, COLLATE(col_b_expression, 'und:ci'))
FROM table_a;

-- This runs. Column 'c' does not have a collation specification, so it uses the
-- collation specification of column 'd'.
SELECT STARTS_WITH(col_c_expression, col_d_expression)
FROM table_a;

排序规则规范详情

排序规则规范确定如何在排序规则支持的运算中对字符串进行排序和比较。您可以为排序规则支持的类型定义 Unicode 排序规则规范 und:ci

如果未定义排序规则规范,则使用默认排序规则规范。如需了解详情,请参阅后续部分。

默认排序规则规范

排序规则规范未分配或为空时,使用 'binary' 排序规则。二进制排序规则指示该运算应以 Unicode 代码点顺序返回数据。您无法明确设置二进制排序规则。

Unicode 排序规则规范

collation_specification:
  'language_tag:collation_attribute'

Unicode 排序规则指示该运算应使用 Unicode 排序算法对字符串进行排序和比较。排序规则规范可以是 STRING 字面量或查询参数。

语言标记

语言标记决定了通常如何对字符串进行排序和比较。允许的 language_tag 值包括:

  • und:表示未确定的语言区域的语言区域字符串。und 是在 IANA 语言子标记注册表中定义的特殊语言标记,用于表示未确定的语言区域。这也称为“根”语言区域,可视为默认 Unicode 排序规则。它定义了与语言区域无关的合理排序规则。

排序规则特性

除了语言标记之外,Unicode 排序规则规范还必须有一个 collation_attribute,用于启用对字符串进行排序和比较的其他规则。允许的值为:

  • ci:排序规则不区分大小写。

排序规则规范示例

COLLATE 函数中与 und 语言标记结合使用时,ci 排序规则特性如下所示:

COLLATE('orange1', 'und:ci')

注意事项

  • 不同的字符串可以被视为相等。例如, (LATIN CAPITAL LETTER SHARP S) 在主要级层被视为等于 'SS',因此 'ẞ1' < 'SS2'。这与不区分大小写的工作方式类似。
  • 有很多 unicode 代码点(标点符号、符号等)会被视为不存在。因此,无论是否包含这些代码点,字符串都会以相同的方式排序。例如,在对以下字符串排序时,系统会忽略格式控制代码点 U+2060:

    SELECT *
    FROM UNNEST([
      COLLATE('oran\u2060ge1', 'und:ci'),
      COLLATE('\u2060orange2', 'und:ci'),
      COLLATE('orange3', 'und:ci')
    ]) AS fruit
    ORDER BY fruit
    
    +---------+
    | fruit   |
    +---------+
    | orange1 |
    | orange2 |
    | orange3 |
    +---------+
    
  • 顺序可以更改。und 排序规则的 Unicode 规范偶尔会发生变化,这可能会影响排序顺序。

限制

前面的部分介绍了受支持函数的限制,但需要牢记以下常规限制:

  • 支持 und:ci 和空排序规则,但不支持其他排序规则规范。
  • 不支持排序规则的运算和函数在遇到排序值时会产生错误。
  • 您无法使用 API 调用或 DDL 查询在聚簇字段上设置非空排序规则。

    CREATE TABLE mydataset.mytable
    (
      word STRING COLLATE 'und:ci',
      number INT64
    )
    CLUSTER BY word;
    
    -- User error:
    -- “CLUSTER BY STRING column word with
    -- collation und:ci is not supported"
    
  • 不支持具有排序输出列的具体化视图。

    CREATE MATERIALIZED VIEW bigquery.public.data.samples.shakespeare
    AS SELECT COLLATE('abc', 'und:ci') x;
    
    -- User error:
    -- “Creating MATERIALIZED VIEW with collation
    -- on the output column is not supported”
    
  • 不支持具有排序输出列的视图。

    CREATE VIEW bigquery.public.data.samples.shakespeare
    AS SELECT COLLATE('abc', 'und:ci') x;
    
    -- User error:
    -- "Creating VIEW with collation on the output column
    -- is not supported"
    
  • 创建视图或具体化视图后,基表的列排序规则会发生变化,以使列经过排序,从而使视图或具体化视图无效且无法查询。

    CREATE TABLE dataset.base (
      col_no_collation STRING,
      col INT64
    )
    AS SELECT 'a', 1
    UNION ALL SELECT 'b', 2
    UNION ALL SELECT 'A', 3
    UNION ALL SELECT 'B', 4;
    
    CREATE VIEW/MATERIALIZED VIEW dataset.view AS SELECT * FROM dataset.base;
    
    -- Change the collation of string column of the base table so
    -- that the string column of view/materialized view is collated.
    ALTER TABLE dataset.base ALTER COLUMN col_no_collation SET DATA TYPE STRING COLLATE 'und:ci';
    
    -- Should return an error since the column of view/materialized
    -- contains collation.
    SELECT * FROM dataset.view ORDER BY col;
    
    -- User error:
    -- "Collation on the output column for view dataset.view is
    -- not supported"
    
  • 您不能在聚合函数中使用具体化的排序键创建具体化视图。

    CREATE TABLE dataset.base (
      col_no_collation STRING,
      col INT64
    )
    AS SELECT 'a', 1
    UNION ALL SELECT 'b', 2
    UNION ALL SELECT 'A', 3
    UNION ALL SELECT 'B', 4;
    
    CREATE VIEW/MATERIALIZED VIEW dataset.view AS SELECT * FROM dataset.base;
    
    -- Change the collation of string column of the base table so
    -- that the string column of view/materialized view is
    -- collated.
    ALTER TABLE dataset.base ALTER COLUMN col_no_collation SET DATA TYPE STRING COLLATE 'und:ci';
    
    -- Should return an error since the column of view/materialized
    -- contains collation.
    SELECT * FROM dataset.view ORDER BY col;
    
    -- User error:
    -- "Collation on the output column for view dataset.view is
    -- not supported"
    
  • 不支持具有排序输出列的表函数。

    CREATE TABLE FUNCTION bigquery.public.data.samples.shakespeare()
    AS SELECT COLLATE('abc', 'und:ci') x;
    
    -- User error:
    -- "Creating TABLE FUNCTION with collation on the output
    -- column is not supported"
    
  • 用户定义的函数 (UDF) 不能使用排序参数。

    CREATE FUNCTION tmp_dataset.f(x STRING) AS (x);
    
    SELECT tmp_dataset.f(col_ci)
    FROM shared_dataset.table_collation_simple
    
    -- User error:
    -- “Collation is not allowed on argument x ("und:ci").
    -- Use COLLATE(arg, '') to remove collation at [1:8]”
    
  • 如果具有用户指定的目标表的 SELECT 查询生成具有排序规则的输出列,则会产生错误。

    -- The following query returns an error since the resulting schema of
    -- the query has a column string_ci with collation ‘und:ci’.
    SELECT string_ci FROM collated_table
    
  • BigQuery BI 引擎不支持具有排序规则或 COLLATE 函数的表。