SELECT 查询

SELECT 查询执行数据检索。默认情况下，请求的数据返回给客户端，同时与 INSERT INTO 结合使用时，它可以转发到不同的表。

语法

[WITH expr_list(subquery)]
SELECT [DISTINCT [ON (column1, column2, ...)]] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table [(alias1 [, alias2 ...])] (ON <expr_list>)|(USING <column_list>)
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH ROLLUP|WITH CUBE] [WITH TOTALS]
[HAVING expr]
[WINDOW window_expr_list]
[QUALIFY expr]
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr] [INTERPOLATE [(expr_list)]]
[LIMIT [offset_value, ]n BY columns]
[LIMIT [n, ]m] [WITH TIES]
[SETTINGS ...]
[UNION  ...]
[INTO OUTFILE filename [COMPRESSION type [LEVEL level]] ]
[FORMAT format]

所有子句都是可选的，除了 SELECT 之后立即需要的表达式列表，这将在下面更详细地介绍。

每个可选子句的细节在单独的章节中介绍，这些章节按照它们执行的顺序排列

• WITH 子句
• SELECT 子句
• DISTINCT 子句
• FROM 子句
• SAMPLE 子句
• JOIN 子句
• PREWHERE 子句
• WHERE 子句
• WINDOW 子句
• GROUP BY 子句
• LIMIT BY 子句
• HAVING 子句
• QUALIFY 子句
• LIMIT 子句
• OFFSET 子句
• UNION 子句
• INTERSECT 子句
• EXCEPT 子句
• INTO OUTFILE 子句
• FORMAT 子句

SELECT 子句

表达式 在 SELECT 子句中指定，在上述子句中的所有操作完成后计算。这些表达式的工作方式就像它们应用于结果中的单独行一样。如果 SELECT 子句中的表达式包含聚合函数，则 ClickHouse 在 GROUP BY 聚合期间处理聚合函数和用作其参数的表达式。

如果要在结果中包含所有列，请使用星号 (*) 符号。例如，SELECT * FROM ...。

动态列选择

动态列选择（也称为 COLUMNS 表达式）允许您将结果中的某些列与 re2 正则表达式匹配。

COLUMNS('regexp')

例如，考虑以下表

CREATE TABLE default.col_names (aa Int8, ab Int8, bc Int8) ENGINE = TinyLog

以下查询从名称中包含 a 符号的所有列中选择数据。

SELECT COLUMNS('a') FROM col_names

┌─aa─┬─ab─┐
│  1 │  1 │
└────┴────┘

选定的列不是按字母顺序返回的。

您可以在查询中使用多个 COLUMNS 表达式，并对其应用函数。

例如

SELECT COLUMNS('a'), COLUMNS('c'), toTypeName(COLUMNS('c')) FROM col_names

┌─aa─┬─ab─┬─bc─┬─toTypeName(bc)─┐
│  1 │  1 │  1 │ Int8           │
└────┴────┴────┴────────────────┘

COLUMNS 表达式返回的每一列都作为单独的参数传递给函数。如果函数支持，您也可以将其他参数传递给函数。使用函数时要小心。如果函数不支持您传递给它的参数数量，ClickHouse 会抛出异常。

例如

SELECT COLUMNS('a') + COLUMNS('c') FROM col_names

Received exception from server (version 19.14.1):
Code: 42. DB::Exception: Received from localhost:9000. DB::Exception: Number of arguments for function plus does not match: passed 3, should be 2.

在此示例中，COLUMNS('a') 返回两列：aa 和 ab。COLUMNS('c') 返回 bc 列。+ 运算符不能应用于 3 个参数，因此 ClickHouse 会抛出带有相关消息的异常。

与 COLUMNS 表达式匹配的列可以具有不同的数据类型。如果 COLUMNS 不匹配任何列并且是 SELECT 中唯一的表达式，ClickHouse 会抛出异常。

星号

您可以在查询的任何部分放置星号而不是表达式。在分析查询时，星号会扩展为所有表列的列表（不包括 MATERIALIZED 和 ALIAS 列）。在少数情况下，使用星号是合理的

• 在创建表转储时。
• 对于仅包含少量列的表，例如系统表。
• 为了获取有关表中列的信息。在这种情况下，设置 LIMIT 1。但最好使用 DESC TABLE 查询。
• 当使用 PREWHERE 对少量列进行强过滤时。
• 在子查询中（因为子查询中排除了外部查询不需要的列）。

在所有其他情况下，我们不建议使用星号，因为它只会给您带来列式 DBMS 的缺点，而不是优点。换句话说，不建议使用星号。

极值

除了结果之外，您还可以获取结果列的最小值和最大值。为此，请将 extremes 设置为 1。最小值和最大值是为数值类型、日期和带时间的日期计算的。对于其他列，输出默认值。

额外计算两行——最小值和最大值。这两行额外的行以 XML、JSON*、TabSeparated*、CSV*、Vertical、Template 和 Pretty* 格式 输出，与其他行分开。它们不以其他格式输出。

在 JSON* 和 XML 格式中，极值在单独的 ‘extremes’ 字段中输出。在 TabSeparated*、CSV* 和 Vertical 格式中，该行在主要结果之后，以及在 ‘totals’（如果存在）之后出现。它前面有一个空行（在其他数据之后）。在 Pretty* 格式中，该行在主要结果之后，以及在 totals（如果存在）之后作为单独的表输出。在 Template 格式中，极值根据指定的模板输出。

极值是为 LIMIT 之前的行计算的，但在 LIMIT BY 之后。但是，当使用 LIMIT offset, size 时，offset 之前的行包含在极值中。在流式请求中，结果也可能包含少量通过 LIMIT 的行。

注释

您可以在查询的任何部分使用同义词（AS 别名）。

GROUP BY、ORDER BY 和 LIMIT BY 子句可以支持位置参数。要启用此功能，请打开 enable_positional_arguments 设置。然后，例如，ORDER BY 1,2 将按表中的第一列和第二列对行进行排序。

实现细节

如果查询省略了 DISTINCT、GROUP BY 和 ORDER BY 子句以及 IN 和 JOIN 子查询，则查询将完全流式处理，使用 O(1) 的 RAM 量。否则，如果未指定适当的限制，则查询可能会消耗大量 RAM

• max_memory_usage
• max_rows_to_group_by
• max_rows_to_sort
• max_rows_in_distinct
• max_bytes_in_distinct
• max_rows_in_set
• max_bytes_in_set
• max_rows_in_join
• max_bytes_in_join
• max_bytes_before_external_sort
• max_bytes_ratio_before_external_sort
• max_bytes_before_external_group_by
• max_bytes_ratio_before_external_group_by

有关更多信息，请参阅“设置”部分。可以使用外部排序（将临时表保存到磁盘）和外部聚合。

SELECT 修饰符

您可以在 SELECT 查询中使用以下修饰符。

APPLY

允许您为查询的外部表表达式返回的每一行调用某个函数。

语法

SELECT <expr> APPLY( <func> ) FROM [db.]table_name

示例

CREATE TABLE columns_transformers (i Int64, j Int16, k Int64) ENGINE = MergeTree ORDER by (i);
INSERT INTO columns_transformers VALUES (100, 10, 324), (120, 8, 23);
SELECT * APPLY(sum) FROM columns_transformers;

┌─sum(i)─┬─sum(j)─┬─sum(k)─┐
│    220 │     18 │    347 │
└────────┴────────┴────────┘

EXCEPT

指定要从结果中排除的一个或多个列的名称。所有匹配的列名都将从输出中省略。

语法

SELECT <expr> EXCEPT ( col_name1 [, col_name2, col_name3, ...] ) FROM [db.]table_name

示例

SELECT * EXCEPT (i) from columns_transformers;

┌──j─┬───k─┐
│ 10 │ 324 │
│  8 │  23 │
└────┴─────┘

REPLACE

指定一个或多个 表达式别名。每个别名必须与 SELECT * 语句中的列名匹配。在输出列列表中，与别名匹配的列将替换为该 REPLACE 中的表达式。

此修饰符不会更改列的名称或顺序。但是，它可以更改值和值类型。

语法

SELECT <expr> REPLACE( <expr> AS col_name) from [db.]table_name

示例

SELECT * REPLACE(i + 1 AS i) from columns_transformers;

┌───i─┬──j─┬───k─┐
│ 101 │ 10 │ 324 │
│ 121 │  8 │  23 │
└─────┴────┴─────┘

修饰符组合

您可以单独使用每个修饰符或将它们组合使用。

示例

多次使用相同的修饰符。

SELECT COLUMNS('[jk]') APPLY(toString) APPLY(length) APPLY(max) from columns_transformers;

┌─max(length(toString(j)))─┬─max(length(toString(k)))─┐
│                        2 │                        3 │
└──────────────────────────┴──────────────────────────┘

在单个查询中使用多个修饰符。

SELECT * REPLACE(i + 1 AS i) EXCEPT (j) APPLY(sum) from columns_transformers;

┌─sum(plus(i, 1))─┬─sum(k)─┐
│             222 │    347 │
└─────────────────┴────────┘

SELECT 查询中的 SETTINGS

您可以在 SELECT 查询中直接指定必要的设置。设置值仅应用于此查询，并在查询执行后重置为默认值或先前值。

有关进行设置的其他方法，请参阅此处。

示例

SELECT * FROM some_table SETTINGS optimize_read_in_order=1, cast_keep_nullable=1;