ClickHouse-4SQL参考

SQL参考

ClickHouse支持以下形式的查询:

SELECT
INSERT INTO
CREATE
ALTER
其他类型的查询

ClickHouse SQL 语句

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

SELECT
INSERT INTO
CREATE
ALTER
SYSTEM
SHOW
GRANT
REVOKE
ATTACH
CHECK TABLE
DESCRIBE TABLE
DETACH
DROP
EXISTS
KILL
OPTIMIZE
RENAME
SET
SET ROLE
TRUNCATE
USE
EXPLAIN

ALL 子句

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

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

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

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

您只能在 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', []);

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

SELECT s, arr
FROM arrays_test
ARRAY JOIN arr;

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

SELECT s, arr
FROM arrays_test
LEFT ARRAY JOIN arr;

使用别名?

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

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

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

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

在 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;

下面的例子使用 arrayEnumerate 功能:

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

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

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', [], []);

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

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

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

这种变化也是有道理的:

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

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

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

使用功能 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;

实现细节?

运行时优化查询执行顺序 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 DESC，我们得到以下结果:

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

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

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

但是，与标准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

从表达式 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.

您可以使用任意数量的相等条件和一个且只有一个最接近的匹配条件。例如, 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)

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采取并在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

查询:

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

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

该 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

添加 WITH TIES 修饰符后

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

虽然指定了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}]

is identical to the query

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

Usage of the ONLY option:

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

Usage of the WITH TIES option:

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

运行查询 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 │
└───┴──────┘

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

但是如果配置了 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

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 值的重复值，并且无法正确计算 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;

结果列通过它们的索引进行匹配（在内部的顺序 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);

带有 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;

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

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

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

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

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

条件?

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;

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;

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

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

CREATE VIEW view AS SELECT ...

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

SELECT a, b, c FROM (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

结果:

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

结果:

┌─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'] [,...]

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 ...

使用指定的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

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

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

示例:

ALTER TABLE visits DROP COLUMN browser

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

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

示例:

ALTER TABLE visits CLEAR COLUMN browser IN PARTITION tuple()

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

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

示例:

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

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

Type
Default expression
TTL

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

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

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

示例:

ALTER TABLE visits MODIFY COLUMN browser Array(String)

该操作仅支持 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;

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

示例:

ALTER TABLE visits DETACH PARTITION 201901

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

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

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

ALTER TABLE table_name DROP DETACHED PARTITION|PART partition_expr

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

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

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

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

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

替换分区?

ALTER TABLE table2 REPLACE PARTITION partition_expr FROM table1

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

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

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

清空分区的列?

ALTER TABLE table_name CLEAR COLUMN column_name IN PARTITION partition_expr

冻结分区?

ALTER TABLE table_name FREEZE [PARTITION partition_expr]

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

获取分区?

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

注意:

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 ... 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操作的同步性?

对于不可复制的表，所有 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的指定列的值。通过 CAST 操作将值转换成对应列的类型。不支持对用于主键或分区键表达式的列进行更新操作。

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

说明?

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

Examples?

设置默认角色：

ALTER USER user DEFAULT ROLE role1, role2

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

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

ALTER USER user DEFAULT ROLE ALL EXCEPT role1, role2

修改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 [,...]}]

修改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
RELOAD DICTIONARIES
RELOAD DICTIONARY
DROP DNS CACHE
DROP MARK CACHE
DROP UNCOMPRESSED CACHE
DROP COMPILED EXPRESSION CACHE
DROP REPLICA
FLUSH LOGS
RELOAD CONFIG
SHUTDOWN
KILL
STOP DISTRIBUTED SENDS
FLUSH DISTRIBUTED
START DISTRIBUTED SENDS
STOP MERGES
START MERGES
STOP TTL MERGES
START TTL MERGES
STOP MOVES
START MOVES
STOP FETCHES
START FETCHES
STOP REPLICATED SENDS
START REPLICATED SENDS
STOP REPLICATION QUEUES
START REPLICATION QUEUES
SYNC REPLICA
RESTART REPLICA
RESTART REPLICAS

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;

该操作将副本的路径从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>

START DISTRIBUTED SENDS?

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

SYSTEM START DISTRIBUTED SENDS [db.]<distributed_table_name>

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

START MERGES?

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

SYSTEM START MERGES [[db.]merge_tree_family_table_name]

START TTL MERGES?

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

SYSTEM START TTL MERGES [[db.]merge_tree_family_table_name]

START MOVES?

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

SYSTEM STOP MOVES [[db.]merge_tree_family_table_name]

START FETCHES?

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

SYSTEM START FETCHES [[db.]replicated_merge_tree_family_table_name]

START REPLICATED SENDS?

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

SYSTEM START REPLICATED SENDS [[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]

RESTART REPLICA?

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

SYSTEM RESTART REPLICA [db.]replicated_merge_tree_family_table_name

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

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

SHOW DATABASES?

SHOW DATABASES [INTO OUTFILE filename] [FORMAT format]

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

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

提示 (在控制台执行):

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

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

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

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

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

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

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

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

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

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

SHOW CREATE USER?

显示 user creation用到的参数。

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

语法?

SHOW CREATE USER [name | CURRENT_USER]

SHOW CREATE ROW POLICY?

显示 row policy creation中用到的参数

语法?

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

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 ...]

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)

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

  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

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

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)

[
  {
    "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 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;

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?中。

算术函数
比较函数
逻辑函数
类型转换函数
Files
Working with maps
IN 运算符
Geo
自省
Tuples
Encryption
NLP
时间窗口
数组函数
arrayJoin函数
位操作函数
位图函数
条件函数
时间日期函数
编码函数
字典函数
Nullable处理函数
GEO函数
Hash函数
高阶函数
IP函数
JSON函数
机器学习函数
数学函数
其他函数
随机函数
取整函数
字符串拆分合并函数
字符串函数
字符串替换函数
字符串搜索函数
URL函数
UUID函数
使用 Yandex.Metrica 字典函数

聚合函数

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

ClickHouse还支持:

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

空处理?

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

例:

考虑这个表:

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

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

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

!!! 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);

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

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

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;

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

INTERVAL?

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

示例:

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

另请参阅

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

CASE条件表达式?

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

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.

大数据