ClickHouse查询语句详解

大白兔黑又黑

已于 2022-04-21 20:55:35 修改

阅读量1.6w

点赞数 5

分类专栏：大数据 # ClickHouse 文章标签： ClickHouse 数据库大数据 sql

于 2022-04-20 20:08:09 首次发布

本文链接：https://blog.csdn.net/haveanybody/article/details/124210922

版权

大数据同时被 2 个专栏收录

19 篇文章 8 订阅

订阅专栏

ClickHouse

10 篇文章 5 订阅

订阅专栏

ClickHouse查询语句兼容大部分SQL语法，并且进行了更加丰富的扩展，查询语句模板如下：

[WITH expr_list|(subquery)]
SELECT [DISTINCT [ON (column1, column2, ...)]] 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 ROLLUP|WITH CUBE] [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]
[SETTINGS ...]
[UNION  ...]
[INTO OUTFILE filename [COMPRESSION type] ]
[FORMAT format]

下面我们会逐一介绍各个子表达式，主要介绍和标准SQL不同的部分，如果某条语句执行报错，则可能是clickhouse版本不支持。

1. SELECT 语句

select 字段查询除了支持直接列名查询外，还支持一些特殊查询：

select col1, col2 from test_table;
select * from test_table;
select 1;   -- 和hive一样，相当于oracle dual表，实际是 select 1 from system.one
select count() from test_table; -- 统计表记录数量
select COLUMNS('a') FROM test_table; -- 基于re2库正则匹配字段名
SELECT COLUMNS('a'), COLUMNS('c'), toTypeName(COLUMNS('c')) FROM test_table; --嵌套在函数中使用
SELECT * APPLY(sum) FROM columns_transformers; -- 为所有列调用sum函数
SELECT * EXCEPT (i) from columns_transformers; -- 查询除 i 以外的其他列
SELECT COLUMNS('[jk]') APPLY(toString) APPLY(length) APPLY(max) from columns_transformers; -- 组合使用修饰符
SELECT * REPLACE(i + 1 AS i) EXCEPT (j) APPLY(sum) from columns_transformers; -- 组合使用修饰符

在统计全表数据时，可以使用count()，前面文章我们介绍过，MergeTree会把每个分区数据量记录在count.txt文件中，所以MergeTree统计全表记录数是很快的，如果是条件查询count，应使用count(主键字段)查询。
COLUMNS表达式支持re2正则表达式匹配字段名，COLUMNS(‘a’) 表示查询所有列名含 "a"的列，COLUMNS也可以嵌套在函数中使用。
在select查询时，尽量避免使用 select * from，显式的指定要查的列，如果为了获取字段信息，应该使用 DESC TABLE test_table，而不是 select * from test_table limit 1。
如果是使用客户端查询样例数据，如使用 DBeaver 查询数据，一定要加 limit 子句，可以提高查询效率，而且节约内存。
使用 SELECT APPLY( ) FROM [db.]table_name 为所有返回字段调用某个函数，需要字段类型支持调用函数。
使用 SELECT EXCEPT ( col_name1 [, col_name2, col_name3, …] ) FROM [db.]table_name 去除不需要的查询字段，尤其是宽表中去掉少量不需要字段时很有用。

2. SETTINGS 语句

如果在多条查询中，只想对某条语句设置查询参数，则可以使用 SETTINGS 选项：

SELECT * FROM some_table SETTINGS optimize_read_in_order=1, cast_keep_nullable=1;

3. DISTINCT 语句

clickhouse distinct选项除了支持对指定列去重以外，还可以返回去重列以外的字段：

SELECT DISTINCT ON (a,b) * FROM t1; -- 基于a，b列去重，同时返回所有字段，返回的是随机一条记录（一般是第一条）

如果有多条为NULL的记录，则distinct只保留一条，换句话说在distinct查询中 NULL = NULL。
distinct子句优先级高于limit n，即返回n个去重值以后查询结束，而不是n条记录的去重值。
使用group by也可以去重，但是distinct可以在处理时输出数据块，而无需等待整个查询完成运行。

4. EXCEPT / UNION / INTERSECT（差集、并集、交集）

SELECT number FROM numbers(1,10) EXCEPT SELECT number FROM numbers(3,6);
SELECT number FROM numbers(1,10) UNION ALL SELECT number FROM numbers(3,6);
SELECT number FROM numbers(1,10) UNION DISTINCT SELECT number FROM numbers(3,6);
SELECT number FROM numbers(1,10) INTERSECT SELECT number FROM numbers(3,6);

EXCEPT（差集）在低版本clickhouse中不支持，使用前应先验证。EXCEPT 和 UNION 具有相同的优先级。
UNION ALL 不会去重，返回所有结果集，UNION DISTINCT 返回结果集的去重结果，如果只使用 UNION，则会根据 union_default_mode 设置参数解析，union_default_mode 可以设置为 ALL 或者 DISTINCT（如：SET union_default_mode = ‘DISTINCT’），使用时最好显式指定 UNION ALL/DISTINCT 结果。
UNION 结果是根据字段位置判断的，无论两条查询语句的字段名是否一致，即使字段一样，但是select时字段顺序不一样，结果也是按照位置UNION，clickhouse不会根据列名解析，这一点尤其要注意。
对于两条查询，如果一条是 non-Nullable 类型，另一条是 Nullable 类型，则UNION结果是 Nullable 类型。
ORDER BY 和 LIMIT 的优先级比 UNION 高，所以如果需要对 UNION 的结果进行处理，需要使用子查询。
INTERSECT（交集）不会对结果去重，且优先级高于UNION和EXCEPT子句。

5. GROUP BY 语句

GROUP BY、ORDER BY 和 LIMIT BY 子句支持位置参数查询，索引位置从1开始，但是在查询之前要打开enable_positional_arguments设置（要确定使用的clickhouse版本支持），例如按照第一列和第二列排序：

SET enable_positional_arguments = 1;
SELECT * FROM positional_arguments ORDER BY 1,2;

对于 NULL，GROUP BY 也会当做一个值处理，例如：SELECT sum(x), y FROM t_null_big GROUP BY y 所有 y 为 NULL 的记录也会计算 sum(x) 并返回一条结果。
clikhouse GROUP BY 语句同样支持组合 WITH ROLLUP/CUBE 使用，且后面也可以接 HAVING 子句。例如对于下面的表：

┌─year─┬─month─┬─day─┐
│ 2019 │     1 │   5 │
│ 2019 │     1 │  15 │
│ 2020 │     1 │   5 │
│ 2020 │     1 │  15 │
│ 2020 │    10 │   5 │
│ 2020 │    10 │  15 │
└──────┴───────┴─────┘

执行：

SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP;

结果如下：

┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │    10 │  15 │       1 │
│ 2020 │     1 │   5 │       1 │
│ 2019 │     1 │   5 │       1 │
│ 2020 │     1 │  15 │       1 │
│ 2019 │     1 │  15 │       1 │
│ 2020 │    10 │   5 │       1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │     1 │   0 │       2 │
│ 2020 │     1 │   0 │       2 │
│ 2020 │    10 │   0 │       2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │     0 │   0 │       2 │
│ 2020 │     0 │   0 │       4 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│    0 │     0 │   0 │       6 │
└──────┴───────┴─────┴─────────┘

GROUP BY year, month, day WITH ROLLUP 会依次执行：

SELECT year, month, day, count(*) FROM t GROUP BY year, month, day;
SELECT year, month, count(*) FROM t GROUP BY year, month;
SELECT year, count(*) FROM t GROUP BY year;
SELECT count(*) FROM t;

对于不参与 GROUP BY 的key补零或者置空。
对于 GROUP BY year, month, day WITH CUBE 会依次执行所有组合查询：

SELECT year, month, day, count(*) FROM t GROUP BY year, month, day;
SELECT year, month, count(*) FROM t GROUP BY year, month;
SELECT year, day, count(*) FROM t GROUP BY year, day;
SELECT year, count(*) FROM t GROUP BY year;
SELECT month, day, count(*) FROM t GROUP BY month, day;
SELECT month, count(*) FROM t GROUP BY month;
SELECT day, count(*) FROM t GROUP BY day;
SELECT count(*) FROM t;

相比于标准SQL，clickhouse GROUP BY 可以结合any函数获得非分组键的第一条记录：

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

6. HAVING 语句

HAVING 可以过滤 GROUP BY 生成的聚合结果，并且可以使用 SELECT 中的字段别名。

7. FROM 语句

和关系型数据库一样，from后面可以是一个表名、子查询、JOIN、ARRAY JOIN、表函数（如numbers）等，也可以直接跟逗号分隔的多个表，表示 CROSS JOIN。如果子查询中的字段没有在外部查询中用到，则子查询会丢弃未使用的字段。

8. FINAL 修饰符

在from表查询时，可以在表名后跟 FINAL 修饰符，当使用 FINAL 修饰符时，ClickHouse会在返回结果之前完全合并数据（执行Merge逻辑），适用于MergeTree引擎表（或者底层是MergeTree引擎表的视图等）。ReplacingMergeTree、CollapsingMergeTree 等引擎表只有在Merge以后才能保证预期的效果，所以在查询此类表时可以使用 FINAL 修饰符。需要注意：

因为需要执行Merge操作，所以 FINAL 查询的速度会变慢，并且消耗资源。慎重使用 FINAL，大多数情况下应避免使用，可以通过聚合等手段来处理没有Merge的表，详情可参考前面文章。
在新版clickhouse中 FINAL 支持多线程，通过 max_final_threads 参数（最大线程数）设置，默认值是16，设置0或1表示单线程。
带有FINAL的查询除读取查询中指定的列外，还读取主键列。

SELECT * FROM test_table FINAL

9. WHERE 语句

对于 NULL 的判断同样使用 IS NULL / IS NOT NULL 或者 isNull / isNotNull 函数。
如果表引擎支持，WHERE 表达式会使用索引和分区优化查询，减少扫描数据量，所以 where 查询尽量使用主键字段。

10. PREWHERE 语句

PREWHERE 是一种更有效地应用过滤优化的方法，只支持 MergeTree 系列表引擎，一般不需要我们显式的定义，clickhouse默认情况下会将 WHERE 子句中的可优化项转为 PREWHERE。PREWHERE 的工作原理是将WHERE条件的部分自动移动到WHERE前阶段，在一个查询中可以同时指定PREWHERE和WHERE，在这种情况下，PREWHERE优先于WHERE执行。如果你觉得自己定义 PREWHERE 语句会比clickhouse默认优化做的更好，则可以显式定义 PREWHERE 语句。可以通过分别执行比较 WHERE 语句和自己定义的 PREWHERE 语句决定，或者使用 explain 查看clickhouse的优化结果。

在prewhere优化中，首先只读取执行prewhere表达式所需的列。然后读取select查询其余字段，但只读取prewhere表达式为真(至少对某些行而言)的块。

在查询数据时，我们会尽可能的利用分区、主键字段，或者二级索引查询，但是也有一些条件查询时很难处理的，例如某个字段的数据非常分散，在不同分区、不同块中存在一些少量记录，也没有明显的min/max区分，MergeTree 就会解压扫描所有包含记录的所有查询列的压缩块。例如下面的表和查询语句：

CREATE TABLE order_info
(
    `oid`   UInt64,             --订单ID
    `buyer_nick`    String,     --买家ID
    `seller_nick`   String,     --店铺ID
    `payment`   Decimal64(4),   --订单金额
    `order_status`  UInt8,      --订单状态
    ...
    `gmt_order_create`  DateTime,   --下单时间
    `gmt_order_pay` DateTime,       --付款时间
    `gmt_update_time` DateTime,     --记录变更时间
    INDEX oid_idx (oid) TYPE minmax GRANULARITY 32
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(gmt_order_create)         --以天为单位分区
ORDER BY (seller_nick, gmt_order_create, oid) --排序键
PRIMARY KEY (seller_nick, gmt_order_create)     --主键
SETTINGS index_granularity = 8192;

--常规
select *
from order_info where
where order_status = 2 --订单取消
and gmt_order_create > '2020-0802 00:00:00' 
and gmt_order_create < '2020-0807 00:00:00';

--两阶段扫描
select *
from order_info where
prewhere order_status = 2 --订单取消
where gmt_order_create > '2020-0802 00:00:00' 
and gmt_order_create < '2020-0807 00:00:00';

第一种执行逻辑存储层扫描会把5天内的全部列数据从磁盘读取出来，然后计算引擎再按照order_status列过滤出满足条件的行。在两阶段扫描的框架下，prewhere表达式会下推到存储扫描过程中执行，优先扫描出order_status列存块，检查是否有记录满足条件，再把满足条件行的其他列读取出来，当没有任何记录满足条件时，其他列的块数据就可以跳过不读了。

PREWHERE 只支持 MergeTree 系列引擎表。
WHERE 是否自动优化前推 PREWHERE 通过 optimize_move_to_prewhere 参数控制，0 禁止，1 开启，默认开启。
如果查询中使用了 FINAL 修饰符，则 PREWHERE 不一定有效，因为此时 PREWHERE 同时受 optimize_move_to_prewhere 和 optimize_move_to_prewhere_if_final 控制，optimize_move_to_prewhere_if_final 默认是禁止的。另外，因为 PREWHERE 在 FINAL 之前执行，所以当对表中不属于ORDER BY区域的字段使用 PREWHERE 时，FINAL 查询可能会发生倾斜。

11. LIMIT 语句

select * from numbers(1, 10) limit 5;    		-- 前5条记录
select * from numbers(1, 10) limit 5, 2; 		-- 跳过前5条记录后，返回剩下的前2条
select * from numbers(1, 10) limit 2 offset 5;	-- 和前一条语句等价
select * from numbers(1, 10) limit 5 settings limit = 3;  -- 只返回前3条记录
select * from 
(select number % 3 as n from numbers(1, 15))
order by n limit 7 with ties;					-- 返回所有的0和1共7条记录

limit n 的返回记录数受 limit 参数限制，limit 参数默认是 0 表示不限制。
order by + limit n with ties 除了返回前n行记录外，还会返回所有与第n行记录order by字段相等的记录。

12. LIMIT BY 语句

我们经常会遇到一种需求：获取分组字段的前n行记录。例如一个id会对应多条记录，要求取出其中的一条记录（去重）；查找每个 user_id 最近的一次消费记录等等。一般我们有两种做法：

-- 根据最早时间查找
select a.* from test_table a
inner join
(select user_id, max(trade_time) as trade_time from test_table group by user_id) b
on a.user_id = b.user_id and a.trade_time = b.trade_time;

-- 使用 row_number() 查找
select user_id, trade_time from
(select user_id, trade_time, row_number() over(partition by user_id order by trade_time desc) as rn from test_table)
where rn = 1;

第一种方法只能查找top1，且如果top1有多条相同的记录会取出多条记录；第二种方法可以解决前面的问题，但是需要子查询。clickhouse提供了一种基于limit by的查询新实现：

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

上面是 LIMIT BY 语句的两种语法形式，不同点和 LIMIT 一样，不再解释。LIMIT n BY expressions 的功能就是根据 expressions 表达式分组后的每个组的前n条记录，再结合 ORDER BY 语句使用，是不是就是我们前面说的功能了？

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

SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id;
┌─id─┬─val─┐
│  1 │  10 │
│  1 │  11 │
│  2 │  20 │
│  2 │  21 │
└────┴─────┘
SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id;
-- 或者 SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id;
┌─id─┬─val─┐
│  1 │  11 │
│  1 │  12 │
│  2 │  21 │
└────┴─────┘

对于我们前面提到的北京案例，可以实现如下：

SELECT * FROM test_table ORDER BY user_id, trade_time desc LIMIT 1 BY user_id;

为了提高效率，建表语句最好也使用 ORDER BY user_id, trade_time。

LIMIT BY 也可以使用位置索引查询，参考 GROUP BY。
LIMIT BY 和 LIMIT 可以同时使用，如下：

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

13. OFFSET FETCH 语句

在 LIMIT 语句中我们已经提到了 OFFSET 语句，标准语法如下：

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

简单理解就是跳过 offset_row_count 行，查询剩下的前 fetch_row_count 行，可以发现 OFFSET FETCH 语句都可以通过 LIMIT 语句实现，没必要刻意记忆，只需能看懂别人的语句即可。其中 {ROW | ROWS}、{FIRST | NEXT} 表示可以使用任意一个关键词，但是不能省略。如果使用 ONLY，则和 LIMIT 等价，例如下面两条语句结果一样：

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

WITH TIES 和 LIMIT 语句中的 WITH TIES 效果也是一样，不再介绍。需要注意的是如果同时使用 OFFSET 和 FETCH，则 OFFSET 必须在 FETCH 之前。

14. ORDER BY 语句

ORDER BY 同样支持字段位置索引，设置 enable_positional_arguments 参数即可，参考 GROUP BY。
对于 NaN 和 NULL，排序时默认是 NULLS LAST，非空值在前面，然后依次是 NaN、NULL。使用 NULLS FIRST 表示首先是 NULL、NaN，然后是非空值。
对于字符串类型，可以使用 COLLATE 指定排序规则，但是建议少使用，因为 COLLATE 比正常的按字节排序效率低。
ORDER BY 语句尽量配合 LIMIT 语句使用，可以结束内存。对于分布式查询，会在远程服务器上完成局部排序，然后在请求服务器上合并结果。这意味着对于分布式排序，要排序的数据量可能会大于单个服务器上的内存量。如果没有足够的RAM，可以在外部内存中执行排序(在磁盘上创建临时文件)，但是需要设置 max_bytes_before_external_sort 参数（默认为0，不开启本地临时文件）。如果启用该选项，当要排序的数据量达到指定字节数时，收集的数据将被排序并转储到一个临时文件中，读取所有数据后，合并所有排序的文件并输出结果。在配置文件中默认指定了临时文件被写入/var/lib/clickhouse/tmp/目录下，但是可以通过 tmp_path 参数改变临时文件路径。需要注意的是外部排序的工作效率远远低于在RAM中进行排序。
如果ORDER BY表达式具有与表排序键一致的前缀，则可以使用 optimize_read_in_order 设置（默认开启）来优化查询。当启用optimize_read_in_order设置时，ClickHouse服务器使用表索引并按ORDER BY键的顺序读取数据。这可以避免在指定LIMIT的情况下读取所有数据。因此，对从大数据中查询 limit n 处理速度更快。当运行具有ORDER BY子句、LIMIT large 和 WHERE 条件的查询时，需要在找到所查询的数据之前读取大量记录，请考虑手动禁用 optimize_read_in_order。
ORDER BY 可以结合 WITH FILL 实现类似于pandas中的reindex功能。例如我们有3、6、7三个id的数据，现在我想补齐0到10所有id的数据，其中3、6、7应该对应实际值，其他id没有值（默认值填充），应该怎么实现呢？我们可以先生成一个0~10的id维表，然后通过join原表实现。clickhouse提供了 WITH FILL 语句实现此类功能，语法格式如下：

ORDER BY expr [WITH FILL] [FROM const_expr] [TO const_expr] [STEP const_numeric_expr], ... exprN [WITH FILL] [FROM expr] [TO expr] [STEP numeric_expr]

其中 FROM const_expr 表示起点，TO const_expr 表示终点，注意是左闭右开，STEP const_numeric_expr 表示步长，相当于python中的range函数生成一个序列。例如下面的查询语句：

SELECT n, source FROM (
   SELECT toFloat32(number % 10) AS n, 'original' AS source
   FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5;

┌───n─┬─source───┐
│   0 │          │
│ 0.5 │          │
│   1 │ original │
│ 1.5 │          │
│   2 │          │
│ 2.5 │          │
│   3 │          │
│ 3.5 │          │
│   4 │ original │
│ 4.5 │          │
│   5 │          │
│ 5.5 │          │
│   7 │ original │
└─────┴──────────┘

子查询的结果只有1、4、7三条，WITH FILL FROM 0 TO 5.51 STEP 0.5 生成了 [0, 0.5, 1, …, 5.5] 的序列数据，其中1和4是原表中存在的数据，故能匹配到source字段，原表中不存在的，source字段填充默认值 ‘’，另外对于序列不存在的7这条记录也会保留，也就是说不会删除原表的数据。

当 FROM const_expr 没有定义时，就使用 ORDER BY 字段的最小值；当 TO const_expr 没有定义时，就使用 ORDER BY 字段的最大值；当 STEP const_numeric_exp 没有定义时，如果 ORDER BY 字段是数值型则默认是1.0，如果 ORDER BY 字段是Date类型则默认是1天，如果 ORDER BY 字段是DateTime类型则默认是1秒。如果 STEP const_numeric_exp 是一个数值型表达式，则如果 ORDER BY 字段是Date类型，解释为天，如果是DateTime类型，则解释为秒。

可以定义多个 WITH FILL 子句，按照顺序优先级，其他字段无法计算时就填充默认值：

SELECT
    toDate((number * 10) * 86400) AS d1,
    toDate(number * 86400) AS d2,
    'original' AS source
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY
    d2 WITH FILL,
    d1 WITH FILL STEP 5;

┌───d1───────┬───d2───────┬─source───┐
│ 1970-01-11 │ 1970-01-02 │ original │
│ 1970-01-01 │ 1970-01-03 │          │
│ 1970-01-01 │ 1970-01-04 │          │
│ 1970-02-10 │ 1970-01-05 │ original │
│ 1970-01-01 │ 1970-01-06 │          │
│ 1970-01-01 │ 1970-01-07 │          │
│ 1970-03-12 │ 1970-01-08 │ original │
└────────────┴────────────┴──────────┘

SELECT
    toDate((number * 10) * 86400) AS d1,
    toDate(number * 86400) AS d2,
    'original' AS source
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY
    d1 WITH FILL STEP 5,
    d2 WITH FILL;

┌───d1───────┬───d2───────┬─source───┐
│ 1970-01-11 │ 1970-01-02 │ original │
│ 1970-01-16 │ 1970-01-01 │          │
│ 1970-01-21 │ 1970-01-01 │          │
│ 1970-01-26 │ 1970-01-01 │          │
│ 1970-01-31 │ 1970-01-01 │          │
│ 1970-02-05 │ 1970-01-01 │          │
│ 1970-02-10 │ 1970-01-05 │ original │
│ 1970-02-15 │ 1970-01-01 │          │
│ 1970-02-20 │ 1970-01-01 │          │
│ 1970-02-25 │ 1970-01-01 │          │
│ 1970-03-02 │ 1970-01-01 │          │
│ 1970-03-07 │ 1970-01-01 │          │
│ 1970-03-12 │ 1970-01-08 │ original │
└────────────┴────────────┴──────────┘

15. WITH 语句

clickhouse同样支持with查询，语法如下：

WITH <expression> AS <identifier>
或者
WITH <identifier> AS <subquery expression>

注意区分两种语法的不同，第一种表达式在前，别名在后，表达式可以是常量值、要计算的逻辑或者一个计算结果，第二种是别名在前，子查询在后，返回的是一个表。

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

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

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

WITH test1 AS (SELECT i + 1, j + 1 FROM test1)
SELECT * FROM test1;

16. JOIN 语句

对于clickhouse来说，join并不是一个擅长的操作，在使用之前请确认join是必须的，没有其他可替代操作。本文主要介绍 join 语句的语法，不会涉及太多 join 操作的优化，关于查询的优化以及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>) ...

clickhouse 支持所有的sql join操作，包括：INNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN、CROSS JOIN（笛卡尔积），JOIN 默认就是 INNER JOIN，OUTER 关键字可以省略，CROSS JOIN的另一种写法是直接使用逗号连接多个表。

JOIN 操作只支持等值连接（AND 和 OR都支持），但是可以拼接针对单个表列的不等值判断条件（把where判断移到on中）。

除了标准 JOIN 外，clickhouse还支持 ASOF JOIN，功能和 pandas 中的 merge_asof 相同，可以进行最接近关联：

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

例如，表A是用户存款记录表，表B是利率表，如果要查询用户每次存款对应的利率，应该怎么查呢？假如 2022-01-21 的利率是 2.1%，2022-02-10 的利率调整为 2.3%，那么一笔在 2022-02-01 的存款利率显然应该是 2.1%，即我们要查的是在 2022-02-01 之前最近的一次利率调整记录，此时就可以通过 ASOF JOIN 实现，equi_cond 是等值条件，closest_match_cond 是不等值条件，支持数值型和时间类型，可以是 >、>=、<、<=。例如下面的表数据：

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

SELECT * FROM table_1 ASOF JOIN table_2
ON table_1.user_id=table_2.user_id AND table_1.ev_time >= table_2.ev_time;

table_1 12:00 的数据匹配的就是 table_2 11:59 的数据。注意：Join 表引擎不支持 ASOF。

在 Distributed 表中join需要注意 JOIN 和 GLOBAL JOIN 的区别，因为数据是跨节点存储的，GLOBAL JOIN 会消耗大量的RAM和IO资源，JOIN 可能会导致错误的结果，使用过程中要小心，详情我们会在后面优化篇介绍，也可以参考下文 GLOBAL IN 的介绍。

join 时对于匹配不到的非空类型字段记录会使用默认值填充，可以通过 join_use_nulls 参数控制，join_use_nulls 为 0 表示使用对应字段类型的默认值填充，为 1 表示使用 NULL（新的字段类型会转为 Nullable），默认是0。
默认情况下 ClickHouse 获取右表并在RAM中为其创建一个hash表，所以一般建议把小表放在右边。如果需要限制 join 操作使用的内存资源，可以设置 max_rows_in_join 和 max_bytes_in_join，当达到这些限制时，clickhouse 将根据 join_overflow_mode 执行相应操作。
join_default_strictness 可以设置join的严格性，如果为 ALL，则结果就是正常的多对多（如果关联键有重复记录），如果为 ANY，则会对结果基于关联键去重（实际是取第一条），如果是 ASOF 就是不确定匹配。

17. FORMAT 语句

ClickHouse支持广泛的序列化格式，这些格式可以用于查询结果以及其他内容。为了方便与其他系统集成、提高性能，可以使用特定的格式。有多种方法可以为SELECT输出选择格式，其中一种方法是在查询结束时指定format格式。对于批处理方式的 HTTP接口和 command-line client，默认使用 TabSeparated，对于交互式的 command-line client，默认是使用 PrettyCompact。

SELECT EventDate, count() AS c FROM test.hits GROUP BY EventDate WITH TOTALS ORDER BY EventDate FORMAT TabSeparated

18. EXISTS 语句

clickhouse 也支持 EXISTS 语句，语法格式是 EXISTS(subquery)，EXISTS 里面嵌套一个查询，如果子查询有记录返回则 EXISTS(subquery) 为真，如果子查询没有记录返回则 EXISTS(subquery) 为假，可作为 WHERE 判断条件。注意和oracle exists不同，clickhouse 的 EXISTS 语句不支持在子查询中使用主查询的表或者字段。

SELECT count() FROM numbers(10) WHERE EXISTS(SELECT number FROM numbers(10) WHERE number > 11);
┌─count()─┐
│       0 │
└─────────┘

19. IN 语句

IN 操作是clickhouse一个非常重要的操作，支持 IN、NOT IN、GLOBAL IN 和 GLOBAL NOT IN。支持单字段判断、多字段判断、子查询判断等操作。

-- 单字段
SELECT number in (3, 5) from numbers(1, 10);
-- 多字段/子查询判断
SELECT (CounterID, UserID) IN (SELECT CounterID, UserID FROM ...) FROM ...;
-- 计算2014-03-17的访问用户在其他时间的访问占比
SELECT
    EventDate,
    avg(UserID IN
    (
        SELECT UserID
        FROM test.hits
        WHERE EventDate = toDate('2014-03-17')
    )) AS ratio
FROM test.hits
GROUP BY EventDate
ORDER BY EventDate ASC

IN / NOT IN 操作对 NULL 默认为假，可以通过设置 transform_null_in 改变默认值。如果 transform_null_in 为0，则在IN操作中返回false，如果为1，则在IN操作中返回true。

┌──idx─┬─────i─┐
│    1 │     1 │
│    2 │  NULL │
│    3 │     3 │
└──────┴───────┘

SELECT idx, i FROM null_in WHERE i IN (1, NULL) SETTINGS transform_null_in = 0;
┌──idx─┬────i─┐
│    1 │    1 │
└──────┴──────┘

SELECT idx, i FROM null_in WHERE i IN (1, NULL) SETTINGS transform_null_in = 1;
┌──idx─┬─────i─┐
│    1 │     1 │
│    2 │  NULL │
└──────┴───────┘

对于分布式表，因为数据是跨节点存储的，使用 IN 操作时需要谨慎。假设在每个节点上有一个本地表 local_table，还有一个分布式表 distributed_table 管理所有本地表。当我们查询分布式表时：

SELECT uniq(UserID) FROM distributed_table;

实际上会发送一个本地查询到所有节点上执行：

SELECT uniq(UserID) FROM local_table;

然后所有中间结果将返回到请求服务器上并进行合并，最终结果发送到客户端。
假如在分布式表中执行一个分布式表和本地表的 IN 查询：

SELECT uniq(UserID) FROM distributed_table 
WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34)

那么发送到每个节点上的实际查询就是：

SELECT uniq(UserID) FROM local_table 
WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34)

假如 UserID 相同的数据都在同一个节点上，那么这条查询是没有问题的，并且是高效的，因为不需要跨节点。假如 UserID 相同的数据分布在不同的节点上，那么查询结果很可能是不对的。例如有两条 UserID = 10，CounterID 分别为 34、101500 的数据分别存在节点A、B上，当在 A 上执行查询时，子查询可以查到 CounterID = 34的记录，但是查不到CounterID=101500的记录，所以返回空，当在 B 上执行查询时，子查询可以查到 CounterID = 101500的记录，但是子查询查不到CounterID=34的记录，所以也返回空，导致 UserID = 10的记录丢失。

为了解决上面的问题，可以在子查询中也使用分布式表：

SELECT uniq(UserID) FROM distributed_table 
WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34);

发送到所有节点上执行的语句就变为：

SELECT uniq(UserID) FROM local_table 
WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34);

因为子查询还是一个分布式表，所以每个节点会向其他所有节点发送查询请求，得到所有节点的查询合并结果后，再执行本地的主查询，所有节点上的主查询完成后，再返回给最初的查询节点，组合后得到最终结果。

假如我们有一个100个节点的集群，在执行子查询的时候，每个节点都要向其他节点发送查询请求，所以需要 100*100 = 10000个请求，会消耗大量的资源。此时，我们就应该使用 GLOBAL IN：

SELECT uniq(UserID) FROM distributed_table 
WHERE CounterID = 101500 AND UserID GLOBAL IN (SELECT UserID FROM distributed_table WHERE CounterID = 34);

查询节点会首先执行子查询，向所有节点发送查询 CounterID = 34 的 UserID，然后合并所有结果后存入一个内存临时表 _data1。因为主查询也是一个分布式表，所以查询节点会再次向所有节点发送查询请求，并同时把 _data1 的数据发给所有节点，这样显然会比前面的方法节约资源。不过也有几点需要我们注意：

为了减少通过网络传输的数据量，建议在子查询中指定DISTINCT。
临时表将被发送到所有远程服务器，且传输不考虑网络拓扑结构。假如请求节点距离其他节点都很远，也会依次发送给所有节点，不会先发送到一个节点，然后基于这个节点发送给其他节点，所以使用 GLOBAL IN 尽量避免发送大数据量的情况，还可能会导致网络超载。
如果需要经常使用 GLOBAL IN，要提前规划后网络位置，尤其不要出现节点跨数据中心的情况。
GLOBAL IN 也可以在本地表中使用，例如可能这个本地表只存在于查询服务器上。
为了防止出现大量数据传输的情况，可以设置 max_rows_in_set 和 max_bytes_in_set，当超过这个设置值时，将根据 set_overflow_mode 的设置触发异常或者结束。

除了 GLOBAL IN 外，还有 GLOBAL JOIN 操作，原理基本一样。