clickhouse基础教程

一、基础概念

Clickhouse由俄罗斯Yandex公司开源的数据库，专为OLAP而设计。 Yandex是俄罗斯最大的搜索引擎公司，官方宣称ClickHouse 日处理记录数”十亿级”。

发布之初跑分要超过很多流行的商业MPP数据库软件，对标老东家HP的Vertica和GP 官方的性能测试显示比vertica快5倍，比GP快10倍。

但是：

clickhouse直接可以安在各种版本的Linux系统上，macos可以通过docker来安装 官方没有提供设计和架构文档，只有开源的C++源码 不理睬Hadoop生态，走自己的路

目前国内社区火热，各个大厂纷纷跟进大规模使用：

今日头条内部用ClickHouse来做用户行为分析，内部一共几千个ClickHouse节点，单集群最大1200节点，总数据量几十PB，日增原始数据300TB左右。
腾讯内部用ClickHouse做游戏数据分析，并且为之建立了一整套监控运维体系。
携程内部从18年7月份开始接入试用，目前80%的业务都跑在ClickHouse上。每天数据增量十多亿，近百万次查询请求。
快手内部也在使用ClickHouse，存储总量大约10PB，每天新增200TB， 90%查询小于3S。

ClickHouse 是一个用于联机分析（OLAP）的列式数据库管理系统（DBMS）。

两个概念：

1）OLTP与OLAP：

OLTP：是传统的关系型数据库，主要操作增删改查，强调事务一致性，比如银行系统、电商系统。
OLAP：是仓库型数据库，主要是读取数据，做复杂数据分析，侧重技术决策支持，提供直观简单的结果。

2）DBMS：

具有DDL（数据定义语言）：可以动态地创建、修改或删除数据库、表和视图，而无须重启服务。
·具有DML（数据操作语言）：可以动态查询、插入、修改或删除数据。 ·
权限控制：可以按照用户粒度设置数据库或者表的操作权限，保障数据的安全性。 ·
数据备份与恢复：提供了数据备份导出与导入恢复机制，满足生产环境的要求。
·分布式管理：提供集群模式，能够自动管理多个数据库节点。

以上表明为什么 Click House 称得上是 DBMS 。

二、架构与安装

目前ClickHouse公开的资料相对匮乏，比如在架构设计层面就很难找到完整的资料，甚至连一张整体的架构图都没有。我想这就是它为何身为一款开源软件，但又显得如此神秘的原因之一吧。即便如此，我们还是能从一些零散的材料中找到一些蛛丝马迹。接下来会说明ClickHouse底层设计中的一些概念，这些概念可以帮助我们了解ClickHouse。

安装

ubuntu安装指南: ubuntu安装ck指南

mac安装指南，首先安装docker:mac安装ck指南

网页版查询工具TABIX：ui.tabix.io

客户端查询工具：DBeaver

Tips: 客户端和网页版查询ClickHouse时，需要开启远程登录。在安装guide有具体步骤。clickhouse的jdbc connector包需要编译，比较麻烦，建议使用dbeaver，第一次连接的时候会自动编译最新的jar包。

三、clickhouse引擎

4.1 数据库引擎

延时引擎Lazy

在距最近一次访问间隔expiration_time_in_seconds时间段内，将表保存在内存中，仅适用于 *Log引擎表

由于针对这类表的访问间隔较长，对保存大量小的 *Log引擎表进行了优化，
```
CREATE DATABASE testlazy ENGINE = Lazy(expiration_time_in_seconds);
```
MySQL引擎

MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许对表进行INSERT和SELECT查询，以方便在ClickHouse与MySQL之间进行数据交换。

MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中，因此可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作。但是不能进行删除更新操作。
```
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')
```
默认的数据库引擎

默认情况下，ClickHouse使用自己的数据库引擎，该引擎提供可配置的表引擎和所有支持的SQL语法.

4.2 数据表引擎

表引擎（即表的类型）决定了：

数据的存储方式和位置，写到哪里以及从哪里读取数据
支持哪些查询以及如何支持。
并发数据访问。
索引的使用（如果存在）。
是否可以执行多线程请求。
数据复制参数。

表引擎种类：

MergeTree

适用于高负载任务的最通用和功能最强大的表引擎。这些引擎的共同特点是可以快速插入数据并进行后续的后台数据处理。 MergeTree系列引擎支持数据复制（使用Replicated* 的引擎版本），分区和一些其他引擎不支持的其他功能。

1.MergeTree

这个引擎是 ClickHouse 的重头戏，它支持一个日期和一组主键的两层式索引，还可以实时更新数据。同时，索引的粒度可以自定义，外加直接支持采样功能。

而且，以这个引擎为基础，后面几种引擎都是在其基础之上附加某种特定功能而实现的“变种”。

使用这个引擎的形式如下：

MergeTree(EventDate, (CounterID, EventDate), 8192)
MergeTree(EventDate, intHash32(UserID), (CounterID, EventDate, intHash32(UserID)), 8192)

含义：

1) EventDate 一个日期的列名。

2) intHash32(UserID) 采样表达式。

3) (CounterID, EventDate) 主键组（里面除了列名，也支持表达式），也可以是一个表达式。

4) 8123 主键索引的粒度。

\2. ReplacingMergeTree

这个引擎是在 MergeTree 的基础上，添加了“处理重复数据”的功能，简直就是在多维数据加工流程中，为“最新值”，“实时数据”场景量身打造的一个引擎啊。这些场景下，如果重复数据不处理，你自己当然可以通过时间倒排，取最新的一条数据来达到目的，但是，至少这样会浪费很多的存储空间。

相比 MergeTree ， ReplacingMergeTree 在最后加一个“版本列”，它跟时间列配合一起，用以区分哪条数据是“新的”，并把旧的丢掉（这个过程是在 merge 时处理，不是数据写入时就处理了的，平时重复的数据还是保存着的，并且查也是跟平常一样会查出来的，所以在 SQL 上排序过滤 Limit 什么的该写还是要写的）。同时，主键列组用于区分重复的行。

create table t (gmt  Date, id UInt16, name String, point UInt16) ENGINE=ReplacingMergeTree(gmt, (name), 10, point);

像上面一样，“版本列”允许的类型是， UInt 一族的整数，或 Date 或 DateTime 。

insert into t (gmt, id, name, point) values ('2017-07-10', 1, 'a', 20);
insert into t (gmt, id, name, point) values ('2017-07-10', 1, 'a', 30);
insert into t (gmt, id, name, point) values ('2017-07-11', 1, 'a', 20);
insert into t (gmt, id, name, point) values ('2017-07-11', 1, 'a', 30);
insert into t (gmt, id, name, point) values ('2017-07-11', 1, 'a', 10);

插入这些数据，用 optimize table t 手动触发一下 merge 行为，然后查询：

select * from t;

\3. SummingMergeTree

ReplacingMergeTree 是替换数据， SummingMergeTree 就是在 merge 阶段把数据加起来了，当然，哪些列要加（一般是针对可加的指标）可以配置，不可加的列，会取一个最先出现的值。

建表：

create table ttt (gmt Date, name String, a UInt16, b UInt16) ENGINE=SummingMergeTree(gmt, (gmt, name), 8192, (a));

插入数据：

insert into ttt (gmt, name, a, b) values ('2017-07-10', 'a', 1, 2),
('2017-07-10', 'b', 2, 1),('2017-07-11', 'b', 3, 8),('2017-07-11', 'b', 3, 8),
('2017-07-11', 'a', 3, 1),('2017-07-12', 'c', 1, 3);

OPTIMIZE TABLE ttt 后查询的结果为：

\4. AggregatingMergeTree

\5. CollapsingMergeTree

\6. VersionedCollapsingMergeTree

\7. GraphiteMergeTree

日志

具有最小功能的轻量级引擎。当需要快速写入许多小表（最多约100万行）并在以后整体读取它们时，该类型的引擎是最有效的。

1.TinyLog

最简单的一种引擎，每一列保存为一个文件，里面的内容是压缩过的，不支持索引。

这种引擎没有并发控制，所以，当你需要在读，又在写时，读会出错。并发写，内容都会坏掉。

所以，它的应用场景，基本上就是那种只写一次，然后就是只读的场景。同时，它也不适用于处理量大的数据，官方推荐，使用这种引擎的表最多 100 万行的数据。

因为这种引擎的实现非常简单，所以当你有很多很多的小表数据要处理时，使用它是比较合适的，最基本的，它在磁盘上的文件量很少，读一列数据只需要打开一个文件就好了。

在 Yandex.Metrica 产品中，这种引擎用于小批量的中间数据处理上。

\2. StripeLog

\3. Log

这种引擎跟 TinyLog 基本一致，它的改进点，是加了一个 __marks.mrk 文件，里面记录了每个数据块的偏移，这种做的一个用处，就是可以准确地切分读的范围，从而使用并发读取成为可能。

但是，它是不能支持并发写的，一个写操作会阻塞其它读写操作。

Log 不支持索引，同时因为有一个 __marks.mrk 的冗余数据，所以在写入数据时，一旦出现问题，这个表就废了。

同 TinyLog 差不多，它适用的场景也是那种写一次之后，后面就是只读的场景，临时数据用它保存也可以。

集成引擎

用于与其他的数据存储与处理系统集成的引擎。

1.Kafka

\2. MySQL

\3. ODBC

\4. JDBC

\5. HDFS

用于其他特定功能的引擎

1.Distributed

Merge 可以看成是单机版的 Distributed ，而真正的 Distributed 具备跨服务器能力，当然，机器地址的配置依赖配置文件中的信息。

与 Merge 类似， Distributed 也是通过一个逻辑表，去访问各个物理表，设置引擎时的样子是：

   Distributed(remote_group, database, table [, sharding_key])

其中：

1) remote_group 是配置文件（默认在 /etc/clickhouse-server/config.xml ）中的 remote_servers 一节的配置信息。

2) database 是各服务器中的库名。

3) table 是表名。

sharding_key 是一个 寻址表达式 ，可以是一个列名，也可以是像 rand() 之类的函数调用，它与 remote_servers 中的 weight 共同作用，决定在写时往哪个 shard 写。

下面的重点，就是配置文件中的 remote_servers 了：

----------------------------------------------------------------------------------------

<remote_servers>
    <log>
        <shard>
            <weight>1</weight>
            <internal_replication>false</internal_replication>
            <replica>
                <host>172.17.0.3</host>
                <port>9000</port>
            </replica>
        </shard>

        <shard>
            <weight>2</weight>
            <internal_replication>false</internal_replication>
            <replica>
                <host>172.17.0.4</host>
                <port>9000</port>
            </replica>
        </shard>
    </log>
</remote_servers>
-----------------------------------------------------------------

log 是某个 shard 组的名字，就是上面的 remote_group 的值。

shard 是固定标签。

weight 是权重，前面说的 sharding_key 与这个有关。简单来说，上面的配置，理论上来看，第一个 shard “被选中”的概率是 1 / 1 + 2 ，第二个是 2 / 1 + 2 ，这很容易理解。但是， sharding_key 的工作情况，是按实际数字的“命中区间”算的，即第一个的区间是 [0, 1) 的周期，第二个区间是 [1, 1+2) 的周期。比如把 sharding_key 设置成 id ，当 id=0 或 id=3 时，一定是写入到第一个 shard 中，如果把 sharding_key 设置成 rand() ，那系统会对应地自己作一般化转换吧，这种时候就是一种概率场景了。

internal_replication 是定义针对多个 replica 时的写入行为的。如果为 false ，则会往所有的 replica 中写入数据，但是并不保证数据写入的一致性，所以这种情况时间一长，各 replica 的数据很可能出现差异。如果为 true ，则只会往第一个可写的 replica 中写入数据（剩下的事“物理表”自己处理）。

replica 就是定义各个冗余副本的，选项有 host ， port ， user ， password 这些。

\2. MaterializedView

\3. Dictionary

\4. Merge

一个工具引擎，本身不保存数据，只用于把指定库中的指定多个表链在一起。这样，读取操作可以并发执行，同时也可以利用原表的索引，但是，此引擎不支持写操作。

指定引擎的同时，需要指定要链接的库及表，库名可以使用一个表达式，表名可以使用正则表达式指定。

create table t1 (id UInt16, name String) ENGINE=TinyLog;
create table t2 (id UInt16, name String) ENGINE=TinyLog;
create table t3 (id UInt16, name String) ENGINE=TinyLog;
insert into t1(id, name) values (1, 'first');
insert into t2(id, name) values (2, 'xxxx');
insert into t3(id, name) values (12, 'i am in t3');
create table tt (id UInt16, name String) ENGINE=Merge(currentDatabase(), '^t');

\5. File

\6. Null

空引擎，写入的任何数据都会被忽略，读取的结果一定是空。

但是注意，虽然数据本身不会被存储，但是结构上的和数据格式上的约束还是跟普通表一样是存在的，同时，你也可以在这个引擎上创建视图。

\7. Set

Set 这个引擎有点特殊，因为它只用在 IN 操作符右侧，你不能对它 select 。

create table scope(id UInt16, name String) ENGINE=Set;

创建了 scope 表之后，是不能： select * from scope 的，会报错。

insert into scope(id, name) values (1, 'hello');

在 IN 右侧使用 scope 时，要求列数量，及每列的类型都必须匹配，所以直接：

select 1 where (1, 'hello') in scope;

Set 引擎表，是全内存运行的，但是相关数据会落到磁盘上保存，启动时会加载到内存中。所以，意外中断或暴力重启，是可能产生数据丢失问题的。

\8. Join

\9. URL

\10. View

\11. Memory

内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。可以并行读，读写互斥锁的时间也非常短。

不支持索引，简单查询下有非常非常高的性能表现。

一般用到它的地方不多，除了用来测试，就是在需要非常高的性能，同时数据量又不太大（上限大概 1 亿行）的场景。

\12. Buffer

Buffer 引擎，像是 Memory 存储的一个上层应用似的（磁盘上也是没有相应目录的）。它的行为是一个缓冲区，写入的数据先被放在缓冲区，达到一个阈值后，这些数据会自动被写到指定的另一个表中。

同时，说它像 Memory 的另一个原因，是它也跟 Memory 一样，有很多的限制，比如没有索引什么的。

Buffer 是接在其它表前面的一层，对它的读操作，也会自动应用到后面表，但是因为前面说到的限制的原因，一般我们读数据，就直接从源表读就好了，缓冲区的这点数据延迟，只要配置得当，影响不大的。

Buffer 后面也可以不接任何表，这样的话，当数据达到阈值，就会被丢弃掉。

关于 Buffer 的其它一些点：

如果一次写入的数据太大或太多，超过了 max 条件，则会直接写入源表。
删源表或改源表的时候，建议 Buffer 表删了重建。
“友好重启”时， Buffer 数据会先落到源表，“暴力重启”， Buffer 表中的数据会丢失。
即使使用了 Buffer ，多次的小数据写入，对比一次大数据写入，也 慢得多 （几千行与百万行的差距）。

四、clickhouse使用

大小写问题

以下场景的关键字是大小写不敏感的： - 标准SQL。例如，SELECT, select 和 SeLeCt 都是允许的 - 在某些流行的RDBMS中被实现的关键字，例如，DateTime 和 datetime是一样的

和标准SQL相反，所有其它的关键字都是 大小写敏感的，包括函数名称。 In contrast to standard SQL, all other keywords (including functions names) are case-sensitive.
关键字问题

关键字不是保留的；它们仅在相应的上下文中才会被处理。如果你使用和关键字同名的变量名，需要使用双引号或转移符将它们包含起来。例如：如果表 table_name 包含列 "FROM"，那么 SELECT "FROM" FROM table_name 是合法的。
查询语言


SELECT [DISTINCT] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] ANY|ALL INNER|LEFT JOIN (subquery)|table USING columns_list
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list]
[LIMIT [n, ]m]
[UNION ALL ...]
[INTO OUTFILE filename]
[FORMAT format]
[LIMIT n BY columns]

ClickHouse 中有两种类型的解析器， full parser 和 data format parser ，前者是一个完整的 SQL 解析器，后者是一个高性能的流解析器。当语句被发到 ClickHouse 时，默认配置下前 1 MB 字节的数据会使用 full parser 来处理，剩下的数据就交给 data format parser 了，所以，像 insert 这类语句，即使整个语句再长，也不会有问题。

语法细节，整体上跟 MySQL 是一样的，当然， ClickHouse 在一些地方有自己特别实现。

比如，对于别名 Synonyms ， ClickHouse 中的限制就少很多：

sql select ((select 1) as n), n

这种语句，在 ClickHouse 中都是被允许的。

4.建表语句：CREATE TABLE

建表语句除了基本形式外，还有两个扩展形式：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [db.]name
(
name1 [type1] [DEFAULT | MATERIALIZED | ALIAS expr1],
name2 [type2] [DEFAULT | MATERIALIZED | ALIAS expr2],
...
) ENGINE = engine

这是基本形式，如果引擎支持索引的话，索引可以在 ENGINE 的地方额外设置。

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [db.]name AS [db2.]name2 [ENGINE = engine]]

第一种扩展形式，可以创建一个跟指定表完全一样的表，但是可以更换不同的引擎。

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [db.]name ENGINE = engine AS SELECT ...

这种形式是“建表并填充”，表字段会自动根据 SELECT 的返回内容设置，并且，返回内容会作为新表内容填充进去。

create table t (id UInt16, name String) ENGINE = Memory;
insert into t(id, name) values (1, 'abc'), (2, 'xxx');

\5. ClickHouse 支持的join类型说明

按照代码Join.h的说明，ClickHouse支持14种Join，如下所示：

JOIN-s could be of these types:

ALL × LEFT/INNER/RIGHT/FULL

ANY × LEFT/INNER/RIGHT

SEMI/ANTI x LEFT/RIGHT

ASOF x LEFT/INNER

CROSS

All和Any的区别如官网文档所示：

ANY 与 ALL

在使用ALL修饰符对JOIN进行修饰时，如果右表中存在多个与左表关联的数据，那么系统则将右表中所有可以与左表关联的数据全部返回在结果中。这与SQL标准的JOIN行为相同。在使用ANY修饰符对JOIN进行修饰时，如果右表中存在多个与左表关联的数据，那么系统仅返回第一个与左表匹配的结果。如果左表与右表一一对应，不存在多余的行时，ANY与ALL的结果相同。

\6. sample子句

1)、近似查询，近工作在MergeTree*类型的表中，且建表时指定采样表达式

2)、sample子句可用sample k来表示，k可以是0-1的小数值或一个足够大的正整数

k为小数时，查询将使用k做百分比选取数据，sample 0.1只检索数据总量的10%

k足够大的正整数，k为最大样本数，sample 10000000检索最多10000000行数据


取数据总量的0.1 （10%）的数据，查不会自动校正聚合函数最终结果，为更精确结果，count()*10
SELECT
Title,
count() * 10 AS PageViews
FROM hits_distributed
SAMPLE 0.1
WHERE
CounterID = 34
AND toDate(EventDate) >= toDate('2013-01-29')
AND toDate(EventDate) <= toDate('2013-02-04')
AND NOT DontCountHits
AND NOT Refresh
AND Title != ''
GROUP BY Title
ORDER BY PageViews DESC LIMIT 1000

\7. null的处理

1、JOIN的行为受 join_use_nulls 的影响。当join_use_nulls=1时，JOIN的工作与SQL标准相同。

2、如果JOIN的key是 Nullable 类型的字段，则至少一个存在 NULL 值的key不会被关联。

对于GROUP BY子句，ClickHouse将 NULL 解释为一个值，并且支持NULL=NULL

\8. where子句

该子句中须包含一个UInt8类型的表达式：通常是一个带有比较和逻辑的表达式：在所有数据转换前用来过滤数据；如果在支持索引的数据库表引擎中，这个表达式将被评估是否使用索引。

\9. group by

必含一个表达式列表，每个表达式将被称之为“key”。 SELECT，HAVING，ORDER BY子句中的表达式列表必须来自于这些“key”或聚合函数。被选择的列中不能包含非聚合函数或key之外的其他列。

如查询表达式中仅含聚合函数，则可省略GROUP BY，这时会假定将所有数据聚合成一组空“key”。

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

1、与SQL标准不同，如表中不存在任何数据（表不存在任何数据，或被WHERE过滤掉了），将返回一个空结果，而不是一个包含聚合函数初始值的结果。

2、与MySQL不同（实际上这是符合SQL标准的），不能获得一个不在key中的非聚合函数列（除了常量表达式），但可使用‘any’（返回遇到的第一个值）、max、min等聚合函数使它工作

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子句会为遇到的每一个不同的key计算一组聚合函数的值
在GROUP BY子句中不使用Array类型的列
常量不能作为聚合函数的参数传入聚合函数中，如sum(1)，这种情况下你可以省略常量：count()

\10. 函数

– 检测函数类型（clickhouse中数据的类型）
SELECT toTypeName(0);-- UInt8(三位数为8)
SELECT toTypeName(-0);-- Int8
SELECT toTypeName(-343);-- Int16
SELECT toTypeName(12.43); – Float64(默认浮点型的数据为64)，所以一般在处理浮点型的数据的时候尽量转成toFloat32(12.43)
SELECT toTypeName(12.34343); – Float64
SELECT toTypeName(toDateTime(1502396027)); – DateTime

– 一、算数函数
–>>>>>> 算数函数(数学上的计算)
–求和
SELECT plus(12, 21), plus(10, -10), plus(-10, -10);
–差值
SELECT minus(10, 5), minus(10, -10),minus(-10, -10);
–积
SELECT multiply(12, 2), multiply(12, -2), multiply(-12, -2);
–平均值
SELECT divide(12, 4), divide(10, 3), divide(2, 4), divide(-4, -2), divide(-4, 2), divide(-4.5, 3);
SELECT intDiv(10, 3), divide(10, 3); – 3, 3.333(保留四位有效数字)
SELECT divide(10, 0), divide(-10, 0); – 出现无穷大字符“ ∞ ”或“ -∞ ”
SELECT divide(0, 0); – 特殊字符（类似乱码）
SELECT intDivOrZero(10, 0); – 0
–求余数
SELECT modulo(10, 3); --1
SELECT modulo(10.5, 3); --1
–取反
SELECT negate(10), negate(-10); – -10 10
–绝对值
SELECT abs(-10), abs(10);
–最大公约数
SELECT gcd(12, 24), gcd(-12, -24), gcd(-12, 24);
–最小公倍数
SELECT lcm(12, 24), lcm(-12, -24), lcm(-3, 4);

– 二、比较函数
–>>>>>> 比较函数(始终返回0表示false 或 1表示true)
SELECT 12 == 12, 12 != 10, 12 == 132, 12 != 12, 12 <> 12;
SELECT equals(12, 12), notEquals(12, 10), equals(12, 10), notEquals(12,123);
SELECT greater(12, 10), greater(10, 12), greater(12, 12);-- 前者是否大于后者
SELECT greaterOrEquals(12,10), greaterOrEquals(12,12);-- 前者是否大于或等于后者
SELECT less(12, 21), less(12, 10), less(120, 120);-- 前者是否小于后者
SELECT lessOrEquals(12, 120), lessOrEquals(12, 12);-- 前世是否小于或等于或者

– 三、逻辑函数
–>>>>>> 逻辑操作符（返回0表示false 或 1表示true）
SELECT 1212 or 12!=10;
SELECT 1212 and 12!=10;
SELECT not 12, not 0;
SELECT or(equals(12, 12), notEquals(12, 10)); --函数表示法：或
SELECT and(equals(12, 12), notEquals(12, 10));–函数表示法：且
SELECT not(12), not(0);

– 四、类型转换函数
–>>>>>> 类型转换函数部分示例：
SELECT toInt8(12.3334343), toFloat32(10.001), toFloat64(1.000040);
SELECT toString(now());
SELECT now() AS now_local, toString(now(), ‘Asia/Yekaterinburg’) AS now_yekat;
SELECT now() AS now_local, toDate(now()), toDateTime(now()), toUnixTimestamp(now());

SELECT
‘2016-06-15 23:00:00’ AS timestamp,
CAST(timestamp AS DateTime) AS datetime,
CAST(timestamp AS Date) AS date,
CAST(timestamp, ‘String’) AS string,
CAST(timestamp, ‘FixedString(22)’) AS fixed_string;

WITH
toDate(‘2019-01-01’) AS date,
INTERVAL 1 WEEK AS interval_week,
toIntervalWeek(1) AS interval_to_week,
toIntervalMonth(1) AS interval_to_month
SELECT
date + interval_week,
date + interval_to_week,
date + interval_to_month;

WITH
toDateTime(‘2019-01-01 12:10:10’) as datetime,
INTERVAL 1 HOUR AS interval_hour,
toIntervalHour(1) as invterval_to_hour
SELECT
plus(datetime, interval_hour),
plus(datetime, invterval_to_hour);

– 五、时间日期函数
—>>>>>> 时间日期函数
SELECT
toDateTime(‘2019-07-30 10:10:10’) AS time,
– 将DateTime转换成Unix时间戳
toUnixTimestamp(time) as unixTimestamp,
– 保留时-分-秒
toDate(time) as date_local,
toTime(time) as date_time,-- 将DateTime中的日期转换为一个固定的日期，同时保留时间部分。
– 获取年份，月份，季度，小时，分钟，秒钟
toYear(time) as get_year,
toMonth(time) as get_month,
– 一年分为四个季度。1（一季度:1-3）,2（二季度:4-6）,3（三季度:7-9）,4（四季度:10-12）
toQuarter(time) as get_quarter,
toHour(time) as get_hour,
toMinute(time) as get_minute,
toSecond(time) as get_second,
– 获取 DateTime中的当前日期是当前年份的第几天，当前月份的第几日，当前星期的周几
toDayOfYear(time) as “当前年份中的第几天”,
toDayOfMonth(time) as “当前月份的第几天”,
toDayOfWeek(time) as “星期”,
toDate(time, ‘Asia/Shanghai’) AS date_shanghai,
toDateTime(time, ‘Asia/Shanghai’) AS time_shanghai,
– 得到当前年份的第一天,当前月份的第一天，当前季度的第一天，当前日期的开始时刻
toStartOfYear(time),
toStartOfMonth(time),
toStartOfQuarter(time),
toStartOfDay(time) AS cur_start_daytime,
toStartOfHour(time) as cur_start_hour,
toStartOfMinute(time) AS cur_start_minute,
– 从过去的某个固定的时间开始，以此得到当前指定的日期的编号
toRelativeYearNum(time),
toRelativeQuarterNum(time);

SELECT
toDateTime(‘2019-07-30 14:27:30’) as time,
toISOYear(time) AS iso_year,
toISOWeek(time) AS iso_week,
now() AS cur_dateTime1, – 返回当前时间yyyy-MM-dd HH:mm:ss
today() AS cur_dateTime2, – 其功能与’toDate(now())'相同
yesterday() AS yesterday, – 当前日期的上一天
– timeSlot(1) AS timeSlot_1, – 出现异常！！将时间向前取整半小时
toDate(time) as getY_M_d;

– 目前只有这三种格式，没有什么toYYYY(),toYYYddmm()之类的函数，不要想当然。
SELECT
now() as nowTime,
– 将Date或DateTime转换为包含年份和月份编号的UInt32类型的数字（YYYY * 100 + MM）
toYYYYMMDDhhmmss(nowTime),
toYYYYMMDD(nowTime),
toYYYYMM(nowTime);

– formatDateTime(Time, Format[,Timezone])函数引用
SELECT
now() as now_time,
toDateTime(‘2019-07-31 18:20:30’) AS def_datetime,
formatDateTime(now_time, ‘%D’) AS now_time_day_month_year,-- 07/30/19
– toDateTime(‘2019-07-31 18:20:30’, ‘Asia/Shanghai’) AS def_datetime1, – 指定时区
formatDateTime(def_datetime, ‘%Y’) AS def_datetime_year, – 2019（指定日期为2019年）
formatDateTime(def_datetime, ‘%y’) AS def_datetime_year_litter, – 19（指定日期为19年,Year, last two digits (00-99),本世纪的第19年）
formatDateTime(def_datetime, ‘%H’) AS hour24, – 18 下午六点
formatDateTime(def_datetime, ‘%I’) AS hour12, – 06下午六点
formatDateTime(def_datetime, ‘%p’) AS PMorAM, – 指定时间是上午还是下午
formatDateTime(def_datetime, ‘%w’) AS def_datetime_get_curWeek,-- 3(指定日期为星期三)
formatDateTime(def_datetime, ‘%F’) AS def_datetime_get_date,-- 2019-07-31
formatDateTime(def_datetime, ‘%T’) AS def_datetime_get_time,-- 18:20:30
formatDateTime(def_datetime, ‘%M’) AS def_datetime_get_minute,-- 20(得到指定事件的“分”，minute (00-59))
formatDateTime(def_datetime, ‘%S’) AS def_datetime_get_second;-- 30(得到指定事件的“秒”，second (00-59))

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dXcIiKFx-1620269005648)(http://hezi.knowbox/download/attachments/35081588/image2021-4-29_15-29-54.png?version=1&modificationDate=1619681394201&api=v2)]

– 1.跳转到之后的日期函数
– 第一种，日期格式（指定日期，需注意时区的问题）
WITH
toDate(‘2019-09-09’) AS date,
toDateTime(‘2019-09-09 00:00:00’) AS date_time
SELECT
addYears(date, 1) AS add_years_with_date,
addYears(date_time, 0) AS add_years_with_date_time;

– 第二种，日期格式（当前，本地时间）
WITH
toDate(now()) as date,
toDateTime(now()) as date_time
SELECT
now() as now_time,-- 当前时间
addYears(date, 1) AS add_years_with_date,-- 之后1年
addYears(date_time, 1) AS add_years_with_date_time,
addMonths(date, 1) AS add_months_with_date,-- 之后1月
addMonths(date_time, 1) AS add_months_with_date_time,
addWeeks(date, 1) AS add_weeks_with_date,–之后1周
addWeeks(date_time, 1) AS add_weeks_with_date_time,
addDays(date, 1) AS add_days_with_date,-- 之后1天
addDays(date_time, 1) AS add_days_with_date_time,
addHours(date_time, 1) AS add_hours_with_date_time,–之后1小时
addMinutes(date_time, 1) AS add_minutes_with_date_time,–之后1分中
addSeconds(date_time, 10) AS add_seconds_with_date_time,-- 之后10秒钟
addQuarters(date, 1) AS add_quarters_with_date, – 之后1个季度
addQuarters(date_time, 1) AS add_quarters_with_date_time;

– 2.跳转到当前日期之前的函数(函数将Date/DateTime减去一段时间间隔，然后返回Date/DateTime)
WITH
toDate(now()) as date,
toDateTime(now()) as date_time
SELECT
subtractYears(date, 1) AS subtract_years_with_date,
subtractYears(date_time, 1) AS subtract_years_with_date_time,
subtractQuarters(date, 1) AS subtract_Quarters_with_date,
subtractQuarters(date_time, 1) AS subtract_Quarters_with_date_time,
subtractMonths(date, 1) AS subtract_Months_with_date,
subtractMonths(date_time, 1) AS subtract_Months_with_date_time,
subtractWeeks(date, 1) AS subtract_Weeks_with_date,
subtractWeeks(date_time, 1) AS subtract_Weeks_with_date_time,
subtractDays(date, 1) AS subtract_Days_with_date,
subtractDays(date_time, 1) AS subtract_Days_with_date_time,
subtractHours(date_time, 1) AS subtract_Hours_with_date_time,
subtractMinutes(date_time, 1) AS subtract_Minutes_with_date_time,
subtractSeconds(date_time, 1) AS subtract_Seconds_with_date_time;

SELECT toDate(‘2019-07-31’, ‘Asia/GuangZhou’) as date_guangzhou;
SELECT toDate(‘2019-07-31’), toDate(‘2019-07-31’, ‘Asia/Beijing’) as date_beijing;
– 亚洲只能加载上海的timezone？？？
SELECT toDateTime(‘2019-07-31 10:10:10’, ‘Asia/Shanghai’) as date_shanghai;

– 计算连个时刻在不同时间单位下的差值
– 第一种：指定时间计算差值示例
WITH
toDateTime(‘2019-07-30 10:10:10’, ‘Asia/Shanghai’) as date_shanghai_one,
toDateTime(‘2020-10-31 11:20:30’, ‘Asia/Shanghai’) as date_shanghai_two
SELECT
dateDiff(‘year’, date_shanghai_one, date_shanghai_two) as diff_years,
dateDiff(‘month’, date_shanghai_one, date_shanghai_two) as diff_months,
dateDiff(‘week’, date_shanghai_one, date_shanghai_two) as diff_week,
dateDiff(‘day’, date_shanghai_one, date_shanghai_two) as diff_days,
dateDiff(‘hour’, date_shanghai_one, date_shanghai_two) as diff_hours,
dateDiff(‘minute’, date_shanghai_one, date_shanghai_two) as diff_minutes,
dateDiff(‘second’, date_shanghai_one, date_shanghai_two) as diff_seconds;

– 第二种：本地当前时间示例
WITH
now() as date_time
SELECT
dateDiff(‘year’, date_time, addYears(date_time, 1)) as diff_years,
dateDiff(‘month’, date_time, addMonths(date_time, 2)) as diff_months,
dateDiff(‘week’, date_time, addWeeks(date_time, 3)) as diff_week,
dateDiff(‘day’, date_time, addDays(date_time, 3)) as diff_days,
dateDiff(‘hour’, date_time, addHours(date_time, 3)) as diff_hours,
dateDiff(‘minute’, date_time, addMinutes(date_time, 30)) as diff_minutes,
dateDiff(‘second’, date_time, addSeconds(date_time, 35)) as diff_seconds;

– timeSlot(StartTime, Duration, [,Size])
– 它返回一个时间数组，其中包括从从“StartTime”开始到“StartTime + Duration 秒”内的所有符合“size”（以秒为单位）步长的时间点
– 作用:搜索在相应会话中综合浏览量是非常有用的。
SELECT
timeSlots(toDateTime(‘2012-01-01 12:20:00’), toUInt32(600)) as dateTimeArray,
dateTimeArray[0] as arr_index_0, – no result.
dateTimeArray[1] as arr_index_1, – 2012-01-01 20:00:00
dateTimeArray[2] as arr_index_2, – 2012-01-01 20:30:00
dateTimeArray[3] as arr_index_3, – no result.
dateTimeArray[4] as arr_index_4; – no result.

– toUInt32(600) 表示之后间距20秒的时刻

SELECT
timeSlots(now(), toUInt32(600), 20) as dateTimeArray, – 类似于：引用地址
dateTimeArray[0] as arr_index_0, – no result.为什么？
dateTimeArray[1] as arr_index_1,
dateTimeArray[2] as arr_index_2,
dateTimeArray[3] as arr_index_3,
dateTimeArray[4] as arr_index_4,
dateTimeArray[5] as arr_index_5;

– 指定时间为基准，之后每个元素增加20秒

SELECT
timeSlots(toDateTime(‘2012-01-01 12:20:00’), toUInt32(600), 20) as cur_dateTimeArray, – 类似于：引用地址
cur_dateTimeArray[0] as arr_index_0, – no result.为什么？
cur_dateTimeArray[1] as arr_index_1, – 2012-01-01 20:20:00
cur_dateTimeArray[2] as arr_index_2, – 2012-01-01 20:20:20
cur_dateTimeArray[3] as arr_index_3, – 2012-01-01 20:20:40
cur_dateTimeArray[4] as arr_index_4, – 2012-01-01 20:21:00
cur_dateTimeArray[5] as arr_index_5; – 2012-01-01 20:21:20

– 六、字符串函数
—>>>>>> 字符串函数：
SELECT
length(‘hello world’) as w0, – 按照Unicode编码计算长度“你好”的长度为6
empty(‘hello world’) as w1,-- 判断字符串是否为空，空为1，非空为0
notEmpty(‘hello world’) as w2,
lengthUTF8(‘hello world’) as w3, – 按照实际字符计算长度“你好”为2
char_length(‘hello world’) as w4, – 同 lengthUTF8()
character_length(‘hello world’) as w5, – 同 lengthUTF8(),
lower(‘abcd123–’) as w6,–字母全部小写（将字符串中的ASCII转换为小写。）
upper(‘abcd123–’) as w7,–字母全部大写（将字符串中的ASCII转换为大写。）
lowerUTF8(‘abcd123-/*\8asd-\’) as w8, – abcd123-/8asd-
upperUTF8(‘abcd123–’) as w9, – ABCD123–
isValidUTF8('abcd123–/*’) as w10; --检查字符串是否为有效的UTF-8编码，是则返回1，否则返回0。

SELECT notEmpty(’’), notEmpty(NULL), notEmpty(‘he’); – 0,空,1
SELECT toValidUTF8(’\x61\xF0\x80\x80\x80b’);
– reverseUTF8():以Unicode字符为单位反转UTF-8编码的字符串。如果字符串不是UTF-8编码，则可能获取到一个非预期的结果（不会抛出异常）
SELECT reverse(‘abcdefg’), reverseUTF8(‘abcdefg’);

– 2.字符串维度自定义安排
SELECT format(’{1} {0} {1}’, ‘World’, ‘Hello’); – 输出：Hello World Hello
SELECT format(’{0} {0} {1} {1}’, ‘one’, ‘two’); – 输出：one one two two
SELECT format(’{} {}’, ‘Hello’, ‘World’); – 输出：Hello World

– 3.字符串拼接 concat(s1,s2,s3,…)
SELECT concat(‘Hello’,’ ‘,‘World’, ‘!’);-- Hello World!
– 与concat相同，区别在于，你需要保证concat(s1, s2, s3) -> s4是单射的，它将用于GROUP BY的优化。
SELECT concatAssumeInjective(‘Hello’,’ ',‘World’, ‘!’);-- Hello World!

– 4.字符串截取：substring(s, offset, length), mid(s, offset, length), substr(s, offset, length)
– 以字节为单位截取指定位置字符串，返回以‘offset’位置为开头，长度为‘length’的子串。‘offset’从1开始（与标准SQL相同）。‘offset’和‘length’参数必须是常量。
SELECT
substring(‘abcdefg’, 1, 3),-- abc
substring(‘你好，世界’, 1, 3),-- 你
substringUTF8(‘你好，世界’, 1, 3); – 你好，

– 5.字符串拼接：appendTrailingCharIfAbsent(s, c)
– 如果‘s’字符串非空并且末尾不包含‘c’字符，则将‘c’字符附加到末尾。
SELECT
appendTrailingCharIfAbsent(‘good’,‘c’), – goodc
appendTrailingCharIfAbsent(‘goodccc’,‘c’); – goodccc

– 6.字符串编码转换：convertCharset(s, from, to) 返回从‘from’中的编码转换为‘to’中的编码的字符串‘s’。
SELECT
convertCharset(‘hello’, ‘UTF8’,‘Unicode’),
convertCharset(‘hello’, ‘Unicode’, ‘UTF8’),
convertCharset(‘hello’, ‘Unicode’, ‘ASCII’),–
convertCharset(‘hello’, ‘ascii’, ‘ascii’),–hello
convertCharset(‘hello’, ‘UTF8’,‘UTF8’);-- hello
SELECT
base64Encode(‘username+password’),-- dXNlcm5hbWUrcGFzc3dvcmQ=
base64Decode(‘dXNlcm5hbWUrcGFzc3dvcmQ=’), – username+password
– 使用base64将字符串解码成原始字符串。但如果出现错误，将返回空字符串。
tryBase64Decode(‘dXNlcm5hbWUrcGFzc3dvcmQ=’);

– 7.判断字符串是否已什么结尾或结束，返回1：true，0：flase
– endsWith(s, suffix) 返回是否以指定的后缀结尾。如果字符串以指定的后缀结束，则返回1，否则返回0
– startWith(s, prefix) 返回是否以指定的前缀开头。如果字符串以指定的前缀开头，则返回1，否则返回0。
SELECT
endsWith(‘string’,‘g’),
startsWith(‘string’, ‘str’); – 1 true

– 8.删除左侧空白字符
– trimLeft(s) 返回一个字符串，用于删除左侧的空白字符
– trimRight(s) 返回一个字符串，用于删除右侧的空白字符
– trimBoth(s) 返回一个字符串，用于删除左侧和右侧的空白字符
SELECT
trimLeft(’ sdfdgs’), – sdfdgs
trimRight('abcd ‘), – abcd
trimBoth(’ abcd '); – abcd

– 七、字符串搜索函数

—>>>>>> 字符串搜索函数
– pasition(haystack, needle), 显示needle在haystack的第一个出现的位置。
SELECT
POSITION(‘2121stringstrstrstrstr’,‘str’) AS positionSearch, – 5
POSITION(‘你好，hello,12323-你好，你，好sdfd*dg’, ‘你，好’),-- 31
positionUTF8(‘n12你好’,‘你好’) AS positionUTF8,-- 4
positionCaseInsensitive(‘ABCDCDEFABCD’,‘bc’) AS positionCaseInsensitive, --2
locate(‘hellohellohellohello’,‘ello’); – 2
– multiSearchAllPositions(haystack, [needle1, needle2, …, needlen])

– 注意：在所有multiSearch*函数中，由于实现规范，needles的数量应小于2^8。

– 函数返回一个数组，其中包含所有匹配needlei的位置
SELECT
multiSearchAllPositions(‘goodnamegoodnamegoodhellohihihi’, [‘dn’, ‘good’]) as multiSearch,-- [4,1]
multiSearchAllPositionsCaseInsensitive(‘nameSsdfagpSSDFDFetgfderef’, [‘SS’,‘fa’]) as multiCaseInsensitive,
multiSearchAllPositionsUTF8(‘nameSsdfazz轴功率gpSSDFDFetgfderef’, [‘Ss’,‘fa’, ‘zz轴’]) AS multiSearchUTF8,
multiSearchAllPositionsCaseInsensitiveUTF8(‘nameSsdfazz轴功率gpSSDFDFetgfderef’, [‘Ss’,‘fa’, ‘zz轴’]) AS multiCaseInsensitiveUTF8;

– 检查字符串是否与pattern正则表达式匹配。pattern可以是一个任意的re2正则表达式。 re2正则表达式的语法比Perl正则表达式的语法存在更多限制。
– match(haystack, pattern) 匹配到了则返回1，否则返回0
SELECT
match(‘1232434sadgaDDFSrefds’, ‘[0-9a-zA-Z]’), – 存在匹配的字符，返回1
match(‘1232321’, ‘[a-z]’); – 不存在匹配的字符，返回0
– 与match相同，但如果所有正则表达式都不匹配，则返回0；如果任何模式匹配，则返回1。它使用hyperscan库。对于在字符串中搜索子字符串的模式，最好使用“multisearchany”，因为它更高效。
– multiMatchAny(haystack, [pattern1, pattern2, …, patternn])
– 注意：任何haystack字符串的长度必须小于232字节，否则抛出异常。这种限制是因为hyperscan API而产生的。

– 多个正则表达式对原始字符进行匹配，如若只有一个正则表达式匹配上了则返回1，否则返回0

SELECT
multiMatchAny(‘abcABC’,[’[0-9]’,’[a-zA-Z]’]) AS multiMatchAnyOne, – 1
multiMatchAny(‘123abcABC’,[’[0-9]’,’[a-zA-Z]’]) AS multiMatchAnyTwo, --1
– 与multiMatchAny相同，但返回与haystack匹配的任何内容的索引位置。
multiMatchAnyIndex(‘123abcABC’, [’[0-9]’,’[a-zA-Z]’]) as multiMatchAnyIndex; --2
– 模糊匹配：like()函数，注意大写敏感。
– % 表示任何字节数（包括零字符）
– _ 表示任何一个字节
SELECT
‘hello’ LIKE ‘%h%’ as LIKE_UP, – 1
‘hello’ like ‘he’ AS like_low, – 0
‘hello’ not like ‘he’ AS not_like, – 1
‘hello’ like ‘%he%’ AS like_litter, – 1
like(‘adgadgadfa1232’, ‘12’) AS like_func,
like(‘sdfasdfasd’, ‘[a-z]’) AS like_func2, – 0
notLike(‘1232423’, ‘[a-zA-Z]’) AS not_like_func; – 1

– 使用字符串截取字符串：extract(haystack, pattern)
– 使用正则表达式截取字符串。如果‘haystack’与‘pattern’不匹配，则返回空字符串。如果正则表达式中不包含子模式，它将获取与整个正则表达式匹配的子串。否则，它将获取与第一个子模式匹配的子串。
SELECT
extractAll(‘hellogoodaimantIdeaIDEAfasd123232’, ‘[0-9]’), – [‘1’,‘2’,‘3’,‘2’,‘3’,‘2’]
extractAll(‘12323dSDFRE’, ‘[A-Z]’),-- [‘S’,‘D’,‘F’,‘R’,‘E’]
extract(‘helloclickhouse’, ‘[a-z]’);-- h
– ngramSearch(haystack, needle)

–八、字符串替换函数

—>>>>>> 字符串替换函数

– 替换匹配到的字符串
– replaceOne(haystack, pattern, replacement)
– 用‘replacement’子串替换‘haystack’中与‘pattern’子串第一个匹配的匹配项（如果存在）。 ‘pattern’和‘replacement’必须是常量。
– replaceAll(haystack, pattern, replacement), replace(haystack, pattern, replacement)
– 用‘replacement’子串替换‘haystack’中出现的所有‘pattern’子串。
SELECT
replaceOne(‘hed1234544’, ‘4’, '’) AS replaceOne,-- hed123544
replaceRegexpOne(‘hed1234544’, ‘4’, '’) AS replaceRegexpOne,-- hed123544
replace(‘hed1234544’, ‘4’, '’) AS replace, – hed1235**
replaceAll(‘hed1234544’, ‘4’, '’) AS replaceAll;-- hed1235**

– 实例：2019-07-31 改变成 07/31/2019
SELECT
toDate(now()) AS now_date,
replaceRegexpOne(toString(now_date), ‘(\d{4})-(\d{2})-(\d{2})’, ‘\2/\3/\1’) AS format_date;

– 示例：赋值字符串10次
SELECT replaceRegexpOne(‘Hello, World!’, ‘.*’, ‘\0\0\0\0\0\0\0\0\0\0’) AS res;
– replaceRegexpAll(haystack, pattern, replacement)

– 与replaceRegexpOne相同，但会替换所有出现的匹配项。例如：
SELECT replaceRegexpAll(‘hello,world!’, ‘.’, ‘\0\0’) as res; – hheelllloo,wwoorrlldd!!
SELECT replaceRegexpAll(‘hello o o, world.’, ’ ', '’) as res; – helloo*o,*world.

– 函数：regexpQuoteMeta(s) 该函数用于在字符串中的某些预定义字符之前添加反斜杠。
– 预定义字符：‘0’，’’，’|’，’(’，’)’，’^’，’KaTeX parse error: Undefined control sequence: \0 at position 81: …uoteMeta略有不同。它以\̲0̲而不是\x00转义零字节，它只…#’); – \\|[]{}+_-=@!~`&^*%$#
SELECT toString(’\\’); – \

–九、条件函数

—>>>>>> 条件函数

– 1. if(cond, then, else)函数：类似于三元操作符。
– 中文字符使用双引号，英文字符可不使用引号也可使用当引号或双引号，根据具体情况而定。
– 如果cond ！= 0则返回then，如果cond = 0则返回else。 cond必须是UInt8类型，then和else必须存在最低的共同类型。
– 注意：then和else可以是NULL
SELECT
12 > 10 ? ‘desc’ : ‘asc’ AS “三元操作符”,
if(12 > 10, ‘desc’ , ‘asc’) AS “if()函数”,
if(12 > 10, NULL, NULL);

– 2. multiIf(cond_1, then_1, cond_2, then_2…else)
– 允许您在查询中更紧凑地编写CASE运算符。类似于java中的switch语法（可以接受2n+1个参数）
SELECT multiIf(1,‘one’,2,‘two’,3,‘three’,‘not this index’);-- 关联case条件表达式

–十、数学函数

—>>>>>> 数学函数
SELECT
1 * e() AS E,
1 * pi() AS PI,
sqrt(25) AS sqrt_25, --接受一个数值类型的参数并返回它的平方根。
cbrt(27) AS cbrt_27, --接受一个数值类型的参数并返回它的立方根。
exp(10), --接受一个数值类型的参数并返回它的指数
exp10(10), --接受一个数值类型的参数并返回它的10的x次幂。
log(10) AS LOG,
log2(10) AS LOG2, --接受一个数值类型的参数并返回它的底2对数。
ln(e()) AS LOG10; --接受一个数值类型的参数并返回它的自然对数

– 示例：三西格玛准则
SELECT erf(3 / sqrt(2)); – 0.997
SELECT
sin(90), – 返回x的三角正弦值。
cos(90), – 返回x的三角余弦值。
tan(90), – 返回x的三角正切值
acos(0), – 返回x的反三角余弦值。
asin(1), – 返回x的反三角正弦值。
atan(45); – 返回x的反三角正切值。
– pow(x, y), power(x, y) 接受x和y两个参数。返回x的y次方。
SELECT
pow(2, 3), – 2的三次方
pow(3, 2); – 3的平方
SELECT
intExp2(4), --2^4 接受一个数值类型的参数并返回它的2的x次幂（UInt64）。
intExp10(2);–10^2 接受一个数值类型的参数并返回它的10的x次幂（UInt64）。

– 十一、取整函数

—>>>>>> 取整函数

– 1.向下取整：floor(x[,N])
SELECT
floor(toFloat32(12.08098), 2), – 12.08
floor(toFloat32(12.2323), 2), – 12.23
floor(toFloat32(12.89788), -1), – 10
floor(toFloat32(12.09590), 3), – 12.095 (注意：如果按照正常的四舍五入，则应该是12.096，为什么呢？)
floor(toFloat32(12.0987), 3),-- 12.098
floor(10, 2); – 10

– 2.四舍五入：round(expression [, decimal_places])
– 如果decimal_places=0,则取整数；
– 如果>0,则将值舍入小数点右侧；
– 如果<0,则将小数点左侧的值四舍五入。
SELECT
round(toFloat32(12.1234), 3),
round(toFloat32(12.0025), 3), – 12.002(注意：为什么不是12.003呢？)
– round函数只会最多保留三位有效数字
round(toFloat32(12.0025), 4), – 12.002
round(toFloat32(12.0025002323), 100); – 12.003

– 示例：
SELECT
round(toFloat32(10 / 3)), – 3
round(toFloat32(10 / 3), 2), – 3.33
round(toFloat32(10.000/3), 3), – 3.333
round(toFloat32(10.000/3), 6); – 3.333
– roundToExp2() 接受一个数字。如果数字小于1，则返回0。否则，它将数字向下舍入到最接近的（整个非负）2的x次幂。
SELECT
roundToExp2(12.0129), – 8 = 2^3
roundToExp2(toFloat32(0.01)); – 0.008

– 3.向上取整：ceil(x[, N]) 或者 ceiling(x[, N])
SELECT
ceil(12.34343, 3), – 12.344
ceil(toFloat64(12.34343), 3), – 12.344
ceil(toFloat32(12.34343), 3), – 12.344
ceil(12.0011, 3); – 12.002

—十二、数组函数

—>>>>>> 数组函数

– 1.数组非空判断相关函数（真为1，假为0）
SELECT empty([]), empty([1,2,3]), notEmpty([1,2,3]), notEmpty([]);

– 2.数组长度 length() 返回数组中的元素个数。结果类型是UInt64。该函数也适用于字符串。
SELECT
– length(), – 出现异常
– length([true, false]), – 异常
– length([1,2,4]), --出现异常！
length([]), – 0
length([‘a’,‘b’,‘c’]), – 3
length([1,2,3]); – 3

– 3.扩展判断非空的部分函数如下：不接受任何参数并返回适当类型的空数组
SELECT
emptyArrayUInt8(), – UInt8的空数组
emptyArrayUInt16(),
emptyArrayUInt32(),
emptyArrayUInt64(),
emptyArrayDate(),
emptyArrayDateTime(),
emptyArrayInt8(),
emptyArrayInt16(),
emptyArrayInt32(),
emptyArrayInt64();

– 接受一个空数组并返回一个仅包含一个默认值元素的数组。(以下是部分示例)
SELECT
emptyArrayToSingle(emptyArrayInt32()), – 0
emptyArrayToSingle(emptyArrayUInt32()), – 0
emptyArrayToSingle(emptyArrayDate()), – 0002-11-30
emptyArrayToSingle(emptyArrayDateTime()); --0002-11-30 08:00:00

– 4.生成一个含有N个元素的数组,元素从0开始增长，步长尾1.

– range(N) 返回从0到N-1的数字数组。以防万一，如果在数据块中创建总长度超过100,000,000个元素的数组，则抛出异常
SELECT
range(10), – [0,1,2,3,4,5,6,7,8,9]
range(2), – [0,1]
– range(5.5), – 出现异常，N为Int8的数据类型，正整数
– range(-10), – 出现异常，DB::Exception: Illegal type Int8 of argument of function range
range(1); – 0

– 5.新建一个数组的函数：array(x1,……) 类似于直接[x1,……]
– 注意：新建数组的每个元素的数据类型需保持一致性。
SELECT
array(1,2,2,3,4) AS “array()函数”,
– [1,‘hello’,3], – 出现异常，DB::Exception: There is no supertype for types UInt8, String, UInt8 because some of them are String/FixedString and some of them are not (version 19.10.1.5 (official build))
[1,2,3,4] AS “[ ]”;

– 6.合并N个数组 arrayConcat(arrays) 合并参数中传递的所有数组。跟java的数组差不多的合并，不会自动去重，不会自动排序
SELECT
arrayConcat(array(1,2),array(2,3),array(4,5)), – [1,2,2,3,4,5]（第一种情况）
arrayConcat(array(1,1),array(2,2),array(3,3)), – [1,1,2,2,3,3]
– arrayConcat(array(1,2),[‘a’,‘c’],array(3,3)), – 出现异常，不能将不同类型的数组进行合并
arrayConcat(array(1,1),[2,3],array(4,5)); – [1,1,2,3,4,5]

– 7.从数组arr中获取索引为“n”的元素。

– n必须是任何整数类型。数组中的索引从一开始。支持负索引。在这种情况下，它选择从末尾开始编号的相应元素。例如，arr [-1]是数组中的最后一项。
– 如果索引超出数组的边界，则返回默认值（数字为0，字符串为空字符串等）.
SELECT
arrayElement(array(10,20,3), 1), – 10
arrayElement(array(1,20,3), 2), – 20
arrayElement(array(1,2,30), 3), – 30
arrayElement(array(10,20,3), 0), – 0
arrayElement(array(10,20,3), -3), – 10
arrayElement(array(10,20,3), -2), – 20
arrayElement(array(10,20,3), -1);-- 3

– 8.检查在数组中是否含有此元素。has(arr, elem) 包含此元素则返回1，否则返回0
– has() 检查’arr’数组是否具有’elem’元素。如果元素不在数组中，则返回0;如果在，则返回1。
– hasAny(arr1, arr2) 返回1表示arr1和arr2存在交集。否则返回0.
–注意：特殊的定义：
– ① “NULL”作为数组中的元素值进行处理。
– ② 忽略两个数组中的元素值的顺序
– hasAll(set, subset) 检查一个数组是否是另一个数组的子集。返回1，表示set包含subset中所有的元素
– set – 具有一组元素的任何类型的数组。
– subset – 任何类型的数组，其元素应该被测试为set的子集。
– 注意：特殊的定义：
– ① 空数组是任何数组的子集。
– ② “NULL”作为数组中的元素值进行处理。
– ③ 忽略两个数组中的元素值的顺序。
SELECT
has([1,2,3], 2), – 1
has(array(1,2,3),2), – 1
has([1,2,NULL], NULL), – 1 (注意：null值的处理)
– has([], 2), – 出现异常，DB::Exception: Types of array and 2nd argument of function has must be identical up to nullability or numeric types or Enum and numeric type. Passed: Array(Nothing) and UInt8
has([1,2], 3); – 0
SELECT
hasAll([], []), – 1
hasAll([1,NULL,NULL], [NULL]), – 1
hasAll([1,2,3], [1,2]), – 1
hasAll([1,2,2,3], [2]), – 1
hasAll(array(1,2,2,3), [2]), – 1
hasAll([1,2,3], [4,5]); – 0
– 多重数组（如下的二维数组）。
SELECT hasAll([[1, 2], [3, 4]], [[1, 2], [3, 5]]); – 0
SELECT
hasAny(array(1,2,3), array(1)), – 1
hasAny(array(1,2,3), array(1,4,56,80)), – 1
– []与array()是一样的含义，本质上是一直的。只不过[]更加简便而已。
hasAny(array(), array()), – 0
hasAny([],[]), – 0
hasAny([1],[]), – 0
– 空数组跟null不是一样的对象
hasAny([1,NULL],[]), – 0
hasAny([1,NULL],[NULL,2]); – 1

– 9.返回数组指定元素的索引
– indexOf(arr, x) 返回数组中第一个‘x’元素的索引（从1开始），如果‘x’元素不存在在数组中，则返回0。
SELECT indexOf([‘one’,‘two’,‘three’], ‘one’); – 1
SELECT indexOf([1, 2, 4], 4); – 3
SELECT
indexOf([‘one’,‘two’,‘three’], ‘one’), – 1
indexOf([‘one’,NULL,NULL], NULL),-- 1返回第一个找到的元素的索引位置
indexOf([1, 2, 4], 4); – 3
– 数组元素的以第一个和最后一个元素。
SELECT length([12,3,4,4,4]);
SELECT array(12,22,31)[1];

WITH
[23,43,565,2,32,34] AS arr
SELECT
arr[1], – 去除数组中的第一个元素
arr[length(arr)]; – 提取元素中的最后一个元素

– 10.计算数组中包含指定元素的个数
– countEqual(arr, x) 返回数组中等于x的元素的个数。相当于arrayCount（elem - > elem = x，arr）。
– 注意：null值将作为单独的元素值处理。
SELECT
countEqual([1, 2, 2, 2, 3, 4], 2), – 3
countEqual([1, 2, NULL, NULL], NULL); – 2

– 11.arrayEnumerate(arr) 返回 Array [1, 2, 3, …, length (arr) ] 此功能通常与ARRAY JOIN一起使用。它允许在应用ARRAY JOIN后为每个数组计算一次。
SELECT arrayEnumerate([1,20,20,3]); – [1,2,3,4]
SELECT arrayEnumerate(array(11,20,13)); – [1,2,3]
SELECT arrayEnumerate(array(11,20,13,NULL)); – [1,2,3,4] 注意：null也算是一个元素。
–arrayEnumerateUniq(arr) 返回与源数组大小相同的数组，其中每个元素表示与其下标对应的源数组元素在源数组中出现的次数
SELECT arrayEnumerateUniq([1,1,2,2]); – [1,2]

– 12.删除数组的元素
– arrayPopBack(array) 删除数组array的最后一项
SELECT arrayPopBack(array(1,2,3,0)) AS res; – [1,2,3]
– arrayPopFront(array) 从数组中删除第一项
SELECT arrayPopFront(array(0,1,2,3)) AS res; – [1,2,3]

– 13.添加数组的元素 arrayPushFront(array, single_value) single_value是单个值
SELECT arrayPushBack([1,2,3], 0) AS res; – [1,2,3,0]
SELECT arrayPushFront([1,2,3], 0) AS res; – [0,1,2,3]

– 14.更改数组的长度 arrayResize(arr, size[, extender])
– 如果arr的长度 > size,则会对arr截取size的长度；
– 如果arr的长度 < size，则其余位置用对应数据类型的默认值填充。
– 注意：extender含义是扩展元素的值。如果没有指定extender，则默认按照对应的数据类型的默认值进行赋值。否则按照extender进行填充。
SELECT arrayResize([1,2,3], 5); – [1,2,3,0,0]
SELECT arrayResize([1,2,3], 2); – [1,2]
SELECT arrayResize([1,2,3], 3); – [1,2,3]
SELECT arrayResize([array(1,2),array(3,4),array(5,6)], 5);
SELECT arrayResize([1,2,3], 5, 12); – [1,2,3,12,12]
SELECT arrayResize([‘one’,‘two’,‘three’], 5); – [‘one’,‘two’,‘three’,’’,’’]
SELECT arrayResize([‘one’,‘two’,‘three’], 5, ‘default’); – [‘one’,‘two’,‘three’,‘default’,‘default’]

– 15.截取数组的部分元素，得到一个新的子数组
– arraySlice(array, offset[, length])
– 解释：
– array: 数组，
– offset – 数组的偏移。正值表示左侧的偏移量，负值表示右侧的缩进值。数组下标从1开始。
– length - 子数组的长度。如果指定负值，则该函数返回[offset，array_length - length。如果省略该值，则该函数返回[offset，the_end_of_array]。
SELECT
arraySlice([1,2,3,4,5,6], 0, 3), – 无返回值
arraySlice([1,2,NULL,5,6], 1, 3), – [1,2,0]
arraySlice([‘one’,‘two’,NULL], 1, 3), – [‘one’,‘two’,’’]
arraySlice([1,2,3,4,5,6], 1, 3); – [1,2,3]

– 16.数组排序：arraySort([func,] arr, ……)
– 注意：如果在字符串数组中，’‘和NULL是需要特别对待的，’‘需要放在最前面，而NULL则是按顺序存放到最后的。
– arraySort是高阶函数。您可以将lambda函数作为第一个参数传递给它。在这种情况下，排序顺序由lambda函数的调用结果决定。
SELECT
arraySort([‘a’,’’,NULL,‘c’,‘b’]) AS hasNullempty1, --[’’,‘a’,‘b’,‘c’,’’] (第一个是’’,最后一个’‘起始是NULL)
arraySort(array(‘ac’,‘ab’,‘bc’,‘ad’,NULL)) AS hasNull, – [‘ab’,‘ac’,‘ad’,‘bc’,’’]
arraySort(array(‘ac’,’’,‘ab’,NULL,‘bc’,‘ad’,NULL)) AS hasNullempty2, – [’’,‘ab’,‘ac’,‘ad’,‘bc’,’’,’’]
arraySort([5,4,3,2,1]) AS numSorted,-- [1,2,3,4,5] (数字排序)
arraySort([‘ca’,‘bb’,‘ac’]) AS strSorted;-- [‘ac’,‘bb’,‘ca’] (字符串排序)

SELECT
arraySort([NULL, 1, 3, NULL, 2]) AS sortedArr, – [1,2,3,0,0]
arrayReverse(sortedArr) AS reverseSortdArr;-- [0,0,3,2,1]

– 下面这种排序的实质，正数转成负数，再在数学上比较升序排序。
SELECT arraySort(x -> -x, [1,2,3]) as res; – [3,2,1] 降序：（高阶函数用法）
SELECT arraySort((x) -> -x, [1,2,3]) as res; – [3,2,1] 降序：（高阶函数用法）
SELECT arraySort(x -> x, [5,4,3,1,2,3]) as res; – [1,2,3,3,4,5] 升序：（高阶函数用法）
SELECT arraySort((x) -> x, [5,4,3,1,2,3]) as res; – [1,2,3,3,4,5] 升序：（高阶函数用法）
– arraySort(lambda, arr1, arr2)
SELECT arraySort((x, y) -> y, [‘hello’, ‘world’], [2, 1]) as res; – [‘world’,‘hello’]
SELECT arraySort((x, y) -> -y, [0, 1, 2], [1, 2, 3]) as res; – [2,1,0]
– 再次提醒：NULL, NaN, Inf的排序顺序：
– 含义：
– -Inf 是数组中的第一个。
– NULL 是数组中的最后一个。
– NaN 在NULL的前面。
– Inf 在NaN的前面。
– 出现异常：RuntimeException: Parse exception:
– ByteFragment{[[-inf,-4,1,2,3,inf,nan,nan,NULL,NULL]], start=0, len=37}
SELECT arraySort([1, nan, 2, NULL, 3, nan, -4, NULL, inf, -inf]);

– 17.数组翻转：arrayReverse([func,] arr, ……)
– 如果是NULL的话在排序的过程中，根据数组的数据类型进行默认值填充。
SELECT
arrayReverse(array(‘a’,‘b’,‘c’,NULL)) AS hasOneNull, – [’’,‘c’,‘b’,‘a’]
arrayReverse(array(‘ac’,‘ab’,‘bc’,‘ad’,NULL)) AS hasNull, – [’’,‘ad’,‘bc’,‘ab’,‘ac’]
–网格视图： [’[NULL]’,‘ad’,‘bc’,’’,‘ab’,’[NULL]’,’’,‘ac’]；文本视图：[’’,‘ad’,‘bc’,’’,‘ab’,’’,’’,‘ac’]
arrayReverse(array(‘ac’,’’,NULL,‘ab’,’’,‘bc’,‘ad’,NULL)) AS hasNullEmpty,
arrayReverse(array(NULL, 3, NULL, 2, 1)),-- [1,2,0,3,0]
arrayReverse([1,2,3,4]);-- [4,3,2,1]

– 18.数组排序并翻转：arraySort([func,] arr, …)
SELECT arrayReverseSort([1, 3, 3, 0]); – [3,3,1,0]
SELECT arrayReverseSort([‘hello’, ‘world’, ‘!’]); – [‘world’,‘hello’,’!’]
SELECT arrayReverseSort([1, nan, 2, NULL, 3, nan, -4, NULL, inf, -inf]) as res;-- [inf,3,2,1,-4,-inf,nan,nan,NULL,NULL]

– 下面的执行顺序为：
– 1.首先，根据lambda函数的调用结果对源数组（[1, 2, 3]）进行排序。结果是[3, 2, 1]。
– 2.反转上一步获得的数组。所以，最终的结果是[1, 2, 3]。
SELECT arrayReverseSort((x) -> -x, [1, 2, 3]) as res; – [1,2,3]
SELECT arrayReverseSort((x) -> x, [1, 2, 3]) as res; – [1,2,3]
– 下面的执行顺序为：
– 1.首先，根据lambda函数的调用结果对源数组（[‘hello’，‘world’]）进行排序。其中，在第二个数组（[2,1]）中定义了源数组中相应元素的排序键。所以，排序结果[‘world’，‘hello’]。
– 2.反转上一步骤中获得的排序数组。所以，最终的结果是[‘hello’，‘world’]。
SELECT arrayReverseSort((x, y) -> y, [‘hello’, ‘world’], [2, 1]) as res;-- [‘hello’,‘world’]
SELECT arrayReverseSort((x, y) -> -y, [‘hello’, ‘world’], [2, 1]) as res;-- [‘world’,‘hello’]
SELECT arrayReverseSort((x, y) -> x, [‘hello’, ‘world’], [2, 1]) as res;-- [‘world’,‘hello’]
SELECT arrayReverseSort((x, y) -> x, [‘hello’, ‘world’], [1, 2]) as res;-- [‘world’,‘hello’]

– 19.统计数组中不重复元素的个数。arrayUniq(arr,……)
– ① 如果传递一个参数，则计算数组中不同元素的数量。
– ② 如果传递了多个参数，则它计算多个数组中相应位置的不同元素元组的数量
SELECT
arrayUniq([1,2,3]), – 3
arrayUniq([1,2,2,2,3]); – 3
SELECT
arrayUniq([1,2,3],[2,3,4]),
arrayUniq([1,2,2],[1,3,3]);

– 20.数组的特殊功能：arrayJoin(arr) 这是一个非常有用的函数。
– 解释：此函数将数组作为参数，并将该行在结果集中复制数组元素个数
SELECT arrayJoin([1, 2, 3] AS src) AS dst, ‘Hello’, src;
– 每个元素扩大两倍；
SELECT arrayJoin([1,2,3]) * 2;
SELECT arrayJoin([-1,-2,0,1,2]) * 2;
–出现异常： Illegal types Array(UInt8) and Array(UInt8) of arguments of function multiply
–SELECT multiply(array(1,2,3), 2);
SELECT multiply(arrayJoin([-1,-2,0,1,2]), 2);
– 每个元素缩小两倍
SELECT arrayJoin([-4,-2,0,2,4]) / 2;
SELECT divide(arrayJoin([-4,-2,0,2,4]) , 2);

– 21.arrayDifference(arr)
– 返回一个数组，其中包含所有相邻元素对之间的差值
SELECT arrayDifference([1,2,3,4]);-- [0,1,1,1]
SELECT arrayDifference([1,3,10,50]);-- [0,2,7,40]

– 22. arrayDistinct(arr)返回一个包含所有数组中不同元素的数组.
– 类似于java的Set集合，对list集合进行去重。
SELECT arrayDistinct(array(1,2,3,4,4,4)); – [1,2,3,4]
SELECT arrayDistinct([1,2,2,3,4,2,2,5,4,5]); – [1,2,3,4,5]
SELECT arrayDistinct(array(0,1,NULL,3,4,4,4)); – [0,1,3,4]
– 数组去重统计元素个数
SELECT uniq(arrayJoin([1,2,3,6,3])); – 4 表示数组去重后元素的个数
SELECT uniqArray([1,2,3,4,1,2,3,4]); – 4 表示数组去重后元素的个数
– 数组元素累计
SELECT sumArray([1,2,3,4,5]);-- 15
SELECT sum(arraySum([1,2,3,4,5])); – 15

– 23. arrayEnumerateDense(arr) 返回与源数组大小相同的数组，指示每个元素首次出现在源数组中的位置
SELECT
arrayEnumerateDense([10,20,20,10,30]) AS numArrEnumDense,-- [1,2,2,1,3]
– [1,1,2,3,4,1,3,5,5]
arrayEnumerateDense([10,10,2,12,3,10,12,NULL,NULL]) as arrEnumDenseHasNull,
– [1,2,1,1,2,3]
arrayEnumerateDense([10,20,10,10,20,30]) AS arrEnumDese2;

– 24. arrayIntersect(arr,……) 返回所有数组元素的交集。
– 如果arr的数目只有一个，则返回它本身；如果有多个数组,则返回所有数组中元素的交集。
SELECT
– 注意：最后得到的数组元素的顺序。（有什么影响吗？）
arrayIntersect([‘one’,‘two’],[‘one’,‘two’,‘three’]) as uniStrArr1, – [‘two’,‘one’]
arrayIntersect([‘aaa’,‘bbb’],[‘bbb’,‘aaa’,‘three’]) as uniStrArr2, – [‘bbb’,‘aaa’]
arrayIntersect([1,2],[1,2,3]) as uniArr1, – [1,2]
arrayIntersect([1,2],[1,2,3],[2,3,4],[2,3,4]) as uniArr2; – 2
SELECT
arrayIntersect([1,2], [3,4]), – []
arrayIntersect([1,2]);-- [1,2]

– 25.arrayReduce(agg_func, arr1, …)
– agg_func 为聚合函数，传入到数组当中。
– 将聚合函数应用于数组并返回其结果.如果聚合函数具有多个参数，则此函数可应用于相同大小的多个数组。
SELECT
arrayReduce(‘max’, [1,2,3]) AS minNum,–最大值 3
arrayReduce(‘min’, [1,2,3]) AS maxNum,–最小值 1
arrayReduce(‘sum’, [1,2,3]) AS sumNum;–求和 6

– 十三、字符串查分合并函数
—>>>>>> 字符串拆分合并函数
– 1.splitByChar(separator, s) 将字符串以‘separator’拆分成多个子串。
– ‘separator’必须为仅包含一个字符的字符串常量。返回拆分后的子串的数组。
– 如果分隔符出现在字符串的开头或结尾，或者如果有多个连续的分隔符，则将在对应位置填充空的子串。
SELECT splitByChar(’,’, ‘hello,world!’); – [‘hello’,‘world!’]
–下面异常：Illegal separator for function splitByChar. Must be exactly one byte.
–SELECT splitByChar(‘or’, ‘hello,world!’);

– 2.splitByString(separator, s)
– 与上面相同，但它使用多个字符的字符串作为分隔符。该字符串必须为非空
SELECT splitByString(‘or’,‘goodorniceorgreat’); – [‘good’,‘nice’,‘great’]

– 3.alphaTokens(s) 从范围a-z和A-Z中选择连续字节的子字符串。返回子字符串数组
SELECT alphaTokens(‘abca1abc’); – [‘abca’,‘abc’]
SELECT alphaTokens(‘abc1232abc2wer3rtty’); – [‘abc’,‘abc’,‘wer’,‘rtty’]

– 4.数组元素合并函数：arrayStringConcat(arr[, sparator])
– 使用separator将数组中列出的字符串拼接起来。
– ‘separator’是一个可选参数：一个常量字符串，默认情况下设置为空字符串。返回拼接后的字符串
SELECT arrayStringConcat([1,2,3], ‘-’); – 出现异常，要求数组必须是字符串string类型的元素
SELECT arrayStringConcat([‘one’,‘two’,‘three’]); – onetwothree
SELECT arrayStringConcat([‘one’,‘two’,‘three’], ‘-’); – one-two-three
SELECT arrayStringConcat([‘one’,‘two’,‘three’,’’], ‘-’);-- one-two-three- 注意：NULL不能存在arr中

–十四、位操作符
—>>>>>> 位操作符
–位操作函数适用于UInt8，UInt16，UInt32，UInt64，Int8，Int16，Int32，Int64，Float32或Float64中的任何类型。
–结果类型是一个整数，其位数等于其参数的最大位。
–如果至少有一个参数为有符数字，则结果为有符数字。如果参数是浮点数，则将其强制转换为Int64。
SELECT
bitAnd(1,0), – 0
bitAnd(1,1), – 1
bitAnd(1,2), – 0
bitAnd(-1,0), – 0
bitAnd(-1,-2), – -2
bitAnd(-10,-1), – -10
bitOr(1,2), – 3
bitOr(1,0), – 1
bitOr(2,0), – 2
bitOr(0,2); – 2
SELECT bitXor(1, 2), bitXor(20, 15), bitNot(2);-- 3 27 253

–十五、Hash函数：可以用于将元素不可逆的伪随机打乱。
– 注意：伪随机！
SELECT
– 计算字符串的MD5值。（如果您不需要一定使用MD5，请使用‘sipHash64’函数。）
halfMD5(‘HELLO WORLD!’),
halfMD5(12);
SELECT
MD5(‘drew-zero,78967’);

SELECT
– 为任何类型的整数计算32位的哈希。这是相对高效的非加密Hash函数
intHash32(1221232132132) AS intHash32,
– 推荐：从任何类型的整数计算64位哈希码。它的工作速度比intHash32函数快。
intHash64(1221232132132) AS intHash64,
– 计算任意数量字符串的CityHash64或使用特定实现的Hash函数计算任意数量其他类型的Hash。
cityHash64(‘username’) AS cityHash64,
– 1.使用sha1或者sha224加密的话，只能用于字符串
– 2.字符串需使用单引号。
SHA1(‘1232131’) AS sha1,
SHA224(‘1232131’) AS sha224,
SHA256(‘DREW-ZERO’) AS sha256;

– URLHash(url[, N]) 一种快速的非加密哈希函数，用于规范化的从URL获得的字符串
– 从一个字符串计算一个哈希，如果结尾存在尾随符号/，？或#则忽略。 URLHash（s，N）
– 计算URL层次结构中字符串到N级别的哈希值，如果末尾存在尾随符号/，？或#则忽略。 URL的层级与URLHierarchy中的层级相同
– 用处：此函数被用于Yandex.Metrica。
SELECT
URLHash(‘www.baidu’), – 11390370829909720855
URLHash(‘www.baidu’, 0), – 11390370829909720855
–
URLHash(‘www.baidu’, 1); – 11160318154034397263

– farmHash64(s) 计算字符串的FarmHash64。接受一个String类型的参数。返回UInt64。
SELECT farmHash64(‘www.runoob’); – 6668483584160323388

– javaHash(s) 计算字符串的JavaHash。接受一个String类型的参数。返回Int32。
SELECT javaHash(‘www.baidu’); – 270263191

– hiveHash(s) 计算字符串的HiveHash。接受一个String类型的参数。返回Int32。与JavaHash相同，但不会返回负数
SELECT hiveHash(‘www.baidu’); – 270263191

–十六、随机函数
—>>>>>> 随机函数
– 解释：随机函数使用非加密方式生成【伪随机】数字。
– ① 所有随机函数都只接受一个参数或不接受任何参数。
– ② 您可以向它传递任何类型的参数，但传递的参数将不会使用在任何随机数生成过程中。
– ③ 此参数的唯一目的是防止公共子表达式消除，以便在相同的查询中使用相同的随机函数生成不同的随机数
– rand() 函数：返回一个UInt32类型的随机数字，所有UInt32类型的数字被生成的概率均相等。
– rand64() 函数：返回一个UInt64类型的随机数字，所有UInt64类型的数字被生成的概率均相等。
– randConstant() 函数：返回一个UInt32类型的随机数字，该函数不同之处在于仅为每个数据块参数一个随机数。
SELECT
rand(), – 1751687411
rand(10), – 1124981728
rand64(),
rand64(10),
randConstant(),
randConstant();

– 十七、编码函数：
– hex(), unhex(), UUIDStringToNum(str), UUIDNumToString(str),bitmaskToList(num) …
– 1.hex函数编码
SELECT
– 68656C6C6F20776F726C64212C68656C6C6F20636C69636B686F757365
hex(‘hello world!,hello clickhouse’) AS hexStr,
hex(now()) AS hexDatetime, – 5D414BA2
hex(toDate(now())) AS hexDate; --46BC

– 2.接受包含36个字符的字符串，格式为“123e4567-e89b-12d3-a456-426655440000”，并将其转化为FixedString（16）返回
SELECT UUIDStringToNum(‘123e4567-e89b-12d3-a456-426655440000’);

– 3. 接受一个整数。返回一个UInt64类型数组，其中包含一组2的幂列表，其列表中的所有值相加等于这个整数。数组中的数字按升序排列。
– bitmaskToArray(num)
SELECT bitmaskToArray(10); – [2,8]
SELECT bitmaskToArray(100); – [4,32,64]

– 4.接受一个整数。返回一个字符串，其中包含一组2的幂列表，其列表中的所有值相加等于这个整数。列表使用逗号分割，按升序排列。
– bitmaskToList(num)
SELECT bitmaskToList(10); – 2,8
SELECT bitmaskToList(100); – 4,32,64
SELECT bitmaskToList(0); – ‘’ 空字符串

–十八、UUID函数
—>>>>>> UUID函数
– 1.generateUUIDv4() 返回 UUID类型的值。
SELECT generateUUIDv4() as randomUUID; – 随机生成一个UUIDv4的字符串（b6940dfe-0dc9-4788-bac7-319d13235a2e）
SELECT replaceAll(toString(generateUUIDv4()), ‘-’, ‘’) AS replaceUUID; – 9d1947ea4fcf450da5391feb6142cab6

– 2.toUUID(s) 将string类型的值转换成UUID类型的值
SELECT toUUID(‘61f0c404-5cb3-11e7-907b-a6006ad3dba0’) AS uuid;

– 3.接受一个String类型的值，其中包含36个字符且格式为xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx，
– 将其转换为UUID的数值并以FixedString(16)将其返回。
SELECT
‘612f3c40-5d3b-217e-707b-6a546a3d7b29’ AS uuid, – 612f3c40-5d3b-217e-707b-6a546a3d7b29
UUIDStringToNum(uuid) AS bytes; --a/<@];!~p{jTj={)

– 4. UUIDNumToString() 接受一个FixedString(16)类型的值，返回其对应的String表现形式。
SELECT ‘a/<@];!~p{jTj={)’ AS bytes,
UUIDNumToString(toFixedString(bytes, 16)) AS uuid;

— 二十、 URL函数：所有这些功能都不遵循RFC。它们被最大程度简化以提高性能。
— 什么是RFC？
---- Request For Comments（RFC），是一系列以编号排定的文件。文件收集了有关互联网相关信息，以及UNIX和互联网社区的软件文件。
– 1. 截取函数：如果URL中没有要截取的内容则返回空字符串。
SELECT protocol(‘http://www.baidu’);-- http
SELECT protocol(‘https://www.baidu’);-- https
SELECT protocol(‘www.baidu’);-- ‘’
– 获取域名。
SELECT domain(‘http://www.baidu’); – www.baidu
SELECT domain(‘https://www.google’); – www.google
– 返回域名并删除第一个‘www.’
SELECT domainWithoutWWW(‘http://www.baidu’);-- baidu
SELECT domainWithoutWWW(‘www.baidu’);-- ‘’
– 返回顶级域名。例如：.ru
SELECT topLevelDomain(‘http://www.runoob’); – cn
SELECT topLevelDomain(‘https://www.huse.edn’); – edu
– 返回“第一个有效子域名”
– 如果顶级域名为‘com’，‘net’，‘org’或者‘co’则第一个有效子域名为二级域名。否则则返回三级域名
SELECT firstSignificantSubdomain(‘https://news.yandex.tr/’); – yandex
– 返回包含顶级域名与第一个有效子域名之间的内容(参阅上面内容)
SELECT cutToFirstSignificantSubdomain(‘https://news.yandex.tr/’); – yandex.tr
– 返回URL路径
SELECT path(‘https://blog.csdn/u012111465/article/details/85250030’);-- /u012111465/article/details/85250030
– 与上面相同，但包括请求参数和fragment。
SELECT pathFull(‘https://clickhouse.yandex/#quick-start’); – /#quick-start
– 返回请求参数。例如：page=1&lr=213。请求参数不包含问号已经# 以及# 之后所有的内容。
SELECT queryString(‘http://www.baidu/?page=1&lr=234’); – page=1&lr=234 (根据？确定)
SELECT queryString(‘http://www.baidu/page=1&lr=234’); – ‘’
– 返回URL的fragment标识。fragment不包含#。
SELECT fragment(‘https://clickhouse.yandex/#quick-start’); – quick-start
– 返回请求参数和fragment标识。例如：page=1#29390。
SELECT queryStringAndFragment(‘https://www.baidu/s?ie=utf-8&rsv_sug7=100#ei-ai’); – ie=utf-8&rsv_sug7=100#ei-ai

– 2. 删除URL中的部分内容 (如果URL中不包含指定的部分，则URL不变。)
SELECT cutWWW(‘www.baidu’);-- www.baidu
SELECT cutWWW(‘https://www.baidu’);-- www.baidu
SELECT cutWWW(‘https://www.baidu’);-- www.baidu
– 删除请求参数
SELECT cutQueryString(‘http://www.baidu/1?page=1’); – http://www.baidu/1
– 删除fragment标识。#同样也会被删除。
SELECT cutFragment(‘http://www.baidu/#quick-demo’); – http://www.baidu/
– 删除请求参数以及fragment标识。问号以及#也会被删除。
SELECT cutQueryStringAndFragment(‘http://www.baidu/1?page=23#we’); – http://www.baidu/1
– cutURLParameter(URL, name) 删除URL中名称为‘name’的参数。下面例子中的参数是：&之后，resv,name
SELECT cutURLParameter(‘http://www.baidu/1?page=1#erre&resv=23&name=user’,'resv’);

–二十一、IP函数

MACNumToString(num)
MACStringToNum(s)
MACStringToOUI(s)
IPv4NumToString(num)
IPv4StringToNum(s)
IPv4NumToStringClassC(num)
IPv6NumToString(x)
IPv6StringToNum(s)

–二十二、条件函数
SELECT IF(12 > 10 , 12, 20);
SELECT 12 > 10 ? 12 : 10;
SELECT if(greater(12, 10), 12, 10);

–二十三、操作符函数替换
– clickhouse自带的计算操作符函数（对接mybatis的时候不用将“<”之类的符号转换成 “age1 <![CDATA[ < ]] 2>”）
– 1.等于(注意函数名称的大小，严格区分大小写)
SELECT
equals(‘hello’,‘hello’), – 1
equals(‘ab’,‘ba’); – 0
– 2.不等于
SELECT
notEquals(‘a’,‘b’), – 1
notEquals(‘a’,‘a’), – 0
notEquals(12, 12), – 1
notEquals(12, 1010); – 0
– 3.大于( 如果前者大于后者，则返回1；否则返回0)
SELECT
greater(12, 10), – 1
greater(10, 12), – 0
greater(12, 12), – 0
greater(‘b’,‘a’), – 1
greater(‘a’,‘b’); – 0
– 3.1 扩展：提取两者中最大的值
SELECT greatest(12,11); – 12
– 4.小于（如果前者小于后者，则返回1；否则返回0）
SELECT less(12,23); – 1
SELECT less(120,23); – 0
– 5.大于或等于
SELECT greaterOrEquals(12,12); – 1
SELECT greaterOrEquals(120,12); – 1
– 6.小于或等于
SELECT lessOrEquals(12,12); – 1
SELECT lessOrEquals(12,129); – 1
– ===== String操作
– *. a LIKE s
SELECT like(‘a’, ‘abcd’); – 0
SELECT like(‘a’, ‘a’); – 1

五、clickhouse优缺点

优点

1. 完备的DBMS功能

2. 列式存储与数据压缩

列式存储和数据压缩，对于一款高性能数据库来说是必不可少的特性。一个非常流行的观点认为，如果你想让查询变得更快，最简单且有效的方法是减少数据扫描范围和数据传输时的大小，而列式存储和数据压缩就可以帮助我们实现上述两点。列式存储和数据压缩通常是伴生的，因为一般来说列式存储是数据压缩的前提。

行式：

列式：

按列存储与按行存储相比，前者可以有效减少查询时所需扫描的数据量，这一点可以用一个示例简单说明。假设一张数据表A拥有50个字段A1～A50，以及100行数据。现在需要查询前5个字段并进行数据分析，则可以用如下SQL实现：

SELECT A1，A2，A3，A4，A5 FROM A

如果数据按行存储，数据库首先会逐行扫描，并获取每行数据的所有50个字段，再从每一行数据中返回A1～A5这5个字段。不难发现，尽管只需要前面的5个字段，但由于数据是按行进行组织的，实际上还是扫描了所有的字段。如果数据按列存储，就不会发生这样的问题。由于数据按列组织，数据库可以直接获取A1～A5这5列的数据，从而避免了多余的数据扫描。

按列存储相比按行存储的另一个优势是对数据压缩的友好性。同样可以用一个示例简单说明压缩的本质是什么。假设有两个字符串abcdefghi和bcdefghi，现在对它们进行压缩，如下所示：

压缩前：abcdefghi_bcdefghi
压缩后：abcdefghi_(9,8)

可以看到，压缩的本质是按照一定步长对数据进行匹配扫描，当发现重复部分的时候就进行编码转换。例如上述示例中的 (9，8)，表示如果从下划线开始向前移动9个字节，会匹配到8个字节长度的重复项，即这里的bcdefghi。

真实的压缩算法自然比这个示例更为复杂，但压缩的实质就是如此。数据中的重复项越多，则压缩率越高；压缩率越高，则数据体量越小；而数据体量越小，则数据在网络中的传输越快，对网络带宽和磁盘IO的压力也就越小。既然如此，那怎样的数据最可能具备重复的特性呢？答案是属于同一个列字段的数据，因为它们拥有相同的数据类型和现实语义，重复项的可能性自然就更高。

ClickHouse就是一款使用列式存储的数据库，数据按列进行组织，属于同一列的数据会被保存在一起，列与列之间也会由不同的文件分别保存 ( 这里主要指MergeTree表引擎 )。数据默认使用LZ4算法压缩，在Yandex.Metrica的生产环境中，数据总体的压缩比可以达到8:1 ( 未压缩前17PB，压缩后2PB )。列式存储除了降低IO和存储的压力之外，还为向量化执行做好了铺垫。

3. 向量化执行引擎

向量化执行，可以简单地看作一项消除程序中循环的优化。这里用一个形象的例子比喻。小胡经营了一家果汁店，虽然店里的鲜榨苹果汁深受大家喜爱，但客户总是抱怨制作果汁的速度太慢。小胡的店里只有一台榨汁机，每次他都会从篮子里拿出一个苹果，放到榨汁机内等待出汁。如果有8个客户，每个客户都点了一杯苹果汁，那么小胡需要重复循环8次上述的榨汁流程，才能榨出8杯苹果汁。如果制作一杯果汁需要5分钟，那么全部制作完毕则需要40分钟。为了提升果汁的制作速度，小胡想出了一个办法。他将榨汁机的数量从1台增加到了8台，这么一来，他就可以从篮子里一次性拿出8个苹果，分别放入8台榨汁机同时榨汁。此时，小胡只需要5分钟就能够制作出8杯苹果汁。为了制作n杯果汁，非向量化执行的方式是用1台榨汁机重复循环制作n次，而向量化执行的方式是用n台榨汁机只执行1次。

为了实现向量化执行，需要利用CPU的SIMD指令。SIMD的全称是Single Instruction Multiple Data，即用单条指令操作多条数据。现代计算机系统概念中，它是通过数据并行以提高性能的一种实现方式 ( 其他的还有指令级并行和线程级并行 )，它的原理是在CPU寄存器层面实现数据的并行操作。

在计算机系统的体系结构中，存储系统是一种层次结构。典型服务器计算机的存储层次结构如图1所示。一个实用的经验告诉我们，存储媒介距离CPU越近，则访问数据的速度越快。

从上图中可以看到，从左向右，距离CPU越远，则数据的访问速度越慢。从寄存器中访问数据的速度，是从内存访问数据速度的300倍，是从磁盘中访问数据速度的3000万倍。所以利用CPU向量化执行的特性，对于程序的性能提升意义非凡。

ClickHouse目前利用SSE4.2指令集实现向量化执行。

4. 关系模型与SQL查询

相比HBase和Redis这类NoSQL数据库，ClickHouse使用关系模型描述数据并提供了传统数据库的概念 ( 数据库、表、视图和函数等 )。与此同时，ClickHouse完全使用SQL作为查询语言 ( 支持GROUP BY、ORDER BY、JOIN、IN等大部分标准SQL )，这使得它平易近人，容易理解和学习。因为关系型数据库和SQL语言，可以说是软件领域发展至今应用最为广泛的技术之一，拥有极高的"群众基础"。也正因为ClickHouse提供了标准协议的SQL查询接口，使得现有的第三方分析可视化系统可以轻松与它集成对接。在SQL解析方面，ClickHouse是大小写敏感的，这意味着SELECT a 和 SELECT A所代表的语义是不同的。

关系模型相比文档和键值对等其他模型，拥有更好的描述能力，也能够更加清晰地表述实体间的关系。更重要的是，在OLAP领域，已有的大量数据建模工作都是基于关系模型展开的 ( 星型模型、雪花模型乃至宽表模型 )。ClickHouse使用了关系模型，所以将构建在传统关系型数据库或数据仓库之上的系统迁移到ClickHouse的成本会变得更低，可以直接沿用之前的经验成果。

5. 多线程与分布式

ClickHouse几乎具备现代化高性能数据库的所有典型特征，对于可以提升性能的手段可谓是一一用尽，对于多线程和分布式这类被广泛使用的技术，自然更是不在话下。

如果说向量化执行是通过数据级并行的方式提升了性能，那么多线程处理就是通过线程级并行的方式实现了性能的提升。相比基于底层硬件实现的向量化执行SIMD，线程级并行通常由更高层次的软件层面控制。现代计算机系统早已普及了多处理器架构，所以现今市面上的服务器都具备良好的多核心多线程处理能力。由于SIMD不适合用于带有较多分支判断的场景，ClickHouse也大量使用了多线程技术以实现提速，以此和向量化执行形成互补。

如果一个篮子装不下所有的鸡蛋，那么就多用几个篮子来装，这就是分布式设计中分而治之的基本思想。同理，如果一台服务器性能吃紧，那么就利用多台服务的资源协同处理。为了实现这一目标，首先需要在数据层面实现数据的分布式。因为在分布式领域，存在一条金科玉律—计算移动比数据移动更加划算。在各服务器之间，通过网络传输数据的成本是高昂的，所以相比移动数据，更为聪明的做法是预先将数据分布到各台服务器，将数据的计算查询直接下推到数据所在的服务器。ClickHouse在数据存取方面，既支持分区 ( 纵向扩展，利用多线程原理 )，也支持分片 ( 横向扩展，利用分布式原理 )，可以说是将多线程和分布式的技术应用到了极致。

6. 多主架构

HDFS、Spark、HBase和Elasticsearch这类分布式系统，都采用了Master-Slave主从架构，由一个管控节点作为Leader统筹全局。而ClickHouse则采用Multi-Master多主架构，集群中的每个节点角色对等，客户端访问任意一个节点都能得到相同的效果。这种多主的架构有许多优势，例如对等的角色使系统架构变得更加简单，不用再区分主控节点、数据节点和计算节点，集群中的所有节点功能相同。所以它天然规避了单点故障的问题，非常适合用于多数据中心、异地多活的场景。

但是如何保证在宕机后的数据一致性？采用热扩容answer

7. 实时数据更新

ClickHouse支持在表中定义主键。为了使查询能够快速在主键中进行范围查找，数据总是以增量的方式有序的存储在MergeTree中。因此，数据可以持续不断高效的写入到表中，并且写入的过程中不会存在任何加锁的行为。

8. 数据的磁盘存储

在一些列式数据库管理系统中(例如：InfiniDB CE 和 MonetDB) 并没有使用数据压缩。但是, 若想达到比较优异的性能，数据压缩确实起到了至关重要的作用。

除了在磁盘空间和CPU消耗之间进行不同权衡的高效通用压缩编解码器之外，ClickHouse还提供针对特定类型数据的专用编解码器，这使得ClickHouse能够与更小的数据库(如时间序列数据库)竞争并超越它们。

缺点

不支持事务，不支持真正的删除/更新；
不支持高并发，官方建议 qps 为 100，可以通过修改配置文件增加连接数，但是在服务器足够好的情况下；
不支持真正的删除/更新支持不支持事务
不支持二级索引
有限的 SQL 支持，join 实现与众不同
不支持窗口功能
元数据管理需要人工干预维护
SQL 满足日常使用 80%以上的语法，join 写法比较特殊；最新版已支持类似 SQL 的 join，但性能不好；
尽量做 1000 条以上批量的写入，避免逐行 insert 或小批量的 insert，update，delete 操作，因为 ClickHouse 底层会不断的做异步的数据合并，会影响查询性能，这个在做实时数据写入的时候要尽量避开；
ClickHouse 快是因为采用了并行处理机制，即使一个查询，也会用服务器一半的 CPU 去执行，所以 ClickHouse 不能支持高并发的使用场景，默认单查询使用 CPU 核数为服务器核数的一半，安装时会自动识别服务器核数，可以通过配置文件修改该参数。

1.CK中添加列：ALTER TABLE 表名称 ON 集群名称 ADD COLUMN 列名称类型 DEFAULT 注解
eg：ALTER TABLE 表名称 ON 集群名称 ADD COLUMN lvl Int32 DEFAULT ‘lvl’
2.CK中修改注释：alter table 表名称 ON 集群名称 COMMENT COLUMN 列名称注解;
alter table adm_sch_d05_content_live_service_di_local ON 集群名称 COMMENT COLUMN pv_sid ‘lvl’;
3.CK中修改数据类型：ALTER table adm_sch_d05_content_live_service_di_local on 集群名称 MODIFY COLUMN ord_dt String;
ALTER table 表名称 ON 集群名称 MODIFY COLUMN 列名称数据类型;
ALTER table adm_sch_d05_content_live_service_di on 集群名称 MODIFY COLUMN ord_dt String;
4.CK中删除列 alter table 表名称 ON 集群名称 drop column 列名称;
ALTER table adm_sch_d05_content_live_service_di_local on 集群名称 drop column pv_sid;
注意问题：
不可以修改列名称，CK中不支持修改列名称。
hive表中的日期为String类型到CK中不可以转为Date,会报错。必须为Sting.

更多推荐

clickhouse基础教程

clickhouse基础教程

一、基础概念

两个概念：

二、架构与安装

安装

三、clickhouse引擎

4.1 数据库引擎

4.2 数据表引擎

四、clickhouse使用

五、clickhouse优缺点

优点

缺点

发布评论取消回复

最近发表

热门文章

标签列表

clickhouse基础教程

一、基础概念

两个概念：

二、架构与安装

安装

三、clickhouse引擎

4.1 数据库引擎

4.2 数据表引擎

四、clickhouse使用

五、clickhouse优缺点

优点

缺点

相关文章

发布评论取消回复

最近发表

热门文章

标签列表