– with语法
with '1' as v select * from tb_user where id=v;
--求平均年龄
with (select count(1) from tb_user) as cnt select (sum(age)/cnt) from tb_user;
array join 语法
array join 相当于explode+lateral view
select id,arr from tb_arr_join array join arr; --会覆盖原先的数组
select id,arr,x from tb_arr_join array join arr as x ;
select id,arr,x from tb_arr_join array join [1,2,3,4,5] as x; --自动拼
┌─id─┬─arr──────────────┬─x─┐
│ 1 │ ['a1','a2'] │ 1 │
│ 1 │ ['a1','a2'] │ 2 │
│ 1 │ ['a1','a2'] │ 3 │
│ 1 │ ['a1','a2'] │ 4 │
│ 1 │ ['a1','a2'] │ 5 │
│ 2 │ ['b1','b2','b3'] │ 1 │
│ 2 │ ['b1','b2','b3'] │ 2 │
│ 2 │ ['b1','b2','b3'] │ 3 │
│ 2 │ ['b1','b2','b3'] │ 4 │
│ 2 │ ['b1','b2','b3'] │ 5 │
└────┴──────────────────┴───┘
案例需求:将如下数据成功转换
代码实现:
select id ,e,i from (select id ,groupArray(name) arr,
arrayEnumerate(arr) arr_index
from tb_test_arr
group by id ) t
array join arr as e,
arr_index as i
;
FORMAT 指定输出和输入的数据格式
clickhouse-client -q "select * from tb_user FORMAT XML"
LIMIT/LIMIT BY 语法
select * from tb_user limit 2,2 ; --从索引为第二个开始找两个
代码实现:
select * from tb_limit limit 2 by name; --通过name分组
create view v_limit as select * from tb_limit ; --创建一个视图临时存储数据 不会存储到磁盘
create table t_limit engine=Log as select * from tb_limit ;
create table tb_limit2 like tb_limit ; --不支持
创建分区表
按照指定字段分组
create table tb_p(
oid String ,
money Float64 ,
cDate Date
) engine = MergeTree
order by oid
partition by cDate ;
insert into tb_p values ('002',99,'2020-12-01') ,('001',98,'2020-12-01') ,('003',199,'2020-12-02');
┌─oid─┬─money─┬──────cDate─┐
│ 003 │ 199 │ 2020-12-02 │
└─────┴───────┴────────────┘
┌─oid─┬─money─┬──────cDate─┐
│ 001 │ 98 │ 2020-12-01 │
│ 002 │ 99 │ 2020-12-01 │
----------------------------
按照月份分组
create table tb_p2(
oid String ,
money Float64 ,
cDate Date
) engine = MergeTree
order by oid
partition by toMonth(cDate) ;
insert into tb_p2 values ('002',99,'2020-12-01') ,('001',98,'2020-12-01') ,('003',199,'2020-12-02'),('004',299,'2020-11-02');
┌─oid─┬─money─┬──────cDate─┐
│ 001 │ 98 │ 2020-12-01 │
│ 002 │ 99 │ 2020-12-01 │
│ 003 │ 199 │ 2020-12-02 │
└─────┴───────┴────────────┘
┌─oid─┬─money─┬──────cDate─┐
│ 004 │ 299 │ 2020-11-02 │
└─────┴───────┴────────────┘
指定两个分区字段 年+月 不同就分区!!!!
create table tb_p3(
oid String ,
money Float64 ,
cDate Date
) engine = MergeTree
order by oid
partition by (toYear(cDate) , toMonth(cDate)) ;-- 月进行分区
insert into tb_p3 values ('002',99,'2020-12-01') ,('001',98,'2020-12-01') ,('003',199,'2020-12-02'),('004',299,'2020-11-02'),('005',299,'2019-11-02')
;
drwxr-x---. 2 clickhouse clickhouse 228 Dec 6 04:33 2019-11_3_3_0
drwxr-x---. 2 clickhouse clickhouse 228 Dec 6 04:33 2020-11_2_2_0
drwxr-x---. 2 clickhouse clickhouse 228 Dec 6 04:33 2020-12_1_1_0
数组函数
高阶函数
方法: 是一段逻辑代码的封装, 实现功能 , 代码的重复使用, 方便调用
函数: 是一段逻辑代码的封装, 实现功能 , 代码的重复使用, 方便调用
public int add(int a , int b){ return a+b}
方法是对象的一部分
函数比方法高级 , 可以作为一个特殊的对象单独存在
arrayMap是一个高阶函数
Array(T) 参数一 是一个函数 function f(T x){} 参数二 数组
select arrayMap(x->x*x ,[1,2,3,4]) ;
。。。。未完待续
字符串函数
字符串切割
select splitByString(':','hello:world:kitty'); --按照':'切割
字符串拼接
select concat('hello,','jj!');
JSON函数
①:visitParamHas(params, name) 参数一 jsonrowdata 参数二 属性是否有(1 , 0)
select visitParamHas(cont,'movie') from tb_json;
②:visitParamExtractString(params, name) 访问数的一个属性获取一个列的指定属性的值
select visitParamExtractString(cont,'rate') from tb_json ;
③:解析多个属性
SELECT JSONExtract(cont, 'Tuple(movie String , rate String , uid String)')
FROM tb_json
③:元组取值
select JSONExtract(cont,'Tuple(movie String,rate String,timeStamp String)').2 from tb_json;
select cast(tupleElement(JSONExtract(cont , 'Tuple(movie String , rate String , uid String)'),2) as Float64)+12 from tb_json;
枚举数据类型
建表
create table tb_color(color Enum('RED'=1,'BLUE'=2,'GREEN'=3))engine=Log;
插入数据
insert into tb_color values(1); --插入进去的是RED
insert into tb_color values('GREEN');
查询数据
select cast(color as Int8) from tb_color;