clickhouse查询语法--withwith

60 阅读 0 评论 40 点赞

我是靠谱客的博主活力战斗机，最近开发中收集的这篇文章主要介绍clickhouse查询语法--withwith，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

with

clickhouse支持CTE（Common Table Expression，公共表达式），以增强查询语句的表达

select pow(2,3);
//pow是计算次方值函数
┌─pow(2, 3)─┐
│
8 │
└───────────┘
select pow(pow(2,2),2);
┌─pow(pow(2, 2), 2)─┐
│
16 │
└───────────────────┘

改用CTE的形式后，可以极大地提高语句的可读性和可维护性。

with pow(2,2) as res select pow(res,2);
┌─pow(res, 2)─┐
│
16 │
└─────────────┘

1）定义变量

with 1 as num select num+1;
┌─plus(num, 1)─┐
│
2 │
└──────────────┘
select * from
test_str;
┌─name─┬─job─┐
│ cxy
│ it
│
└──────┴─────┘
┌─name─────┬─job─┐
│ zhangsan │ DBA │
└──────────┴─────┘
with 'cxy' as n select * from test_str where name=n;
┌─name─┬─job─┐
│ cxy
│ it
│
└──────┴─────┘
with 'cxy' as a,'zhangsan' as b select * from test_str where name=a or name=b;
┌─name─┬─job─┐
│ cxy
│ it
│
└──────┴─────┘
┌─name─────┬─job─┐
│ zhangsan │ DBA │
└──────────┴─────┘

2）调用函数

select toYear(now());
┌─toYear(now())─┐
│
2021 │
└───────────────┘
with toYear(now()) as year select year;
┌─year─┐
│ 2021 │
└──────┘

3）子查询

可以定义子查询，但是一定还要注意的是，子查询只能返回一行结果，否则会输出异常,子查询返回的是元组类型的结果

with
(select * from tb_merge_tree where uid=1)
as res select res from tb_merge_tree;
//报错，因为子查询返回多行结果
with
(select * from tb_merge_tree_partition where uid=1)
as res select uid,name,res from tb_merge_tree_partition;
┌─uid─┬─name─┬─res────────────────────────────┐
│
3 │ ww
│ (1,'zs','2020-09-27 10:00:00') │
│
4 │ zl
│ (1,'zs','2020-09-27 10:00:00') │
│
13 │ www
│ (1,'zs','2020-09-27 10:00:00') │
│
14 │ zll
│ (1,'zs','2020-09-27 10:00:00') │
└─────┴──────┴────────────────────────────────┘
┌─uid─┬─name─┬─res────────────────────────────┐
│
1 │ zs
│ (1,'zs','2020-09-27 10:00:00') │
│
2 │ ls
│ (1,'zs','2020-09-27 10:00:00') │
│
11 │ zss
│ (1,'zs','2020-09-27 10:00:00') │
│
12 │ lss
│ (1,'zs','2020-09-27 10:00:00') │
└─────┴──────┴────────────────────────────────┘

4）with模型

create table tb_with(
id UInt8,
visit 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 * from tb_with;
┌─id─┬─visit─┬─province─┬─city─┬─area───┐
│
1 │
12 │ 山东
│ 济南 │ 历下
│
│
2 │
12 │ 山东
│ 济南 │ 历下
│
│
3 │
12 │ 山东
│ 济南 │ 天桥
│
│
4 │
12 │ 山东
│ 济南 │ 天桥
│
│
5 │
88 │ 山东
│ 青岛 │ 黄岛
│
│
6 │
88 │ 山东
│ 青岛 │ 黄岛
│
│
7 │
12 │ 山西
│ 太原 │ 小店
│
│
8 │
12 │ 山西
│ 太原 │ 小店
│
│
9 │
112 │ 山西
│ 太原 │ 尖草坪
│
└────┴───────┴──────────┴──────┴────────┘
select province,city,area,sum(visit) from tb_with group by province,city,area;
┌─province─┬─city─┬─area───┬─sum(visit)─┐
│ 山东
│ 青岛 │ 黄岛
│
176
│
│ 山东
│ 济南 │ 天桥
│
24
│
│ 山西
│ 太原 │ 尖草坪 │
112
│
│ 山东
│ 济南 │ 历下
│
24
│
│ 山西
│ 太原 │ 小店
│
24
│
└──────────┴──────┴────────┴────────────┘
select province,city,area,sum(visit) from tb_with group by province,city,area with CUBE;
┌─province─┬─city─┬─area───┬─sum(visit)─┐
│ 山东
│ 青岛 │ 黄岛
│
176 │
│ 山东
│ 济南 │ 天桥
│
24 │
│ 山西
│ 太原 │ 尖草坪 │
112 │
│ 山东
│ 济南 │ 历下
│
24 │
│ 山西
│ 太原 │ 小店
│
24 │
└──────────┴──────┴────────┴────────────┘
┌─province─┬─city─┬─area─┬─sum(visit)─┐
│ 山东
│ 青岛 │
│
176 │
│ 山东
│ 济南 │
│
48 │
│ 山西
│ 太原 │
│
136 │
└──────────┴──────┴──────┴────────────┘
┌─province─┬─city─┬─area───┬─sum(visit)─┐
│ 山东
│
│ 历下
│
24 │
│ 山东
│
│ 天桥
│
24 │
│ 山西
│
│ 尖草坪 │
112 │
│ 山西
│
│ 小店
│
24 │
│ 山东
│
│ 黄岛
│
176 │
└──────────┴──────┴────────┴────────────┘
┌─province─┬─city─┬─area─┬─sum(visit)─┐
│ 山西
│
│
│
136 │
│ 山东
│
│
│
224 │
└──────────┴──────┴──────┴────────────┘
┌─province─┬─city─┬─area───┬─sum(visit)─┐
│
│ 济南 │ 历下
│
24 │
│
│ 济南 │ 天桥
│
24 │
│
│ 太原 │ 尖草坪 │
112 │
│
│ 青岛 │ 黄岛
│
176 │
│
│ 太原 │ 小店
│
24 │
└──────────┴──────┴────────┴────────────┘
┌─province─┬─city─┬─area─┬─sum(visit)─┐
│
│ 青岛 │
│
176 │
│
│ 济南 │
│
48 │
│
│ 太原 │
│
136 │
└──────────┴──────┴──────┴────────────┘
┌─province─┬─city─┬─area───┬─sum(visit)─┐
│
│
│ 天桥
│
24 │
│
│
│ 小店
│
24 │
│
│
│ 黄岛
│
176 │
│
│
│ 历下
│
24 │
│
│
│ 尖草坪 │
112 │
└──────────┴──────┴────────┴────────────┘
┌─province─┬─city─┬─area─┬─sum(visit)─┐
│
│
│
│
360 │
└──────────┴──────┴──────┴────────────┘
select province,city,area,sum(visit) from tb_with group by province,city,area with rollup;
┌─province─┬─city─┬─area───┬─sum(visit)─┐
│ 山东
│ 青岛 │ 黄岛
│
176 │
│ 山东
│ 济南 │ 天桥
│
24 │
│ 山西
│ 太原 │ 尖草坪 │
112 │
│ 山东
│ 济南 │ 历下
│
24 │
│ 山西
│ 太原 │ 小店
│
24 │
└──────────┴──────┴────────┴────────────┘
┌─province─┬─city─┬─area─┬─sum(visit)─┐
│ 山东
│ 青岛 │
│
176 │
│ 山东
│ 济南 │
│
48 │
│ 山西
│ 太原 │
│
136 │
└──────────┴──────┴──────┴────────────┘
┌─province─┬─city─┬─area─┬─sum(visit)─┐
│ 山西
│
│
│
136 │
│ 山东
│
│
│
224 │
└──────────┴──────┴──────┴────────────┘
┌─province─┬─city─┬─area─┬─sum(visit)─┐
│
│
│
│
360 │
└──────────┴──────┴──────┴────────────┘
select province,city,area,sum(visit) from tb_with group by province,city,area with totals;
┌─province─┬─city─┬─area───┬─sum(visit)─┐
│ 山东
│ 青岛 │ 黄岛
│
176 │
│ 山东
│ 济南 │ 天桥
│
24 │
│ 山西
│ 太原 │ 尖草坪 │
112 │
│ 山东
│ 济南 │ 历下
│
24 │
│ 山西
│ 太原 │ 小店
│
24 │
└──────────┴──────┴────────┴────────────┘
Totals:
┌─province─┬─city─┬─area─┬─sum(visit)─┐
│
│
│
│
360 │
└──────────┴──────┴──────┴────────────┘