ClickHouse SQL 篇

语法
SELECT [DISTINCT] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN …]
[GLOBAL] ANY|ALL INNER|LEFT JOIN (subquery)|table USING columns_list
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list]
[LIMIT [n, ]m]
[UNION ALL …]
[INTO OUTFILE filename]
[FORMAT format]
[LIMIT n BY columns]

Join字句
支持的 JOIN 类型
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
ASOF JOIN
ANY 与 ALL
在使用 ALL 修饰符对JOIN进行修饰时，如果右表中存在多个与左表关联的数据，那么系统则将右表中所有可以与左表关联的数据全部返回在结果中。这与SQL标准的JOIN行为相同。
在使用 ANY 修饰符对JOIN进行修饰时，如果右表中存在多个与左表关联的数据，那么系统仅返回第一个与左表匹配的结果。如果左表与右表一一对应，不存在多余的行时， ANY 与 ALL 的结果相同。

示列：
--建表

 drop  table jion_a;
 create table join_a(
   user_id UInt64 comment '用户ID',
   user_name String comment '用户名称',
   start_time DateTime comment '开始时间',
   end_time DateTime comment '截止时间'
 ) engine=Log;
 truncate  table join_a;
 insert into join_a
 (user_id ,user_name ,start_time,end_time)
 values (1, 'a', toDateTime('2017-09-20 00:00:00'), toDateTime('2018-09-20 00:00:00'))
        (1, 'a', toDateTime('2018-09-20 00:00:00'), toDateTime('2019-09-01 00:00:00')),
        (1, 'b', toDateTime('2019-09-01 00:00:00'), toDateTime('2020-09-01 00:00:00'));
 drop table join_b;
 create table join_b(
 user_id UInt64 comment '用户ID',
 pay_time DateTime comment '支付时间',
 pay_channel String comment '支付渠道',
 cost UInt64 comment '支付金额'
 ) engine = Log;
 insert into join_b(user_id, pay_time, pay_channel, cost)
      values (1, toDateTime('2022-09-25 00:00:00'), 'Vivo', 979797);

--关联
假设: 左边记录数为: n 右边为: m
select * from join_a;
select * from join_b;
---------------------------------- any join ----------------------------------
匹配右边表的第一条.
set any_join_distinct_right_table_keys=1;
select b., a.user_name, a.start_time, a.end_time from join_b b any inner join join_a a on b.user_id = a.user_id ;

---------------------------------- all join ----------------------------------

匹配右表所有行

. select b.,
a.user_name,
a.start_time,
a.end_time
from join_b b
all inner join join_a a on b.user_id = a.user_id
where b.pay_time >= a.start_time and b.pay_time < a.end_time;

---------------------------------- ASOF join ----------------------------------

条件关联:

select b.*, a.user_name, a.start_time, a.end_time from join_b b ASOF join join_a a on b.user_id = a.user_id and b.pay_time >= a.start_time;

---------------------------------- any join ----------------------------------
匹配右边表的第一条.
set any_join_distinct_right_table_keys=1;
select b., a.user_name, a.start_time, a.end_time from join_b b any inner join join_a a on b.user_id = a.user_id ;

---------------------------------- all join ----------------------------------

匹配右表所有行. select b.,
a.user_name,
a.start_time,
a.end_time
from join_b b
all inner join join_a a on b.user_id = a.user_id
where b.pay_time >= a.start_time and b.pay_time < a.end_time;

---------------------------------- ASOF join ----------------------------------

条件关联:

select b.*, a.user_name, a.start_time, a.end_time from join_b b ASOF join join_a a on b.user_id = a.user_id and b.pay_time >= a.start_time;