ClickHouse基础部分告一段落。

SQL语法

• ClickHouse实现了大部分的ANSI标准语法，它提供了两种解析器：完整SQL解析器（递归式解析器）和数据格式解析器（快速流式解析器）。除了INSERT语句之外，其他语句只会用到完整SQL解析器。INSERT语句会同时使用两种解析器，其中INSERT INTO TABLE VALUES
  部分用到完整SQL解析器，后面的数据部分用到了快速流式解析器。

• ClickHouse未能做到完全忽略大小写，对于关键字，标准SQL如select\insert\values
  等是支持大小写的，某些数据类型关键字也是不敏感的，具体得查看system.data_type_families
  ,case_insensitive=1
  的数据类型是不敏感的。

• ClickHouse的表名、函数名等一般都是大小写敏感的。

• 输入的数字类型的值类型为能容纳该值的最小数据类型，做数字运算时会自动按数字里面最大类型对齐，使用toUInt64()
  之类的方法可以做类型转换，使用toTypeName()
  可以打印类型名。

• ClickHouse的字符串只支持单引号括住，有必要时要用\转义。

• 如果列中有NULL值，那么列要被定义为可支持NULL值的：Nullable(Int8)
  ，最好不要在列中存NULL。

• 其它的一些用法，跟MySQL其实比较接近，比如说支持CAST()语法、支持show 语法等。

SELECT语句

select语句是最常用的语句，用于数据的查询。它的完整语句如下：

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

可以看到，一个完整的查询SQL里面有若干部分子句，可以划分为以下子句：

• WITH
  ，所谓的公共表表达式，支持查询或表达式复用；

• SELECT
  ，指明要输出的列；

• FROM
  ，指定数据查询的来源；

• SAMPLE
  ，抽样近似查询；

• ARRAY JOIN
  ，解出数组的值到行上；

• JOIN
  ，表示表关联；

• PREWHERE
  ，用于提前一步过滤某列，加快查询效率；、

• WHERE
  ，主要的过滤条件；

• GROUP BY|HAVING
  ，分组聚合，过滤数据；

• ORDER BY
  ，排序；

• LIMIT
  ，分页条件；

• UNION ALL
  ，数据叠加，和另一个查询的数据组合在一起；

• INTO
  ，查询结果输出到文件系统；

• FORMAT
  ，指定输出结果的反序列化结果。

WITH子句

WITH子句可以定义某些想要复用的表达式或者查询，WITH (SUBQUERY) AS ALIAS
，它的使用有几个限制：

• 不支持递归子查询；

• 它必须只返回一行数据，也就是不能直接返回一个表的数据；

• with子句不能当一个正常表使用，而应该被视为一个标量。

SELECT子句

SELECT子句中列出的列会在查询结果中输出，如果使用*，那么会将所有的列输出。还有一些需要注意的事项：

• 可以使用正则匹配输出列名：columns('regexp')，符合正则条件的列将会输出；

• 可以使用字段另名AS colName
  ，在其他地方可以把别名直接使用。

FROM子句

FROM子句指明从哪里读取数据，它和JOIN|ARRAY JOIN
共同决定了数据的来源。

• 如果有指定FINAL，那么会在返回结果前合并数据，会更耗费时间；

• 如果省略表名，会默认从system.one
  表查询，类似于dual
  ；

ARRAY JOIN子句

可以将某些数组列展开到行上，用法为:

SELECT s, arr
FROM arrays_test
ARRAY JOIN arr;

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

其中，arr列是数组列。也可以为这个列指定别名，原列名还是可以用来指代数组。

JOIN子句

ClickHouse支持大部分的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都可以用来指定关联列，ON可以指定不同列名，USING要求两个表都有相同的列名。
还有一个不一样的点在于ON子句后面不能再跟关联列以外的其他条件，想对左表或者右表进行过滤的，得写在子查询或者WHERE后面。

• ASOF JOIN，用来支持非等值连接的场景，比如说<=|>=等；

• 对于分布式表，直接用JOIN会在每个服务器上先查询生成右表，参与连接；

• 也可以用GLOBAL JOIN
  让右表先生成，再广播到每一个服务器；

• JOIN操作性能有点弱，一般会使用hash_join
  ，如果内存吃不消再切换成merged_join
  ，而且同一个表的查询不会缓存；

• 某些场景下使用字典比使用JOIN更高效一点。

PREWHERE和WHERE子句

PREWHERE用于先执行某些列的过滤，以加速查询。默认情况下优化器会判断，把一些WHERE条件移到PREWHERE去。
WHERE会进行逻辑计算，但实际上逻辑运算会返回0和1。对于有些条件，会使用索引和分区进行剪枝，加快查询速度。

GROUP BY|HAVING子句

GROUP BY
用来做分组计算，必须配置SELECT子句里面的聚合函数使用。
HAVING
用来过滤分组后的结果。

ORDER BY子句

指定结果的排序方向，可以配合NULL LAST|NULL FIRST
改变NULL值的位置。

ALTER语句

ALTER仅支持 *MergeTree ，Merge以及Distributed等引擎表。
ALTER TABLE支持一系列的列操作、分区操作，还支持mutation操作，即对表中的数据进行更新、删除。

-- 修改表中的列
ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN ...

-- 操作分区
ALTER TABLE table_name DETACH|DROP|CLEAR PARTITION partition_expr

-- 修改TTL
ALTER TABLE table-name MODIFY TTL ttl-expression

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

OPTIMIZE语句

正常来说，表的后台文件合并会在不定时进行，OPTIMIZE提供了一个手工合并的选项。

OPTIMIZE TABLE [db.]name [ON CLUSTER cluster] [PARTITION partition | PARTITION ID 'partition_id'] [FINAL] [DEDUPLICATE [BY expression]]

函数部分

函数被区分为三种：普通函数、聚合函数和表函数。这一节讨论到的函数专指普通函数，它们会作用到每一行上，并为每行返回一个结果。
下面介绍一下普通函数的一些特性：

• 强类型：跟其他DBMS相比，ClickHouse的函数的接收参数和返回结果都是有明确类型的，它并不会在使用函数时进行默认的类型转换。

• 子表达式消除：也即同一个表达式函数使用被视为相同值，它只会被执行一次。

• 常量：某些函数的参数必须是常量，比如LIKE；几乎所有的函数都会为常量参数返回常数；now返回的值也被视为常量。

• 执行分布式查询：对于分片表，如果它的分片被发布在不同服务器，那有可能函数是在不同服务上执行的。

接下来，介绍一些常用的函数，其他未提及的函数可以在官方文档里面搜索。

类型转换函数

• toInt(8|16|32|64)：将数字或者代表数字的字符串转换成对应类型的数值。

• toInt(8|16|32|64)OrZero：转换失败会置为0.

• toInt(8|16|32|64)OrNull：转换失败会置为null.

• toDate|toDateTime：字符串转换成日期、日期时间，要求输入的字符串是yyyy-mm-dd hh:mi:ss
  类型的，不能自己指定格式；

• formatDateTime：把日期格式化成字符串，格式串用的是%Y%m%d
  形式，例formatDateTime(toDate('2020-01-01 12:12:32'), '%Y%m%d')
  。

• parseDateTimeBestEffort：会尝试多种方法解析字符串转换成DateTime，可以输入时区，例如parseDateTimeBestEffort('01-02-2020 12:12:32', 'Asia/Tokyo')
  。

• CAST(X, TYPE)|CAST(X AS TYPE)：强制类型转换。

• toInterval(Year|Quarter|Month|Week|Day|Hour|Minute|Second)：把数值转换成某种时间长度，效果跟interval n day|week|month
  差不多。

• toUnixTimestamp64Milli：把一个DateTime64类型的数据转换为Int64类型的数据，结果包含固定亚秒的精度。

• fromUnixTimestamp64Milli：把Int64类型的数据转换为DateTime64类型的数据，结果包含固定的亚秒精度和可选的时区。

Null处理函数

• isNull：判断是否为Null。

• isNotNull：是否非Null。

• coalesce：返回从左往右找到的第一个非Null值。

• ifNull(x, other)：判断x是空则返回other，否则返回x。

• nullIf(x, y)：如果x = y则返回null，否则返回x。

• assumeNotNull：将可为空类型的值转换为非Nullable类型的默认值。

• toNullable(x)：将参数的类型转换为Nullable。

字符串函数

• length：返回字符串的字节长度。

• reverse：反转字符串。

• concat：拼接字符串。

• substring|substr：截取字符串。

• position(haystack, needle), locate(haystack, needle)：找到子串needle在haystack中的位置。

• match(haystack, pattern)：检测haystack是否符合正则表达式pattern。

• extract(haystack, pattern)：提取符合正则的字符串第一个子串。

• ilike(haystack, pattern)：忽视大小写的like写法。

• replaceOne(haystack, pattern, replacement)：字符串替换。

条件函数

• if(cond, then, else)：如果cond为1则返回then，否则返回else。

• cond ? then : else：三元表达式，跟if效果一样。

• multiIf(cond_1, then_1, cond_2, then_2...else)：类似多个CASE WHEN的效果。

聚合函数

聚合函数作用在分组下的多行上，把多行结果汇聚成一行结果。
常见的聚合函数如sum、min、max、avg、count，我已经不想介绍，这里特别介绍一下ClickHouse的几个近似去重算法。

• uniqExact：等同于count(distinct col)，做最精确的去重。

• uniq：计算聚合中所有参数的哈希值，然后在计算中使用它做去重。使用自适应采样算法。对于计算状态，该函数使用最多65536个元素哈希值的样本。

• uniqCombined(64)：使用三种算法的组合：数组、哈希表和包含错误修正表的HyperLogLog。相比于 uniq 函数,消耗内存要少几倍。计算精度高出几倍。通常具有略低的性能。

• uniqHLL12：计算不同参数值的近似数量，使用 HyperLogLog 算法。

• uniqTheta：使用Theta Sketch Framework
  算法做近似计算。

窗口函数

ClickHouse现在已经支持窗口函数，这种函数也叫分析函数，是用于划分整体窗口再进行总体计算的方法。
但它的使用还有一些限制：

• 不支持直接在窗口函数外再做其他计算，如/2操作，而要做成子查询在外层再进行计算。

• lag/lead(value, offset)
  ，不支持

表函数

表函数是用来构建表数据的函数，可以使用到FROM和JOIN后面。
常用的有：

• numbers：返回一个数据序列表，以下查询是等价的

SELECT * FROM numbers(10);
SELECT * FROM numbers(0, 10);
SELECT * FROM system.numbers LIMIT 10;

• mysql，允许对存储远程MySQL库上的表进行查询和插入操作：

mysql('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);

SELECT name FROM mysql(`mysql1:3306|mysql2:3306|mysql3:3306`, 'mysql_database', 'mysql_table', 'user', 'password');