Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

ORDER BY

View as MarkdownEdit this pageReport an issueSet up docs MCP

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 都会排在末尾。换句话说，在升序排序中，它们会被视为大于所有其他数字；而在降序排序中，它们会被视为小于其余数字。

排序规则支持

对于按 String 值排序，可以指定排序规则 (比较规则) 。示例：ORDER BY SearchPhrase COLLATE 'tr' —— 使用土耳其字母表按关键字升序排序，不区分大小写，并假定字符串采用 UTF-8 编码。ORDER BY 中的每个 表达式 都可以单独指定或不指定 COLLATE。如果指定了 ASC 或 DESC，则 COLLATE 应写在其后。使用 COLLATE 时，排序始终不区分大小写。

LowCardinality、Nullable、Array 和 Tuple 支持 COLLATE。

我们仅建议将 COLLATE 用于少量行的最终排序，因为使用 COLLATE 排序的效率低于普通的按字节排序。

排序规则示例

仅使用 String 值的示例：

输入表：

┌─x─┬─s────┐
│ 1 │ bca  │
│ 2 │ ABC  │
│ 3 │ 123a │
│ 4 │ abc  │
│ 5 │ BCA  │
└───┴──────┘
Querysql
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
Responsetext
┌─x─┬─s────┐
│ 3 │ 123a │
│ 4 │ abc  │
│ 2 │ ABC  │
│ 1 │ bca  │
│ 5 │ BCA  │
└───┴──────┘

使用 Nullable 的示例：

输入表：

┌─x─┬─s────┐
│ 1 │ bca  │
│ 2 │ ᴺᵁᴸᴸ │
│ 3 │ ABC  │
│ 4 │ 123a │
│ 5 │ abc  │
│ 6 │ ᴺᵁᴸᴸ │
│ 7 │ BCA  │
└───┴──────┘
Querysql
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
Responsetext
┌─x─┬─s────┐
│ 4 │ 123a │
│ 5 │ abc  │
│ 3 │ ABC  │
│ 1 │ bca  │
│ 7 │ BCA  │
│ 6 │ ᴺᵁᴸᴸ │
│ 2 │ ᴺᵁᴸᴸ │
└───┴──────┘

使用 Array 的示例：

输入表：

┌─x─┬─s─────────────┐
│ 1 │ ['Z']         │
│ 2 │ ['z']         │
│ 3 │ ['a']         │
│ 4 │ ['A']         │
│ 5 │ ['z','a']     │
│ 6 │ ['z','a','a'] │
│ 7 │ ['']          │
└───┴───────────────┘
Querysql
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
Responsetext
┌─x─┬─s─────────────┐
│ 7 │ ['']          │
│ 3 │ ['a']         │
│ 4 │ ['A']         │
│ 2 │ ['z']         │
│ 5 │ ['z','a']     │
│ 6 │ ['z','a','a'] │
│ 1 │ ['Z']         │
└───┴───────────────┘

LowCardinality 字符串示例：

输入表：

┌─x─┬─s───┐
│ 1 │ Z   │
│ 2 │ z   │
│ 3 │ a   │
│ 4 │ A   │
│ 5 │ za  │
│ 6 │ zaa │
│ 7 │     │
└───┴─────┘
Querysql
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
Responseresponse
┌─x─┬─s───┐
│ 7 │     │
│ 3 │ a   │
│ 4 │ A   │
│ 2 │ z   │
│ 1 │ Z   │
│ 5 │ za  │
│ 6 │ zaa │
└───┴─────┘

使用 Tuple 的示例：

Responseresponse
┌─x─┬─s───────┐
│ 1 │ (1,'Z') │
│ 2 │ (1,'z') │
│ 3 │ (1,'a') │
│ 4 │ (2,'z') │
│ 5 │ (1,'A') │
│ 6 │ (2,'Z') │
│ 7 │ (2,'A') │
└───┴─────────┘
Querysql
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';
Responseresponse
┌─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，内存占用就会更少。否则，排序消耗的内存量与待排序数据量成正比。对于分布式查询处理，如果省略了 GROUP BY，排序会在远程服务器上部分完成，并在发起请求的服务器上合并结果。这意味着在分布式排序中，待排序的数据量可能会超过单台服务器的内存容量。

如果 RAM 不足，可以在外部内存中执行排序 (即在磁盘上创建临时 File) 。为此，请使用设置 max_bytes_before_external_sort。如果将其设为 0 (默认值) ，则外部排序会被禁用。如果启用该功能，当待排序数据量达到指定字节数时，已收集的数据会先排序并转储到临时文件中。读取完所有数据后，再将所有已排序的文件合并并输出结果。文件会写入配置中的 /var/lib/clickhouse/tmp/ 目录 (默认如此，但你也可以使用 tmp_path 参数更改此设置) 。你还可以仅在查询超出内存限制时启用落盘，也就是说，max_bytes_ratio_before_external_sort=0.6 会仅在查询达到内存限制 (用户/服务器) 的 60% 后启用落盘。

运行查询时，实际使用的内存可能会超过 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 表达式的前缀与表的 sorting key 一致，可以通过使用 optimize_read_in_order 设置来优化查询。

启用 optimize_read_in_order 设置后，ClickHouse server 会使用表索引，并按 ORDER BY 键的顺序读取数据。这样，在指定了 LIMIT 的情况下，就可以避免读取全部数据。因此，对于 LIMIT 较小的大数据查询，处理速度会更快。

该优化同时适用于 ASC 和 DESC，但不能与 GROUP BY 子句和 FINAL 修饰符 一起使用。

禁用 optimize_read_in_order 设置后，ClickHouse server 在处理 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 修饰符。

Querysql
ORDER BY expr [WITH FILL] [FROM const_expr] [TO const_expr] [STEP const_numeric_expr] [STALENESS const_numeric_expr], ... exprN [WITH FILL] [FROM expr] [TO expr] [STEP numeric_expr] [STALENESS numeric_expr]
[INTERPOLATE [(col [AS expr], ... colN [AS exprN])]]

WITH FILL 可用于 Numeric (所有类型的 float、decimal、int) 或 Date/DateTime 类型的字段。用于 String 字段时，缺失值会用空字符串填充。 如果未定义 FROM const_expr，则填充序列将使用 ORDER BY 中 expr 字段的最小值。 如果未定义 TO const_expr，则填充序列将使用 ORDER BY 中 expr 字段的最大值。 如果定义了 STEP const_numeric_expr，则对于数值类型，const_numeric_expr 按原样解释；对于 Date 类型，按 days 解释；对于 DateTime 类型，按 seconds 解释。它还支持表示时间和日期时间间隔的 INTERVAL 数据类型。 如果省略 STEP const_numeric_expr，则填充序列对数值类型使用 1.0，对 Date 类型使用 1 day，对 DateTime 类型使用 1 second。 如果定义了 STALENESS const_numeric_expr，查询将持续生成行，直到原始数据中当前行与前一行的差值超过 const_numeric_expr。 INTERPOLATE 可用于未参与 ORDER BY WITH FILL 的列。这些列会通过应用 expr，根据前一个字段的值进行填充。如果未指定 expr，则会重复前一个值。省略列列表则表示包含所有允许的列。

不使用 WITH FILL 的查询示例：

Querysql
SELECT n, source FROM (
   SELECT toFloat32(number % 10) AS n, 'original' AS source
   FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n;
Responsetext
┌─n─┬─source───┐
│ 1 │ original │
│ 4 │ original │
│ 7 │ original │
└───┴──────────┘

应用 WITH FILL 修饰符后的同一查询：

Querysql
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;
Responsetext
┌───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 子句中字段的顺序进行。

示例：

Querysql
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;
Responsetext
┌───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 中字段后的查询：

Querysql
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;
Responsetext
┌───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 │
└────────────┴────────────┴──────────┘

以下查询对列 d1 中填充的每条数据使用了 1 天的 INTERVAL 数据类型：

Querysql
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;
Responseresponse
┌─────────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 │
└────────────┴────────────┴──────────┘

不使用 STALENESS 的查询示例：

Querysql
SELECT number AS key, 5 * number value, 'original' AS source
FROM numbers(16) WHERE key % 5 == 0
ORDER BY key WITH FILL;
Responsetext
    ┌─key─┬─value─┬─source───┐
 1. │   0 │     0 │ original │
 2. │   1 │     0 │          │
 3. │   2 │     0 │          │
 4. │   3 │     0 │          │
 5. │   4 │     0 │          │
 6. │   5 │    25 │ original │
 7. │   6 │     0 │          │
 8. │   7 │     0 │          │
 9. │   8 │     0 │          │
10. │   9 │     0 │          │
11. │  10 │    50 │ original │
12. │  11 │     0 │          │
13. │  12 │     0 │          │
14. │  13 │     0 │          │
15. │  14 │     0 │          │
16. │  15 │    75 │ original │
    └─────┴───────┴──────────┘

应用 STALENESS 3 后的同一查询：

Querysql
SELECT number AS key, 5 * number value, 'original' AS source
FROM numbers(16) WHERE key % 5 == 0
ORDER BY key WITH FILL STALENESS 3;
Responsetext
    ┌─key─┬─value─┬─source───┐
 1. │   0 │     0 │ original │
 2. │   1 │     0 │          │
 3. │   2 │     0 │          │
 4. │   5 │    25 │ original │
 5. │   6 │     0 │          │
 6. │   7 │     0 │          │
 7. │  10 │    50 │ original │
 8. │  11 │     0 │          │
 9. │  12 │     0 │          │
10. │  15 │    75 │ original │
11. │  16 │     0 │          │
12. │  17 │     0 │          │
    └─────┴───────┴──────────┘

不使用 INTERPOLATE 的查询示例：

Querysql
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;
Responsetext
┌───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 后的同一查询：

Querysql
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);
Responsetext
┌───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;

┌

并且我们希望按 1 秒的时间间隔，为每个传感器分别补齐缺失值。 实现这一点的方法是将 sensor_id 列用作填充列 timestamp 的排序前缀：

SELECT *
FROM timeseries
ORDER BY
    sensor_id,
    timestamp WITH FILL
INTERPOLATE ( value AS 9999 )

┌

这里将 value 列插值为 9999，只是为了让填充出的行更醒目。 此行为由设置项 use_with_fill_by_sorting_prefix 控制 (默认启用)

相关内容

Navigation