|NO.Z.00039|——————————|BigDataEnd|——|Hadoop&OLAP_ClickHouse.V11|——|ClickHouse.v11|ClickHouse语法|Join子句|

一、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 │
│ student │ [1,2,3] │ 3 │
│ teacher │ [4,5]   │ 4 │
│ teacher │ [4,5]   │ 5 │
│ suguan  │ []      │ 0 │
└─────────┴─────────┴───┘
二、JOIN三张表：
### --- 创建表并加载数据：join_tb1

~~~     # 创建表
hadoop01 :) create table join_tb1 (id UInt8,name String,time DateTime) engine=Log;
~~~     # 加载数据
hadoop01 :) insert into join_tb1 values(1,'ClickHouse','2021-02-01 12:00:00');
hadoop01 :) insert into join_tb1 values(2,'Spark','2021-02-01 12:30:00');
hadoop01 :) insert into join_tb1 values(3,'ElasticSearch','2021-02-01 13:00:00');
hadoop01 :) insert into join_tb1 values(4,'HBase','2021-02-01 13:30:00');
hadoop01 :) insert into join_tb1 (name,time)values('ClickHouse','2021-02-01 12:00:00');
hadoop01 :) insert into join_tb1 (name,time)values('Spark','2021-02-01 12:00:00');
### --- 创建表并加载数据：join_tb2

~~~     # 创建表
hadoop01 :) create table join_tb2 (id UInt8, rate UInt8, time DateTime) engine=Log;
~~~     # 加载数据
hadoop01 :) insert into join_tb2 values(1,100,'2021-05-02 10:00:00');
hadoop01 :) insert into join_tb2 values(2,90,'2021-05-02 10:01:00');
hadoop01 :) insert into join_tb2 values(3,80,'2021-05-02 10:02:00');
hadoop01 :) insert into join_tb2 values(5,70,'2021-05-02 10:03:00');
hadoop01 :) insert into join_tb2 values(6,60,'2021-05-02 10:04:00');
### --- 创建表并加载数据：join_tb3

~~~     # 创建表
hadoop01 :) create table join_tb3 (id UInt8, star UInt8) engine=Log;
~~~     # 加载数据
hadoop01 :) insert into join_tb3 values(1,1000);
hadoop01 :) insert into join_tb3 values(2,900);
### --- 查询数据

~~~     # 查询join_tb1表中的数据：
hadoop01 :) SELECT * FROM join_tb1;

┌─id─┬─name──────────┬────────────────time─┐
│  1 │ ClickHouse    │ 2021-02-01 12:00:00 │
│  2 │ Spark         │ 2021-02-01 12:30:00 │
│  3 │ ElasticSearch │ 2021-02-01 13:00:00 │
│  4 │ HBase         │ 2021-02-01 13:30:00 │
│  0 │ ClickHouse    │ 2021-02-01 12:00:00 │
│  0 │ Spark         │ 2021-02-01 12:00:00 │
└────┴───────────────┴─────────────────────┘
~~~     # 查询join_tb2表中的数据

hadoop01 :) SELECT * FROM join_tb2;

┌─id─┬─rate─┬────────────────time─┐
│  1 │  100 │ 2021-05-02 10:00:00 │
│  2 │   90 │ 2021-05-02 10:01:00 │
│  3 │   80 │ 2021-05-02 10:02:00 │
│  5 │   70 │ 2021-05-02 10:03:00 │
│  6 │   60 │ 2021-05-02 10:04:00 │
└────┴──────┴─────────────────────┘
~~~     # 查询join_tb3表中的数据

hadoop01 :) SELECT * FROM join_tb3;

┌─id─┬─star─┐
│  1 │  232 │
│  2 │  132 │
└────┴──────┘
### --- 查询：ALL

hadoop01 :) select a.id,a.name,
            b.rate from join_tb1 
            as a all inner 
            join join_tb2 as 
            b on a.id=b.id;
~~~输出参数
┌─id─┬─name──────────┬─rate─┐
│  1 │ ClickHouse    │  100 │
│  2 │ Spark         │   90 │
│  3 │ ElasticSearch │   80 │
└────┴───────────────┴──────┘
### --- ANY

hadoop01 :) select a.id,a.name,
            b.rate from join_tb1 
            as a any inner 
            join join_tb2 as b 
            on a.id=b.id;
~~~输出参数：
┌─id─┬─name──────────┬─rate─┐
│  1 │ ClickHouse    │  100 │
│  2 │ Spark         │   90 │
│  3 │ ElasticSearch │   80 │
└────┴───────────────┴──────┘
### --- ASOF

hadoop01 :) select a.id,a.name,
            b.rate,a.time,b.time 
            from join_tb1 as a 
            any inner join join_tb2
            as b on a.id=b.id;
~~~输出参数：
ANY INNER JOIN join_tb2 AS b ON a.id = b.id
┌─id─┬─name──────────┬─rate─┬────────────────time─┬──────────────b.time─┐
│  1 │ ClickHouse    │  100 │ 2021-02-01 12:00:00 │ 2021-05-02 10:00:00 │
│  2 │ Spark         │   90 │ 2021-02-01 12:30:00 │ 2021-05-02 10:01:00 │
│  3 │ ElasticSearch │   80 │ 2021-02-01 13:00:00 │ 2021-05-02 10:02:00 │
└
────┴───────────────┴──────┴─────────────────────┴─────────────────────┘
hadoop01 :) select a.id,a.name,
            b.rate,a.time,b.time 
            from join_tb1 as a asof 
            inner join join_tb2
            as b on a.id=b.id 
            and a.time <= b.time;
~~~输出参数：
ASOF INNER JOIN join_tb2 AS b ON (a.id = b.id) AND (a.time <= b.time)
┌─id─┬─name──────────┬─rate─┬────────────────time─┬──────────────b.time─┐
│  1 │ ClickHouse    │  100 │ 2021-02-01 12:00:00 │ 2021-05-02 10:00:00 │
│  2 │ Spark         │   90 │ 2021-02-01 12:30:00 │ 2021-05-02 10:01:00 │
│  3 │ ElasticSearch │   80 │ 2021-02-01 13:00:00 │ 2021-05-02 10:02:00 │
└────┴───────────────┴──────┴─────────────────────┴─────────────────────┘
三、Join：self-join
### --- self-join

~~~     Join通过使用一个或多个表的公共值合并来自一个或多个表的列来生成新表。 
~~~     它是支持SQL的数据库中的常见操作，
~~~     它对应于 关系代数 加入。 一个表连接的特殊情况通常被称为 “self-join”.
### --- 语法:
~~~     从表达式 ON 从子句和列 USING 子句被称为 “join keys”. 除非另有说明，
~~~     加入产生一个 笛卡尔积 从具有匹配的行“join keys”，这可能会产生比源表更多的行的结果。

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>) ...
### --- 支持的联接类型：所有标准 SQL JOIN 支持类型：

~~~     INNER JOIN，只返回匹配的行。
~~~     LEFT OUTER JOIN，除了匹配的行之外，还返回左表中的非匹配行。
~~~     RIGHT OUTER JOIN，除了匹配的行之外，还返回右表中的非匹配行。
~~~     FULL OUTER JOIN，除了匹配的行之外，还会返回两个表中的非匹配行。
~~~     CROSS JOIN，产生整个表的笛卡尔积, “join keys” 是 不 指定。
~~~     JOIN 没有指定类型暗指 INNER. 关键字 OUTER 可以安全地省略。 
~~~     替代语法 CROSS JOIN 在指定多个表 FROM 用逗号分隔。
### --- ClickHouse中提供的其他联接类型:
~~~     LEFT SEMI JOIN 和 RIGHT SEMI JOIN,白名单 “join keys”，而不产生笛卡尔积。
~~~     LEFT ANTI JOIN 和 RIGHT ANTI JOIN，黑名单 “join keys”，而不产生笛卡尔积。
~~~     LEFT ANY JOIN, RIGHT ANY JOIN and INNER ANY JOIN, 
~~~     partially (for opposite side of LEFT and RIGHT) 
~~~     orcompletely (for INNER and FULL) disables the cartesian product for standard JOIN types.
~~~     ASOF JOIN and LEFT ASOF JOIN, joining sequences with a non-exact match. 
~~~     ASOF JOIN usage is describedbelow.

~~~     # 严格注
~~~     可以使用以下方式复盖默认的严格性值 join_default_strictness 设置。
~~~     Also the behavior of ClickHouse server for ANY 
~~~     JOIN operations depends on the any_join_distinct_right_table_keys setting.
### --- ASOF加入使用

~~~     ASOF JOIN 当您需要连接没有完全匹配的记录时非常有用。
~~~     算法需要表中的特殊列。 本专栏:
~~~     必须包含有序序列。
~~~     可以是以下类型之一: Int，UInt, 浮动, 日期, 日期时间, 十进制.
~~~     不能是唯一的列 JOIN
~~~     语法 ASOF JOIN ... ON:
SELECT expressions_list
FROM table_1
ASOF LEFT JOIN table_2
ON equi_cond AND closest_match_cond
~~~     您可以使用任意数量的相等条件和恰好一个最接近的匹配条件。 
~~~     例如, SELECT count() FROM table_1 ASOF LEFT JOIN table_2 ON 
~~~     table_1.a == table_2.b AND table_2.t <= table_1.t.
~~~     支持最接近匹配的条件: >, >=, <, <=.
~~~     语法 ASOF JOIN ... USING:
SELECT expressions_list
FROM table_1
ASOF JOIN table_2
USING (equi_column1, ... equi_columnN, asof_column)
~~~     ASOF JOIN 用途 equi_columnX 对于加入平等和 asof_column 
~~~     用于加入与最接近的比赛 table_1.asof_column >=table_2.asof_column 条件。 
~~~     该 asof_column 列总是在最后一个 USING 条款
### --- 例如，请考虑下表:

~~~     ASOF JOIN 可以从用户事件的时间戳 table_1 
~~~     并找到一个事件 table_2 其中时间戳最接近事件的时间戳 table_1 对应于最接近的匹配条件。 
~~~     如果可用，则相等的时间戳值是最接近的值。 
~~~     在这里，该 user_id 列可用于连接相等和ev_time 列可用于在最接近的匹配加入。
~~~     在我们的例子中, event_1_1 可以加入 event_2_1 和 event_1_2 可以加入event_2_3，
~~~     但是 event_2_2 不能加入。
~~~     注：ASOF 加入是 不 支持在 加入我们 表引擎。
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 ... ...
### --- 分布式联接：有两种方法可以执行涉及分布式表的join:

~~~     当使用正常 JOIN，将查询发送到远程服务器。 为了创建正确的表，
~~~     在每个子查询上运行子查询，并使用此表执行联接。 换句话说，在每个服务器上单独形成右表。
~~~     使用时 GLOBAL ... JOIN，首先请求者服务器运行一个子查询来计算正确的表。 
~~~     此临时表将传递到每个远程服务器，并使用传输的临时数据对其运行查询。
~~~     使用时要小心 GLOBAL. 有关详细信息，请参阅 分布式子查询 科。
~~~     # 使用建议
~~~     处理空单元格或空单元格
~~~     在连接表时，可能会出现空单元格。 设置 join_use_nulls 定义ClickHouse如何填充这些单元格。
~~~     如果 JOIN 键是 可为空 字段，其中至少有一个键具有值的行 NULL 没有加入。

~~~     # 语法
~~~     在指定的列 USING 两个子查询中必须具有相同的名称，并且其他列必须以不同的方式命名。 
~~~     您可以使用别名更改子查询中的列名。
~~~     该 USING 子句指定一个或多个要联接的列，这将建立这些列的相等性。 
~~~     列的列表设置不带括号。 不支持更复杂的连接条件。
~~~     # 语法限制对于多个 JOIN 单个子句 SELECT 查询:
~~~     通过以所有列 * 仅在联接表时才可用，而不是子查询。
~~~     该 PREWHERE 条款不可用。

~~~     # 为 ON, WHERE，和 GROUP BY 条款:
~~~     任意表达式不能用于 ON, WHERE，和 GROUP BY 子句，
~~~     但你可以定义一个表达式 SELECT 子句，然后通过别名在这些子句中使用它。
~~~     # 性能
~~~     当运行 JOIN，与查询的其他阶段相关的执行顺序没有优化。 
~~~     连接（在右表中搜索）在过滤之前运行 WHERE 和聚集之前。
~~~     每次使用相同的查询运行 JOIN，子查询再次运行，因为结果未缓存。 
~~~     为了避免这种情况，使用特殊的 加入我们 表引擎，
~~~     它是一个用于连接的准备好的数组，总是在RAM中。

~~~     # 在某些情况下，使用效率更高 IN 而不是 JOIN.
~~~     如果你需要一个 JOIN 对于连接维度表（这些是包含维度属性的相对较小的表，
~~~     例如广告活动的名称）， JOIN 由于每个查询都会重新访问正确的表，因此可能不太方便。 
~~~     对于这种情况下，有一个 “external dictionaries” 您应该使用的功能 JOIN. 有关详细信息，
~~~     请参阅 外部字典 科。
~~~     # 内存限制
~~~     默认情况下，ClickHouse使用 哈希联接 算法。 ClickHouse采取 `` 并在RAM中为其创建哈希表。 
~~~     在某个内存消耗阈值之后，ClickHouse回退到合并联接算法。

~~~     # 如果需要限制联接操作内存消耗，请使用以下设置:
~~~     max_rows_in_join — Limits number of rows in the hash table.
~~~     max_bytes_in_join — Limits size of the hash table.
~~~     当任何这些限制达到，ClickHouse作为 join_overflow_mode 设置指示。
### --- 例子示例:

hadoop01 :) SELECT CounterID, hits, visits FROM (
            SELECT CounterID, count() AS hits FROM test.hits
            GROUP BY CounterID ) ANY LEFT JOIN (
            SELECT CounterID, sum(Sign) AS visits FROM test.visits
            GROUP BY CounterID ) USING CounterID ORDER BY hits DESC
            LIMIT 10;
~~~输出参数
┌─CounterID─┬───hits─┬─visits─┐
│ 1143050   │ 523264 │ 13665  │
│ 731962    │ 475698 │ 102716 │
│ 722545    │ 337212 │ 108187 │
│ 722889    │ 252197 │ 10547  │
│ 2237260   │ 196036 │ 9522   │
│ 23057320  │ 147211 │ 7689   │
│ 722818    │ 90109  │ 17847  │
│ 48221     │ 85379  │ 4652   │
│ 19762435  │ 77807  │ 7026   │
│ 722884    │ 77492  │ 11056  │
└───────────┴────────┴────────┘
### --- 拆分分析：

hadoop01 :) select CounterID as id, count() as hits from hits_v1 group by id limit 10;
~~~输出参数
┌───────id─┬─hits─┐
│ 24141121 │ 4    │
│ 31790479 │ 2    │
│ 17424666 │ 1    │
│ 29229146 │ 1    │
│ 8325557  │ 2    │
│ 2314381  │ 26   │
│ 899083   │ 2    │
│ 19151207 │ 96   │
│ 31138002 │ 7    │
│ 129995   │ 4    │
└──────────┴──────┘
hadoop01 :) select CounterID as id,sum(Sign) as visits from visits_v1 group by id limit 10;
~~~输出参数
┌───────id─┬─visits─┐
│ 1602763  │ 10     │
│ 205593   │ 1      │
│ 406018   │ 1      │
│ 1398990  │ 1      │
│ 17891807 │ 1      │
│ 24614464 │ 1      │
│ 14339903 │ 3      │
│ 9991977  │ 5      │
│ 31934585 │ 1      │
│ 15525311 │ 3      │
└──────────┴────────┘
~~~     # LEFT JOIN 各种join

hadoop01 :) select * from (select CounterID as id, 
            count() as hits from hits_v1 group by id
            limit 10) as a left join (select CounterID as id,sum(Sign) 
            as visits from visits_v1 group by id limit 10) as b on a.id = b.id;
~~~输出参数
┌───────id─┬─hits─┬─b.id─┬─visits─┐
│ 24141121 │ 4    │ 0    │ 0      │
│ 31790479 │ 2    │ 0    │ 0      │
│ 17424666 │ 1    │ 0    │ 0      │
│ 29229146 │ 1    │ 0    │ 0      │
│ 8325557  │ 2    │ 0    │ 0      │
│ 2314381  │ 26   │ 0    │ 0      │
│ 899083   │ 2    │ 0    │ 0      │
│ 19151207 │ 96   │ 0    │ 0      │
│ 31138002 │ 7    │ 0    │ 0      │
│ 129995   │ 4    │ 0    │ 0      │
└──────────┴──────┴──────┴────────┘
 
 
 
 
 
 
 
 
 
Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart
                                                                                                                                                   ——W.S.Landor
 

 

posted on   yanqi_vip  阅读(19)  评论(0)  编辑  收藏  举报

相关博文：
· |NO.Z.00005|——————————|BigDataEnd|——|Hadoop&OLAP_ClickHouse.V02|——|ClickHouse.v02|表引擎日志|Memory|Merge|
· |NO.Z.00035|——————————|BigDataEnd|——|Hadoop&OLAP_ClickHouse.V07|——|ClickHouse.v07|ClickHouse语法|CreateInsertAlter|DescribeCheck|
· ClickHouse-4SQL参考
· clickHouse常用语法升阶版(JOIN/WHERE/PREWHERE/GROUP BY等)
· ClickHouse 中的数据查询以及各种子句
阅读排行：
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· 上周热点回顾（2.24-3.2）
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5

导航

统计

点击右上角即可分享
微信分享提示