ORDER BY 子句
ORDER BY 子句包含
- 表达式列表，例如
ORDER BY visits, search_phrase， - 引用
SELECT子句中列的数字列表，例如ORDER BY 2, 1，或者 ALL，表示SELECT子句中的所有列，例如ORDER BY ALL。
要禁用按列号排序，请设置配置项 enable_positional_arguments = 0。要禁用按 ALL 排序，请设置配置项 enable_order_by_all = 0。
ORDER BY 子句可以通过 DESC（降序）或 ASC（升序）修饰符进行修饰，以确定排序方向。除非指定了明确的排序顺序，否则默认使用 ASC。排序方向应用于单个表达式，而不是整个列表，例如 ORDER BY Visits DESC, SearchPhrase。此外，排序区分大小写。
对于具有相同排序表达式值的行，将以任意且非确定性的顺序返回。如果在 SELECT 语句中省略了 ORDER BY 子句，则行顺序也是任意的，且非确定性的。
特殊值的排序
对于 NaN 和 NULL 的排序顺序，有两种方法
- 默认情况下或使用
NULLS LAST修饰符：首先是值，然后是NaN，最后是NULL。 - 使用
NULLS FIRST修饰符：首先是NULL，然后是NaN，最后是其他值。
示例
对于表
┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │ 2 │
│ 1 │ nan │
│ 2 │ 2 │
│ 3 │ 4 │
│ 5 │ 6 │
│ 6 │ nan │
│ 7 │ ᴺᵁᴸᴸ │
│ 6 │ 7 │
│ 8 │ 9 │
└───┴──────┘
运行查询 SELECT * FROM t_null_nan ORDER BY y NULLS FIRST 获取
┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
│ 7 │ ᴺᵁᴸᴸ │
│ 1 │ nan │
│ 6 │ nan │
│ 2 │ 2 │
│ 2 │ 2 │
│ 3 │ 4 │
│ 5 │ 6 │
│ 6 │ 7 │
│ 8 │ 9 │
└───┴──────┘
对浮点数进行排序时，NaN 与其他值是分开的。无论排序顺序如何，NaN 都排在最后。换句话说，对于升序排序，它们被放置在比所有其他数字都大的位置，而对于降序排序，它们被放置在比其他数字都小的位置。
排序规则支持
对于按 字符串 值排序，您可以指定排序规则（比较）。例如：ORDER BY SearchPhrase COLLATE 'tr' - 按关键字升序排序，使用土耳其字母表，不区分大小写，假设字符串是 UTF-8 编码的。COLLATE 可以为 ORDER BY 中的每个表达式独立指定或不指定。如果指定了 ASC 或 DESC，则 COLLATE 在其之后指定。使用 COLLATE 时，排序始终不区分大小写。
我们仅建议对少量行进行最终排序时使用 COLLATE，因为使用 COLLATE 进行排序效率低于按字节进行正常排序。
排序规则示例
仅使用 字符串 值的示例
输入表
┌─x─┬─s────┐
│ 1 │ bca │
│ 2 │ ABC │
│ 3 │ 123a │
│ 4 │ abc │
│ 5 │ BCA │
└───┴──────┘
查询
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
结果
┌─x─┬─s────┐
│ 3 │ 123a │
│ 4 │ abc │
│ 2 │ ABC │
│ 1 │ bca │
│ 5 │ BCA │
└───┴──────┘
使用 可空 的示例
输入表
┌─x─┬─s────┐
│ 1 │ bca │
│ 2 │ ᴺᵁᴸᴸ │
│ 3 │ ABC │
│ 4 │ 123a │
│ 5 │ abc │
│ 6 │ ᴺᵁᴸᴸ │
│ 7 │ BCA │
└───┴──────┘
查询
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
结果
┌─x─┬─s────┐
│ 4 │ 123a │
│ 5 │ abc │
│ 3 │ ABC │
│ 1 │ bca │
│ 7 │ BCA │
│ 6 │ ᴺᵁᴸᴸ │
│ 2 │ ᴺᵁᴸᴸ │
└───┴──────┘
使用 数组 的示例
输入表
┌─x─┬─s─────────────┐
│ 1 │ ['Z'] │
│ 2 │ ['z'] │
│ 3 │ ['a'] │
│ 4 │ ['A'] │
│ 5 │ ['z','a'] │
│ 6 │ ['z','a','a'] │
│ 7 │ [''] │
└───┴───────────────┘
查询
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
结果
┌─x─┬─s─────────────┐
│ 7 │ [''] │
│ 3 │ ['a'] │
│ 4 │ ['A'] │
│ 2 │ ['z'] │
│ 5 │ ['z','a'] │
│ 6 │ ['z','a','a'] │
│ 1 │ ['Z'] │
└───┴───────────────┘
使用 低基数 字符串的示例
输入表
┌─x─┬─s───┐
│ 1 │ Z │
│ 2 │ z │
│ 3 │ a │
│ 4 │ A │
│ 5 │ za │
│ 6 │ zaa │
│ 7 │ │
└───┴─────┘
查询
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
结果
┌─x─┬─s───┐
│ 7 │ │
│ 3 │ a │
│ 4 │ A │
│ 2 │ z │
│ 1 │ Z │
│ 5 │ za │
│ 6 │ zaa │
└───┴─────┘
使用 元组 的示例
┌─x─┬─s───────┐
│ 1 │ (1,'Z') │
│ 2 │ (1,'z') │
│ 3 │ (1,'a') │
│ 4 │ (2,'z') │
│ 5 │ (1,'A') │
│ 6 │ (2,'Z') │
│ 7 │ (2,'A') │
└───┴─────────┘
查询
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
结果
┌─x─┬─s───────┐
│ 3 │ (1,'a') │
│ 5 │ (1,'A') │
│ 2 │ (1,'z') │
│ 1 │ (1,'Z') │
│ 7 │ (2,'A') │
│ 4 │ (2,'z') │
│ 6 │ (2,'Z') │
└───┴─────────┘
实现细节
如果除了 ORDER BY 之外还指定了足够小的 LIMIT，则会使用更少的 RAM。否则，使用的内存量与用于排序的数据量成正比。对于分布式查询处理，如果省略了 GROUP BY，则排序会在远程服务器上部分完成，结果将在请求者服务器上合并。这意味着对于分布式排序，要排序的数据量可能大于单个服务器上的内存量。
如果 RAM 不足，可以将排序在外部内存中执行（在磁盘上创建临时文件）。为此，请使用配置项 max_bytes_before_external_sort。如果将其设置为 0（默认值），则外部排序将被禁用。如果启用，当要排序的数据量达到指定的字节数时，收集到的数据将被排序并转储到一个临时文件中。读取完所有数据后，将合并所有排序文件，并输出结果。文件将写入配置中的 /var/lib/clickhouse/tmp/ 目录（默认情况下，但您可以使用 tmp_path 参数更改此设置）。
运行查询可能使用的内存超过 max_bytes_before_external_sort。因此，此配置项的值必须明显小于 max_memory_usage。例如，如果您的服务器拥有 128 GB 的 RAM 并且您需要运行单个查询，请将 max_memory_usage 设置为 100 GB，将 max_bytes_before_external_sort 设置为 80 GB。
外部排序的效率远低于在 RAM 中排序。
数据读取优化
如果 ORDER BY 表达式具有与表排序键匹配的前缀，您可以使用配置项 optimize_read_in_order 优化查询。
当启用 optimize_read_in_order 配置项时，ClickHouse 服务器会使用表索引并按 ORDER BY 键的顺序读取数据。这样可以避免在指定了 LIMIT 时读取所有数据。因此，对具有较小限制的大量数据进行的查询处理速度更快。
优化适用于 ASC 和 DESC，但不适用于 GROUP BY 子句和 FINAL 修饰符。
当禁用 optimize_read_in_order 配置项时，ClickHouse 服务器在处理 SELECT 查询时不会使用表索引。
在运行具有 ORDER BY 子句、较大 LIMIT 和需要在找到查询数据之前读取大量记录的 WHERE 条件的查询时，请考虑手动禁用 optimize_read_in_order。
优化支持以下表引擎
在 MaterializedView 引擎表中，优化适用于类似于 SELECT ... FROM merge_tree_table ORDER BY pk 的视图。但是，如果视图查询没有 ORDER BY 子句，则在类似于 SELECT ... FROM view ORDER BY pk 的查询中不支持它。
ORDER BY Expr WITH FILL 修饰符
此修饰符也可以与 LIMIT ... WITH TIES 修饰符 结合使用。
WITH FILL 修饰符可以在 ORDER BY expr 后设置，并包含可选的 FROM expr、TO expr 和 STEP expr 参数。expr 列的所有缺失值将按顺序填充，其他列将填充为默认值。
要填充多个列，请在 ORDER BY 部分的每个字段名称后面添加 WITH FILL 修饰符（包含可选参数）。
ORDER BY expr [WITH FILL] [FROM const_expr] [TO const_expr] [STEP const_numeric_expr], ... exprN [WITH FILL] [FROM expr] [TO expr] [STEP numeric_expr]
[INTERPOLATE [(col [AS expr], ... colN [AS exprN])]]
WITH FILL 可以应用于数值类型（所有浮点数、小数、整数）或日期/时间类型字段。当应用于String 字段时，缺失值将填充为空字符串。当未定义FROM const_expr 填充序列时，将使用ORDER BY 中的最小expr 字段值。当未定义TO const_expr 填充序列时，将使用ORDER BY 中的最大expr 字段值。当定义了STEP const_numeric_expr 时，对于数值类型，const_numeric_expr 将按原样解释；对于日期类型，将解释为days；对于时间类型，将解释为seconds。它还支持INTERVAL 数据类型，用于表示时间和日期间隔。当省略STEP const_numeric_expr 时，填充序列将使用1.0 作为数值类型，1 day 作为日期类型，以及1 second 作为时间类型。INTERPOLATE 可以应用于未参与ORDER BY WITH FILL 的列。此类列将根据先前的字段值通过应用expr 进行填充。如果expr 不存在，将重复先前的值。省略列表将导致包含所有允许的列。
没有WITH FILL 的查询示例
SELECT n, source FROM (
SELECT toFloat32(number % 10) AS n, 'original' AS source
FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n;
结果
┌─n─┬─source───┐
│ 1 │ original │
│ 4 │ original │
│ 7 │ original │
└───┴──────────┘
应用WITH FILL 修饰符后的相同查询
SELECT n, source FROM (
SELECT toFloat32(number % 10) AS n, 'original' AS source
FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5;
结果
┌───n─┬─source───┐
│ 0 │ │
│ 0.5 │ │
│ 1 │ original │
│ 1.5 │ │
│ 2 │ │
│ 2.5 │ │
│ 3 │ │
│ 3.5 │ │
│ 4 │ original │
│ 4.5 │ │
│ 5 │ │
│ 5.5 │ │
│ 7 │ original │
└─────┴──────────┘
对于多个字段的情况，ORDER BY field2 WITH FILL, field1 WITH FILL 的填充顺序将遵循ORDER BY 子句中字段的顺序。
示例
SELECT
toDate((number * 10) * 86400) AS d1,
toDate(number * 86400) AS d2,
'original' AS source
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY
d2 WITH FILL,
d1 WITH FILL STEP 5;
结果
┌───d1───────┬───d2───────┬─source───┐
│ 1970-01-11 │ 1970-01-02 │ original │
│ 1970-01-01 │ 1970-01-03 │ │
│ 1970-01-01 │ 1970-01-04 │ │
│ 1970-02-10 │ 1970-01-05 │ original │
│ 1970-01-01 │ 1970-01-06 │ │
│ 1970-01-01 │ 1970-01-07 │ │
│ 1970-03-12 │ 1970-01-08 │ original │
└────────────┴────────────┴──────────┘
字段d1 不会填充并使用默认值，因为我们没有d2 值的重复值，并且无法正确计算d1 的序列。
在ORDER BY 中更改字段后的以下查询
SELECT
toDate((number * 10) * 86400) AS d1,
toDate(number * 86400) AS d2,
'original' AS source
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY
d1 WITH FILL STEP 5,
d2 WITH FILL;
结果
┌───d1───────┬───d2───────┬─source───┐
│ 1970-01-11 │ 1970-01-02 │ original │
│ 1970-01-16 │ 1970-01-01 │ │
│ 1970-01-21 │ 1970-01-01 │ │
│ 1970-01-26 │ 1970-01-01 │ │
│ 1970-01-31 │ 1970-01-01 │ │
│ 1970-02-05 │ 1970-01-01 │ │
│ 1970-02-10 │ 1970-01-05 │ original │
│ 1970-02-15 │ 1970-01-01 │ │
│ 1970-02-20 │ 1970-01-01 │ │
│ 1970-02-25 │ 1970-01-01 │ │
│ 1970-03-02 │ 1970-01-01 │ │
│ 1970-03-07 │ 1970-01-01 │ │
│ 1970-03-12 │ 1970-01-08 │ original │
└────────────┴────────────┴──────────┘
以下查询使用INTERVAL 数据类型，每个填充在d1 列上的数据为 1 天
SELECT
toDate((number * 10) * 86400) AS d1,
toDate(number * 86400) AS d2,
'original' AS source
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY
d1 WITH FILL STEP INTERVAL 1 DAY,
d2 WITH FILL;
结果
┌─────────d1─┬─────────d2─┬─source───┐
│ 1970-01-11 │ 1970-01-02 │ original │
│ 1970-01-12 │ 1970-01-01 │ │
│ 1970-01-13 │ 1970-01-01 │ │
│ 1970-01-14 │ 1970-01-01 │ │
│ 1970-01-15 │ 1970-01-01 │ │
│ 1970-01-16 │ 1970-01-01 │ │
│ 1970-01-17 │ 1970-01-01 │ │
│ 1970-01-18 │ 1970-01-01 │ │
│ 1970-01-19 │ 1970-01-01 │ │
│ 1970-01-20 │ 1970-01-01 │ │
│ 1970-01-21 │ 1970-01-01 │ │
│ 1970-01-22 │ 1970-01-01 │ │
│ 1970-01-23 │ 1970-01-01 │ │
│ 1970-01-24 │ 1970-01-01 │ │
│ 1970-01-25 │ 1970-01-01 │ │
│ 1970-01-26 │ 1970-01-01 │ │
│ 1970-01-27 │ 1970-01-01 │ │
│ 1970-01-28 │ 1970-01-01 │ │
│ 1970-01-29 │ 1970-01-01 │ │
│ 1970-01-30 │ 1970-01-01 │ │
│ 1970-01-31 │ 1970-01-01 │ │
│ 1970-02-01 │ 1970-01-01 │ │
│ 1970-02-02 │ 1970-01-01 │ │
│ 1970-02-03 │ 1970-01-01 │ │
│ 1970-02-04 │ 1970-01-01 │ │
│ 1970-02-05 │ 1970-01-01 │ │
│ 1970-02-06 │ 1970-01-01 │ │
│ 1970-02-07 │ 1970-01-01 │ │
│ 1970-02-08 │ 1970-01-01 │ │
│ 1970-02-09 │ 1970-01-01 │ │
│ 1970-02-10 │ 1970-01-05 │ original │
│ 1970-02-11 │ 1970-01-01 │ │
│ 1970-02-12 │ 1970-01-01 │ │
│ 1970-02-13 │ 1970-01-01 │ │
│ 1970-02-14 │ 1970-01-01 │ │
│ 1970-02-15 │ 1970-01-01 │ │
│ 1970-02-16 │ 1970-01-01 │ │
│ 1970-02-17 │ 1970-01-01 │ │
│ 1970-02-18 │ 1970-01-01 │ │
│ 1970-02-19 │ 1970-01-01 │ │
│ 1970-02-20 │ 1970-01-01 │ │
│ 1970-02-21 │ 1970-01-01 │ │
│ 1970-02-22 │ 1970-01-01 │ │
│ 1970-02-23 │ 1970-01-01 │ │
│ 1970-02-24 │ 1970-01-01 │ │
│ 1970-02-25 │ 1970-01-01 │ │
│ 1970-02-26 │ 1970-01-01 │ │
│ 1970-02-27 │ 1970-01-01 │ │
│ 1970-02-28 │ 1970-01-01 │ │
│ 1970-03-01 │ 1970-01-01 │ │
│ 1970-03-02 │ 1970-01-01 │ │
│ 1970-03-03 │ 1970-01-01 │ │
│ 1970-03-04 │ 1970-01-01 │ │
│ 1970-03-05 │ 1970-01-01 │ │
│ 1970-03-06 │ 1970-01-01 │ │
│ 1970-03-07 │ 1970-01-01 │ │
│ 1970-03-08 │ 1970-01-01 │ │
│ 1970-03-09 │ 1970-01-01 │ │
│ 1970-03-10 │ 1970-01-01 │ │
│ 1970-03-11 │ 1970-01-01 │ │
│ 1970-03-12 │ 1970-01-08 │ original │
└────────────┴────────────┴──────────┘
没有INTERPOLATE 的查询示例
SELECT n, source, inter FROM (
SELECT toFloat32(number % 10) AS n, 'original' AS source, number as inter
FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5;
结果
┌───n─┬─source───┬─inter─┐
│ 0 │ │ 0 │
│ 0.5 │ │ 0 │
│ 1 │ original │ 1 │
│ 1.5 │ │ 0 │
│ 2 │ │ 0 │
│ 2.5 │ │ 0 │
│ 3 │ │ 0 │
│ 3.5 │ │ 0 │
│ 4 │ original │ 4 │
│ 4.5 │ │ 0 │
│ 5 │ │ 0 │
│ 5.5 │ │ 0 │
│ 7 │ original │ 7 │
└─────┴──────────┴───────┘
应用INTERPOLATE 后的相同查询
SELECT n, source, inter FROM (
SELECT toFloat32(number % 10) AS n, 'original' AS source, number as inter
FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5 INTERPOLATE (inter AS inter + 1);
结果
┌───n─┬─source───┬─inter─┐
│ 0 │ │ 0 │
│ 0.5 │ │ 0 │
│ 1 │ original │ 1 │
│ 1.5 │ │ 2 │
│ 2 │ │ 3 │
│ 2.5 │ │ 4 │
│ 3 │ │ 5 │
│ 3.5 │ │ 6 │
│ 4 │ original │ 4 │
│ 4.5 │ │ 5 │
│ 5 │ │ 6 │
│ 5.5 │ │ 7 │
│ 7 │ original │ 7 │
└─────┴──────────┴───────┘
按排序前缀分组填充
对在特定列中具有相同值的独立行进行填充可能很有用，例如，填充时间序列中的缺失值。假设有以下时间序列表
CREATE TABLE timeseries
(
`sensor_id` UInt64,
`timestamp` DateTime64(3, 'UTC'),
`value` Float64
)
ENGINE = Memory;
SELECT * FROM timeseries;
┌─sensor_id─┬───────────────timestamp─┬─value─┐
│ 234 │ 2021-12-01 00:00:03.000 │ 3 │
│ 432 │ 2021-12-01 00:00:01.000 │ 1 │
│ 234 │ 2021-12-01 00:00:07.000 │ 7 │
│ 432 │ 2021-12-01 00:00:05.000 │ 5 │
└───────────┴─────────────────────────┴───────┘
我们希望以 1 秒的间隔独立地为每个传感器填充缺失值。实现方法是使用sensor_id 列作为填充列timestamp 的排序前缀
SELECT *
FROM timeseries
ORDER BY
sensor_id,
timestamp WITH FILL
INTERPOLATE ( value AS 9999 )
┌─sensor_id─┬───────────────timestamp─┬─value─┐
│ 234 │ 2021-12-01 00:00:03.000 │ 3 │
│ 234 │ 2021-12-01 00:00:04.000 │ 9999 │
│ 234 │ 2021-12-01 00:00:05.000 │ 9999 │
│ 234 │ 2021-12-01 00:00:06.000 │ 9999 │
│ 234 │ 2021-12-01 00:00:07.000 │ 7 │
│ 432 │ 2021-12-01 00:00:01.000 │ 1 │
│ 432 │ 2021-12-01 00:00:02.000 │ 9999 │
│ 432 │ 2021-12-01 00:00:03.000 │ 9999 │
│ 432 │ 2021-12-01 00:00:04.000 │ 9999 │
│ 432 │ 2021-12-01 00:00:05.000 │ 5 │
└───────────┴─────────────────────────┴───────┘
在此，value 列用9999 进行插值，只是为了使填充的行更加明显。此行为受use_with_fill_by_sorting_prefix 设置控制（默认情况下启用）