ClickHouse-4SQL参考

SQL参考

ClickHouse支持以下形式的查询:

ClickHouse SQL 语句

语句表示可以使用 SQL 查询执行的各种操作。每种类型的语句都有自己的语法和用法详细信息，这些语法和用法详细信息单独描述如下所示:

ALL 子句

SELECT ALL 和 SELECT 不带 DISTINCT 是一样的。

如果指定了 ALL ，则忽略它。
如果同时指定了 ALL 和 DISTINCT ，则会抛出异常。

ALL 也可以在聚合函数中指定，具有相同的效果（空操作）。例如：

SELECT sum(ALL number) FROM numbers(10);

等于

SELECT sum(number) FROM numbers(10);

ARRAY JOIN子句

对于包含数组列的表来说是一种常见的操作，用于生成一个新表，该表具有包含该初始列中的每个单独数组元素的列，而其他列的值将被重复显示。这是 ARRAY JOIN 语句最基本的场景。

它可以被视为执行 JOIN 并具有数组或嵌套数据结构。类似于 arrayJoin 功能，但该子句功能更广泛。

语法:

SELECT <expr_list>
FROM <left_subquery>
[LEFT] ARRAY JOIN <array>
[WHERE|PREWHERE <expr>]
...

您只能在 SELECT 查询指定一个 ARRAY JOIN 。

ARRAY JOIN 支持的类型有:

ARRAY JOIN - 一般情况下，空数组不包括在结果中 JOIN.
LEFT ARRAY JOIN - 的结果 JOIN 包含具有空数组的行。空数组的值设置为数组元素类型的默认值（通常为0、空字符串或NULL）。

基本 ARRAY JOIN 示例

下面的例子展示 ARRAY JOIN 和 LEFT ARRAY JOIN 的用法，让我们创建一个表包含一个 Array 的列并插入值:

CREATE TABLE arrays_test
(
    s String,
    arr Array(UInt8)
) ENGINE = Memory;

INSERT INTO arrays_test
VALUES ('Hello', [1,2]), ('World', [3,4,5]), ('Goodbye', []);

┌─s───────────┬─arr─────┐
│ Hello       │ [1,2]   │
│ World       │ [3,4,5] │
│ Goodbye     │ []      │
└─────────────┴─────────┘

下面的例子使用 ARRAY JOIN 子句:

SELECT s, arr
FROM arrays_test
ARRAY JOIN arr;

┌─s─────┬─arr─┐
│ Hello │   1 │
│ Hello │   2 │
│ World │   3 │
│ World │   4 │
│ World │   5 │
└───────┴─────┘

下一个示例使用 LEFT ARRAY JOIN 子句:

SELECT s, arr
FROM arrays_test
LEFT ARRAY JOIN arr;

┌─s───────────┬─arr─┐
│ Hello       │   1 │
│ Hello       │   2 │
│ World       │   3 │
│ World       │   4 │
│ World       │   5 │
│ Goodbye     │   0 │
└─────────────┴─────┘

使用别名

在使用ARRAY JOIN 时可以为数组指定别名，数组元素可以通过此别名访问，但数组本身则通过原始名称访问。示例:

SELECT s, arr, a
FROM arrays_test
ARRAY JOIN arr AS a;

┌─s─────┬─arr─────┬─a─┐
│ Hello │ [1,2]   │ 1 │
│ Hello │ [1,2]   │ 2 │
│ World │ [3,4,5] │ 3 │
│ World │ [3,4,5] │ 4 │
│ World │ [3,4,5] │ 5 │
└───────┴─────────┴───┘

可以使用别名与外部数组执行 ARRAY JOIN 。例如:

SELECT s, arr_external
FROM arrays_test
ARRAY JOIN [1, 2, 3] AS arr_external;

┌─s───────────┬─arr_external─┐
│ Hello       │            1 │
│ Hello       │            2 │
│ Hello       │            3 │
│ World       │            1 │
│ World       │            2 │
│ World       │            3 │
│ Goodbye     │            1 │
│ Goodbye     │            2 │
│ Goodbye     │            3 │
└─────────────┴──────────────┘

在 ARRAY JOIN 中，多个数组可以用逗号分隔, 在这例子中 JOIN 与它们同时执行（直接sum，而不是笛卡尔积）。请注意，所有数组必须具有相同的大小。示例:

SELECT s, arr, a, num, mapped
FROM arrays_test
ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num, arrayMap(x -> x + 1, arr) AS mapped;

┌─s─────┬─arr─────┬─a─┬─num─┬─mapped─┐
│ Hello │ [1,2]   │ 1 │   1 │      2 │
│ Hello │ [1,2]   │ 2 │   2 │      3 │
│ World │ [3,4,5] │ 3 │   1 │      4 │
│ World │ [3,4,5] │ 4 │   2 │      5 │
│ World │ [3,4,5] │ 5 │   3 │      6 │
└───────┴─────────┴───┴─────┴────────┘

下面的例子使用 arrayEnumerate 功能:

SELECT s, arr, a, num, arrayEnumerate(arr)
FROM arrays_test
ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num;

┌─s─────┬─arr─────┬─a─┬─num─┬─arrayEnumerate(arr)─┐
│ Hello │ [1,2]   │ 1 │   1 │ [1,2]               │
│ Hello │ [1,2]   │ 2 │   2 │ [1,2]               │
│ World │ [3,4,5] │ 3 │   1 │ [1,2,3]             │
│ World │ [3,4,5] │ 4 │   2 │ [1,2,3]             │
│ World │ [3,4,5] │ 5 │   3 │ [1,2,3]             │
└───────┴─────────┴───┴─────┴─────────────────────┘

具有嵌套数据结构的数组连接

ARRAY JOIN 也适用于嵌套数据结构:

CREATE TABLE nested_test
(
    s String,
    nest Nested(
    x UInt8,
    y UInt32)
) ENGINE = Memory;

INSERT INTO nested_test
VALUES ('Hello', [1,2], [10,20]), ('World', [3,4,5], [30,40,50]), ('Goodbye', [], []);

┌─s───────┬─nest.x──┬─nest.y─────┐
│ Hello   │ [1,2]   │ [10,20]    │
│ World   │ [3,4,5] │ [30,40,50] │
│ Goodbye │ []      │ []         │
└─────────┴─────────┴────────────┘

SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN nest;

┌─s─────┬─nest.x─┬─nest.y─┐
│ Hello │      1 │     10 │
│ Hello │      2 │     20 │
│ World │      3 │     30 │
│ World │      4 │     40 │
│ World │      5 │     50 │
└───────┴────────┴────────┘

当指定嵌套数据结构的名称 ARRAY JOIN，意思是一样的 ARRAY JOIN 它包含的所有数组元素。下面列出了示例:

SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN `nest.x`, `nest.y`;

┌─s─────┬─nest.x─┬─nest.y─┐
│ Hello │      1 │     10 │
│ Hello │      2 │     20 │
│ World │      3 │     30 │
│ World │      4 │     40 │
│ World │      5 │     50 │
└───────┴────────┴────────┘

这种变化也是有道理的:

SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN `nest.x`;

┌─s─────┬─nest.x─┬─nest.y─────┐
│ Hello │      1 │ [10,20]    │
│ Hello │      2 │ [10,20]    │
│ World │      3 │ [30,40,50] │
│ World │      4 │ [30,40,50] │
│ World │      5 │ [30,40,50] │
└───────┴────────┴────────────┘

可以将别名用于嵌套数据结构，以便选择 JOIN 结果或源数组。例如:

SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN nest AS n;

┌─s─────┬─n.x─┬─n.y─┬─nest.x──┬─nest.y─────┐
│ Hello │   1 │  10 │ [1,2]   │ [10,20]    │
│ Hello │   2 │  20 │ [1,2]   │ [10,20]    │
│ World │   3 │  30 │ [3,4,5] │ [30,40,50] │
│ World │   4 │  40 │ [3,4,5] │ [30,40,50] │
│ World │   5 │  50 │ [3,4,5] │ [30,40,50] │
└───────┴─────┴─────┴─────────┴────────────┘

使用功能 arrayEnumerate 的例子:

SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`, num
FROM nested_test
ARRAY JOIN nest AS n, arrayEnumerate(`nest.x`) AS num;

┌─s─────┬─n.x─┬─n.y─┬─nest.x──┬─nest.y─────┬─num─┐
│ Hello │   1 │  10 │ [1,2]   │ [10,20]    │   1 │
│ Hello │   2 │  20 │ [1,2]   │ [10,20]    │   2 │
│ World │   3 │  30 │ [3,4,5] │ [30,40,50] │   1 │
│ World │   4 │  40 │ [3,4,5] │ [30,40,50] │   2 │
│ World │   5 │  50 │ [3,4,5] │ [30,40,50] │   3 │
└───────┴─────┴─────┴─────────┴────────────┴─────┘

实现细节

运行时优化查询执行顺序 ARRAY JOIN. 虽然 ARRAY JOIN 必须始终之前指定 WHERE/PREWHERE 子句中的查询，从技术上讲，它们可以以任何顺序执行，除非结果 ARRAY JOIN 用于过滤。处理顺序由查询优化器控制。

DISTINCT子句

如果 SELECT DISTINCT 被声明，则查询结果中只保留唯一行。因此，在结果中所有完全匹配的行集合中，只有一行被保留。

空处理

DISTINCT 适用于 NULL 就好像 NULL 是一个特定的值，并且 NULL==NULL. 换句话说，在 DISTINCT 结果，不同的组合 NULL 仅发生一次。它不同于 NULL 在大多数其他情况中的处理方式。

替代办法

通过应用可以获得相同的结果 GROUP BY 在同一组值指定为 SELECT 子句，并且不使用任何聚合函数。但与 GROUP BY 有几个不同的地方:

DISTINCT 可以与 GROUP BY 一起使用.
当 ORDER BY 被省略并且 LIMIT 被定义时，在读取所需数量的不同行后立即停止运行。
数据块在处理时输出，而无需等待整个查询完成运行。

限制

DISTINCT 不支持当 SELECT 包含有数组的列。

例子

ClickHouse支持使用 DISTINCT 和 ORDER BY 在一个查询中的不同的列。 DISTINCT 子句在 ORDER BY 子句前被执行。

示例表:

┌─a─┬─b─┐
│ 2 │ 1 │
│ 1 │ 2 │
│ 3 │ 3 │
│ 2 │ 4 │
└───┴───┘

当执行 SELECT DISTINCT a FROM t1 ORDER BY b ASC 来查询数据，我们得到以下结果:

┌─a─┐
│ 2 │
│ 1 │
│ 3 │
└───┘

如果我们改变排序方向 SELECT DISTINCT a FROM t1 ORDER BY b DESC，我们得到以下结果:

┌─a─┐
│ 3 │
│ 1 │
│ 2 │
└───┘

行 2, 4 排序前被切割。

在编程查询时考虑这种实现特性。

格式化子句

ClickHouse支持广泛的序列化格式可用于查询结果等。有多种方法可以选择格式化 SELECT 的输出，其中之一是指定 FORMAT format 在查询结束时以任何特定格式获取结果集。

特定的格式方便使用，与其他系统集成或增强性能。

默认格式

如果 FORMAT 被省略则使用默认格式，这取决于用于访问ClickHouse服务器的设置和接口。为 HTTP接口和命令行客户端在批处理模式下，默认格式为 TabSeparated. 对于交互模式下的命令行客户端，默认格式为 PrettyCompact （它生成紧凑的人类可读表）。

实现细节

使用命令行客户端时，数据始终以内部高效格式通过网络传递 (Native). 客户端独立解释 FORMAT 查询子句并格式化数据本身（以减轻网络和服务器的额外负担）。

FROM子句

FROM 子句指定从以下数据源中读取数据:

表
子查询 {## TODO: better link ##}
表函数

JOIN 和 ARRAY JOIN 子句也可以用来扩展 FROM 的功能

子查询是另一个 SELECT 可以指定在 FROM 后的括号内的查询。

FROM 子句可以包含多个数据源，用逗号分隔，这相当于在他们身上执行 CROSS JOIN

FINAL 修饰符

当 FINAL 被指定，ClickHouse会在返回结果之前完全合并数据，从而执行给定表引擎合并期间发生的所有数据转换。

它适用于从使用 MergeTree-引擎族. 还支持:

Replicated 版本 MergeTree 引擎
View, Buffer, Distributed，和 MaterializedView 在其他引擎上运行的引擎，只要是它们底层是 MergeTree-引擎表即可。

现在使用 FINAL 修饰符的 SELECT 查询启用了并发执行, 这会快一点。但是仍然存在缺陷 (见下)。 max_final_threads 设置使用的最大线程数限制。

缺点

使用的查询 FINAL 执行速度比类似的查询慢一点，因为:

在查询执行期间合并数据。
查询与 FINAL 除了读取查询中指定的列之外，还读取主键列。

在大多数情况下，避免使用 FINAL. 常见的方法是使用假设后台进程的不同查询 MergeTree 引擎还没有发生，并通过应用聚合（例如，丢弃重复项）来处理它。 {## TODO: examples ##}

实现细节

如果 FROM 子句被省略，数据将从读取 system.one 表。该 system.one 表只包含一行（此表满足与其他 DBMS 中的 DUAL 表有相同的作用）。

若要执行查询，将从相应的表中提取查询中列出的所有列。外部查询不需要的任何列都将从子查询中抛出。如果查询未列出任何列（例如, SELECT count() FROM t），无论如何都会从表中提取一些列（首选是最小的列），以便计算行数。

GROUP BY子句

GROUP BY 子句将 SELECT 查询结果转换为聚合模式，其工作原理如下:

GROUP BY 子句包含表达式列表（或单个表达式 -- 可以认为是长度为1的列表）。这份名单充当 “grouping key”，而每个单独的表达式将被称为 “key expressions”.
在所有的表达式在 SELECT, HAVING，和 ORDER BY 子句中必须基于键表达式进行计算或上聚合函数在非键表达式（包括纯列）上。换句话说，从表中选择的每个列必须用于键表达式或聚合函数内，但不能同时使用。
聚合结果 SELECT 查询将包含尽可能多的行，因为有唯一值 “grouping key” 在源表中。通常这会显着减少行数，通常是数量级，但不一定：如果所有行数保持不变 “grouping key” 值是不同的。

!!! note "注" 还有一种额外的方法可以在表上运行聚合。如果查询仅在聚合函数中包含表列，则 GROUP BY 可以省略，并且通过一个空的键集合来假定聚合。这样的查询总是只返回一行。

空处理

对于分组，ClickHouse解释 NULL 作为一个值，并且 NULL==NULL. 它不同于 NULL 在大多数其他上下文中的处理方式。

这里有一个例子来说明这意味着什么。

假设你有一张表:

┌─x─┬────y─┐
│ 1 │    2 │
│ 2 │ ᴺᵁᴸᴸ │
│ 3 │    2 │
│ 3 │    3 │
│ 3 │ ᴺᵁᴸᴸ │
└───┴──────┘

查询 SELECT sum(x), y FROM t_null_big GROUP BY y 结果:

┌─sum(x)─┬────y─┐
│      4 │    2 │
│      3 │    3 │
│      5 │ ᴺᵁᴸᴸ │
└────────┴──────┘

你可以看到 GROUP BY 为 y = NULL 总结 x，仿佛 NULL 是这个值。

如果你通过几个键 GROUP BY，结果会给你选择的所有组合，就好像 NULL 是一个特定的值。

WITH TOTAL 修饰符

如果 WITH TOTALS 被指定，将计算另一行。此行将具有包含默认值（零或空行）的关键列，以及包含跨所有行计算值的聚合函数列（ “total” 值）。

这个额外的行仅产生于 JSON*, TabSeparated*，和 Pretty* 格式，与其他行分开:

在 JSON* 格式，这一行是作为一个单独的输出 ‘totals’ 字段。
在 TabSeparated* 格式，该行位于主结果之后，前面有一个空行（在其他数据之后）。
在 Pretty* 格式时，该行在主结果之后作为单独的表输出。
在其他格式中，它不可用。

WITH TOTALS 可以以不同的方式运行时 HAVING 是存在的。该行为取决于 totals_mode 设置。

配置总和处理

默认情况下, totals_mode = 'before_having'. 在这种情况下, ‘totals’ 是跨所有行计算，包括那些不通过具有和 max_rows_to_group_by.

其他替代方案仅包括通过具有在 ‘totals’，并与设置不同的行为 max_rows_to_group_by 和 group_by_overflow_mode = 'any'.

after_having_exclusive – Don't include rows that didn't pass through max_rows_to_group_by. 换句话说, ‘totals’ 将有少于或相同数量的行，因为它会 max_rows_to_group_by 被省略。

after_having_inclusive – Include all the rows that didn't pass through ‘max_rows_to_group_by’ 在 ‘totals’. 换句话说, ‘totals’ 将有多个或相同数量的行，因为它会 max_rows_to_group_by 被省略。

after_having_auto – Count the number of rows that passed through HAVING. If it is more than a certain amount (by default, 50%), include all the rows that didn't pass through ‘max_rows_to_group_by’ 在 ‘totals’. 否则，不包括它们。

totals_auto_threshold – By default, 0.5. The coefficient for after_having_auto.

如果 max_rows_to_group_by 和 group_by_overflow_mode = 'any' 不使用，所有的变化 after_having 是相同的，你可以使用它们中的任何一个（例如, after_having_auto).

您可以使用 WITH TOTALS 在子查询中，包括在子查询 JOIN 子句（在这种情况下，将各自的总值合并）。

例子

示例:

SELECT
    count(),
    median(FetchTiming > 60 ? 60 : FetchTiming),
    count() - sum(Refresh)
FROM hits

但是，与标准SQL相比，如果表没有任何行（根本没有任何行，或者使用 WHERE 过滤之后没有任何行），则返回一个空结果，而不是来自包含聚合函数初始值的行。

相对于MySQL（并且符合标准SQL），您无法获取不在键或聚合函数（常量表达式除外）中的某些列的某些值。要解决此问题，您可以使用 ‘any’ 聚合函数（获取第一个遇到的值）或 ‘min/max’.

示例:

SELECT
    domainWithoutWWW(URL) AS domain,
    count(),
    any(Title) AS title -- getting the first occurred page header for each domain.
FROM hits
GROUP BY domain

对于遇到的每个不同的键值, GROUP BY 计算一组聚合函数值。

GROUP BY 不支持数组列。

不能将常量指定为聚合函数的参数。示例: sum(1). 相反，你可以摆脱常数。示例: count().

实现细节

聚合是面向列的 DBMS 最重要的功能之一，因此它的实现是ClickHouse中最优化的部分之一。默认情况下，聚合使用哈希表在内存中完成。它有 40+ 的特殊化自动选择取决于 “grouping key” 数据类型。

在外部存储器中分组

您可以启用将临时数据转储到磁盘以限制内存使用期间 GROUP BY. 该 max_bytes_before_external_group_by 设置确定倾销的阈值RAM消耗 GROUP BY 临时数据到文件系统。如果设置为0（默认值），它将被禁用。

使用时 max_bytes_before_external_group_by，我们建议您设置 max_memory_usage 大约两倍高。这是必要的，因为聚合有两个阶段：读取数据和形成中间数据（1）和合并中间数据（2）。将数据转储到文件系统只能在阶段1中发生。如果未转储临时数据，则阶段2可能需要与阶段1相同的内存量。

例如，如果 max_memory_usage 设置为10000000000，你想使用外部聚合，这是有意义的设置 max_bytes_before_external_group_by 到10000000000，和 max_memory_usage 到20000000000。当触发外部聚合（如果至少有一个临时数据转储）时，RAM的最大消耗仅略高于 max_bytes_before_external_group_by.

通过分布式查询处理，在远程服务器上执行外部聚合。为了使请求者服务器只使用少量的RAM，设置 distributed_aggregation_memory_efficient 到1。

当合并数据刷新到磁盘时，以及当合并来自远程服务器的结果时， distributed_aggregation_memory_efficient 设置被启用，消耗高达 1/256 * the_number_of_threads 从RAM的总量。

当启用外部聚合时，如果数据量小于 max_bytes_before_external_group_by (例如数据没有被 flushed), 查询执行速度和不在外部聚合的速度一样快. 如果临时数据被flushed到外部存储, 执行的速度会慢几倍 (大概是三倍).

如果你有一个 ORDER BY 用一个 LIMIT 后 GROUP BY，然后使用的RAM的量取决于数据的量 LIMIT，不是在整个表。但如果 ORDER BY 没有 LIMIT，不要忘记启用外部排序 (max_bytes_before_external_sort).

HAVING 子句

允许过滤由 GROUP BY 生成的聚合结果. 它类似于 WHERE ，但不同的是 WHERE 在聚合之前执行，而 HAVING 之后进行。

可以从 SELECT 生成的聚合结果中通过他们的别名来执行 HAVING 子句。或者 HAVING 子句可以筛选查询结果中未返回的其他聚合的结果。

限制

HAVING 如果不执行聚合则无法使用。使用 WHERE 则相反。

INTO OUTFILE 子句

添加 INTO OUTFILE filename 子句（其中filename是字符串） SELECT query 将其输出重定向到客户端上的指定文件。

实现细节

此功能是在可用命令行客户端和 clickhouse-local. 因此通过 HTTP接口发送查询将会失败。
如果具有相同文件名的文件已经存在，则查询将失败。
默认值输出格式是 TabSeparated （就像在命令行客户端批处理模式中一样）。

JOIN子句

Join通过使用一个或多个表的公共值合并来自一个或多个表的列来生成新表。它是支持SQL的数据库中的常见操作，它对应于关系代数加入。一个表连接的特殊情况通常被称为 “self-join”.

语法:

SELECT <expr_list>
FROM <left_table>
[GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ASOF] JOIN <right_table>
(ON <expr_list>)|(USING <column_list>) ...

从表达式 ON 从子句和列 USING 子句被称为 “join keys”. 除非另有说明，加入产生一个笛卡尔积从具有匹配的行 “join keys”，这可能会产生比源表更多的行的结果。

支持的联接类型

所有标准 SQL JOIN 支持类型:

INNER JOIN，只返回匹配的行。
LEFT OUTER JOIN，除了匹配的行之外，还返回左表中的非匹配行。
RIGHT OUTER JOIN，除了匹配的行之外，还返回右表中的非匹配行。
FULL OUTER JOIN，除了匹配的行之外，还会返回两个表中的非匹配行。
CROSS JOIN，产生整个表的笛卡尔积, “join keys” 是不指定。

JOIN 没有指定类型暗指 INNER. 关键字 OUTER 可以安全地省略。替代语法 CROSS JOIN 在指定多个表 FROM 用逗号分隔。

ClickHouse中提供的其他联接类型:

LEFT SEMI JOIN 和 RIGHT SEMI JOIN,白名单 “join keys”，而不产生笛卡尔积。
LEFT ANTI JOIN 和 RIGHT ANTI JOIN，黑名单 “join keys”，而不产生笛卡尔积。
LEFT ANY JOIN, RIGHT ANY JOIN and INNER ANY JOIN, partially (for opposite side of LEFT and RIGHT) or completely (for INNER and FULL) disables the cartesian product for standard JOIN types.
ASOF JOIN and LEFT ASOF JOIN, joining sequences with a non-exact match. ASOF JOIN usage is described below.

严格

!!! note "注" 可以使用以下方式复盖默认的严格性值 join_default_strictness 设置。

Also the behavior of ClickHouse server for `ANY JOIN` operations depends on the [any_join_distinct_right_table_keys](/docs/zh/operations/settings/settings#any_join_distinct_right_table_keys) setting.

ASOF JOIN使用

ASOF JOIN 当您需要连接没有完全匹配的记录时非常有用。

该算法需要表中的特殊列。该列需要满足:

必须包含有序序列。
可以是以下类型之一: Int，UInt, Float*, Date, DateTime, Decimal*.
不能是JOIN子句中唯一的列

语法 ASOF JOIN ... ON:

SELECT expressions_list
FROM table_1
ASOF LEFT JOIN table_2
ON equi_cond AND closest_match_cond

您可以使用任意数量的相等条件和一个且只有一个最接近的匹配条件。例如, SELECT count() FROM table_1 ASOF LEFT JOIN table_2 ON table_1.a == table_2.b AND table_2.t <= table_1.t.

支持最接近匹配的运算符: >, >=, <, <=.

语法 ASOF JOIN ... USING:

SELECT expressions_list
FROM table_1
ASOF JOIN table_2
USING (equi_column1, ... equi_columnN, asof_column)

table_1.asof_column >= table_2.asof_column 中， ASOF JOIN 使用 equi_columnX 来进行条件匹配， asof_column 用于JOIN最接近匹配。 asof_column 列总是在最后一个 USING 条件中。

例如，参考下表:

     table_1                           table_2
  event   | ev_time | user_id       event   | ev_time | user_id
----------|---------|----------   ----------|---------|----------
              ...                               ...
event_1_1 |  12:00  |  42         event_2_1 |  11:59  |   42
              ...                 event_2_2 |  12:30  |   42
event_1_2 |  13:00  |  42         event_2_3 |  13:00  |   42
              ...                               ...

ASOF JOIN会从 table_2 中的用户事件时间戳找出和 table_1 中用户事件时间戳中最近的一个时间戳，来满足最接近匹配的条件。如果有得话，则相等的时间戳值是最接近的值。在此例中，user_id 列可用于条件匹配，ev_time 列可用于最接近匹配。在此例中，event_1_1 可以 JOIN event_2_1，event_1_2 可以JOIN event_2_3，但是 event_2_2 不能被JOIN。

!!! note "注" ASOF JOIN在 JOIN 表引擎中不受支持。

分布式联接

有两种方法可以执行涉及分布式表的join:

当使用正常 JOIN，将查询发送到远程服务器。为了创建正确的表，在每个子查询上运行子查询，并使用此表执行联接。换句话说，在每个服务器上单独形成右表。
使用时 GLOBAL ... JOIN，首先请求者服务器运行一个子查询来计算正确的表。此临时表将传递到每个远程服务器，并使用传输的临时数据对其运行查询。

使用时要小心 GLOBAL. 有关详细信息，请参阅分布式子查询科。

使用建议

处理空单元格或空单元格

在连接表时，可能会出现空单元格。设置 join_use_nulls 定义ClickHouse如何填充这些单元格。

如果 JOIN 键是可为空字段，其中至少有一个键具有值的行 NULL 没有加入。

语法

在指定的列 USING 两个子查询中必须具有相同的名称，并且其他列必须以不同的方式命名。您可以使用别名更改子查询中的列名。

该 USING 子句指定一个或多个要联接的列，这将建立这些列的相等性。列的列表设置不带括号。不支持更复杂的连接条件。

语法限制

对于多个 JOIN 单个子句 SELECT 查询:

通过以所有列 * 仅在联接表时才可用，而不是子查询。
该 PREWHERE 条款不可用。

为 ON, WHERE，和 GROUP BY 条款:

任意表达式不能用于 ON, WHERE，和 GROUP BY 子句，但你可以定义一个表达式 SELECT 子句，然后通过别名在这些子句中使用它。

性能

当运行 JOIN，与查询的其他阶段相关的执行顺序没有优化。连接（在右表中搜索）在过滤之前运行 WHERE 和聚集之前。

每次使用相同的查询运行 JOIN，子查询再次运行，因为结果未缓存。为了避免这种情况，使用特殊的加入我们表引擎，它是一个用于连接的准备好的数组，总是在RAM中。

在某些情况下，使用效率更高 IN 而不是 JOIN.

如果你需要一个 JOIN 对于连接维度表（这些是包含维度属性的相对较小的表，例如广告活动的名称）， JOIN 由于每个查询都会重新访问正确的表，因此可能不太方便。对于这种情况下，有一个 “external dictionaries” 您应该使用的功能 JOIN. 有关详细信息，请参阅外部字典科。

内存限制

默认情况下，ClickHouse使用哈希联接算法。 ClickHouse采取 <right_table> 并在RAM中为其创建哈希表。在某个内存消耗阈值之后，ClickHouse回退到合并联接算法。

如果需要限制联接操作内存消耗，请使用以下设置:

max_rows_in_join — Limits number of rows in the hash table.
max_bytes_in_join — Limits size of the hash table.

当任何这些限制达到，ClickHouse作为 join_overflow_mode 设置指示。

例子

示例:

SELECT
    CounterID,
    hits,
    visits
FROM
(
    SELECT
        CounterID,
        count() AS hits
    FROM test.hits
    GROUP BY CounterID
) ANY LEFT JOIN
(
    SELECT
        CounterID,
        sum(Sign) AS visits
    FROM test.visits
    GROUP BY CounterID
) USING CounterID
ORDER BY hits DESC
LIMIT 10

┌─CounterID─┬───hits─┬─visits─┐
│   1143050 │ 523264 │  13665 │
│    731962 │ 475698 │ 102716 │
│    722545 │ 337212 │ 108187 │
│    722889 │ 252197 │  10547 │
│   2237260 │ 196036 │   9522 │
│  23057320 │ 147211 │   7689 │
│    722818 │  90109 │  17847 │
│     48221 │  85379 │   4652 │
│  19762435 │  77807 │   7026 │
│    722884 │  77492 │  11056 │
└───────────┴────────┴────────┘

LIMIT BY子句

与查询 LIMIT n BY expressions 子句选择第一个 n 每个不同值的行 expressions. LIMIT BY 可以包含任意数量的表达式.

ClickHouse支持以下语法变体:

LIMIT [offset_value, ]n BY expressions
LIMIT n OFFSET offset_value BY expressions

在进行查询处理时，ClickHouse选择按排序键排序的数据。排序键设置显式地使用一个ORDER BY条款或隐式属性表的引擎(行顺序只是保证在使用ORDER BY,否则不会命令行块由于多线程)。然后ClickHouse应用LIMIT n BY 表达式，并为每个不同的表达式组合返回前n行。如果指定了OFFSET，那么对于每个属于不同表达式组合的数据块，ClickHouse将跳过offset_value从块开始的行数，并最终返回最多n行的结果。如果offset_value大于数据块中的行数，则ClickHouse从数据块中返回零行。

!!! note "注" LIMIT BY 是不相关的 LIMIT. 它们都可以在同一个查询中使用。

例

样例表:

CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory;
INSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21);

查询:

SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id

┌─id─┬─val─┐
│  1 │  10 │
│  1 │  11 │
│  2 │  20 │
│  2 │  21 │
└────┴─────┘

SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id

┌─id─┬─val─┐
│  1 │  11 │
│  1 │  12 │
│  2 │  21 │
└────┴─────┘

该 SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id 查询返回相同的结果。

以下查询返回每个引用的前5个引用 domain, device_type 最多可与100行配对 (LIMIT n BY + LIMIT).

SELECT
    domainWithoutWWW(URL) AS domain,
    domainWithoutWWW(REFERRER_URL) AS referrer,
    device_type,
    count() cnt
FROM hits
GROUP BY domain, referrer, device_type
ORDER BY cnt DESC
LIMIT 5 BY domain, device_type
LIMIT 100

LIMIT

LIMIT m 允许选择结果中起始的 m 行。

LIMIT n, m 允许选择个 m 从跳过第一个结果后的行 n 行。与 LIMIT m OFFSET n 语法是等效的。

n 和 m 必须是非负整数。

如果没有 ORDER BY 子句显式排序结果，结果的行选择可能是任意的和非确定性的。

LIMIT … WITH TIES 修饰符

如果为 LIMIT n[,m] 设置了 WITH TIES ，并且声明了 ORDER BY expr_list, 除了得到无修饰符的结果（正常情况下的 limit n, 前n行数据), 还会返回与第n行具有相同排序字段的行(即如果第n+1行的字段与第n行拥有相同的排序字段，同样返回该结果.

此修饰符可以与： ORDER BY … WITH FILL modifier 组合使用.

例如以下查询：

SELECT * FROM (
    SELECT number%50 AS n FROM numbers(100)
) ORDER BY n LIMIT 0,5

┌─n─┐
│ 0 │
│ 0 │
│ 1 │
│ 1 │
│ 2 │
└───┘

添加 WITH TIES 修饰符后

SELECT * FROM (
    SELECT number%50 AS n FROM numbers(100)
) ORDER BY n LIMIT 0,5 WITH TIES

则返回了以下的数据行

┌─n─┐
│ 0 │
│ 0 │
│ 1 │
│ 1 │
│ 2 │
│ 2 │
└───┘

虽然指定了LIMIT 5, 但第6行的n字段值为2，与第5行相同，因此也作为满足条件的记录返回。简而言之，该修饰符可理解为是否增加“并列行”的数据。

``` sql

OFFSET FETCH Clause

OFFSET and FETCH allow you to retrieve data by portions. They specify a row block which you want to get by a single query.

OFFSET offset_row_count {ROW | ROWS}] [FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} {ONLY | WITH TIES}]

The offset_row_count or fetch_row_count value can be a number or a literal constant. You can omit fetch_row_count; by default, it equals to 1.

OFFSET specifies the number of rows to skip before starting to return rows from the query result set.

The FETCH specifies the maximum number of rows that can be in the result of a query.

The ONLY option is used to return rows that immediately follow the rows omitted by the OFFSET. In this case the FETCH is an alternative to the LIMIT clause. For example, the following query

SELECT * FROM test_fetch ORDER BY a OFFSET 1 ROW FETCH FIRST 3 ROWS ONLY;

is identical to the query

SELECT * FROM test_fetch ORDER BY a LIMIT 3 OFFSET 1;

The WITH TIES option is used to return any additional rows that tie for the last place in the result set according to the ORDER BY clause. For example, if fetch_row_count is set to 5 but two additional rows match the values of the ORDER BY columns in the fifth row, the result set will contain seven rows.

NOTE

According to the standard, the OFFSET clause must come before the FETCH clause if both are present.

NOTE

The real offset can also depend on the offset setting.

Examples

Input table:

┌─a─┬─b─┐
│ 1 │ 1 │
│ 2 │ 1 │
│ 3 │ 4 │
│ 1 │ 3 │
│ 5 │ 4 │
│ 0 │ 6 │
│ 5 │ 7 │
└───┴───┘

Usage of the ONLY option:

SELECT * FROM test_fetch ORDER BY a OFFSET 3 ROW FETCH FIRST 3 ROWS ONLY;

Result:

┌─a─┬─b─┐
│ 2 │ 1 │
│ 3 │ 4 │
│ 5 │ 4 │
└───┴───┘

Usage of the WITH TIES option:

SELECT * FROM test_fetch ORDER BY a OFFSET 3 ROW FETCH FIRST 3 ROWS WITH TIES;

Result:

┌─a─┬─b─┐
│ 2 │ 1 │
│ 3 │ 4 │
│ 5 │ 4 │
│ 5 │ 7 │
└───┴───┘

ORDER BY

ORDER BY 子句包含一个表达式列表，每个表达式都可以用 DESC （降序）或 ASC （升序）修饰符确定排序方向。如果未指定方向, 默认是 ASC ，所以它通常被省略。排序方向适用于单个表达式，而不适用于整个列表。示例: ORDER BY Visits DESC, SearchPhrase

对于排序表达式列表具有相同值的行以任意顺序输出，也可以是非确定性的（每次都不同）。如果省略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 可以按顺序独立地指定或不按每个表达式。如果 ASC 或 DESC 被指定, COLLATE 在它之后指定。使用时 COLLATE，排序始终不区分大小写。

我们只建议使用 COLLATE 对于少量行的最终排序，因为排序与 COLLATE 比正常的按字节排序效率低。

实现细节

更少的RAM使用，如果一个足够小 LIMIT 除了指定 ORDER BY. 否则，所花费的内存量与用于排序的数据量成正比。对于分布式查询处理，如果 GROUP BY 省略排序，在远程服务器上部分完成排序，并将结果合并到请求者服务器上。这意味着对于分布式排序，要排序的数据量可以大于单个服务器上的内存量。

如果没有足够的RAM，则可以在外部存储器中执行排序（在磁盘上创建临时文件）。使用设置 max_bytes_before_external_sort 为此目的。如果将其设置为0（默认值），则禁用外部排序。如果启用，则当要排序的数据量达到指定的字节数时，将对收集的数据进行排序并转储到临时文件中。读取所有数据后，将合并所有已排序的文件并输出结果。文件被写入到 /var/lib/clickhouse/tmp/ 目录中的配置（默认情况下，但你可以使用 tmp_path 参数来更改此设置）。

运行查询可能占用的内存比 max_bytes_before_external_sort 大. 因此，此设置的值必须大大小于 max_memory_usage. 例如，如果您的服务器有128GB的RAM，并且您需要运行单个查询，请设置 max_memory_usage 到100GB，和 max_bytes_before_external_sort 至80GB。

外部排序的工作效率远远低于在RAM中进行排序。

ORDER BY Expr WITH FILL Modifier

此修饰符可以与 LIMIT … WITH TIES modifier 进行组合使用.

可以在ORDER BY expr之后用可选的FROM expr，TO expr和STEP expr参数来设置WITH FILL修饰符。所有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]

WITH FILL 仅适用于具有数字（所有类型的浮点，小数，整数）或日期/日期时间类型的字段。当未定义 FROM const_expr 填充顺序时，则使用 ORDER BY 中的最小 expr 字段值。如果未定义 TO const_expr 填充顺序，则使用 ORDER BY 中的最大expr字段值。当定义了 STEP const_numeric_expr 时，对于数字类型，const_numeric_expr 将 as is 解释为 days 作为日期类型，将 seconds 解释为DateTime类型。如果省略了 STEP const_numeric_expr，则填充顺序使用 1.0 表示数字类型，1 day表示日期类型，1 second 表示日期时间类型。

例如下面的查询：

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 │
└─────┴──────────┘

For the case when we have multiple fields ORDER BY field2 WITH FILL, field1 WITH FILL order of filling will follow the order of fields in ORDER BY clause. 对于我们有多个字段 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 │
└────────────┴────────────┴──────────┘

UNION ALL子句

你可以使用 UNION ALL 结合任意数量的 SELECT 来扩展其结果。示例:

SELECT CounterID, 1 AS table, toInt64(count()) AS c
    FROM test.hits
    GROUP BY CounterID

UNION ALL

SELECT CounterID, 2 AS table, sum(Sign) AS c
    FROM test.visits
    GROUP BY CounterID
    HAVING c > 0

结果列通过它们的索引进行匹配（在内部的顺序 SELECT). 如果列名称不匹配，则从第一个查询中获取最终结果的名称。

对联合执行类型转换。例如，如果合并的两个查询具有相同的字段与非-Nullable 和 Nullable 从兼容类型的类型，由此产生的 UNION ALL 有一个 Nullable 类型字段。

属于以下部分的查询 UNION ALL 不能用圆括号括起来。 ORDER BY 和 LIMIT 应用于单独的查询，而不是最终结果。如果您需要将转换应用于最终结果，则可以将所有查询 UNION ALL 在子查询中 FROM 子句。

限制

只有 UNION ALL 支持。 UNION (UNION DISTINCT）不支持。如果你需要 UNION DISTINCT，你可以写 SELECT DISTINCT 子查询中包含 UNION ALL.

实现细节

属于 UNION ALL 的查询可以同时运行，并且它们的结果可以混合在一起。

WHERE

WHERE 子句允许过滤来自SELECT的子句 FROM 的数据.

如果有一个 WHERE 子句，它必须包含一个表达式与 UInt8 类型。这通常是一个带有比较和逻辑运算符的表达式。表达式计算结果为0的行将被排除在在进一步的转换或结果之外。

如果基础表引擎支持，WHERE表达式会使用索引和分区进行剪枝。

!!! note "注" 有一个叫做过滤优化 prewhere 的东西.

如果需要测试一个 NULL 值，请使用 IS NULL and IS NOT NULL 运算符或 isNull 和 isNotNull 函数。否则带有 NULL 的表达式永远不会通过。

示例

在 numbers table 表上执行下述语句以找到为3的倍数且大于10的数字： To find numbers that are multiples of 3 and are greater than 10 execute the following query on the :

SELECT number FROM numbers(20) WHERE (number > 10) AND (number % 3 == 0);

结果:

┌─number─┐
│     12 │
│     15 │
│     18 │
└────────┘

带有 NULL 值的查询:

CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE=MergeTree() ORDER BY x;
INSERT INTO t_null VALUES (1, NULL), (2, 3);

SELECT * FROM t_null WHERE y IS NULL;
SELECT * FROM t_null WHERE y != 0;

结果:

┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
└───┴──────┘
┌─x─┬─y─┐
│ 2 │ 3 │
└───┴───┘

WITH子句

本节提供对公共表表达式的支持 (CTE），所以结果 WITH 子句可以在其余部分中使用 SELECT 查询。

限制

不支持递归查询。
当在section中使用子查询时，它的结果应该是只有一行的标量。
Expression的结果在子查询中不可用。

例

示例1: 使用常量表达式作为 “variable”

WITH '2019-08-01 15:23:00' as ts_upper_bound
SELECT *
FROM hits
WHERE
    EventDate = toDate(ts_upper_bound) AND
    EventTime <= ts_upper_bound

示例2: 从SELECT子句列表中逐出sum(bytes)表达式结果

WITH sum(bytes) as s
SELECT
    formatReadableSize(s),
    table
FROM system.parts
GROUP BY table
ORDER BY s

例3: 使用标量子查询的结果

/* this example would return TOP 10 of most huge tables */
WITH
    (
        SELECT sum(bytes)
        FROM system.parts
        WHERE active
    ) AS total_disk_usage
SELECT
    (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
    table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10

例4: 在子查询中重用表达式

作为子查询中表达式使用的当前限制的解决方法，您可以复制它。

WITH ['hello'] AS hello
SELECT
    hello,
    *
FROM
(
    WITH ['hello'] AS hello
    SELECT hello
)

┌─hello─────┬─hello─────┐
│ ['hello'] │ ['hello'] │

CREATE DATABASE

创建数据库.

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = engine(...)]

条件

IF NOT EXISTS

如果db_name数据库已经存在，则ClickHouse不会创建新数据库并且：

如果指定了子句，则不会引发异常。
如果未指定子句，则抛出异常。

ON CLUSTER

ClickHouse在指定集群的所有服务器上创建db_name数据库。更多细节在 Distributed DDL article.

ENGINE

MySQL 允许您从远程MySQL服务器检索数据. 默认情况下，ClickHouse使用自己的database engine. 还有一个lazy引擎.

CREATE TABLE

With Explicit Schema

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1] [compression_codec] [TTL expr1],
    name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr2] [compression_codec] [TTL expr2],
    ...
) ENGINE = engine

Examples:

Consider the table:

CREATE DATABASE base ENGINE = Atomic;
CREATE OR REPLACE TABLE base.t1 (n UInt64, s String) ENGINE = MergeTree ORDER BY n;
INSERT INTO base.t1 VALUES (1, 'test');
SELECT * FROM base.t1;

┌─n─┬─s────┐
│ 1 │ test │
└───┴──────┘

Using REPLACE query to clear all data:

CREATE OR REPLACE TABLE base.t1 (n UInt64, s Nullable(String)) ENGINE = MergeTree ORDER BY n;
INSERT INTO base.t1 VALUES (2, null);
SELECT * FROM base.t1;

┌─n─┬─s──┐
│ 2 │ \N │

CREATE VIEW

创建一个新视图。有两种类型的视图：普通视图，物化视图，Live视图和Window视图。

Normal

语法:

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] AS SELECT ...

普通视图不存储任何数据。他们只是在每次访问时从另一个表执行读取。换句话说，普通视图只不过是一个保存的查询。从视图中读取时，此保存的查询用作FROM子句中的子查询.

例如，假设您已经创建了一个视图：

CREATE VIEW view AS SELECT ...

并写了一个查询：

SELECT a, b, c FROM view

这个查询完全等同于使用子查询：

SELECT a, b, c FROM (SELECT ...)

Materialized

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...

物化视图存储由相应的SELECT管理.

创建不带TO [db].[table]的物化视图时，必须指定ENGINE – 用于存储数据的表引擎。

使用TO [db].[table] 创建物化视图时，不得使用POPULATE。

一个物化视图的实现是这样的：当向SELECT中指定的表插入数据时，插入数据的一部分被这个SELECT查询转换，结果插入到视图中。

!!! important "重要" ClickHouse 中的物化视图更像是插入触发器。如果视图查询中有一些聚合，则它仅应用于一批新插入的数据。对源表现有数据的任何更改（如更新、删除、删除分区等）都不会更改物化视图。

如果指定POPULATE，则在创建视图时将现有表数据插入到视图中，就像创建一个CREATE TABLE ... AS SELECT ...一样。否则，查询仅包含创建视图后插入表中的数据。我们不建议使用POPULATE，因为在创建视图期间插入表中的数据不会插入其中。

SELECT 查询可以包含DISTINCT、GROUP BY、ORDER BY、LIMIT……请注意，相应的转换是在每个插入数据块上独立执行的。例如，如果设置了GROUP BY，则在插入期间聚合数据，但仅在插入数据的单个数据包内。数据不会被进一步聚合。例外情况是使用独立执行数据聚合的ENGINE，例如SummingMergeTree。

在物化视图上执行ALTER查询有局限性，因此可能不方便。如果物化视图使用构造TO [db.]name，你可以DETACH视图，为目标表运行ALTER，然后ATTACH先前分离的（DETACH）视图。

请注意，物化视图受optimize_on_insert设置的影响。在插入视图之前合并数据。

视图看起来与普通表相同。例如，它们列在1SHOW TABLES1查询的结果中。

删除视图,使用DROP VIEW. DROP TABLE也适用于视图。

CREATE FUNCTION

用一个lambda表达式创建用户自定义函数。该表达式必须由函数参数、常数、运算符或其他函数调用组成。

语法

CREATE FUNCTION name AS (parameter0, ...) -> expression

一个函数可以有任意数量的参数。

存在一些限制如下:

函数名在用户自定义函数和系统函数中必须是唯一的。
递归函数是不允许的。
函数所使用的所有变量必须在其参数列表中指定。

如果违反了任何限制，就会产生异常。

示例

查询:

CREATE FUNCTION linear_equation AS (x, k, b) -> k*x + b;
SELECT number, linear_equation(number, 2, 1) FROM numbers(3);

结果:

┌─number─┬─plus(multiply(2, number), 1)─┐
│      0 │                            1 │
│      1 │                            3 │
│      2 │                            5 │
└────────┴──────────────────────────────┘

在下面的查询中，conditional function在用户自定义函数中被调用:

CREATE FUNCTION parity_str AS (n) -> if(n % 2, 'odd', 'even');
SELECT number, parity_str(number) FROM numbers(3);

结果:

┌─number─┬─if(modulo(number, 2), 'odd', 'even')─┐
│      0 │ even                                 │
│      1 │ odd                                  │
│      2 │ even                                 │
└────────┴──────────────────────────────────────┘

CREATE USER

Creates user accounts.

Syntax:

CREATE USER [IF NOT EXISTS | OR REPLACE] name1 [ON CLUSTER cluster_name1]
        [, name2 [ON CLUSTER cluster_name2] ...]
    [NOT IDENTIFIED | IDENTIFIED {[WITH {no_password | plaintext_password | sha256_password | sha256_hash | double_sha1_password | double_sha1_hash}] BY {'password' | 'hash'}} | {WITH ldap SERVER 'server_name'} | {WITH kerberos [REALM 'realm']}]
    [HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
    [DEFAULT ROLE role [,...]]
    [DEFAULT DATABASE database | NONE]
    [GRANTEES {user | role | ANY | NONE} [,...] [EXCEPT {user | role} [,...]]]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY | WRITABLE] | PROFILE 'profile_name'] [,...]

ON CLUSTER clause allows creating users on a cluster, see Distributed DDL.

Identification

There are multiple ways of user identification:

IDENTIFIED WITH no_password
IDENTIFIED WITH plaintext_password BY 'qwerty'
IDENTIFIED WITH sha256_password BY 'qwerty' or IDENTIFIED BY 'password'
IDENTIFIED WITH sha256_hash BY 'hash'
IDENTIFIED WITH double_sha1_password BY 'qwerty'
IDENTIFIED WITH double_sha1_hash BY 'hash'
IDENTIFIED WITH ldap SERVER 'server_name'
IDENTIFIED WITH kerberos or IDENTIFIED WITH kerberos REALM 'realm'

CREATE ROLE

Creates new roles. Role is a set of privileges. A user assigned a role gets all the privileges of this role.

Syntax:

CREATE ROLE [IF NOT EXISTS | OR REPLACE] name1 [, name2 ...]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]

Managing Roles

A user can be assigned multiple roles. Users can apply their assigned roles in arbitrary combinations by the SET ROLE statement. The final scope of privileges is a combined set of all the privileges of all the applied roles. If a user has privileges granted directly to it’s user account, they are also combined with the privileges granted by roles.

User can have default roles which apply at user login. To set default roles, use the SET DEFAULT ROLE statement or the ALTER USER statement.

To revoke a role, use the REVOKE statement.

To delete role, use the DROP ROLE statement. The deleted role is being automatically revoked from all the users and roles to which it was assigned.

Examples

CREATE ROLE accountant;
GRANT SELECT ON db.* TO accountant;

ALTER

ALTER 仅支持 *MergeTree ，Merge以及Distributed等引擎表。该操作有多种形式。

列操作

改变表结构：

ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN ...

在语句中，配置一个或多个用逗号分隔的动作。每个动作是对某个列实施的操作行为。

支持下列动作：

ADD COLUMN — 添加列
DROP COLUMN — 删除列
CLEAR COLUMN — 重置列的值
COMMENT COLUMN — 给列增加注释说明
MODIFY COLUMN — 改变列的值类型，默认表达式以及TTL

这些动作将在下文中进行详述。

增加列

ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [codec] [AFTER name_after]

使用指定的name, type, codec 以及 default_expr (请参见 Default expressions)，往表中增加新的列。

如果sql中包含 IF NOT EXISTS ，执行语句时如果列已经存在，CH不会报错。如果指定AFTER name_after（表中另一个列的名称），则新的列会加在指定列的后面。否则，新的列将被添加到表的末尾。注意，不能将新的列添加到表的开始位置， name_after 可以是执行该动作时已经在表中存在的任意列。

添加列仅仅是改变原有表的结构不会对已有数据产生影响。执行完 ALTER后磁盘中也不会出现新的数据。如果查询表时列的数据为空，那么CH会使用列的默认值来进行填充（如果有默认表达式，则使用这个；或者用0或空字符串）。当数据块完成合并(参见MergeTree)后，磁盘中会出现该列的数据。

这种方式允许 ALTER 语句能马上执行。不需要增加原有数据的大小。

示例:

ALTER TABLE visits ADD COLUMN browser String AFTER user_id

删除列

DROP COLUMN [IF EXISTS] name

通过指定 name删除列。如果语句包含 IF EXISTS，执行时遇到不存在的列也不会报错。

从文件系统中删除数据。由于是删除列的整个文件，该语句几乎是立即执行完成的。

示例:

ALTER TABLE visits DROP COLUMN browser

清空列

CLEAR COLUMN [IF EXISTS] name IN PARTITION partition_name

重置指定分区中列的值。分区名称 partition_name 请参见怎样设置分区表达式

如果语句中包含 IF EXISTS ，遇到不存在的列，sql执行不会报错。

示例:

ALTER TABLE visits CLEAR COLUMN browser IN PARTITION tuple()

增加注释

COMMENT COLUMN [IF EXISTS] name 'comment'

给列增加注释说明。如果语句中包含 IF EXISTS ，遇到不存在的列，sql执行不会报错。

每个列都可以包含注释。如果列的注释已经存在，新的注释会替换旧的。注释信息保存在 DESCRIBE TABLE查询的 comment_expression 字段中。

示例:

ALTER TABLE visits COMMENT COLUMN browser 'The table shows the browser used for accessing the site.'

修改列

MODIFY COLUMN [IF EXISTS] name [type] [default_expr] [TTL]

该语句可以改变 name 列的属性：

Type
Default expression
TTL

有关修改列TTL的示例，请参见 Column TTL.

如果语句中包含 IF EXISTS ，遇到不存在的列，sql执行不会报错。

当改变列的类型时，列的值也被转换了，如同对列使用 toType函数一样。如果只改变了默认表达式，该语句几乎不会做任何复杂操作，并且几乎是立即执行完成的。

示例:

ALTER TABLE visits MODIFY COLUMN browser Array(String)

改变列的类型是唯一的复杂型动作 - 它改变了数据文件的内容。对于大型表，执行起来要花费较长的时间。该操作分为如下处理步骤：

为修改的数据准备新的临时文件
重命名原来的文件
将新的临时文件改名为原来的数据文件名
删除原来的文件

仅仅在第一步是耗费时间的。如果该阶段执行失败，那么数据没有变化。如果执行后续的步骤中失败了，数据可以手动恢复。例外的情形是，当原来的文件从文件系统中被删除了，但是新的数据没有写入到临时文件中并且丢失了。

列操作的 ALTER行为是可以被复制的。这些指令会保存在ZooKeeper中，这样每个副本节点都能执行它们。所有的 ALTER 将按相同的顺序执行。 The query waits for the appropriate actions to be completed on the other replicas. 然而，改变可复制表的列是可以被中断的，并且所有动作都以异步方式执行。

ALTER 操作限制

ALTER 操作允许在嵌套的数据结构中创建和删除单独的元素（列），但是不是整个嵌套结构。添加一个嵌套数据结构的列时，你可以用类似这样的名称 name.nested_name 及类型 Array(T) 来操作。嵌套数据结构等同于列名前带有同样前缀的多个数组列。

不支持对primary key或者sampling key中的列（在 ENGINE 表达式中用到的列）进行删除操作。改变包含在primary key中的列的类型时，如果操作不会导致数据的变化（例如，往Enum中添加一个值，或者将DateTime 类型改成 UInt32），那么这种操作是可行的。

如果 ALTER 操作不足以完成你想要的表变动操作，你可以创建一张新的表，通过 INSERT SELECT将数据拷贝进去，然后通过 RENAME将新的表改成和原有表一样的名称，并删除原有的表。你可以使用 clickhouse-copier 代替 INSERT SELECT。

ALTER 操作会阻塞对表的所有读写操作。换句话说，当一个大的 SELECT 语句和 ALTER同时执行时，ALTER会等待，直到 SELECT 执行结束。与此同时，当 ALTER 运行时，新的 sql 语句将会等待。

对于不存储数据的表（例如 Merge 及 Distributed 表）， ALTER 仅仅改变了自身的表结构，不会改变从属的表结构。例如，对 Distributed 表执行 ALTER 操作时，需要对其它包含该表的服务器执行该操作。

key表达式的修改

支持下列表达式：

MODIFY ORDER BY new_expression

该操作仅支持 MergeTree 系列表 (含 replicated 表)。它会将表的排序键变成 new_expression (元组表达式)。主键仍保持不变。

该操作是轻量级的，仅会改变元数据。

跳过索引来更改数据

该操作仅支持 MergeTree 系列表 (含 replicated 表)。下列操作是允许的：

ALTER TABLE [db].name ADD INDEX name expression TYPE type GRANULARITY value [FIRST|AFTER name] - 在表的元数据中增加索引说明
ALTER TABLE [db].name DROP INDEX name - 从表的元数据中删除索引描述，并从磁盘上删除索引文件

由于只改变表的元数据或者删除文件，因此该操作是轻量级的，也可以被复制到其它节点（通过Zookeeper同步索引元数据）

更改约束

参见 constraints查看更多信息。

通过下面的语法，可以添加或删除约束：

ALTER TABLE [db].name ADD CONSTRAINT constraint_name CHECK expression;
ALTER TABLE [db].name DROP CONSTRAINT constraint_name;

上述语句会从表中增加或删除约束的元数据，因此会被立即处理。对已有数据的约束检查 将不会执行 。

对可复制表的操作可通过Zookeeper传播到其它副本节点。

更改分区及文件块

允许进行下列关于 partitions 的操作：

DETACH PARTITION — 将分区数据移动到 detached ，并且忘记它
DROP PARTITION — 删除一个partition.
ATTACH PART|PARTITION — 将detached 目录中的分区重新添加到表中.
ATTACH PARTITION FROM — 从表中复制数据分区到另一张表，并添加分区
REPLACE PARTITION — 从表中复制数据分区到其它表及副本
MOVE PARTITION TO TABLE — 从表中复制数据分区到其它表.
CLEAR COLUMN IN PARTITION — 重置分区中某个列的值
CLEAR INDEX IN PARTITION — 重置分区中指定的二级索引
FREEZE PARTITION — 创建分区的备份
FETCH PARTITION — 从其它服务器上下载分
MOVE PARTITION|PART — 将分区/数据块移动到另外的磁盘/卷

分区剥离

ALTER TABLE table_name DETACH PARTITION partition_expr

将指定分区的数据移动到 detached 目录。服务器会忽略被分离的数据分区。只有当你使用 ATTACH 时，服务器才会知晓这部分数据。

示例:

ALTER TABLE visits DETACH PARTITION 201901

从如何设置分区表达式章节中获取分区表达式的设置说明。

当执行操作以后，可以对 detached 目录的数据进行任意操作，例如删除文件，或者放着不管。

该操作是可以复制的，它会将所有副本节点上的数据移动到 detached 目录。注意仅能在副本的leader节点上执行该操作。想了解副本是否是leader节点，需要在 system.replicas 表执行 SELECT 操作。或者，可以很方便的在所有副本节点上执行 DETACH操作，但除leader外其它的副本节点会抛出异常。

删除分区

ALTER TABLE table_name DROP PARTITION partition_expr

从表中删除指定分区。该操作会将分区标记为不活跃的，然后在大约10分钟内删除全部数据。

在如何设置分区表达式中获取分区表达式的设置说明。该操作是可复制的，副本节点的数据也将被删除。

删除已剥离的分区|数据块

ALTER TABLE table_name DROP DETACHED PARTITION|PART partition_expr

从detached目录中删除指定分区的特定部分或所有数据。访问如何设置分区表达式可获取设置分区表达式的详细信息。

关联分区|数据块

ALTER TABLE table_name ATTACH PARTITION|PART partition_expr

从detached目录中添加数据到数据表。可以添加整个分区的数据，或者单独的数据块。例如：

ALTER TABLE visits ATTACH PARTITION 201901;
ALTER TABLE visits ATTACH PART 201901_2_2_0;

访问如何设置分区表达式可获取设置分区表达式的详细信息。

该操作是可以复制的。副本启动器检查 detached目录是否有数据。如果有，该操作会检查数据的完整性。如果一切正常，该操作将数据添加到表中。其它副本节点通过副本启动器下载这些数据。

因此可以在某个副本上将数据放到 detached目录，然后通过 ALTER ... ATTACH 操作将这部分数据添加到该表的所有副本。

从...关联分区

ALTER TABLE table2 ATTACH PARTITION partition_expr FROM table1

该操作将 table1 表的数据分区复制到 table2 表的已有分区。注意table1表的数据不会被删除。

为保证该操作能成功运行，下列条件必须满足：

2张表必须有相同的结构
2张表必须有相同的分区键

替换分区

ALTER TABLE table2 REPLACE PARTITION partition_expr FROM table1

该操作将 table1 表的数据分区复制到 table2表，并替换 table2表的已有分区。注意table1表的数据不会被删除。

为保证该操作能成功运行，下列条件必须满足：

2张表必须有相同的结构
2张表必须有相同的分区键

将分区移动到表

ALTER TABLE table_source MOVE PARTITION partition_expr TO TABLE table_dest

该操作将 table_source表的数据分区移动到 table_dest表，并删除table_source表的数据。

为保证该操作能成功运行，下列条件必须满足：

2张表必须有相同的结构
2张表必须有相同的分区键
2张表必须属于相同的引擎系列（可复制表或不可复制表）
2张表必须有相同的存储方式

清空分区的列

ALTER TABLE table_name CLEAR COLUMN column_name IN PARTITION partition_expr

重置指定分区的特定列的值。如果建表时使用了 DEFAULT 语句，该操作会将列的值重置为该默认值。

示例:

ALTER TABLE visits CLEAR COLUMN hour in PARTITION 201902

冻结分区

ALTER TABLE table_name FREEZE [PARTITION partition_expr]

该操作为指定分区创建一个本地备份。如果 PARTITION 语句省略，该操作会一次性为所有分区创建备份。

!!! 注意 "Note" 整个备份过程不需要停止服务

注意对于老式的表，可以指定分区名前缀（例如，‘2019’），然后该操作会创建所有对应分区的备份。访问如何设置分区表达式可获取设置分区表达式的详细信息。

在执行操作的同时，对于数据快照，该操作会创建到表数据的硬链接。硬链接放置在 /var/lib/clickhouse/shadow/N/...，也就是：

/var/lib/clickhouse/ 服务器配置文件中指定的CH工作目录
N 备份的增长序号

!!! 注意 "Note" 如果你使用多个磁盘存储数据表，那么每个磁盘上都有 shadow/N目录，用来保存PARTITION 表达式对应的数据块。

备份内部也会创建和 /var/lib/clickhouse/ 内部一样的目录结构。该操作在所有文件上执行‘chmod’，禁止往里写入数据

当备份创建完毕，你可以从 /var/lib/clickhouse/shadow/复制数据到远端服务器，然后删除本地数据。注意 ALTER t FREEZE PARTITION操作是不能复制的，它仅在本地服务器上创建本地备份。

该操作创建备份几乎是即时的（但是首先它会等待相关表的当前操作执行完成）

ALTER TABLE t FREEZE PARTITION 仅仅复制数据, 而不是元数据信息. 要复制表的元数据信息, 拷贝这个文件 /var/lib/clickhouse/metadata/database/table.sql

从备份中恢复数据，按如下步骤操作：

如果表不存在，先创建。查看.sql 文件获取执行语句 (将ATTACH 替换成 CREATE).
从备份的 data/database/table/目录中将数据复制到 /var/lib/clickhouse/data/database/table/detached/目录
运行 ALTER TABLE t ATTACH PARTITION操作，将数据添加到表中

恢复数据不需要停止服务进程。想了解备份及数据恢复的更多信息，请参见数据备份。

删除分区的索引

ALTER TABLE table_name CLEAR INDEX index_name IN PARTITION partition_expr

该操作和 CLEAR COLUMN类似，但是它重置的是索引而不是列的数据。

获取分区

ALTER TABLE table_name FETCH PARTITION partition_expr FROM 'path-in-zookeeper'

从另一服务器上下载分区数据。仅支持可复制引擎表。该操作做了如下步骤：

从指定数据分片上下载分区。在 path-in-zookeeper 这一参数你必须设置Zookeeper中该分片的path值。
然后将已下载的数据放到 table_name 表的 detached 目录下。通过 ATTACH PARTITION|PART将数据加载到表中。

示例:

ALTER TABLE users FETCH PARTITION 201902 FROM '/clickhouse/tables/01-01/visits';
ALTER TABLE users ATTACH PARTITION 201902;

注意:

ALTER ... FETCH PARTITION 操作不支持复制，它仅在本地服务器上将分区移动到 detached目录。
ALTER TABLE ... ATTACH操作是可复制的。它将数据添加到所有副本。数据从某个副本的detached 目录中添加进来，然后添加到邻近的副本

在开始下载之前，系统检查分区是否存在以及和表结构是否匹配。然后从健康的副本集中自动选择最合适的副本。

虽然操作叫做 ALTER TABLE，但是它并不能改变表结构，也不会立即改变表中可用的数据。

移动分区|数据块

将 MergeTree引擎表的分区或数据块移动到另外的卷/磁盘中。参见使用多个块设备存储数据

ALTER TABLE table_name MOVE PARTITION|PART partition_expr TO DISK|VOLUME 'disk_name'

ALTER TABLE t MOVE 操作:

不支持复制，因为不同副本可以有不同的存储方式
如果指定的磁盘或卷没有配置，返回错误。如果存储方式中设定的数据移动条件不能满足，该操作同样报错。
这种情况也会报错：即将移动的数据已经由后台进程在进行移动操作时，并行的 ALTER TABLE t MOVE操作或者作为后台数据合并的结果。这种情形下用户不能任何额外的动作。

示例:

ALTER TABLE hits MOVE PART '20190301_14343_16206_438' TO VOLUME 'slow'
ALTER TABLE hits MOVE PARTITION '2019-09-01' TO DISK 'fast_ssd'

如何设置分区表达式

通过不同方式在 ALTER ... PARTITION 操作中设置分区表达式：

system.parts表 partition列的某个值，例如， ALTER TABLE visits DETACH PARTITION 201901
表的列表达式。支持常量及常量表达式。例如， ALTER TABLE visits DETACH PARTITION toYYYYMM(toDate('2019-01-25'))
使用分区ID。分区ID是字符串变量（可能的话有较好的可读性），在文件系统和ZooKeeper中作为分区名称。分区ID必须配置在 PARTITION ID中，用单引号包含，例如， ALTER TABLE visits DETACH PARTITION ID '201901'
在 ALTER ATTACH PART 和 DROP DETACHED PART 操作中，要配置块的名称，使用 system.detached_parts表中 name列的字符串值，例如： ALTER TABLE visits ATTACH PART '201901_1_1_0'

设置分区时，引号使用要看分区表达式的类型。例如，对于 String类型，需要设置用引号(')包含的名称。对于 Date 和 Int*引号就不需要了。对于老式的表，可以用数值201901 或字符串 '201901'来设置分区。新式的表语法严格和类型一致（类似于VALUES输入的解析）

上述所有规则同样适用于 OPTIMIZE 操作。在对未分区的表进行 OPTIMIZE 操作时，如果需要指定唯一的分区，这样设置表达式PARTITION tuple()。例如：

OPTIMIZE TABLE table_not_partitioned PARTITION tuple() FINAL;

ALTER ... PARTITION 操作的示例在 00502_custom_partitioning_local 和 00502_custom_partitioning_replicated_zookeeper 提供了演示。

更改表的TTL

通过以下形式的请求可以修改 table TTL

ALTER TABLE table-name MODIFY TTL ttl-expression

ALTER操作的同步性

对于不可复制的表，所有 ALTER操作都是同步执行的。对于可复制的表，ALTER操作会将指令添加到ZooKeeper中，然后会尽快的执行它们。然而，该操作可以等待其它所有副本将指令执行完毕。

对于 ALTER ... ATTACH|DETACH|DROP操作，可以通过设置 replication_alter_partitions_sync 来启用等待。可用参数值： 0 – 不需要等待; 1 – 仅等待自己执行(默认); 2 – 等待所有节点

Mutations

Mutations是一类允许对表的行记录进行删除或更新的ALTER操作。相较于标准的 UPDATE 和 DELETE 用于少量行操作而言，Mutations用来对表的很多行进行重量级的操作。该操作支持 MergeTree系列表，包含支持复制功能的表。

已有的表已经支持mutations操作（不需要转换）。但是在首次对表进行mutation操作以后，它的元数据格式变得和和之前的版本不兼容，并且不能回退到之前版本。

目前可用的命令:

ALTER TABLE [db.]table DELETE WHERE filter_expr

filter_expr必须是 UInt8型。该操作将删除表中 filter_expr表达式值为非0的列

ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE filter_expr

filter_expr必须是 UInt8型。该操作将更新表中各行 filter_expr表达式值为非0的指定列的值。通过 CAST 操作将值转换成对应列的类型。不支持对用于主键或分区键表达式的列进行更新操作。

ALTER TABLE [db.]table MATERIALIZE INDEX name IN PARTITION partition_name

该操作更新 partition_name分区中的二级索引 name. 单次操作可以包含多个逗号分隔的命令。

对于 *MergeTree引擎表，mutation操作通过重写整个数据块来实现。没有原子性保证 - 被mutation操作的数据会被替换，在mutation期间开始执行的SELECT查询能看到所有已经完成mutation的数据，以及还没有被mutation替换的数据。

mutation总是按照它们的创建顺序来排序并以同样顺序在每个数据块中执行。mutation操作也会部分的和Insert操作一起排序 - 在mutation提交之前插入的数据会参与mutation操作，在mutation提交之后的插入的数据则不会参与mutation。注意mutation从来不会阻塞插入操作。

mutation操作在提交后（对于可复制表，添加到Zookeeper,对于不可复制表，添加到文件系统）立即返回。mutation操作本身是根据系统的配置参数异步执行的。要跟踪mutation的进度，可以使用系统表 system.mutations。已经成功提交的mutation操作在服务重启后仍会继续执行。一旦mutation完成提交，就不能回退了，但是如果因为某种原因操作被卡住了，可以通过 KILL MUTATION操作来取消它的执行。

已完成的mutations记录不会立即删除（要保留的记录数量由 finished_mutations_to_keep 这一参数决定）。之前的mutation记录会被删除。

修改用户

修改CH的用户账号

语法

ALTER USER [IF EXISTS] name [ON CLUSTER cluster_name]
    [RENAME TO new_name]
    [IDENTIFIED [WITH {PLAINTEXT_PASSWORD|SHA256_PASSWORD|DOUBLE_SHA1_PASSWORD}] BY {'password'|'hash'}]
    [[ADD|DROP] HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
    [DEFAULT ROLE role [,...] | ALL | ALL EXCEPT role [,...] ]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]

说明

要使用 ALTER USER，你必须拥有 ALTER USER 操作的权限

Examples

设置默认角色：

ALTER USER user DEFAULT ROLE role1, role2

如果角色之前没分配给用户，CH会抛出异常。

将所有分配的角色设为默认

ALTER USER user DEFAULT ROLE ALL

如果以后给用户分配了某个角色，它将自动成为默认角色

将除了 role1 和 role2之外的其它角色设为默认

ALTER USER user DEFAULT ROLE ALL EXCEPT role1, role2

修改角色

修改角色.

语法

ALTER ROLE [IF EXISTS] name [ON CLUSTER cluster_name]
    [RENAME TO new_name]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]

修改row policy

修改row policy.

语法

ALTER [ROW] POLICY [IF EXISTS] name [ON CLUSTER cluster_name] ON [database.]table
    [RENAME TO new_name]
    [AS {PERMISSIVE | RESTRICTIVE}]
    [FOR SELECT]
    [USING {condition | NONE}][,...]
    [TO {role [,...] | ALL | ALL EXCEPT role [,...]}]

修改配额quotas

修改配额quotas.

语法

ALTER QUOTA [IF EXISTS] name [ON CLUSTER cluster_name]
    [RENAME TO new_name]
    [KEYED BY {'none' | 'user name' | 'ip address' | 'client key' | 'client key or user name' | 'client key or ip address'}]
    [FOR [RANDOMIZED] INTERVAL number {SECOND | MINUTE | HOUR | DAY | WEEK | MONTH | QUARTER | YEAR}
        {MAX { {QUERIES | ERRORS | RESULT ROWS | RESULT BYTES | READ ROWS | READ BYTES | EXECUTION TIME} = number } [,...] |
        NO LIMITS | TRACKING ONLY} [,...]]
    [TO {role [,...] | ALL | ALL EXCEPT role [,...]}]

修改settings配置

修改settings配置.

语法

ALTER SETTINGS PROFILE [IF EXISTS] name [ON CLUSTER cluster_name]
    [RENAME TO new_name]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | INHERIT 'profile_name'] [,...]

SYSTEM Queries

RELOAD EMBEDDED DICTIONARIES]

重新加载所有内置字典。默认情况下内置字典是禁用的。总是返回 ‘OK.’，不管这些内置字典的更新结果如何。

RELOAD DICTIONARIES

重载已经被成功加载过的所有字典。默认情况下，字典是延时加载的（ dictionaries_lazy_load），不是在服务启动时自动加载，而是在第一次使用dictGet函数或通过 SELECT from tables with ENGINE = Dictionary 进行访问时被初始化。这个命令 SYSTEM RELOAD DICTIONARIES 就是针对这类表进行重新加载的。

RELOAD DICTIONARY Dictionary_name

完全重新加载指定字典 dictionary_name，不管该字典的状态如何(LOADED / NOT_LOADED / FAILED)。不管字典的更新结果如何，总是返回 OK. 字典的状态可以通过查询 system.dictionaries表来检查。

SELECT name, status FROM system.dictionaries;

DROP DNS CACHE

重置CH的dns缓存。有时候（对于旧的ClickHouse版本）当某些底层环境发生变化时（修改其它Clickhouse服务器的ip或字典所在服务器的ip），需要使用该命令。更多自动化的缓存管理相关信息，参见disable_internal_dns_cache, dns_cache_update_period这些参数。

DROP MARK CACHE

重置mark缓存。在进行ClickHouse开发或性能测试时使用。

DROP REPLICA

使用下面的语句可以删除已经无效的副本。

SYSTEM DROP REPLICA 'replica_name' FROM TABLE database.table;
SYSTEM DROP REPLICA 'replica_name' FROM DATABASE database;
SYSTEM DROP REPLICA 'replica_name';
SYSTEM DROP REPLICA 'replica_name' FROM ZKPATH '/path/to/table/in/zk';

该操作将副本的路径从Zookeeper中删除。当副本失效，并且由于该副本已经不存在导致它的元数据不能通过 DROP TABLE从zookeeper中删除，这种情形下可以使用该命令。它只会删除失效或过期的副本，不会删除本地的副本。请使用 DROP TABLE 来删除本地副本。 DROP REPLICA 不会删除任何表，并且不会删除磁盘上的任何数据或元数据信息。

第1条语句：删除 database.table表的 replica_name副本的元数据第2条语句：删除 database 数据库的所有replica_name副本的元数据第3条语句：删除本地服务器所有 replica_name副本的元数据第4条语句：用于在表的其它所有副本都删除时，删除已失效副本的元数据。使用时需要明确指定表的路径。该路径必须和创建表时 ReplicatedMergeTree引擎的第一个参数一致。

DROP UNCOMPRESSED CACHE

重置未压缩数据的缓存。用于ClickHouse开发和性能测试。管理未压缩数据缓存的参数，使用以下的服务器级别设置 uncompressed_cache_size以及 query/user/profile级别设置 use_uncompressed_cache

DROP COMPILED EXPRESSION CACHE

重置已编译的表达式缓存。用于ClickHouse开发和性能测试。当 query/user/profile 启用配置项 compile-expressions时，编译的表达式缓存开启。

FLUSH LOGS

将日志信息缓冲数据刷入系统表（例如system.query_log）。调试时允许等待不超过7.5秒。当信息队列为空时，会创建系统表。

RELOAD CONFIG

重新加载ClickHouse的配置。用于当配置信息存放在ZooKeeper时。

SHUTDOWN

关闭ClickHouse服务（类似于 service clickhouse-server stop / kill {$pid_clickhouse-server}）

KILL

关闭ClickHouse进程（ kill -9 {$ pid_clickhouse-server}）

Managing Distributed Tables

ClickHouse可以管理 distribute表。当用户向这类表插入数据时，ClickHouse首先为需要发送到集群节点的数据创建一个队列，然后异步的发送它们。你可以维护队列的处理过程，通过STOP DISTRIBUTED SENDS, FLUSH DISTRIBUTED, 以及 START DISTRIBUTED SENDS。你也可以设置 insert_distributed_sync参数来以同步的方式插入分布式数据。

STOP DISTRIBUTED SENDS

当向分布式表插入数据时，禁用后台的分布式数据分发。

SYSTEM STOP DISTRIBUTED SENDS [db.]<distributed_table_name>

FLUSH DISTRIBUTED

强制让ClickHouse同步向集群节点同步发送数据。如果有节点失效，ClickHouse抛出异常并停止插入操作。当所有节点都恢复上线时，你可以重试之前的操作直到成功执行。

SYSTEM FLUSH DISTRIBUTED [db.]<distributed_table_name>

START DISTRIBUTED SENDS

当向分布式表插入数据时，允许后台的分布式数据分发。

SYSTEM START DISTRIBUTED SENDS [db.]<distributed_table_name>

Managing MergeTree Tables

ClickHouse可以管理 MergeTree表的后台处理进程。

STOP MERGES

为MergeTree系列引擎表停止后台合并操作。

SYSTEM STOP MERGES [[db.]merge_tree_family_table_name]

!!! note "Note" DETACH / ATTACH 表操作会在后台进行表的merge操作，甚至当所有MergeTree表的合并操作已经停止的情况下。

START MERGES

为MergeTree系列引擎表启动后台合并操作。

SYSTEM START MERGES [[db.]merge_tree_family_table_name]

STOP TTL MERGES

根据 TTL expression，为MergeTree系列引擎表停止后台删除旧数据。不管表存在与否，都返回 OK.。当数据库不存在时返回错误。

SYSTEM STOP TTL MERGES [[db.]merge_tree_family_table_name]

START TTL MERGES

根据 TTL expression，为MergeTree系列引擎表启动后台删除旧数据。不管表存在与否，都返回 OK.。当数据库不存在时返回错误。

SYSTEM START TTL MERGES [[db.]merge_tree_family_table_name]

STOP MOVES

根据 TTL expression，为MergeTree系列引擎表停止后台移动数据。不管表存在与否，都返回 OK.。当数据库不存在时返回错误。

SYSTEM STOP MOVES [[db.]merge_tree_family_table_name]

START MOVES

根据 TTL expression，为MergeTree系列引擎表启动后台移动数据。不管表存在与否，都返回 OK.。当数据库不存在时返回错误。

SYSTEM STOP MOVES [[db.]merge_tree_family_table_name]

Managing ReplicatedMergeTree Tables

管理 ReplicatedMergeTree表的后台复制相关进程。

STOP FETCHES

停止后台获取 ReplicatedMergeTree系列引擎表中插入的数据块。不管表引擎类型如何或表/数据库是否存，都返回 OK.。

SYSTEM STOP FETCHES [[db.]replicated_merge_tree_family_table_name]

START FETCHES

启动后台获取 ReplicatedMergeTree系列引擎表中插入的数据块。不管表引擎类型如何或表/数据库是否存，都返回 OK.。

SYSTEM START FETCHES [[db.]replicated_merge_tree_family_table_name]

STOP REPLICATED SENDS

停止通过后台分发 ReplicatedMergeTree系列引擎表中新插入的数据块到集群的其它副本节点。

SYSTEM STOP REPLICATED SENDS [[db.]replicated_merge_tree_family_table_name]

START REPLICATED SENDS

启动通过后台分发 ReplicatedMergeTree系列引擎表中新插入的数据块到集群的其它副本节点。

SYSTEM START REPLICATED SENDS [[db.]replicated_merge_tree_family_table_name]

STOP REPLICATION QUEUES

停止从Zookeeper中获取 ReplicatedMergeTree系列表的复制队列的后台任务。可能的后台任务类型包含：merges, fetches, mutation，带有 ON CLUSTER的ddl语句

SYSTEM STOP REPLICATION QUEUES [[db.]replicated_merge_tree_family_table_name]

START REPLICATION QUEUES

启动从Zookeeper中获取 ReplicatedMergeTree系列表的复制队列的后台任务。可能的后台任务类型包含：merges, fetches, mutation，带有 ON CLUSTER的ddl语句

SYSTEM START REPLICATION QUEUES [[db.]replicated_merge_tree_family_table_name]

SYNC REPLICA

直到 ReplicatedMergeTree表将要和集群的其它副本进行同步之前会一直运行。如果当前对表的获取操作禁用的话，在达到 receive_timeout之前会一直运行。

SYSTEM SYNC REPLICA [db.]replicated_merge_tree_family_table_name

RESTART REPLICA

重置 ReplicatedMergeTree表的Zookeeper会话状态。该操作会以Zookeeper为参照，对比当前状态，有需要的情况下将任务添加到ZooKeeper队列。基于ZooKeeper的日期初始化复制队列，类似于 ATTACH TABLE语句。短时间内不能对表进行任何操作。

SYSTEM RESTART REPLICA [db.]replicated_merge_tree_family_table_name

RESTART REPLICAS

重置所有 ReplicatedMergeTree表的ZooKeeper会话状态。该操作会以Zookeeper为参照，对比当前状态，有需要的情况下将任务添加到ZooKeeper队列。

SHOW 查询

SHOW CREATE TABLE

SHOW CREATE [TEMPORARY] [TABLE|DICTIONARY] [db.]table [INTO OUTFILE filename] [FORMAT format]

返回单个字符串类型的 ‘statement’列，其中只包含了一个值 - 用来创建指定对象的 CREATE 语句。

注意，如果使用该查询去获取系统表的 CREATE 语句，你得到的是一个虚构的语句，仅用来展示系统的表结构，而不能实际创建表。

SHOW DATABASES

SHOW DATABASES [INTO OUTFILE filename] [FORMAT format]

打印所有的数据库列表，该查询等同于 SELECT name FROM system.databases [INTO OUTFILE filename] [FORMAT format]

SHOW PROCESSLIST

SHOW PROCESSLIST [INTO OUTFILE filename] [FORMAT format]

输出 system.processes表的内容，包含有当前正在处理的请求列表，除了 SHOW PROCESSLIST查询。

SELECT * FROM system.processes 查询返回和当前请求相关的所有数据

提示 (在控制台执行):

$ watch -n1 "clickhouse-client --query='SHOW PROCESSLIST'"

SHOW TABLES

显示表的清单

SHOW [TEMPORARY] TABLES [{FROM | IN} <db>] [LIKE '<pattern>' | WHERE expr] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]

如果未使用 FROM 字句，该查询返回当前数据库的所有表清单

可以用下面的方式获得和 SHOW TABLES一样的结果：

SELECT name FROM system.tables WHERE database = <db> [AND name LIKE <pattern>] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]

示例

下列查询获取最前面的2个位于system库中且表名包含 co的表。

SHOW TABLES FROM system LIKE '%co%' LIMIT 2

┌─name───────────────────────────┐
│ aggregate_function_combinators │
│ collations                     │
└────────────────────────────────┘

SHOW DICTIONARIES

以列表形式显示外部字典.

SHOW DICTIONARIES [FROM <db>] [LIKE '<pattern>'] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]

如果 FROM字句没有指定，返回当前数据库的字典列表

可以通过下面的查询获取和 SHOW DICTIONARIES相同的结果：

SELECT name FROM system.dictionaries WHERE database = <db> [AND name LIKE <pattern>] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]

示例

下列查询获取最前面的2个位于 system库中且名称包含 reg的字典表。

SHOW DICTIONARIES FROM db LIKE '%reg%' LIMIT 2

┌─name─────────┐
│ regions      │
│ region_names │
└──────────────┘

SHOW GRANTS

显示用户的权限

语法

SHOW GRANTS [FOR user]

如果未指定用户，输出当前用户的权限

SHOW CREATE USER

显示 user creation用到的参数。

SHOW CREATE USER 不会输出用户的密码信息

语法

SHOW CREATE USER [name | CURRENT_USER]

SHOW CREATE ROLE

显示 role creation 中用到的参数。

语法

SHOW CREATE ROLE name

SHOW CREATE ROW POLICY

显示 row policy creation中用到的参数

语法

SHOW CREATE [ROW] POLICY name ON [database.]table

SHOW CREATE QUOTA

显示 quota creation中用到的参数

语法

SHOW CREATE QUOTA [name | CURRENT]

SHOW CREATE SETTINGS PROFILE

显示 settings profile creation中用到的参数

语法

SHOW CREATE [SETTINGS] PROFILE name

EXPLAIN Statement

Shows the execution plan of a statement.

Syntax:

EXPLAIN [AST | SYNTAX | PLAN | PIPELINE | ESTIMATE | TABLE OVERRIDE] [setting = value, ...]
    [
      SELECT ... |
      tableFunction(...) [COLUMNS (...)] [ORDER BY ...] [PARTITION BY ...] [PRIMARY KEY] [SAMPLE BY ...] [TTL ...]
    ]
    [FORMAT ...]

Example:

EXPLAIN SELECT sum(number) FROM numbers(10) UNION ALL SELECT sum(number) FROM numbers(10) ORDER BY sum(number) ASC FORMAT TSV;

Union
  Expression (Projection)
    Expression (Before ORDER BY and SELECT)
      Aggregating
        Expression (Before GROUP BY)
          SettingQuotaAndLimits (Set limits and quota after reading from storage)
            ReadFromStorage (SystemNumbers)
  Expression (Projection)
    MergingSorted (Merge sorted streams for ORDER BY)
      MergeSorting (Merge sorted blocks for ORDER BY)
        PartialSorting (Sort each block for ORDER BY)
          Expression (Before ORDER BY and SELECT)
            Aggregating
              Expression (Before GROUP BY)
                SettingQuotaAndLimits (Set limits and quota after reading from storage)
                  ReadFromStorage (SystemNumbers)

EXPLAIN Types

AST — Abstract syntax tree.
SYNTAX — Query text after AST-level optimizations.
PLAN — Query execution plan.
PIPELINE — Query execution pipeline.

EXPLAIN AST

Dump query AST. Supports all types of queries, not only SELECT.

Examples:

EXPLAIN AST SELECT 1;

SelectWithUnionQuery (children 1)
 ExpressionList (children 1)
  SelectQuery (children 1)
   ExpressionList (children 1)
    Literal UInt64_1

EXPLAIN AST ALTER TABLE t1 DELETE WHERE date = today();

  explain
  AlterQuery  t1 (children 1)
   ExpressionList (children 1)
    AlterCommand 27 (children 1)
     Function equals (children 1)
      ExpressionList (children 2)
       Identifier date
       Function today (children 1)
        ExpressionList

EXPLAIN SYNTAX

Returns query after syntax optimizations.

Example:

EXPLAIN SYNTAX SELECT * FROM system.numbers AS a, system.numbers AS b, system.numbers AS c;

SELECT
    `--a.number` AS `a.number`,
    `--b.number` AS `b.number`,
    number AS `c.number`
FROM
(
    SELECT
        number AS `--a.number`,
        b.number AS `--b.number`
    FROM system.numbers AS a
    CROSS JOIN system.numbers AS b
) AS `--.s`
CROSS JOIN system.numbers AS c

EXPLAIN PLAN

Dump query plan steps.

Settings:

header — Prints output header for step. Default: 0.
description — Prints step description. Default: 1.
indexes — Shows used indexes, the number of filtered parts and the number of filtered granules for every index applied. Default: 0. Supported for MergeTree tables.
actions — Prints detailed information about step actions. Default: 0.
json — Prints query plan steps as a row in JSON format. Default: 0. It is recommended to use TSVRaw format to avoid unnecessary escaping.

Example:

EXPLAIN SELECT sum(number) FROM numbers(10) GROUP BY number % 4;

Union
  Expression (Projection)
  Expression (Before ORDER BY and SELECT)
    Aggregating
      Expression (Before GROUP BY)
        SettingQuotaAndLimits (Set limits and quota after reading from storage)
          ReadFromStorage (SystemNumbers)

NOTE

Step and query cost estimation is not supported.

When json = 1, the query plan is represented in JSON format. Every node is a dictionary that always has the keys Node Type and Plans. Node Type is a string with a step name. Plans is an array with child step descriptions. Other optional keys may be added depending on node type and settings.

Example:

EXPLAIN json = 1, description = 0 SELECT 1 UNION ALL SELECT 2 FORMAT TSVRaw;

[
  {
    "Plan": {
      "Node Type": "Union",
      "Plans": [
        {
          "Node Type": "Expression",
          "Plans": [
            {
              "Node Type": "SettingQuotaAndLimits",
              "Plans": [
                {
                  "Node Type": "ReadFromStorage"
                }
              ]
            }
          ]
        },
        {
          "Node Type": "Expression",
          "Plans": [
            {
              "Node Type": "SettingQuotaAndLimits",
              "Plans": [
                {
                  "Node Type": "ReadFromStorage"
                }
              ]
            }
          ]
        }
      ]
    }
  }
]

With description = 1, the Description key is added to the step:

{
  "Node Type": "ReadFromStorage",
  "Description": "SystemOne"
}

With header = 1, the Header key is added to the step as an array of columns.

Example:

EXPLAIN json = 1, description = 0, header = 1 SELECT 1, 2 + dummy;

[
  {
    "Plan": {
      "Node Type": "Expression",
      "Header": [
        {
          "Name": "1",
          "Type": "UInt8"
        },
        {
          "Name": "plus(2, dummy)",
          "Type": "UInt16"
        }
      ],
      "Plans": [
        {
          "Node Type": "SettingQuotaAndLimits",
          "Header": [
            {
              "Name": "dummy",
              "Type": "UInt8"
            }
          ],
          "Plans": [
            {
              "Node Type": "ReadFromStorage",
              "Header": [
                {
                  "Name": "dummy",
                  "Type": "UInt8"
                }
              ]
            }
          ]
        }
      ]
    }
  }
]

With indexes = 1, the Indexes key is added. It contains an array of used indexes. Each index is described as JSON with Type key (a string MinMax, Partition, PrimaryKey or Skip) and optional keys:

Name — An index name (for now, is used only for Skip index).
Keys — An array of columns used by the index.
Condition — A string with condition used.
Description — An index (for now, is used only for Skip index).
Initial Parts — A number of parts before the index is applied.
Selected Parts — A number of parts after the index is applied.
Initial Granules — A number of granules before the index is applied.
Selected Granulesis — A number of granules after the index is applied.

函数

ClickHouse中至少存在两种类型的函数 - 常规函数（它们称之为«函数»）和聚合函数。常规函数的工作就像分别为每一行执行一次函数计算一样（对于每一行，函数的结果不依赖于其他行）。聚合函数则从各行累积一组值（即函数的结果以来整个结果集）。

在本节中，我们将讨论常规函数。有关聚合函数，请参阅«聚合函数»一节。

* - ’arrayJoin’函数与表函数均属于第三种类型的函数。 *

强类型

与标准SQL相比，ClickHouse具有强类型。换句话说，它不会在类型之间进行隐式转换。每个函数适用于特定的一组类型。这意味着有时您需要使用类型转换函数。

常见的子表达式消除

查询中具有相同AST（相同语句或语法分析结果相同）的所有表达式都被视为具有相同的值。这样的表达式被连接并执行一次。通过这种方式也可以消除相同的子查询。

结果类型

所有函数都只能够返回一个返回值。结果类型通常由参数的类型决定。但tupleElement函数（a.N运算符）和toFixedString函数是例外的。

常量

为了简单起见，某些函数的某些参数只能是常量。例如，LIKE运算符的右参数必须是常量。几乎所有函数都为常量参数返回常量。除了用于生成随机数的函数。 ’now’函数为在不同时间运行的查询返回不同的值，但结果被视为常量，因为常量在单个查询中很重要。常量表达式也被视为常量（例如，LIKE运算符的右半部分可以由多个常量构造）。

对于常量和非常量参数，可以以不同方式实现函数（执行不同的代码）。但是，对于包含相同数据的常量和非常量参数它们的结果应该是一致的。

NULL值处理

函数具有以下行为：

如果函数的参数至少一个是«NULL»，则函数结果也是«NULL»。
在每个函数的描述中单独指定的特殊行为。在ClickHouse源代码中，这些函数具有«UseDefaultImplementationForNulls = false»。

不可变性

函数不能更改其参数的值 - 任何更改都将作为结果返回。因此，计算单独函数的结果不依赖于在查询中写入函数的顺序。

错误处理

如果数据无效，某些函数可能会抛出异常。在这种情况下，将取消查询并将错误信息返回给客户端。对于分布式处理，当其中一个服务器发生异常时，其他服务器也会尝试中止查询。

表达式参数的计算

在几乎所有编程语言中，某些函数可能无法预先计算其中一个参数。这通常是运算符&&，||和? :。但是在ClickHouse中，函数（运算符）的参数总是被预先计算。这是因为一次评估列的整个部分，而不是分别计算每一行。

执行分布式查询处理的功能

对于分布式查询处理，在远程服务器上执行尽可能多的查询处理阶段，并且在请求者服务器上执行其余阶段（合并中间结果和之后的所有内容）。

这意味着可以在不同的服务器上执行功能。例如，在查询SELECT f（sum（g（x）））FROM distributed_table GROUP BY h（y）中，

如果distributed_table至少有两个分片，则在远程服务器上执行函数’g’和’h’，并在请求服务器上执行函数’f’。
如果distributed_table只有一个分片，则在该分片的服务器上执行所有’f’，’g’和’h’功能。

函数的结果通常不依赖于它在哪个服务器上执行。但是，有时这很重要。例如，使用字典的函数时将使用运行它们的服务器上存在的字典。另一个例子是hostName函数，它返回运行它的服务器的名称，以便在SELECT查询中对服务器进行GROUP BY。

如果查询中的函数在请求服务器上执行，但您需要在远程服务器上执行它，则可以将其包装在«any»聚合函数中，或将其添加到«GROUP BY»中。

聚合函数

聚合函数如数据库专家预期的方式正常工作。

ClickHouse还支持:

参数聚合函数，它接受除列之外的其他参数。
组合器，这改变了聚合函数的行为。

空处理

在聚合过程中，所有 NULL 被跳过。

例:

考虑这个表:

┌─x─┬────y─┐
│ 1 │    2 │
│ 2 │ ᴺᵁᴸᴸ │
│ 3 │    2 │
│ 3 │    3 │
│ 3 │ ᴺᵁᴸᴸ │
└───┴──────┘

比方说，你需要计算 y 列的总数:

SELECT sum(y) FROM t_null_big

┌─sum(y)─┐
│      7 │
└────────┘

现在你可以使用 groupArray 函数用 y 列创建一个数组:

SELECT groupArray(y) FROM t_null_big

┌─groupArray(y)─┐
│ [2,2,3]       │
└───────────────┘

在 groupArray 生成的数组中不包括 NULL。

表函数

表函数是用来构造表的方法。

您可以在以下位置使用表函数:

SELECT 查询的FROM子句。

创建临时表的方法，该临时表仅在当前查询中可用。当查询完成后，该临时表将被删除。
CREATE TABLE AS \<table_function()> 查询。
```
这是创建表的方法之一。
```

!!! warning "警告" 如果 allow_ddl 设置被禁用，则不能使用表函数。

函数	描述
file	创建一个file引擎表。
merge	创建一个merge引擎表。
numbers	创建一个单列的表，其中包含整数。
remote	允许您访问远程服务器，而无需创建分布式表。
url	创建一个URL引擎表。
mysql	创建一个MySQL引擎表。
jdbc	创建一个JDBC引擎表。
odbc	创建一个ODBC引擎表。
hdfs	创建一个HDFS引擎表。

字典

字典是一个映射 (键 -> 属性）, 是方便各种类型的参考清单。

ClickHouse支持一些特殊函数配合字典在查询中使用。将字典与函数结合使用比将 JOIN 操作与引用表结合使用更简单、更有效。

NULL 值不能存储在字典中。

ClickHouse支持:

内置字典 ,这些字典具有特定的函数集.
插件（外部）字典 ,这些字典拥有一个函数集.

操作符

所有的操作符（运算符）都会在查询时依据他们的优先级及其结合顺序在被解析时转换为对应的函数。下面按优先级从高到低列出各组运算符及其对应的函数：

下标运算符

a[N] – 数组中的第N个元素; 对应函数 arrayElement(a, N)

a.N – 元组中第N个元素; 对应函数 tupleElement(a, N)

负号

-a – 对应函数 negate(a)

乘号、除号和取余

a * b – 对应函数 multiply(a, b)

a / b – 对应函数 divide(a, b)

a % b – 对应函数 modulo(a, b)

加号和减号

a + b – 对应函数 plus(a, b)

a - b – 对应函数 minus(a, b)

关系运算符

a = b – 对应函数 equals(a, b)

a == b – 对应函数 equals(a, b)

a != b – 对应函数 notEquals(a, b)

a <> b – 对应函数 notEquals(a, b)

a <= b – 对应函数 lessOrEquals(a, b)

a >= b – 对应函数 greaterOrEquals(a, b)

a < b – 对应函数 less(a, b)

a > b – 对应函数 greater(a, b)

a LIKE b – 对应函数 like(a, b)

a NOT LIKE b – 对应函数 notLike(a, b)

a BETWEEN b AND c – 等价于 a >= b AND a <= c

集合关系运算符

详见此节 IN 相关操作符。

a IN ... – 对应函数 in(a, b)

a NOT IN ... – 对应函数 notIn(a, b)

a GLOBAL IN ... – 对应函数 globalIn(a, b)

a GLOBAL NOT IN ... – 对应函数 globalNotIn(a, b)

逻辑非

NOT a – 对应函数 not(a)

逻辑与

a AND b – 对应函数and(a, b)

逻辑或

a OR b – 对应函数 or(a, b)

条件运算符

a ? b : c – 对应函数 if(a, b, c)

注意:

条件运算符会先计算表达式b和表达式c的值，再根据表达式a的真假，返回相应的值。如果表达式b和表达式c是 arrayJoin() 函数，则不管表达式a是真是假，每行都会被复制展开。

使用日期和时间的操作员

EXTRACT

EXTRACT(part FROM date);

从给定日期中提取部件。例如，您可以从给定日期检索一个月，或从时间检索一秒钟。

该 part 参数指定要检索的日期部分。以下值可用:

DAY — The day of the month. Possible values: 1–31.
MONTH — The number of a month. Possible values: 1–12.
YEAR — The year.
SECOND — The second. Possible values: 0–59.
MINUTE — The minute. Possible values: 0–59.
HOUR — The hour. Possible values: 0–23.

该 part 参数不区分大小写。

该 date 参数指定要处理的日期或时间。无论是日期或日期时间支持类型。

例:

SELECT EXTRACT(DAY FROM toDate('2017-06-15'));
SELECT EXTRACT(MONTH FROM toDate('2017-06-15'));
SELECT EXTRACT(YEAR FROM toDate('2017-06-15'));

在下面的例子中，我们创建一个表，并在其中插入一个值 DateTime 类型。

CREATE TABLE test.Orders
(
    OrderId UInt64,
    OrderName String,
    OrderDate DateTime
)
ENGINE = Log;

INSERT INTO test.Orders VALUES (1, 'Jarlsberg Cheese', toDateTime('2008-10-11 13:23:44'));

SELECT
    toYear(OrderDate) AS OrderYear,
    toMonth(OrderDate) AS OrderMonth,
    toDayOfMonth(OrderDate) AS OrderDay,
    toHour(OrderDate) AS OrderHour,
    toMinute(OrderDate) AS OrderMinute,
    toSecond(OrderDate) AS OrderSecond
FROM test.Orders;

┌─OrderYear─┬─OrderMonth─┬─OrderDay─┬─OrderHour─┬─OrderMinute─┬─OrderSecond─┐
│      2008 │         10 │       11 │        13 │          23 │          44 │
└───────────┴────────────┴──────────┴───────────┴─────────────┴─────────────┘

你可以看到更多的例子测试.

INTERVAL

创建一个间隔-应在算术运算中使用的类型值日期和日期时间-类型值。

示例:

SELECT now() AS current_date_time, current_date_time + INTERVAL 4 DAY + INTERVAL 3 HOUR

┌───current_date_time─┬─plus(plus(now(), toIntervalDay(4)), toIntervalHour(3))─┐
│ 2019-10-23 11:16:28 │                                    2019-10-27 14:16:28 │
└─────────────────────┴────────────────────────────────────────────────────────┘

另请参阅

间隔数据类型
toInterval 类型转换函数

CASE条件表达式

CASE [x]
    WHEN a THEN b
    [WHEN ... THEN ...]
    [ELSE c]
END

如果指定了 x ，该表达式会转换为 transform(x, [a, ...], [b, ...], c) 函数。否则转换为 multiIf(a, b, ..., c)

如果该表达式中没有 ELSE c 子句，则默认值就是 NULL

但 transform 函数不支持 NULL

连接运算符

s1 || s2 – 对应函数 concat(s1, s2)

创建 Lambda 函数

x -> expr – 对应函数 lambda(x, expr)

接下来的这些操作符因为其本身是括号没有优先级：

创建数组

[x1, ...] – 对应函数 array(x1, ...)

创建元组

(x1, x2, ...) – 对应函数 tuple(x2, x2, ...)

结合方式

所有的同级操作符从左到右结合。例如， 1 + 2 + 3 会转换成 plus(plus(1, 2), 3)。所以，有时他们会跟我们预期的不太一样。例如， SELECT 4 > 2 > 3 的结果是0。

为了高效， and 和 or 函数支持任意多参数，一连串的 AND 和 OR 运算符会转换成其对应的单个函数。

判断是否为 `NULL`

ClickHouse 支持 IS NULL 和 IS NOT NULL 。

IS NULL

对于可为空类型的值， IS NULL 会返回：
- 1 值为 NULL
- 0 否则
对于其他类型的值， IS NULL 总会返回 0

:) SELECT x+100 FROM t_null WHERE y IS NULL

SELECT x + 100
FROM t_null
WHERE isNull(y)

┌─plus(x, 100)─┐
│          101 │
└──────────────┘

1 rows in set. Elapsed: 0.002 sec.

IS NOT NULL

对于可为空类型的值， IS NOT NULL 会返回：
- 0 值为 NULL
- 1 否则
对于其他类型的值，IS NOT NULL 总会返回 1

:) SELECT * FROM t_null WHERE y IS NOT NULL

SELECT *
FROM t_null
WHERE isNotNull(y)

┌─x─┬─y─┐
│ 2 │ 3 │
└───┴───┘

1 rows in set. Elapsed: 0.002 sec.

posted @ 2022-04-30 21:08 hanease 阅读(158) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

hanease

ClickHouse-4SQL参考

SQL参考

ClickHouse SQL 语句

ALL 子句

ARRAY JOIN子句

基本 ARRAY JOIN 示例​

使用别名​

具有嵌套数据结构的数组连接​

实现细节​

DISTINCT子句

空处理​

替代办法​

限制​

例子​

格式化子句

默认格式​

实现细节​

FROM子句

FINAL 修饰符​

缺点​

实现细节​

GROUP BY子句

空处理​

WITH TOTAL 修饰符​

配置总和处理​

例子​

实现细节​

在外部存储器中分组​

HAVING 子句

限制​

INTO OUTFILE 子句

实现细节​

JOIN子句

支持的联接类型​

严格​

ASOF JOIN使用​

分布式联接​

使用建议​

处理空单元格或空单元格​

语法​

语法限制​

性能​

内存限制​

例子​

LIMIT BY子句

例​

LIMIT

LIMIT … WITH TIES 修饰符​

OFFSET FETCH Clause

NOTE

NOTE

Examples​

ORDER BY

特殊值的排序​

示例​

排序规则支持​

实现细节​

ORDER BY Expr WITH FILL Modifier​

UNION ALL子句

限制​

实现细节​

WHERE

WITH子句

限制​

例​

CREATE DATABASE

条件​

IF NOT EXISTS​

ON CLUSTER​

ENGINE​

CREATE TABLE

With Explicit Schema​

Examples:​

CREATE VIEW

Normal​

Materialized​

CREATE FUNCTION

CREATE USER

Identification​

基本 ARRAY JOIN 示例

使用别名

具有嵌套数据结构的数组连接

实现细节

空处理

替代办法

限制

例子

默认格式

实现细节

FINAL 修饰符

缺点

实现细节

空处理

WITH TOTAL 修饰符

配置总和处理

例子

实现细节

在外部存储器中分组

限制

实现细节

支持的联接类型

严格

ASOF JOIN使用

分布式联接

使用建议

处理空单元格或空单元格

语法

语法限制

性能

内存限制

例子

例

LIMIT … WITH TIES 修饰符

Examples

特殊值的排序

示例

排序规则支持

实现细节

ORDER BY Expr WITH FILL Modifier

限制

实现细节

限制

例

条件

IF NOT EXISTS

ON CLUSTER

ENGINE

With Explicit Schema

Examples:

Normal

Materialized

Identification

Managing Roles

Examples

ALTER

列操作

增加列

删除列

清空列

增加注释

修改列

ALTER 操作限制

key表达式的修改

跳过索引来更改数据

更改约束

更改分区及文件块

分区剥离

删除分区

删除已剥离的分区|数据块

关联分区|数据块

从...关联分区

替换分区

将分区移动到表

清空分区的列

冻结分区

删除分区的索引

获取分区

移动分区|数据块

如何设置分区表达式