关于Clickhouse 的子查询
Clkhouse 支持非关联主表的子查询语句,如下
SELECT `number` AS number1,
(SELECT number FROM numbers(3) WHERE number=2) AS number2 ,
(SELECT number FROM numbers(3) WHERE number=5) AS number3
FROM test2 a LIMIT 10;
number1 | number2 | number3 |
---|---|---|
0 | 2 | |
1 | 2 | |
2 | 2 | |
3 | 2 | |
4 | 2 | |
5 | 2 | |
6 | 2 | |
7 | 2 | |
8 | 2 | |
9 | 2 |
不支持像Oracle 一样的 两表需要关联查询的子查询语句。比如
但是,Clikhouse可以通过Join引擎来实现此类关联子查询。
Join 引擎(关联表引擎)
一般语法
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 = Join(join_strictness, join_type, k1[, k2, ...])
参见官方文档:https://clickhouse.com/docs/zh/engines/table-engines/special/join
使用该引擎,即可实现关联子查询。举例如下:
SELECT *FROM test1 LIMIT 10;
number | number1 | number2 |
---|---|---|
0 | 1 | 0 |
1 | 2 | 2 |
2 | 3 | 4 |
3 | 4 | 6 |
4 | 5 | 8 |
5 | 6 | 10 |
6 | 7 | 12 |
7 | 8 | 14 |
8 | 9 | 16 |
9 | 10 | 18 |
CREATE TABLE test1_joinner engine=Join(ANY,LEFT,number)
AS
SELECT number,number1,number2
FROM test1;
SELECT `number` AS number
,joinGet('test1_joinner','number1',a.number) AS number1
,joinGet('test1_joinner','number2',a.number) AS number2
FROM test2 a LIMIT 10;
number | number1 | number2 |
---|---|---|
0 | 1 | 0 |
1 | 2 | 2 |
2 | 3 | 4 |
3 | 4 | 6 |
4 | 5 | 8 |
5 | 6 | 10 |
6 | 7 | 12 |
7 | 8 | 14 |
8 | 9 | 16 |
9 | 10 | 18 |
关于Join引擎和left join语句的效率问题
在我的实际工作过程中,400万的数据,使用join引擎子查询和left join 语句查询,耗费的时间完全一样。
测试语句较为简单,仅是两个表的左外关联。