clickhouse查询语法--with

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 │
└──────────┴──────┴──────┴────────────┘