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 (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 子句
- 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
对少量列进行强过滤时。 - 在子查询中(因为外部查询不需要的列将从子查询中排除)。
在所有其他情况下,我们不建议使用星号,因为它只会让您获得列式数据库管理系统的缺点,而不是优点。换句话说,不建议使用星号。
极值
除了结果之外,您还可以获取结果列的最小值和最大值。为此,将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
之前的行包含在extremes
中。在流请求中,结果可能还包括少量通过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_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;