五 ClickHouse查询语法

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决定了程序从那里读取数据

  1. 表中查询数据

  2. 子查询中查询数据

  3. 表函数中查询数据 select * from numbers(3) ;

表函数

构建表的函数 , 使用场景如下:

SELECT查询的[FROM)子句。

创建表AS 查询。

 五 ClickHouse查询语法

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')

五 ClickHouse查询语法

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')

五 ClickHouse查询语法

 

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子句可以对左右两张表的数据进行连接,这是最常用的查询子句之一。它的语法包含连接精度和连接类型两部分。

 五 ClickHouse查询语法

 

 

连接精度

连接精度决定了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 │
└──────────┴──────┴──────┴───────────┘ 

 

上一篇:ClickHouse-1(简介)


下一篇:大数据之ClickHouse极具战斗性的数据库-未来可期