5.1 with
ClickHouse支持CTE(Common Table Expression,公共表表达式),以增强查询语句的表达
SELECT pow(2, 2) ┌─pow(2, 2)─┐ │ 4 │ └───────────┘ SELECT pow(pow(2, 2), 2) ┌─pow(pow(2, 2), 2)─┐ │ 16 │ └───────────────────┘
在改用CTE的形式后,可以极大地提高语句的可读性和可维护性,\
with pow(2,2) as a select pow(a,3) ;
1) 定义变量
WITH 1 AS start, 10 AS end SELECT id + start, * FROM tb_mysql ┌─plus(id, start)─┬─id─┬─name─┬─age─┐ │ 2 │ 1 │ zss │ 23 │ │ 3 │ 2 │ lss │ 33 │ │ 4 │ 3 │ ww │ 44 │ │ 2 │ 1 │ zss │ 23 │ │ 3 │ 2 │ lss │ 33 │ │ 2 │ 1 │ zss │ 23 │ │ 3 │ 2 │ lss │ 33 │ └─────────────────┴────┴──────┴─────┘
2) 调用函数
SELECT * FROM tb_partition ┌─id─┬─name─┬────────────birthday─┐ │ 1 │ xl │ 2021-05-20 10:50:46 │ │ 2 │ xy │ 2021-05-20 11:17:47 │ └────┴──────┴─────────────────────┘ ┌─id─┬─name─┬────────────birthday─┐ │ 3 │ xf │ 2021-05-19 11:11:12 │ └────┴──────┴───────────---------─┘ WITH toDate(birthday) AS bday SELECT id, name, bday FROM tb_partition ┌─id─┬─name─┬───────bday─┐ │ 1 │ xl │ 2021-05-20 │ │ 2 │ xy │ 2021-05-20 │ └────┴──────┴────────────┘ ┌─id─┬─name─┬───────bday─┐ │ 3 │ xf │ 2021-05-19 │ └────┴──────┴────────────┘
3) 子查询
可以定义子查询 ,但是一定还要注意的是,子查询只能返回一行结果 ,否则会跑出异常
WITH ( SELECT * FROM tb_partition WHERE id = 1 ) AS sub SELECT *, sub FROM tb_partition ┌─id─┬─name─┬────────────birthday─┬─sub────────────────────────────┐ │ 1 │ xl │ 2021-05-20 10:50:46 │ (1,'xl','2021-05-20 10:50:46') │ │ 2 │ xy │ 2021-05-20 11:17:47 │ (1,'xl','2021-05-20 10:50:46') │ └────┴──────┴─────────────────────┴────────────────────────────────┘ ┌─id─┬─name─┬────────────birthday─┬─sub────────────────────────────┐ │ 3 │ xf │ 2021-05-19 11:11:12 │ (1,'xl','2021-05-20 10:50:46') │ └────┴──────┴─────────────────────┴────────────────────────────────┘
5.2 from
SQL是一种面向集合的编程语言 ,from决定了程序从那里读取数据
-
表中查询数据
-
子查询中查询数据
-
表函数中查询数据 select * from numbers(3) ;
表函数
构建表的函数 , 使用场景如下:
SELECT查询的[FROM)子句。
创建表AS 查询。
1 file
file(path, format, structure) path — The relative path to the file from user_files_path. Path to file support following globs in readonly mode: *, ?, {abc,def} and {N..M} where N, M — numbers, `'abc', 'def' — strings. format — The format of the file. structure — Structure of the table. Format 'column1_name column1_type, column2_name column2_type, ...'.
数据文件必须在指定的目录下 /var/lib/clickhouse/user_files
SELECT * FROM file('demo.csv', 'CSV', 'id Int8,name String , age UInt8') -- 文件夹下任意的文件 SELECT * FROM file('*', 'CSV', 'id Int8,name String , age UInt8')
2 numbers
SELECT * FROM numbers(10) ; SELECT * FROM numbers(2, 10) ; SELECT * FROM numbers(10) limit 3 ; SELECT toDate('2020-01-01') + number AS d FROM numbers(365)
3 mysql
CH可以直接从mysql服务中查询数据
mysql('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']); SELECT * FROM mysql('linux01:3306', 'db_doit_ch', 'emp', 'root', 'root')
4 hdfs
SELECT *FROM hdfs('hdfs://hdfs1:9000/test', 'TSV', 'column1 UInt32, column2 UInt32, column3 UInt32')LIMIT 2 SELECT * FROM hdfs('hdfs://linux01:8020/demo.csv', 'CSV', 'id Int8 ,name String , age Int8')
5.3 array join
ARRAY JOIN子句允许在数据表的内部,与数组或嵌套类型的字段进行JOIN操作,从而将一行数组展开为多行。类似于hive中的explode炸裂函数的功能!
CREATE TABLE test_arrayjoin ( `name` String, `vs` Array(Int8) ) ENGINE = Memory ; insert into test_arrayjoin values('xw',[1,2,3]),('xl',[4,5]),('xk',[1]); -- 将数组中的数据展开 SELECT *, s FROM test_arrayjoin ARRAY JOIN vs AS s ┌─name─┬─vs──────┬─s─┐ │ xw │ [1,2,3] │ 1 │ │ xw │ [1,2,3] │ 2 │ │ xw │ [1,2,3] │ 3 │ │ xl │ [4,5] │ 4 │ │ xl │ [4,5] │ 5 │ │ xk │ [1] │ 1 │ └──────┴─────────┴───┘ -- arrayMap 高阶函数,对数组中的每个元素进行操作 SELECT *, arrayMap(x->x*2 , vs) vs2 FROM test_arrayjoin ; SELECT *, arrayMap(x -> (x * 2), vs) AS vs2 FROM test_arrayjoin ┌─name─┬─vs──────┬─vs2─────┐ │ xw │ [1,2,3] │ [2,4,6] │ │ xl │ [4,5] │ [8,10] │ │ xk │ [1] │ [2] │ └──────┴─────────┴─────────┘ SELECT *, arrayMap(x -> (x * 2), vs) AS vs2 , vv1 , vv2 FROM test_arrayjoin array join vs as vv1 , vs2 as vv2 ; ┌─name─┬─vs──────┬─vs2─────┬─vv1─┬─vv2─┐ │ xw │ [1,2,3] │ [2,4,6] │ 1 │ 2 │ │ xw │ [1,2,3] │ [2,4,6] │ 2 │ 4 │ │ xw │ [1,2,3] │ [2,4,6] │ 3 │ 6 │ │ xl │ [4,5] │ [8,10] │ 4 │ 8 │ │ xl │ [4,5] │ [8,10] │ 5 │ 10 │ │ xk │ [1] │ [2] │ 1 │ 2 │ └──────┴─────────┴─────────┴─────┴─────┘ select id , h , xx from tb_array_join array join hobby as h , arrayEnumerate(hobby) as xx ; ┌─id─┬─h─────┬─xx─┐ │ 1 │ eat │ 1 │ │ 1 │ drink │ 2 │ │ 1 │ sleep │ 3 │ │ 2 │ study │ 1 │ │ 2 │ sport │ 2 │ │ 2 │ read │ 3 │ └────┴───────┴────┘ ┌─id─┬─h─────┬─xx─┐ │ 3 │ eat │ 1 │ │ 3 │ drink │ 2 │
案例
a,2017-02-05,200 a,2017-02-06,300 a,2017-02-07,200 a,2017-02-08,400 a,2017-02-08,300 a,2017-02-10,600 b,2017-02-05,200 b,2017-02-06,300 b,2017-02-08,200 b,2017-02-09,400 b,2017-02-10,600 c,2017-01-31,200 c,2017-02-01,300 c,2017-02-02,200 c,2017-02-03,400 c,2017-02-10,600 a,2017-03-01,200 a,2017-03-02,300 a,2017-03-03,200 a,2017-03-04,400 a,2017-03-05,600 drop table if exists tb_shop ; CREATE TABLE tb_shop ( `name` String, `cdate` Date, `cost` Float64 )engine=ReplacingMergeTree(cdate) order by (name,cdate) ; -- 导入数据 clickhouse-client -q 'insert into doit23.tb_shop format CSV' < shop.txt ; ┌─name─┬──────cdate─┬─cost─┐ │ a │ 2017-02-05 │ 200 │ │ a │ 2017-02-06 │ 300 │ │ a │ 2017-02-07 │ 200 │ │ a │ 2017-02-08 │ 400 │ │ a │ 2017-02-10 │ 600 │ │ a │ 2017-03-01 │ 200 │ │ a │ 2017-03-02 │ 300 │ │ a │ 2017-03-03 │ 200 │ │ a │ 2017-03-04 │ 400 │ │ a │ 2017-03-05 │ 888 │ │ b │ 2017-02-05 │ 200 │ │ b │ 2017-02-06 │ 300 │ │ b │ 2017-02-08 │ 200 │ │ b │ 2017-02-09 │ 400 │ │ b │ 2017-02-10 │ 600 │ │ c │ 2017-01-31 │ 200 │ │ c │ 2017-02-01 │ 300 │ │ c │ 2017-02-02 │ 200 │ │ c │ 2017-02-03 │ 400 │ │ c │ 2017-02-10 │ 600 │ └──────┴────────────┴──────┘ select name , groupArray(cdate) arr , arrayEnumerate(arr) as indexs from tb_shop group by name; ┌─name─┬─arr─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─indexs─────────────────┐ │ b │ ['2017-02-05','2017-02-06','2017-02-08','2017-02-09','2017-02-10'] │ [1,2,3,4,5] │ │ c │ ['2017-01-31','2017-02-01','2017-02-02','2017-02-03','2017-02-10'] │ [1,2,3,4,5] │ │ a │ ['2017-02-05','2017-02-06','2017-02-07','2017-02-08','2017-02-10','2017-03-01','2017-03-02','2017-03-03','2017-03-04','2017-03-05'] │ [1,2,3,4,5,6,7,8,9,10] │ └──────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴────────────────────────┘ select name , dt - num from (select name , groupArray(cdate) arr , arrayEnumerate(arr) as indexs from tb_shop group by name ) array join arr as dt , indexs as num ; ┌─name─┬─minus(dt, num)─┐ │ b │ 2017-02-04 │ │ b │ 2017-02-04 │ │ b │ 2017-02-05 │ │ b │ 2017-02-05 │ │ b │ 2017-02-05 │ │ c │ 2017-01-30 │ │ c │ 2017-01-30 │ │ c │ 2017-01-30 │ │ c │ 2017-01-30 │ │ c │ 2017-02-05 │ │ a │ 2017-02-04 │ │ a │ 2017-02-04 │ │ a │ 2017-02-04 │ │ a │ 2017-02-04 │ │ a │ 2017-02-05 │ │ a │ 2017-02-23 │ │ a │ 2017-02-23 │ │ a │ 2017-02-23 │ │ a │ 2017-02-23 │ │ a │ 2017-02-23 │ └──────┴────────────────┘ select name , diff , count(1) cnt from (select name , (dt - num) as diff from (select name , groupArray(cdate) arr , arrayEnumerate(arr) as indexs from tb_shop group by name ) array join arr as dt , indexs as num ) group by name , diff; ┌─name─┬───────diff─┬─count(1)─┐ │ b │ 2017-02-04 │ 2 │ │ a │ 2017-02-23 │ 5 │ │ c │ 2017-01-30 │ 4 │ │ c │ 2017-02-05 │ 1 │ │ a │ 2017-02-04 │ 4 │ │ b │ 2017-02-05 │ 3 │ │ a │ 2017-02-05 │ 1 │ └──────┴────────────┴──────────┘ select name , diff , count(1) cnt from (select name , (dt - num) as diff from (select name , groupArray(cdate) arr , arrayEnumerate(arr) as indexs from tb_shop group by name ) array join arr as dt , indexs as num ) group by name , diff order by cnt desc limit 1 by name ; ┌─name─┬───────diff─┬─cnt─┐ │ a │ 2017-02-23 │ 5 │ │ c │ 2017-01-30 │ 4 │ │ b │ 2017-02-05 │ 3 │ └──────┴────────────┴─────┘
5.4 关联查询
所有标准 SQL JOIN 支持类型:
-
INNER JOIN, only matching rows are returned.
-
LEFT OUTER JOIN, non-matching rows from left table are returned in addition to matching rows.
-
RIGHT OUTER JOIN, non-matching rows from right table are returned in addition to matching rows.
-
FULL OUTER JOIN, non-matching rows from both tables are returned in addition to matching rows.
-
CROSS JOIN, produces cartesian product of whole tables, “join keys” are not specified.
JOIN子句可以对左右两张表的数据进行连接,这是最常用的查询子句之一。它的语法包含连接精度和连接类型两部分。
连接精度
连接精度决定了JOIN查询在连接数据时所使用的策略,目前支持ALL、ANY和ASOF三种类型。如果不主动声明,则默认是ALL。可以通过join_default_strictness配置参数修改默认的连接精度类型。
对数据是否连接匹配的判断是通过JOIN KEY进行的,目前只支持等式(EQUAL JOIN)。交叉连接(CROSS JOIN)不需要使用JOIN KEY,因为它会产生笛卡儿积。
-- 准备数据 drop table if exists yg ; create table yg( id Int8 , name String , age UInt8 , bid Int8 )engine=Log ; insert into yg values(1,'AA',23,1) , (2,'BB',24,2) , (3,'VV',27,1) , (4,'CC',13,3) , (5,'KK',53,3) , (6,'MM',33,3) ; drop table if exists bm ; create table bm( bid Int8 , name String )engine=Log ; insert into bm values(1,'x'),(2,'Y'),(3,'Z'); drop table if exists gz ; drop table gz ; create table gz( id Int8 , jb Int64 , jj Int64 )engine=Log ; insert into gz values (1,1000,2000),(1,1000,2000),(2,2000,1233),(3,2000,3000),(4,4000,1000),(5,5000,2000);
1)all
如果左表内的一行数据,在右表中有多行数据与之连接匹配,则返回右表中全部连接的数据。而判断连接匹配的依据是左表与右表内的数据,基于连接键(JOIN KEY)的取值完全相等(equal),等同于 left.key=right.key。
SELECT * FROM yg AS inser ALL INNER JOIN gz ON yg.id = gz.id ; SELECT * FROM yg AS inser ALL JOIN gz ON yg.id = gz.id ; SELECT * FROM yg AS inser JOIN gz ON yg.id = gz.id ; ┌─id─┬─name─┬─age─┬─bid─┬─gz.id─┬───jb─┬───jj─┐ │ 1 │ AA │ 23 │ 1 │ 1 │ 1000 │ 2000 │ │ 1 │ AA │ 23 │ 1 │ 1 │ 1000 │ 2000 │ │ 2 │ BB │ 24 │ 2 │ 2 │ 2000 │ 1233 │ │ 3 │ VV │ 27 │ 1 │ 3 │ 2000 │ 3000 │ │ 4 │ CC │ 13 │ 3 │ 4 │ 4000 │ 1000 │ │ 5 │ KK │ 53 │ 3 │ 5 │ 5000 │ 2000 │ └────┴──────┴─────┴─────┴───────┴──────┴──────┘
2)any
如果左表内的一行数据,在右表中有多行数据与之连接匹配,则仅返回右表中第一行连接的数据。ANY与ALL判断连接匹配的依据相同。
SELECT * FROM yg ANY INNER JOIN gz ON yg.id = gz.id ┌─id─┬─name─┬─age─┬─bid─┬─gz.id─┬───jb─┬───jj─┐ │ 1 │ AA │ 23 │ 1 │ 1 │ 1000 │ 2000 │ │ 2 │ BB │ 24 │ 2 │ 2 │ 2000 │ 1233 │ │ 3 │ VV │ 27 │ 1 │ 3 │ 2000 │ 3000 │ │ 4 │ CC │ 13 │ 3 │ 4 │ 4000 │ 1000 │ │ 5 │ KK │ 53 │ 3 │ 5 │ 5000 │ 2000 │ └────┴──────┴─────┴─────┴───────┴──────┴──────┘
3)asof
asof连接键之后追加定义一个模糊连接的匹配条件asof_column。
drop table if exists emp1 ; create table emp1( id Int8 , name String , ctime DateTime )engine=Log ; insert into emp1 values(1,'AA','2021-01-03 00:00:00'), (1,'AA','2021-01-02 00:00:00'), (2,'CC','2021-01-01 00:00:00'), (3,'DD','2021-01-01 00:00:00'), (4,'EE','2021-01-01 00:00:00'); drop table if exists emp2 ; create table emp2( id Int8 , name String , ctime DateTime )engine=Log ; insert into emp2 values(1,'aa','2021-01-02 00:00:00'), (1,'aa','2021-01-02 00:00:00'), (2,'cc','2021-01-01 00:00:00'), (3,'dd','2021-01-01 00:00:00'); -- ASOF inner join SELECT * FROM emp2 ASOF INNER JOIN emp1 ON (emp1.id = emp2.id) AND (emp1.ctime > emp2.ctime) ┌─id─┬─name─┬───────────────ctime─┬─emp1.id─┬─emp1.name─┬──────────emp1.ctime─┐ │ 1 │ aa │ 2021-01-02 00:00:00 │ 1 │ AA │ 2021-01-03 00:00:00 │ │ 1 │ aa │ 2021-01-02 00:00:00 │ 1 │ AA │ 2021-01-03 00:00:00 │ └────┴──────┴─────────────────────┴─────────┴───────────┴─────────────────────┘
5.5 with模型
-
With cube
-
With rollup
-
With totals
drop table is exists tb_with ; create table tb_with( id UInt8 , vist UInt8, province String , city String , area String )engine=MergeTree() order by id ; insert into tb_with values(1,12,'山东','济南','历下') ; insert into tb_with values(2,12,'山东','济南','历下') ; insert into tb_with values(3,12,'山东','济南','天桥') ; insert into tb_with values(4,12,'山东','济南','天桥') ; insert into tb_with values(5,88,'山东','青岛','黄岛') ; insert into tb_with values(6,88,'山东','青岛','黄岛') ; insert into tb_with values(7,12,'山西','太原','小店') ; insert into tb_with values(8,12,'山西','太原','小店') ; insert into tb_with values(9,112,'山西','太原','尖草坪') ; SELECT province, city, area, sum(vist) FROM tb_with GROUP BY province, city, area WITH CUBE ; ┌─province─┬─city─┬─area───┬─sum(vist)─┐ │ 山东 │ 青岛 │ 黄岛 │ 176 │ │ 山东 │ 济南 │ 天桥 │ 24 │ │ 山东 │ 太原 │ 尖草坪 │ 112 │ │ 山东 │ 济南 │ 历下 │ 24 │ │ 山西 │ 太原 │ 小店 │ 12 │ │ 山东 │ 太原 │ 小店 │ 12 │ └──────────┴──────┴────────┴───────────┘ ┌─province─┬─city─┬─area─┬─sum(vist)─┐ │ 山东 │ 青岛 │ │ 176 │ │ 山东 │ 济南 │ │ 48 │ │ 山西 │ 太原 │ │ 12 │ │ 山东 │ 太原 │ │ 124 │ └──────────┴──────┴──────┴───────────┘ ┌─province─┬─city─┬─area───┬─sum(vist)─┐ │ 山东 │ │ 历下 │ 24 │ │ 山东 │ │ 小店 │ 12 │ │ 山东 │ │ 天桥 │ 24 │ │ 山西 │ │ 小店 │ 12 │ │ 山东 │ │ 尖草坪 │ 112 │ │ 山东 │ │ 黄岛 │ 176 │ └──────────┴──────┴────────┴───────────┘ ┌─province─┬─city─┬─area─┬─sum(vist)─┐ │ 山西 │ │ │ 12 │ │ 山东 │ │ │ 348 │ └──────────┴──────┴──────┴───────────┘ ┌─province─┬─city─┬─area───┬─sum(vist)─┐ │ │ 济南 │ 历下 │ 24 │ │ │ 济南 │ 天桥 │ 24 │ │ │ 太原 │ 尖草坪 │ 112 │ │ │ 青岛 │ 黄岛 │ 176 │ │ │ 太原 │ 小店 │ 24 │ └──────────┴──────┴────────┴───────────┘ ┌─province─┬─city─┬─area─┬─sum(vist)─┐ │ │ 青岛 │ │ 176 │ │ │ 济南 │ │ 48 │ │ │ 太原 │ │ 136 │ └──────────┴──────┴──────┴───────────┘ ┌─province─┬─city─┬─area───┬─sum(vist)─┐ │ │ │ 天桥 │ 24 │ │ │ │ 小店 │ 24 │ │ │ │ 黄岛 │ 176 │ │ │ │ 历下 │ 24 │ │ │ │ 尖草坪 │ 112 │ └──────────┴──────┴────────┴───────────┘ ┌─province─┬─city─┬─area─┬─sum(vist)─┐ │ │ │ │ 360 │ └──────────┴──────┴──────┴───────────┘ SELECT province, city, area, sum(vist) FROM tb_with GROUP BY province, city, area WITH ROLLUP; ┌─province─┬─city─┬─area───┬─sum(vist)─┐ │ 山东 │ 青岛 │ 黄岛 │ 176 │ │ 山东 │ 济南 │ 天桥 │ 24 │ │ 山东 │ 太原 │ 尖草坪 │ 112 │ │ 山东 │ 济南 │ 历下 │ 24 │ │ 山西 │ 太原 │ 小店 │ 12 │ │ 山东 │ 太原 │ 小店 │ 12 │ └──────────┴──────┴────────┴───────────┘ ┌─province─┬─city─┬─area─┬─sum(vist)─┐ │ 山东 │ 青岛 │ │ 176 │ │ 山东 │ 济南 │ │ 48 │ │ 山西 │ 太原 │ │ 12 │ │ 山东 │ 太原 │ │ 124 │ └──────────┴──────┴──────┴───────────┘ ┌─province─┬─city─┬─area─┬─sum(vist)─┐ │ 山西 │ │ │ 12 │ │ 山东 │ │ │ 348 │ └──────────┴──────┴──────┴───────────┘ ┌─province─┬─city─┬─area─┬─sum(vist)─┐ │ │ │ │ 360 │ └──────────┴──────┴──────┴───────────┘ SELECT province, city, area, sum(vist) FROM tb_with GROUP BY province, city, area WITH TOTALS; ┌─province─┬─city─┬─area───┬─sum(vist)─┐ │ 山东 │ 青岛 │ 黄岛 │ 176 │ │ 山东 │ 济南 │ 天桥 │ 24 │ │ 山东 │ 太原 │ 尖草坪 │ 112 │ │ 山东 │ 济南 │ 历下 │ 24 │ │ 山西 │ 太原 │ 小店 │ 12 │ │ 山东 │ 太原 │ 小店 │ 12 │ └──────────┴──────┴────────┴───────────┘ Totals: ┌─province─┬─city─┬─area─┬─sum(vist)─┐ │ │ │ │ 360 │ └──────────┴──────┴──────┴───────────┘