【Clickhouse系列】clickhouse中的join

檀越剑指大厂

2729人浏览 · 2022-02-24 11:40:46

檀越剑指大厂 · 2022-02-24 11:40:46 发布

💝💝💝欢迎来到我的博客，很高兴能够在这里和您见面！希望您在这里可以感受到一份轻松愉快的氛围，不仅可以获得有趣的内容和知识，也可以畅所欲言、分享您的想法和见解。

推荐:kuan 的首页,持续学习,不断总结,共同进步,活到老学到老
导航
檀越剑指大厂系列:全面总结 java 核心技术点,如集合,jvm,并发编程 redis,kafka,Spring,微服务,Netty 等
常用开发工具系列:罗列常用的开发工具,如 IDEA,Mac,Alfred,electerm,Git,typora,apifox 等
数据库系列:详细总结了常用数据库 mysql 技术点,以及工作中遇到的 mysql 问题等
懒人运维系列:总结好用的命令,解放双手不香吗?能用一个命令完成绝不用两个操作
数据结构与算法系列:总结数据结构和算法,不同类型针对性训练,提升编程思维,剑指大厂

非常期待和您一起在这个小小的网络世界里共同探索、学习和成长。💝💝💝 ✨✨ 欢迎订阅本专栏 ✨✨

博客目录

基本语法

ClickHouse JOIN 查询语法如下：

SELECT <expr_list>FROM <left_table>[GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ASOF] JOIN <right_table>(ON <expr_list>)|(USING <column_list>) ...

从 right_table 读取该表全量数据，在内存中构建 HASH MAP；
从 left_table 分批读取数据，根据 JOIN KEY 到 HASH MAP 中进行查找，如果命中，则该数据作为 JOIN 的输出；

从这个实现中可以看出，如果 right_table 的数据量超过单机可用内存空间的限制，则 JOIN 操作无法完成。通常，两表 JOIN 时，将较小表作为 right_table.

创建表

CREATE TABLE default.tmp_pro_size_contribution_rate
(

    `product_key` String,

    `size_code` String,

    `sal_rank` UInt64,

    `total_sal_qty` Nullable(Int64),

    `total_sal_size_qty` Nullable(Int64),

    `contribution_rate` Nullable(Float64)
)
ENGINE = MergeTree
ORDER BY (product_key,
 size_code)
SETTINGS index_granularity = 8192;


CREATE TABLE default.tmp_pro_size_t12
(

    `product_key` String,

    `size_code` String,

    `name` String
)
ENGINE = MergeTree
ORDER BY (product_key,
 size_code)
SETTINGS index_granularity = 8192;

插入数据

INSERT INTO default.tmp_pro_size_contribution_rate (`product_key`, `size_code`, `sal_rank`, `total_sal_qty`,
                                                    `total_sal_size_qty`, `contribution_rate`)
VALUES ('1', '215', 99, 98, 97, 50);
INSERT INTO default.tmp_pro_size_contribution_rate (`product_key`, `size_code`, `sal_rank`, `total_sal_qty`,
                                                    `total_sal_size_qty`, `contribution_rate`)
VALUES ('1', '220', 99, 98, 97, 50);
INSERT INTO default.tmp_pro_size_contribution_rate (`product_key`, `size_code`, `sal_rank`, `total_sal_qty`,
                                                    `total_sal_size_qty`, `contribution_rate`)
VALUES ('1', '225', 99, 98, 97, 50);
INSERT INTO default.tmp_pro_size_contribution_rate (`product_key`, `size_code`, `sal_rank`, `total_sal_qty`,
                                                    `total_sal_size_qty`, `contribution_rate`)
VALUES ('1', '230', 99, 98, 97, 50);
INSERT INTO default.tmp_pro_size_contribution_rate (`product_key`, `size_code`, `sal_rank`, `total_sal_qty`,
                                                    `total_sal_size_qty`, `contribution_rate`)
VALUES ('1', '235', 99, 98, 97, 50);
INSERT INTO default.tmp_pro_size_contribution_rate (`product_key`, `size_code`, `sal_rank`, `total_sal_qty`,
                                                    `total_sal_size_qty`, `contribution_rate`)
VALUES ('1', '240', 99, 98, 97, 50);
INSERT INTO default.tmp_pro_size_contribution_rate (`product_key`, `size_code`, `sal_rank`, `total_sal_qty`,
                                                    `total_sal_size_qty`, `contribution_rate`)
VALUES ('1', '245', 99, 98, 97, 50);
INSERT INTO default.tmp_pro_size_contribution_rate (`product_key`, `size_code`, `sal_rank`, `total_sal_qty`,
                                                    `total_sal_size_qty`, `contribution_rate`)
VALUES ('1', '250', 99, 98, 97, 50);

INSERT INTO default.tmp_pro_size_t12
(product_key, size_code, name)
VALUES('1', '215', '1');
INSERT INTO default.tmp_pro_size_t12
(product_key, size_code, name)
VALUES('1', '215', '2');
INSERT INTO default.tmp_pro_size_t12
(product_key, size_code, name)
VALUES('1', '220', '');
INSERT INTO default.tmp_pro_size_t12
(product_key, size_code, name)
VALUES('1', '225', '');
INSERT INTO default.tmp_pro_size_t12
(product_key, size_code, name)
VALUES('1', '230', '');
INSERT INTO default.tmp_pro_size_t12
(product_key, size_code, name)
VALUES('1', '235', '');
INSERT INTO default.tmp_pro_size_t12
(product_key, size_code, name)
VALUES('1', '240', '');
INSERT INTO default.tmp_pro_size_t12
(product_key, size_code, name)
VALUES('1', '245', '');
INSERT INTO default.tmp_pro_size_t12
(product_key, size_code, name)
VALUES('1', '250', '');

不带 any 查询

select * from default.tmp_pro_size_contribution_rate t1 left join default.tmp_pro_size_t12 t2 on t1.size_code = t2.size_code;

带 any 查询

select * from default.tmp_pro_size_contribution_rate t1 left any join 	default.tmp_pro_size_t12 t2 on  t1.size_code=t2.size_code  where size_code ='215';

总结

消除笛卡尔乘积最根本的原因不是在于连接，而是在于唯一 ID，就像学号，一个学生就只有一个学号，学号就是这个学生的唯一标识码。
左连接只是以左边的表为基准，左边的 ID 和右边 ID 都是唯一，就不会产生笛卡尔现象，如果右边有两个 ID 对应左边一个 ID，就算你是左连接，一样会产生 1 对多的现象。

使用 any 可以消除笛卡尔积

觉得有用的话点个赞 👍🏻 呗。