ClickHouse是近年来备受关注的开源列式数据库，主要用于数据分析（OLAP）领域。目前国内社区火热，各个大厂纷纷跟进大规模使用：

今日头条内部用ClickHouse来做用户行为分析，内部一共几千个ClickHouse节点，单集群最大1200节点，总数据量几十PB，日增原始数据300TB左右。

腾讯内部用ClickHouse做游戏数据分析，并且为之建立了一整套监控运维体系。

携程内部从18年7月份开始接入试用，目前80%的业务都跑在ClickHouse上。每天数据增量十多亿，近百万次查询请求。

快手内部也在使用ClickHouse，存储总量大约10PB，每天新增200TB， 90%查询小于3S。

在国外，Yandex内部有数百节点用于做用户点击行为分析，CloudFlare、Spotify等头部公司也在使用。

在社区方面，github star数目增速惊人。

在DB-engines排名上，如下图中红色曲线所示。ClickHouse开源时间虽短，但是增势迅猛。

为何ClickHouse获得了如此广泛的关注，得到了社区的青睐，也得到了诸多大厂的应用呢?

1 简介

Clickhouse是一款分布式实时分析型列式数据库服务。高性能，开箱即用，企业特性支持。应用于流量分析，广告营销分析，行为分析，人群划分，客户画像，敏捷BI，数据集市，网络监控，分布式服务和链路监控等业务场景。

我相信您一定会关注如下内容:

1 极致性能

充分发挥多核并行优势（SIMD高效指令集、向量化执行引擎）并借助分布式技术，加速计算提供实时分析能力。开源公开benchmark显示比传统方法快100~1000倍，提供50MB~200MB/s的高吞吐实时导入能力。

2 简单灵活

提供完善SQL支持，上手十分简单；提供json、map、array等灵活数据类型适配业务快速变化；同时支持近似计算、概率数据结构等应对海量数据处理。

3 低成本

借助于精心设计的列存、高效的数据压缩算法，提供高达10倍的压缩比，大幅提升单机数据存储和计算能力，大幅降低使用成本，是构建海量数据仓库的绝佳方案。

4 架构灵活

支持单节点、单副本、多节点、多副本多种架构

简单总结一下clickhouse的特点,方便大家快速了解clickhouse

Ø 1.真正的面向列的DBMS

Ø 2.数据高效压缩

Ø 3.磁盘存储的数据

Ø 4.多核并行处理

Ø 5.在多个服务器上分布式处理

Ø 6.SQL语法支持

Ø 7.向量化引擎

Ø 8.实时数据更新

Ø 9.索引

Ø 10.适合在线查询

Ø 11.支持近似预估计算

Ø 12.支持嵌套的数据结构

Ø 13支持数组作为数据类型

Ø 14.支持限制查询复杂性以及配额

Ø 15.复制数据复制和对数据完整性的支持

—————————————————

Ø 1.不支持事物。

Ø 2.不支持Update/Delete操作。

Ø 3.支持有限操作系统。

2 再深入一点

2.1 存储层

ClickHouse从OLAP场景需求出发，定制开发了一套全新的高效列式存储引擎，并且实现了数据有序存储、主键索引、稀疏索引、数据Sharding、数据Partitioning、TTL、主备复制等丰富功能。以上功能共同为ClickHouse极速的分析性能奠定了基础。

列式存储

与行存将每一行的数据连续存储不同，列存将每一列的数据连续存储。示例图如下：

相比于行式存储，列式存储在分析场景下有着许多优良的特性。

1）如前所述，分析场景中往往需要读大量行但是少数几个列。在行存模式下，数据按行连续存储，所有列的数据都存储在一个block中，不参与计算的列在IO时也要全部读出，读取操作被严重放大。而列存模式下，只需要读取参与计算的列即可，极大的减低了IO cost，加速了查询。

2）同一列中的数据属于同一类型，压缩效果显著。列存往往有着高达十倍甚至更高的压缩比，节省了大量的存储空间，降低了存储成本。

3）更高的压缩比意味着更小的data size，从磁盘中读取相应数据耗时更短。

4）自由的压缩算法选择。不同列的数据具有不同的数据类型，适用的压缩算法也就不尽相同。可以针对不同列类型，选择最合适的压缩算法。

5）高压缩比，意味着同等大小的内存能够存放更多数据，系统cache效果更好。

官方数据显示，通过使用列存，在某些分析场景下，能够获得100倍甚至更高的加速效应

数据有序存储

ClickHouse支持在建表时，指定将数据按照某些列进行sort by。

排序后，保证了相同sort key的数据在磁盘上连续存储，且有序摆放。在进行等值、范围查询时，where条件命中的数据都紧密存储在一个或若干个连续的Block中，而不是分散的存储在任意多个Block，大幅减少需要IO的block数量。另外，连续IO也能够充分利用操作系统page cache的预取能力，减少page fault。

主键索引

ClickHouse支持主键索引，它将每列数据按照index granularity（默认8192行）进行划分，每个index granularity的开头第一行被称为一个mark行。主键索引存储该mark行对应的primary key的值。

对于where条件中含有primary key的查询，通过对主键索引进行二分查找，能够直接定位到对应的index granularity，避免了全表扫描从而加速查询。

但是值得注意的是：ClickHouse的主键索引与MySQL等数据库不同，它并不用于去重，即便primary key相同的行，也可以同时存在于数据库中。要想实现去重效果，需要结合具体的表引擎ReplacingMergeTree、CollapsingMergeTree、VersionedCollapsingMergeTree实现，我们会在未来的文章系列中再进行详细解读。

稀疏索引

ClickHouse支持对任意列创建任意数量的稀疏索引。其中被索引的value可以是任意的合法SQL Expression，并不仅仅局限于对column value本身进行索引。之所以叫稀疏索引，是因为它本质上是对一个完整index granularity（默认8192行）的统计信息，并不会具体记录每一行在文件中的位置。目前支持的稀疏索引类型包括：

minmax: 以index granularity为单位，存储指定表达式计算后的min、max值；在等值和范围查询中能够帮助快速跳过不满足要求的块，减少IO。
set(max_rows)：以index granularity为单位，存储指定表达式的distinct value集合，用于快速判断等值查询是否命中该块，减少IO。
ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)：将string进行ngram分词后，构建bloom filter，能够优化等值、like、in等查询条件。
tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)：与ngrambf_v1类似，区别是不使用ngram进行分词，而是通过标点符号进行词语分割。
bloom_filter([false_positive])：对指定列构建bloom filter，用于加速等值、like、in等查询条件的执行。

数据Sharding

ClickHouse支持单机模式，也支持分布式集群模式。在分布式模式下，ClickHouse会将数据分为多个分片，并且分布到不同节点上。不同的分片策略在应对不同的SQL Pattern时，各有优势。ClickHouse提供了丰富的sharding策略，让业务可以根据实际需求选用。

1） random随机分片：写入数据会被随机分发到分布式集群中的某个节点上。

2） constant固定分片：写入数据会被分发到固定一个节点上。

3）column value分片：按照某一列的值进行hash分片。

4）自定义表达式分片：指定任意合法表达式，根据表达式被计算后的值进行hash分片。

数据分片，让ClickHouse可以充分利用整个集群的大规模并行计算能力，快速返回查询结果。
更重要的是，多样化的分片功能，为业务优化打开了想象空间。比如在hash sharding的情况下，JOIN计算能够避免数据shuffle，直接在本地进行local join；支持自定义sharding，可以为不同业务和SQL Pattern定制最适合的分片策略；利用自定义sharding功能，通过设置合理的sharding expression可以解决分片间数据倾斜问题等。

另外，sharding机制使得ClickHouse可以横向线性拓展，构建大规模分布式集群，从而具备处理海量数据的能力。

数据Partitioning

ClickHouse支持PARTITION BY子句，在建表时可以指定按照任意合法表达式进行数据分区操作，比如通过toYYYYMM()将数据按月进行分区、toMonday()将数据按照周几进行分区、对Enum类型的列直接每种取值作为一个分区等。

数据Partition在ClickHouse中主要有两方面应用：

在partition key上进行分区裁剪，只查询必要的数据。灵活的partition expression设置，使得可以根据SQL Pattern进行分区设置，最大化的贴合业务特点。
对partition进行TTL管理，淘汰过期的分区数据。

数据TTL

在分析场景中，数据的价值随着时间流逝而不断降低，多数业务出于成本考虑只会保留最近几个月的数据，ClickHouse通过TTL提供了数据生命周期管理的能力。

ClickHouse支持几种不同粒度的TTL：

1）列级别TTL：当一列中的部分数据过期后，会被替换成默认值；当全列数据都过期后，会删除该列。

2）行级别TTL：当某一行过期后，会直接删除该行。

3）分区级别TTL：当分区过期后，会直接删除该分区。

高吞吐写入能力

ClickHouse采用类LSM Tree的结构，数据写入后定期在后台Compaction。通过类LSM tree的结构，ClickHouse在数据导入时全部是顺序append写，写入后数据段不可更改，在后台compaction时也是多个段merge sort后顺序写回磁盘。顺序写的特性，充分利用了磁盘的吞吐能力，即便在HDD上也有着优异的写入性能。

官方公开benchmark测试显示能够达到50MB-200MB/s的写入吞吐能力，按照每行100Byte估算，大约相当于50W-200W条/s的写入速度。

有限支持delete、update

在分析场景中，删除、更新操作并不是核心需求。ClickHouse没有直接支持delete、update操作，而是变相支持了mutation操作，语法为alter table delete where filter_expr, alter table update col=val where filter_expr。

目前主要限制为删除、更新操作为异步操作，需要后台compation之后才能生效。

主备同步

ClickHouse通过主备复制提供了高可用能力，主备架构下支持无缝升级等运维操作。而且相比于其他系统它的实现有着自己的特色：

1）默认配置下，任何副本都处于active模式，可以对外提供查询服务；

2）可以任意配置副本个数，副本数量可以从0个到任意多个；

3）不同shard可以配置不提供副本个数，用于解决单个shard的查询热点问题；

2.2 计算层

ClickHouse在计算层做了非常细致的工作，竭尽所能榨干硬件能力，提升查询速度。它实现了单机多核并行、分布式计算、向量化执行与SIMD指令、代码生成等多种重要技术。

多核并行

ClickHouse将数据划分为多个partition，每个partition再进一步划分为多个index granularity，然后通过多个CPU核心分别处理其中的一部分来实现并行数据处理。

在这种设计下，单条Query就能利用整机所有CPU。极致的并行处理能力，极大的降低了查询延时。

分布式计算

除了优秀的单机并行处理能力，ClickHouse还提供了可线性拓展的分布式计算能力。ClickHouse会自动将查询拆解为多个task下发到集群中，然后进行多机并行处理，最后把结果汇聚到一起。

在存在多副本的情况下，ClickHouse提供了多种query下发策略：

随机下发：在多个replica中随机选择一个；
最近hostname原则：选择与当前下发机器最相近的hostname节点，进行query下发。在特定的网络拓扑下，可以降低网络延时。而且能够确保query下发到固定的replica机器，充分利用系统cache。
in order：按照特定顺序逐个尝试下发，当前一个replica不可用时，顺延到下一个replica。
first or random：在In Order模式下，当第一个replica不可用时，所有workload都会积压到第二个Replica，导致负载不均衡。first or random解决了这个问题：当第一个replica不可用时，随机选择一个其他replica，从而保证其余replica间负载均衡。另外在跨region复制场景下，通过设置第一个replica为本region内的副本，可以显著降低网络延时。

向量化执行与SIMD

ClickHouse不仅将数据按列存储，而且按列进行计算。传统OLTP数据库通常采用按行计算，原因是事务处理中以点查为主，SQL计算量小，实现这些技术的收益不够明显。但是在分析场景下，单个SQL所涉及计算量可能极大，将每行作为一个基本单元进行处理会带来严重的性能损耗：

1）对每一行数据都要调用相应的函数，函数调用开销占比高；

2）存储层按列存储数据，在内存中也按列组织，但是计算层按行处理，无法充分利用CPU cache的预读能力，造成CPU Cache miss严重；

3）按行处理，无法利用高效的SIMD指令；

ClickHouse实现了向量执行引擎（Vectorized execution engine），对内存中的列式数据，一个batch调用一次SIMD指令（而非每一行调用一次），不仅减少了函数调用次数、降低了cache miss，而且可以充分发挥SIMD指令的并行能力，大幅缩短了计算耗时。向量执行引擎，通常能够带来数倍的性能提升。

动态代码生成Runtime Codegen

在经典的数据库实现中，通常对表达式计算采用火山模型，也即将查询转换成一个个operator，比如HashJoin、Scan、IndexScan、Aggregation等。为了连接不同算子，operator之间采用统一的接口，比如open/next/close。在每个算子内部都实现了父类的这些虚函数，在分析场景中单条SQL要处理数据通常高达数亿行，虚函数的调用开销不再可以忽略不计。另外，在每个算子内部都要考虑多种变量，比如列类型、列的size、列的个数等，存在着大量的if-else分支判断导致CPU分支预测失效。

ClickHouse实现了Expression级别的runtime codegen，动态地根据当前SQL直接生成代码，然后编译执行。如下图例子所示，对于Expression直接生成代码，不仅消除了大量的虚函数调用（即图中多个function pointer的调用），而且由于在运行时表达式的参数类型、个数等都是已知的，也消除了不必要的if-else分支判断。

近似计算

近似计算以损失一定结果精度为代价，极大地提升查询性能。在海量数据处理中，近似计算价值更加明显。

ClickHouse实现了多种近似计算功能：

近似估算distinct values、中位数，分位数等多种聚合函数；
建表DDL支持SAMPLE BY子句，支持对于数据进行抽样处理；

复杂数据类型支持

ClickHouse还提供了array、json、tuple、set等复合数据类型，支持业务schema的灵活变更。

3 再近一点

3.1 安装

clickhouse架构灵活,可以单节点安装,也可以多节点安装 !操作十分便捷, 多节点安装,在多个机器上分别安装Clickhouse,然后安装按成以后做集群配置即可

在虚拟机Linux01 上安装:


1)安装curl工具
yum install -y curl
2)添加clickhouse的yum镜像
curl -s https://packagecloud.io/install/repositories/altinity/clickhouse/script.rpm.sh | sudo bash
3)检查镜像情况
[root@doit04 yum.repos.d]# yum list | grep clickhouse                        
clickhouse-client.x86_64                    20.5.4.40-1.el7            @Altinity_clickhouse
clickhouse-common-static.x86_64             20.5.4.40-1.el7            @Altinity_clickhouse
4)安装clickhouse的服务端和客户端
yum install -y clickhouse-server clickhouse-client
5)启动服务端
service clickhouse-server start 
6)启动交互式客户端
clickhouse-client  -m

在虚拟机Linux02 上安装:

1)安装curl工具
yum install -y curl
2)添加clickhouse的yum镜像
curl -s https://packagecloud.io/install/repositories/altinity/clickhouse/script.rpm.sh | sudo bash
3)检查镜像情况
[root@doit04 yum.repos.d]# yum list | grep clickhouse                        
clickhouse-client.x86_64                    20.5.4.40-1.el7            @Altinity_clickhouse
clickhouse-common-static.x86_64             20.5.4.40-1.el7            @Altinity_clickhouse
4)安装clickhouse的服务端和客户端
yum install -y clickhouse-server clickhouse-client
5)启动服务端
service clickhouse-server start 
6)启动交互式客户端
clickhouse-client  -m

……以此类推……

[root@linux01 flink-1.11.2]# clickhouse-client  -m
ClickHouse client version 20.8.3.18.
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.8.3 revision 54438.

linux01 :) 在此处操作clickhouse
SHOW DATABASES
┌─name───────────────────────────┐
│ _temporary_and_external_tables │
│ db_ch_mysql                    │
│ db_doit19                      │
│ default                        │
│ system                         │
└────────────────────────────────┘
CREATE DATABASE db1
Ok.
0 rows in set. Elapsed: 0.007 sec. 
USE  db1 ;
 create  table tb_t1 ... ;

3.2 引擎

表引擎(table engine)。如果对MySQL熟悉的话，或许你应该听说过InnoDB和MyISAM存储引擎。不同的存储引擎提供不同的存储机制、索引方式、锁定水平等功能，也可以称之为表类型。ClickHouse提供了丰富的表引擎，这些不同的表引擎也代表着不同的表类型。比如数据表拥有何种特性、数据以何种形式被存储以及如何被加载

作用

决定表存储在哪里以及以何种方式存储
支持哪些查询以及如何支持
并发数据访问
索引的使用
是否可以执行多线程请求
数据复制参数

引擎分类	引擎名称
MergeTree系列	MergeTree 、ReplacingMergeTree 、SummingMergeTree 、 AggregatingMergeTree CollapsingMergeTree 、 VersionedCollapsingMergeTree 、GraphiteMergeTree
Log系列	TinyLog 、StripeLog 、Log
Integration Engines	Kafka 、MySQL、ODBC 、JDBC、HDFS
Special Engines	Distributed 、MaterializedView、 Dictionary 、Merge 、File、Null 、Set 、Join 、 URL View、Memory 、 Buffer

clickhouse提供了大量的引擎,下面我们列举两个代表性的引擎示例 :

3.2.1 MergeTree引擎

MergeTree系列的表引擎是ClickHouse数据存储功能的核心。它们提供了用于弹性和高性能数据检索的大多数功能：列存储，自定义分区，稀疏的主索引，辅助数据跳过索引等。

基本MergeTree表引擎可以被认为是单节点ClickHouse实例的默认表引擎，因为它在各种用例中通用且实用。

对于生产用途，ReplicatedMergeTree是必经之路，因为它为常规MergeTree引擎的所有功能增加了高可用性。一个额外的好处是在数据提取时自动进行重复数据删除，因此如果插入过程中出现网络问题，该软件可以安全地重试。

MergeTree系列的所有其他引擎为某些特定用例添加了额外的功能。通常，它是作为后台的其他数据操作实现的。

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster](
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2) 
ENGINE = MergeTree()ORDER BY expr[PARTITION BY expr][PRIMARY KEY expr][SAMPLE BY expr][TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...][SETTINGS name=value, ...]

3.2.2VersionedCollapsingMergeTree

这种引擎是以uid为主键,以uid为排序字段,我们可以保留或者删除指定版本的数据

CREATE TABLE tb_vscmt
(
    uid UInt64,
    name String,
    age UInt8,
    sign Int8,
    version UInt8
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY uid;

3.2.2 分布式引擎

使用ReplicatedMergeTree和Distributed引擎构建我们的分布式表

-- 每个机器都需要建立各自的replica table，也需要建Distributed table当做路由

create table dm.delphi_membership_properties_replica
(
  membership_id  int,  -- comment '会员id',
  membership_uid String, -- comment '会员uid',
  business_group_id int, -- comment '商户id',
  business_group_uid String , --comment '商户uid',
  business_group_name String, -- comment '商户名',
  business_id Nullable(int), -- comment '门店id',
  business_uid Nullable(String), -- comment '门店uid',
  business_name Nullable(String), -- comment '门店name',
  membership_source String, -- comment '会员入会来源',
  created_at DateTime,
  calendar_date Date,
  last_visited_date Date, -- comment '最近一次访问时间',
  membership_level int, -- comment '会员等级',
  customer_type String, -- comment '会员类型:新会员/忠诚会员/常来会员/淡忘会员/流失会员，根据最后一次访问时间和商户配置计算而来',
  visit_count int, -- comment '到访次数',
  consumptions_count Nullable(int), -- comment '消费次数',
  consumptions_original_amount Nullable(Decimal128(2)), -- comment '消费总金额：原始金额',
  consumptions_amount Nullable(Decimal128(2)), -- comment '消费总金额：实付金额',
  average_consume Nullable(Decimal128(2)), -- comment '平均消费金额：原始金额/消费次数',
  account_id int, -- comment '用户id',
  account_uid String, -- comment '用户uid',
  account_phone String, -- comment '用户手机',
  age Nullable(int), -- comment '年龄',
  birthday Nullable(String), -- comment '生日',
  birthday_month Nullable(int), -- comment '生日月份',
  birthday_day Nullable(int), -- comment '生日天',
  birthday_year Nullable(int), -- comment '生日年',
  zodiac String, -- comment '星座',
  name Nullable(String), -- comment '姓名',
  gender int, -- comment '性别',
  profession Nullable(String), -- comment '职业',
  country Nullable(String), -- comment '国家',
  province Nullable(String), -- comment '省份',
  city Nullable(String), -- comment '城市',
  region Nullable(String), -- comment '商圈',
  head_img_url Nullable(String), -- comment '头像',
  wechat_name Nullable(String), -- comment '微信名',
  wechat_city Nullable(String), -- comment '微信城市',
  wechat_country Nullable(String), -- comment '微信国家',
  wechat_province Nullable(String), -- comment '微信省份',
  wechat_head_img_url Nullable(String), -- comment '微信头像',
  wechat_groupid int, -- comment '微信组',
  wechat_remark Nullable(String), -- comment '微信备注'
  insert_time DateTime DEFAULT now(), -- 数据插入时间
  insert_date Date DEFAULT toDate(now()) -- 数据插入日期
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/delphi_membership_properties_replica', '{replica}')
order by (business_group_uid, calendar_date, created_at, membership_uid);

create table dm.delphi_membership_properties as dm.delphi_membership_properties_replica
ENGINE = Distributed(ck_cluster, dm, delphi_membership_properties_replica, rand());

delphi_membership_properties_replica是各个机器上的本地表，delphi_membership_properties是分布式表，比对下两个表的创建engine的区别。

ReplicatedMergeTree

在表引擎名称上加上 Replicated 前缀，就表示是一种复制表。

ReplicatedMergeTree 参数：

zoo_path — ZooKeeper 中该表的路径。
replica_name — ZooKeeper 中的该表所在的副本名称。

ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/delphi_membership_properties_replica', '{replica}')
order by (business_group_uid, calendar_date, created_at, membership_uid);

这些参数可以包含宏替换的占位符，即大括号的部分。它们会被替换为配置文件里 ‘macros’ 那部分配置的值

<yandex> <macros> <replica>172.31.59.118</replica> <shard>01</shard> <layer>01</layer> </macros> </yandex>

Distributed

Distributed(logs, default, hits[, sharding_key])

分布式引擎参数：服务器配置文件中的集群名，远程数据库名，远程表名，数据分片键（可选）。数据分片键的概念就是数据插入时是根据什么原则分配到具体分片上的。

在上面的表结构中：

ENGINE = Distributed(ck_cluster, dm, delphi_membership_properties_replica, rand());

表示将会从ck_cluster集群中dm.delphi_membership_properties_replica中读取数据。

集群的名称是在集群搭建时的metrika.xml文件中配置的，具体的可以看集群搭建部分的配置。可以在配置中配置任意数量的集群。

要查看集群，可使用“system.clusters”表。

通过分布式引擎可以像使用本地服务器一样使用集群。但是，集群不是自动扩展的：必须编写集群配置到服务器配置文件中。

这样我们就可以使用clickhouse创建分布式表 ,实现分布式数据库功能, 并且表中的数据也支持数据备份

3.3 集成

clickhouse中实现很多集成引擎和表函数 ,clickhouse和其他数据源集成十分方便

Function	Description
file	Creates a File-engine table.
merge	Creates a Merge-engine table.
numbers	Creates a table with a single column filled with integer numbers.
remote	Allows you to access remote servers without creating a Distributed-engine table.
url	Creates a Url-engine table.
mysql	Creates a MySQL-engine table.
jdbc	Creates a JDBC-engine table.
odbc	Creates a ODBC-engine table.
hdfs	Creates a HDFS-engine table.

以集成mysql为例 ; 使用如下建表语句指定MySQL引擎,ck就可以读取mysql中数据

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
) ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);

CREATE TABLE hdfs_engine_table (name String, value UInt32) ENGINE=HDFS('hdfs://hdfs1:9000/other_storage', 'TSV')

这个例子是CK直接加载HDFS 上的结构化数据

3.4 函数

clickhouse中提供了大量的函数,并且还支持表函数 ,高阶函数,链路函数,集合函数……为我们使用CK数据分析带来了极大的便利!

3.4.1日期类函数

函数	用途	举例	结果
toYear()	取日期或时间日期的年份	toYear(toDateTime(‘2018-12-11 11:12:13’)) toYear(toDate(‘2018-12-11’))	返回 2018 返回 2018
toMonth()	取日期或时间日期的月份	toMonth(toDateTime(‘2018-12-11 11:12:13’)) toMonth(toDate(‘2018-12-11’))	返回 12返回 12
toDayOfMonth()	取日期或时间日期的天（1-31）	toMonth(toDayOfMonth(‘2018-12-11 11:12:13’)) toMonth(toDayOfMonth(‘2018-12-11’))	返回 11返回 11
toDayOfWeek()	取日期或时间日期的星期（星期一为1，星期日为7）。	toDayOfWeek(toDateTime(‘2018-12-11 11:12:13’)) toDayOfWeek(toDate(‘2018-12-11’))	返回 2返回 2
toHour()	取时间日期的小时	toHour(toDateTime(‘2018-12-11 11:12:13’))	返回 11
toMinute()	取时间日期的分钟	toMinute(toDateTime(‘2018-12-11 11:12:13’))	返回 12
toSecond()	取时间日期的秒	toSecond(toDateTime(‘2018-12-11 11:12:13’))	返回 13
toMonday()	取时间日期最近的周一（返回日期）	toMonday(toDate(‘2018-12-11’)) toMonday(toDateTime(‘2018-12-11 11:12:13’))	返回 2018-12-10返回 2018-12-10
toTime()	将时间日期的日期固定到某一天，保留原始时间	toTime(toDateTime(‘2018-12-11 11:12:13’))	返回 1970-01-02 11:12:13

函数	用途	举例	结果
toStartOfMonth()	取日期或时间日期的月份的第一天，返回日期	toStartOfMonth(toDateTime(‘2018-12-11 11:12:13’))toStartOfMonth(toDate(‘2018-12-11’))	返回 2018-12-01返回 2018-12-01
toStartOfQuarter()	取日期或时间日期的季度的第一天，返回日期	toStartOfQuarter(toDateTime(‘2018-12-11 11:12:13’))toStartOfQuarter(toDate(‘2018-12-11’))	返回 2018-10-01返回 2018-10-01
toStartOfYear()	取日期或时间日期的年份的第一天，返回日期	toStartOfYear(toDateTime(‘2018-12-11 11:12:13’))toStartOfYear(toDate(‘2018-12-11’))	返回 2018-01-01返回 2018-01-01
toStartOfMinute()	截取时间日期到分钟（之后归零），返回日期	toStartOfMinute(toDateTime(‘2018-12-11 11:12:13’))	返回 2018-12-11 11:12:00
toStartOfFiveMinute()	截取时间日期到最近的5的倍数分钟（之后归零），返回日期	toStartOfFiveMinute(toDateTime(‘2018-12-11 11:12:13’))	返回 2018-12-11 11:10:00
toStartOfFifteenMinutes()	截取时间日期到最近的15的倍数分钟（之后归零），返回日期	toStartOfFifteenMinutes(toDateTime(‘2018-12-11 11:12:13’))	返回 2018-12-11 11:00:00
toStartOfHour()	截取时间日期到小时（之后归零），返回日期	toStartOfHour(toDateTime(‘2018-12-11 11:12:13’))	返回 2018-12-11 11:00:00
toStartOfDay()	截取时间日期到天（之后归零），返回日期	toStartOfDay(toDateTime(‘2018-12-11 11:12:13’))	返回 2018-12-11 00:00:00
timeSlot()	将时间日期中，分钟大于等于30的归于30，分钟数小于30的归为00	timeSlot(toDateTime(‘2018-12-11 11:33:13’))timeSlot(toDateTime(‘2018-12-11 11:33:13’))	返回 2018-12-11 11:00:00返回 2018-12-11 11:30:00

3.4.2日期或时间日期生成函数

函数	用途	举例	结果
now()	生成当前时间日期	now()	返回 2018-12-13 10:10:12
today()	生成今天的日期	today()	返回 2018-12-13
yesterday()	生成昨天的日期	yesterday()	返回 2018-12-12

3.4.3类型转化类函数

函数	用途	举例	结果
toDecimal32(‘whdwjfew’,8)	将数值型或者含有非数字的字符串进行精度保留	toDecimal32(23.12291, 3)toDecimal32(’_23.12291’, 3)	返回 23.122返回 0.000
toDecimal64(‘whdwjfew’,8)	将数值型或者含有非数字的字符串进行精度保留	toDecimal64(23.12291, 3)toDecimal64(’_23.12291’, 3)	返回 23.122返回 0.000
toDecimal128(‘whdwjfew’,8)	将数值型或者含有非数字的字符串进行精度保留	toDecimal128(23.12291, 3)toDecimal128(’_23.12291’, 3)	返回 23.122返回 0.000

函数	用途	举例	结果
toUInt8OrZero()	将无符号整数字符型转化为整数型，否则返回0	toUInt8OrZero(‘123’)toUInt8OrZero(‘123.12’)	返回 123返回 0
toInt8OrZero()	将整数字符型转化为整数型，否则返回0	toInt8OrZero(‘123’)toInt8OrZero(’-123’)	返回 123返回 -123
toFloat32OrZero()	将数值字符串型转化为数值型，注意：从toFloat32OrZero开始，丢32的没有对应的函数	toFloat32OrZero(‘-123’)toFloat32OrZero(‘123.123’)	返回 -123返回 123.123

函数	用途	举例	结果
toDate()	将字符型日期转化为日期型	toDate(‘2018-12-24’)	返回 2018-12-24
toDateTime()	将字符型时间日期转化为时间日期型	toDateTime(‘2018-12-24 10:10:00’)	返回 2018-12-24 10:10:00

函数	用途	举例	结果
toString()	将数值型、字符型、日期等转化为字符型	toString(‘2018-12-24’)toString(‘123’)	返回 2018-12-24返回 123

函数	用途	举例	结果
toTypeName()	返回数据的类型	toTypeName(toString(‘123’))toTypeName(toDate(‘2018-12-24’))	返回 String返回 Date

3.4.4 字符串操作

函数	用途	举例	结果
empty()	判断字符串是空为1，否则为0	empty(’’)empty(‘123a’)	返回 1返回 0
notEmpty()	判断字符串是非空为1，否则为0	notEmpty(’’)notEmpty(‘123a’)	返回 0返回 1
length()	返回字符串的长度	length(’’)length(‘123a’)	返回 0返回 4
lower()	将字符串转为小写	lower(‘aBc’)	返回 abc
upper()	将字符串转为大写	upper(‘aBc’)	返回 ABC
reverse()	将字符串反转	reverse(‘abc’)	返回 cba
substring(s, offset, length)	字符串截取	substring(‘123abcABC’, 2, 3)	返回 23a
appendTrailingCharIfAbsent(s, c)	如果字符串s非空，则将s后追加一个字符c(s最后一个字符与c不同)，否则不处理	appendTrailingCharIfAbsent(‘123abc’, ‘b’)appendTrailingCharIfAbsent(‘123abc’, ‘c’)	返回 123abcb返回 123abc

函数	用途	举例	结果
match(haystack,pattern)	字符串正则匹配，返回0或1	match(‘avhsca’,’vh’)	返回 1
extract(haystack,pattern)	返回匹配到的第一个子串	extract(‘iioomAj12123124OOBJB’, ‘\d+’)	返回 12123124
extractAll(haystack,pattern)	返回匹配到的所有子串，输出列表	extractAll(‘iioomAj12123124OOBJ123B’, ‘\d+’)	返回 [12123124,123]
like(haystack,pattern)	匹配到的数据返回1，否则返回0	like(‘avhsca’,’%vh%’)like(‘avhsca’,’%vabjh%’)	返回 1返回 0
notLike(haystack, pattern)	与like()函数相反	notLike(‘avhsca’,’%vh%’)notLike(‘avhsca’,’%vabjh%’)	返回 0返回 1

函数	用途	举例	结果
replaceOne(haystack,pattern,replacement)	替换第一个匹配到的pattern	replaceOne(‘asd123cbbj464sd’, ‘sd’, ‘-’)	返回 a-123cbbj464sd
replaceAll(haystack,pattern,replacement)	替换所有匹配到的pattern	replaceOne(‘asd123cbbj464sd’, ‘sd’, ‘-’)	返回 a-123cbbj464-
replaceRegexpOne(haystack, pattern, replacement)	正则匹配替换第一个匹配到的pattern	replaceRegexpOne(‘Hello, World!’, ‘o’, ‘- ‘)	返回 Hell- , World!
replaceRegexpAll(haystack,pattern,replacement)	正则匹配替换所有匹配到的pattern	replaceRegexpAll(‘Hello, World!’, ‘^’, ‘here: ‘)replaceRegexpAll(‘Hello, World!’, ‘o’, ‘– ‘)	返回 here: Hello, World!返回 Hell– , W– rld!

函数	用途	举例	结果
splitByChar(separator, s)	以单个字符分割字符串	splitByChar(’-’, ‘qw-asaf-asfqw-2312-asd’)	返回 [‘qw’,‘asaf’,‘asfqw’,‘2312’,‘asd’]
splitByString(separator, s)	以单个或多个字符分割字符串	splitByString(’-’, ‘qw-asaf-asfqw-2312-asd’)splitByString(’-a’, ‘qw-asaf-asfqw-2312-asd’)	返回 [‘qw’,‘asaf’,‘asfqw’,‘2312’,‘asd’]返回 [‘qw’,‘saf’,‘sfqw-2312’,‘sd’]

函数	用途	举例	结果
concat(s1,s2,…)	将字符串拼接	concat(‘123’, ‘abc’, ‘ABC’)	返回 123abcABC

3.4.5 条件语句

函数	用途	举例	结果
if(cond,then,else)	条件输出	if(1 > 2, ‘正确’, ‘错误’)	返回错误
multiIf(cond_1, then_1, cond_2, then_2…else)	多条件输出	multiIf(1 > 2, ‘正确’, 2 < 0, ‘正确’, ‘错误’)	返回错误

3.4.6 数学函数

函数	用途	举例	结果
e()	返回e的值	e()	返回 2.718281828459045
pi()	返回pi的值	pi()	返回 3.141592653589793
exp(x)	返回e的x次方	exp(1)	返回 2.718281828459045
exp2(x)	返回2的x次方	exp2(2)	返回 4
exp10(x)	返回10的x次方	exp10(1)	返回 10
log(x)	返回log以e为底的对数值	log(e())	返回 1
log2(x)	返回log以2为底的对数值	log2(2)	返回 1
log10(x)	返回log以10为底的对数值	log10(100)	返回 2
sqrt(x)	对x开平方	sqrt(4)	返回 2
cbrt(x)	对x开立方	cbrt(8)	返回 2
pow(x, y)	返回x的y次方	pow(2, 3)	返回 8

3.4.7舍入函数

函数	用途	举例	结果
floor(x[, N])	向下取数	floor(123.883, 1)floor(123.883, -1)	返回 123.8返回 120
ceil(x[, N])	向上取数	ceil(123.883, 1)ceil(123.883, -1)	返回 123.9返回 130
round(x[, N])	四舍五入	round(123.883, 1)round(123.883, -1)	返回 123.9返回 120

3.4.8 URL操作函数

函数	用途	举例	结果
protocol()	返回URL的协议类型	protocol(‘http://www.baidu.com.cn’)	返回 http
domain()	返回URL的域名	domain(‘http://www.baidu.com.cn’)	返回 www.baidu.com.cn
domainWithoutWWW()	返回URL不带www的域名	domainWithoutWWW(‘http://www.baidu.com.cn’)	返回 baidu.com.cn
topLevelDomain()	返回顶级域名	topLevelDomain(‘http://www.baidu.com.cn’)	返回 cn
firstSignificantSubdomain()	Returns the “first significant subdomain”.	firstSignificantSubdomain(‘http://www.baidu.com.cn’)	返回 baidu
cutToFirstSignificantSubdomain()	Returns the part of the domain that includes top-level subdomains up to the “first significant subdomain” (see the explanation above).	cutToFirstSignificantSubdomain(‘http://www.baidu.com.cn’)	返回 baidu.com.cn
path()	返回URL的路径	path(‘https://www.baidu.com/s?wd=SQL%E4%B8%AD%E7%9A%84split’)	返回 /s
pathFull()	返回URL的完整路径	pathFull(‘https://www.baidu.com/s?wd=SQL%E4%B8%AD%E7%9A%84split’)	返回 /s?wd=SQL%E4%B8%AD%E7%9A%84split
queryString()	返回URL的参数（查询字符串）	queryString(‘https://www.baidu.com/s?wd=SQL%E4%B8%AD%E7%9A%84split’)	返回 wd=SQL%E4%B8%AD%E7%9A%84split
extractURLParameters()	以列表的形式返回URL的参数	extractURLParameters(‘https://www.baidu.com/s?wd=SQL%E4%B8%AD%E7%9A%84split&ur=qwguq’)	返回 [‘wd=SQL%E4%B8%AD%E7%9A%84split’,‘ur=qwguq’]
extractURLParameterNames()	以列表的形式返回URL的参数名	extractURLParameterNames(‘https://www.baidu.com/s?wd=SQL%E4%B8%AD%E7%9A%84split&ur=qwguq’)	返回 [‘wd’,‘ur’]
cutQueryString()	返回URL？（参数）前面的内容	cutQueryString(‘https://www.baidu.com/s?wd=SQL%E4%B8%AD%E7%9A%84split&ur=qwguq’)	返回 https://www.baidu.com/s

3.4.9 IP操作函数

函数	用途	举例	结果
IPv4StringToNum(s)	将IPV4转为数值，非IPV4的转化为0	IPv4StringToNum(‘23.217.198.69’)IPv4StringToNum(‘adwh.124.qwfqw’)	返回 400148037返回 0
IPv4NumToString(num)	将数值转为IPV4	IPv4NumToString(400148037)	返回 23.217.198.69
IPv4NumToStringClassC(num)	将数值转为IPV4，且最后的段位用xxx代替	IPv4NumToStringClassC(400148037)	返回 23.217.198.xxx

3.4.10 表操作

函数	用途	举例	结果
INNER JOIN	内连接	A表 INNER JOIN B表	A表与B表的公共部分
LEFT OUTER JOIN	左外连接	A 表 LEFT OUTER JOIN B表	A表不在B表中的部分
RIGHT OUTER JOIN	右外连接	A 表 RIGHT OUTER JOIN B表	B表不在A表中的部分
FULL OUTER JOIN	全外连接	A 表 FULL OUTER JOIN B表	A与B表全部，没有为NULL

函数	用途	举例	结果
LIMIT N	查询N条数据，一般跟ORDER BY 连用	ORDER BY hit DESC LIMIT 10	按照hit列降排取前10
LIMIT N BY Clause	按照Clause列查询N条数据，一般跟ORDER BY 连用	SELECT date， domain, count(1) AS hit from db.tb where…GROUP BY date， domain,ORDER BY hit DESCLIMIT 10 BY date	取每天TOP10的域名

3.4.11 字典操作

函数	用途	举例	结果
dictGetString()	字典映射	dictGetString(‘ck_abc_dic’, ‘ck_value’, tuple(_abc))

4 用户行为分析

ClickHouse为用户提供了丰富的多参聚合函数（parametric aggregate function）和基于数组+Lambda表达式的高阶函数（higher-order function），将它们灵活使用可以达到魔法般的效果。在我们的体系中，ClickHouse定位点击流数仓，所以下面举几个用它来做用户行为（路径）分析的实战例子，包括：

路径匹配
智能路径检测
有序漏斗转化
用户留存
Session统计

4.1 路径匹配

CK默认提供了sequenceMatch函数检查是否有事件链满足输入的模式，sequenceCount函数则统计满足输入模式的事件链的数量。示例：

SELECT
  site_id,
  sequenceMatch('(?1)(?t<=15)(?2).*(?3)')(
    ts_date_time,
    event_type = 'shtLogon',
    event_type = 'shtKkclick' AND column_type = 'homePage',
    event_type = 'shtAddCart'
  ) AS is_match
FROM ods.analytics_access_log_all
WHERE ts_date >= '2020-07-01'
AND site_id IN (10266,10022,10339,10030)
GROUP BY site_id;
┌─site_id─┬─is_match─┐
│   10030 │        1 │
│   10339 │        1 │
│   10266 │        1 │
│   10022 │        1 │
└─────────┴──────────

SELECT
  site_id,
  sequenceCount('(?1)(?t<=15)(?2).*(?3)')(
    ts_date_time,
    event_type = 'shtLogon',
    event_type = 'shtKkclick' AND column_type = 'homePage',
    event_type = 'shtAddCart'
  ) AS seq_count
FROM ods.analytics_access_log_all
WHERE ts_date >= '2020-07-01'
AND site_id IN (10266,10022,10339,10030)
GROUP BY site_id;
┌─site_id─┬─seq_count─┐
│   10030 │     33611 │
│   10339 │     14045 │
│   10266 │     74542 │
│   10022 │     31534 │
└─────────┴───────────┘

4.2 智能路径检测

CK内置的sequenceMatch和sequenceCount函数只能满足部分需求，现有一个更复杂的需求：

给定期望的路径终点、途经点和最大事件时间间隔，查询出符合条件的路径详情及符合路径的用户数（按用户数降序排列）。

目前并没有现成的函数可以直接出结果，但是我们可以曲线救国，用数组和高阶函数的组合间接实现。完整SQL语句如下，


SELECT
  result_chain,
  uniqCombined(user_id) AS user_count
FROM (
  WITH
    toUInt32(maxIf(ts_date_time, event_type = 'shtOrderDone')) AS end_event_maxt,
    arrayCompact(arraySort(
      x -> x.1,
      arrayFilter(
        x -> x.1 <= end_event_maxt,
        groupArray((toUInt32(ts_date_time), (event_type, column_type)))
      )
    )) AS sorted_events,
    arrayEnumerate(sorted_events) AS event_idxs,
    arrayFilter(
      (x, y, z) -> z.1 <= end_event_maxt AND (z.2.1 = 'shtOrderDone' OR y > 600),
      event_idxs,
      arrayDifference(sorted_events.1),
      sorted_events
    ) AS gap_idxs,
    arrayMap(x -> x + 1, gap_idxs) AS gap_idxs_,
    arrayMap(x -> if(has(gap_idxs_, x), 1, 0), event_idxs) AS gap_masks,
    arraySplit((x, y) -> y, sorted_events, gap_masks) AS split_events
  SELECT
    user_id,
    arrayJoin(split_events) AS event_chain_,
    arrayCompact(event_chain_.2) AS event_chain,
    hasAll(event_chain, [('shtKkClick', 'homePage')]) AS has_midway_hit,
    arrayStringConcat(arrayMap(
      x -> concat(x.1, '#', x.2),
      event_chain
    ), ' -> ') AS result_chain
  FROM (
    SELECT ts_date,ts_date_time,event_type,column_type,user_id
    FROM ods.analytics_access_log_all
    WHERE ts_date >= '2020-06-30' AND ts_date <= '2020-07-02'
    AND site_id IN (10266,10022,10339,10030)
  )
  GROUP BY user_id
  HAVING length(event_chain) > 1
)
WHERE event_chain[length(event_chain)].1 = 'shtOrderDone'
AND has_midway_hit = 1
GROUP BY result_chain
ORDER BY user_count DESC LIMIT 20;

主要思路

将用户的行为用groupArray函数整理成<时间, <事件名, 页面名>>的元组，并用arraySort函数按时间升序排序；
利用arrayEnumerate函数获取原始行为链的下标数组；
利用arrayFilter和arrayDifference函数，过滤出原始行为链中的分界点下标。分界点的条件是路径终点或者时间差大于最大间隔；
利用arrayMap和has函数获取下标数组的掩码（由0和1组成的序列），用于最终切分，1表示分界点；
调用arraySplit函数将原始行为链按分界点切分成单次访问的行为链。注意该函数会将分界点作为新链的起始点，所以前面要将分界点的下标加1；
调用arrayJoin和arrayCompact函数将事件链的数组打平成多行单列，并去除相邻重复项。
调用hasAll函数确定是否全部存在指定的途经点。如果要求有任意一个途经点存在即可，就换用hasAny函数。当然，也可以修改WHERE谓词来排除指定的途经点。
将最终结果整理成可读的字符串，按行为链统计用户基数，完成。

4.3 有序漏斗转化

CK提供了windowFunnel函数实现漏斗，以指定时长（单位为秒）滑动窗口按序匹配事件链，并返回在窗口内转化到的步数。如有多种匹配，以步数最大（转换最深）的为准。

以自定义漏斗模型为例:

@Mapper
public interface FunnelMapper {
    @Select("select\n" +
            "total ,\n" +
            "(o1+o2+o3+o4) as v1 ,\n" +
            "(o2+o3+o4) as v2 ,\n" +
            "(o3+o4) as v3 ,\n" +
            "(o4) as v4 \n" +
            "from\n" +
            "(select\n" +
            "count(1) as total ,\n" +
            "sum(if(cc=4 , 1 , 0)) as o4 ,\n" +
            "sum(if(cc=3 , 1 , 0)) as o3 ,\n" +
            "sum(if(cc=2 , 1 , 0)) as o2 ,\n" +
            "sum(if(cc=1 , 1 , 0)) as o1 \n" +
            "from\n" +
            "(select\n" +
            "deviceId ,\n" +
            "windowFunnel(3600)(\n" +
            "toDateTime(ctime) ,\n" +
            "eventId='adShow'  ,\n" +
            "eventId='adClick',\n" +
            "eventId='productView' ,\n" +
            "eventId='submitOrder'\n" +
            ") as cc\n" +
            "from\n" +
            "tb_log \n" +
            "group by deviceId))")
    public EventCountBean getStaticFunnel() ;

    @Insert("insert into tb_funnel values(#{id},#{name},#{eventChannel})")
    public  void  addFunnel(TbFunnel tbFunnel) ;

    @Select("select * from tb_funnel")
    public List<TbFunnel> getAllFunnel() ;

    /**
     * mybatis的SQL输入
     *     #{}   建议使用
     *     ${}  like   ${}
     * @param name
     * @return
     */
    @Select("select * from tb_funnel where  name = #{name}")
    public  TbFunnel getFunnelByName(String name) ;

    @SelectProvider(type = MySQlProvider.class ,method = "getdynamicSQL")
    public EventCountBean getStaticFunnelByEventChannel(String  eventChannel) ;

    class MySQlProvider{
        public String  getdynamicSQL(String eventChannel){
            // 拼接 事件链条属性
            String[] split = eventChannel.split("-");
            StringBuilder sb = new StringBuilder();
            StringBuilder sb2 =  new StringBuilder();
            StringBuilder sb3 =  new StringBuilder();
            for(int i = 0 ; i<split.length ; i++)  {
                sb.append("eventId=").append("'"+split[i]+"'").append(",");
                sb2.append("sum(if(cc=").append(split.length-i).append(",1,0)) as o").append(split.length-i+",") ;
                for(int j = split.length ; j > i ; j--){
                    sb3.append("o").append(j).append("+") ;
                }
                sb3.deleteCharAt(sb3.length()-1).append(" as v").append(i+1).append(",");
            }
            String s1 = sb.deleteCharAt(sb.length() - 1).toString();
            String s2 = sb2.deleteCharAt(sb2.length() - 1).toString();
            String s3 = sb3.deleteCharAt(sb3.length() - 1).toString();
            String sql = "select\n" +
                    "total ,\n" +
                    s3+
                    " from\n" +
                    "(select\n" +
                    "count(1) as total ,\n" +
                    s2+
                    " from\n" +
                    "(select\n" +
                    "deviceId ,\n" +
                    "windowFunnel(3600)(\n" +
                    "toDateTime(ctime) ,\n" +
                    s1+
                    ") as cc\n" +
                    "from\n" +
                    "tb_log \n" +
                    "group by deviceId));";
            return  sql ;
        }
    }
}

4.4 用户留存

retention函数可以方便地计算留存情况。该函数接受多个条件，以第一个条件的结果为基准，观察后面的各个条件是否也满足，若满足则置1，不满足则置0，最终返回0和1的数组。通过统计1的数量，即可计算出留存率。

SELECT
  sum(ret[1]) AS original,
  sum(ret[2]) AS next_day_ret,
  round(next_day_ret / original * 100, 3) AS next_day_ratio,
  sum(ret[3]) AS seven_day_ret,
  round(seven_day_ret / original * 100, 3) AS seven_day_ratio
FROM (
  WITH toDate('2020-06-24') AS first_date
  SELECT
    user_id,
    retention(
      ts_date = first_date,
      ts_date = first_date + INTERVAL 1 DAY,
      ts_date = first_date + INTERVAL 7 DAY
    ) AS ret
  FROM ods.ms_order_done_all
  WHERE ts_date >= first_date AND ts_date <= first_date + INTERVAL 7 DAY
  GROUP BY user_id
);



SELECT 
    sum(r[1]) AS r1,
    sum(r[2]) AS r2,
    sum(r[3]) AS r3,
    sum(r[4]) AS r4
FROM 
(
    SELECT 
        uid,
        retention(date = '2020-01-01', date = '2020-01-02', date = '2020-01-03', date = '2020-01-04') AS r
    FROM retention_test
    WHERE date IN ('2020-01-01', '2020-01-02', '2020-01-03', '2020-01-04')
    GROUP BY uid
)

┌─r1─┬─r2─┬─r3─┬─r4─┐
│  5 │  3 │  3 │  1 │
└────┴────┴────┴────┘

4.5 session统计


SELECT
  ts_date,
  sum(length(session_gaps)) AS session_cnt
FROM (
  WITH
    arraySort(groupArray(toUInt32(ts_date_time))) AS times,
    arrayDifference(times) AS times_diff
  SELECT
    ts_date,
    arrayFilter(x -> x > 1800, times_diff) AS session_gaps
  FROM ods.analytics_access_log_all
  WHERE ts_date >= '2020-06-30'
  GROUP BY ts_date,user_id
)
GROUP BY ts_date;

关注易学在线公众号
每日更新大量优质技术文档
第一时间获知最新上架课程
与众多大数据猿分享与交流

DOIT.EDU 资料来自网络收集整理，如有侵权请告知

程序员必备技能之ClickHouse