LOAP引擎：clickhouse04： 特殊sql语句写法

1.with

with  (select  count(1) from tb_user) as  cnt 
select  sum(age) / cnt  from tb_user ;

2.array  join

create table tb_user4(
id Int8,
arr Array(String)
)
engine=Log;

insert into tb_user4 values(1,['yyy','jjj','www']),(2,Array('uuu','ooo'));

select
id,
arr_e,
arr_i
from
(select
id,
arr,
arrayEnumerate(arr) arr_index 
from
tb_user4)
array join
arr as arr_e,
arr_index as arr_i ;

3.distinct

 去重

4.format

指定输出和输入的数据格式

clickhouse-client  -q  "select * from db_doit19.tb_user  FORMAT  XML" 

file    Creates a File-engine table.
merge    Creates a Merge-engine table.
numbers    Creates a table with a single column filled with integer numbers.
remote    Allows you to access remote servers without creating a Distributed-engine table.
url    Creates a Url-engine table.
mysql    Creates a MySQL-engine table.
jdbc    Creates a JDBC-engine table.
odbc    Creates a ODBC-engine table.
hdfs    Creates a HDFS-engine table.

5.limit

create  table  tb_limit(
id Int8 ,
name String ,
score  Float64
)engine=Log ;

insert  into  tb_limit values(1,'zss',77),(1,'zss',79),(1,'zss',99),(1,'zss',89) ;
insert  into  tb_limit values(2,'lss',66),(2,'lss',69),(2,'lss',61),(2,'lss',69) ;

select * from tb_limit order by  score desc  limit 2  by name;

6.partition by
 

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


地址中显示为3个文件
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

7.left join