CC00039.clickhouse——|Hadoop&OLAP_ClickHouse.V11|——|ClickHouse.v11|ClickHouse语法|Join|

yanqi_vip

已于 2022-04-15 19:33:26 修改

阅读量132

点赞数

分类专栏： bigdatav021——olapclickHouse 文章标签： mysql java python 数据库 oracle

于 2022-04-14 14:15:00 首次发布

不予转载

本文链接：https://blog.csdn.net/yanqi_vip/article/details/124186314

版权

本文主要探讨了ClickHouse中的JOIN子句，包括如何使用JOIN连接三张表以及自连接(self-join)的概念，旨在理解ClickHouse在大数据分析场景下的查询语法。

摘要由CSDN通过智能技术生成

一、JOIN子句

### --- array join

~~~     # 创建数据表
hadoop01 :) use default;

hadoop01 :) create table query_v1 (title String, value Array(UInt8))engine=Log;
hadoop01 :) show tables;
┌─name─────────────┐
│ query_v1         │
└──────────────────┘

~~~     # 插入数据

hadoop01 :) insert into query_v1 values(
            'student',[1,2,3]),('teacher',
            [4,5]),('suguan',[]);

~~~     # 查询方式一：

hadoop01 :) select * from query_v1;

┌─title───┬─value───┐
│ student │ [1,2,3] │
│ teacher │ [4,5]   │
│ suguan  │ []      │
└─────────┴─────────┘

~~~     # 查询方式二：

hadoop01 :) select title,value from query_v1 array join value;

┌─title───┬─value─┐
│ student │     1 │
│ student │     2 │
│ student │     3 │
│ teacher │     4 │
│ teacher │     5 │
└─────────┴───────┘

~~~     # 查询方式三：

hadoop01 :) select title,value,v from query_v1 array join value as v;

┌─title───┬─value───┬─v─┐
│ student │ [1,2,3] │ 1 │
│ student │ [1,2,3] │ 2 │
│ student │ [1,2,3] │ 3 │
│ teacher │ [4,5]   │ 4 │
│ teacher │ [4,5]   │ 5 │
└─────────┴─────────┴───┘

### --- arrayMap
~~~     # arrayMap(func, arr1, …)
~~~     # Returns an array obtained from the original application of the func function to each element in the arr array.
~~~     # Examples:

hadoop01 :) SELECT arrayMap(x -> (x + 2), [1, 2, 3]) as res;

┌─res─────┐
│ [3,4,5] │
└─────────┘

~~~     # The following example shows how to create a tuple of elements from different arrays:

hadoop01 :) SELECT arrayMap((x, y) -> (x, y), [1, 2, 3], [4, 5, 6]) AS res;

┌─res─────────────────┐
│ [(1,4),(2,5),(3,6)] │
└─────────────────────┘

~~~     # Note that the arrayMap is a higher-order function. You must pass a lambda function to it as the first argument, and it can’t be omitted.

hadoop01 :) select arrayMap(x->x*2,value) from query_v1;

┌─arrayMap(lambda(tuple(x), multiply(x, 2)), value)─┐
│ [2,4,6]                                           │
│ [8,10]                                            │
│ []                                                │
└───────────────────────────────────────────────────┘

hadoop01 :) select title,value,v,
            arrayMap(x->x*2,value) 
            as mapv,v_1 from query_v1 left array
            join value as v,mapv as v_1;
~~~输出参数
┌─title───┬─value───┬─v─┬─mapv────┬─v_1─┐
│ student │ [1,2,3] │ 1 │ [2,4,6] │   2 │
│ student │ [1,2,3] │ 2 │ [2,4,6] │   4 │
│ student │ [1,2,3] │ 3 │ [2,4,6] │   6 │
│ teacher │ [4,5]   │ 4 │ [8,10]  │   8 │
│ teacher │ [4,5]   │ 5 │ [8,10]  │  10 │
│ suguan  │ []      │ 0 │ []      │   0 │
└─────────┴─────────┴───┴─────────┴─────┘

hadoop01 :) select title,value,v from query_v1 left array join value as v;

┌─title───┬─value───┬─v─┐
│ student │ [1,2,3] │ 1 │
│ student │ [1,2,3] │ 2