19、ClickHouse企业中常见的20种用法
– 1、表结构添加字段
-- 表结构添加字段之添加字符串
DROP TABLE dev_nanmu.user ON CLUSTER ` default` ;
ALTER TABLE dev_nanmu.user_local ON CLUSTER ` default`
ADD COLUMN ` balance` Float64,
ADD COLUMN ` time ` String,
ADD COLUMN ` createTime` DateTime64( 3 , 'Asia/Shanghai' ) ;
CREATE TABLE dev_nanmu.user ON CLUSTER ` default` AS dev_nanmu.user_local ENGINE = Distributed( 'default' , 'dev_nanmu.user' , 'dev_nanmu.user_local' , toYYYYMM( timestamp)) ;
– 2、删除语句
ALTER TABLE ` dev_nanmu` . ` user_local` ON CLUSTER ` default` DELETE where ` name` = '张三' ;
– 3、更新语法
alter table ` dev_nanmu` . ` user_local` ON CLUSTER ` default` update ` confirmTime` = '2022-12-02 15:26:06' where ` name` = '张三' ;
– 4、查询表字段结构
describe table dev_nanmu.user;
– 5、展示字段加密处理 身份证号(字母加数字)加密
if( empty( ` user` . ` idCardNo` ) = 1 , '' , replaceRegexpOne( ` user` . ` idCardNo` , '(\\w{6})(\\w{8})(\\w{3})(\\w{1})' , '\\1********\\3*' )) AS ` 证件号`
– 6、展示字段加密处理 手机号(纯数字)加密
replaceRegexpOne( ` user` . ` mobile` , '(\\d{3})(\\d{4})(\\d{4})' , '\\1****\\3' ) AS ` 联系方式`
– 7、计数 去重后
uniq( if(( `user`.`name` = '张三' or `user`.`name` = '李四') , `user`.mobile, null)) as user_count
– 8、计数 不去重
count( if(( `user`.`name` = '报价成功' or `user`.`name` = '报价失败') , `user`.mobile, null)) AS user_count
– 9、运算 divide–除法 *–乘法
concat( if( user_valid_count = 0 ,'0' ,toString( floor( divide( user_valid_count * 100 , user_count) ,1)) ) ,'%' ) as user_valid_rate
– 10、模糊查询
WHERE like( ` user` . ` name` , '%三' )
– 11、变量设置
and { { userType} }
– 12、日期转换后 与变量范围匹配
[ [ and toDate( ` user` . ` timestamp` ) >= { { start} } ] ] [ [ and toDate( ` user` . ` timestamp` ) <= { { end} } ] ]
– 13、字符串切割 保留前两位
substring( ` user` . ` name` ,1,6) ` 名称`
– 14、求和
sum( if(( `user`.`name` = '张三') , `user`.amount, null)) AS amount
– 15、转为日期类型 java long类型clickhouse DateTime64(3, ‘Asia/Shanghai’)类型
toDate( ` bcs` . ` timestamp` )
– 16、查询阻塞的sql
select * from system.mutations where is_done = 0 ;
– 17、将阻塞的sql杀掉
KILL MUTATION WHERE database = 'aaa' AND table = 'user' ;
– 18、分组
group by user.userType,formatDateTime( toDate( ` user` . ` timestamp` ) ,'%Y-%m' )
– 19、排序
order by toDate( ` user` . ` timestamp` ) , ` bcs` . ` userType` desc;
– 20、WHEN…THEN…
WHEN ` user` . ` userType` = '企业A' THEN '企业' WHEN ` user` . ` userType` = '企业B' THEN '企业'
– 21、关于字符串(yyyy-MM-dd HH:mm:ss)转日期类型补充
空值处理
* toDateTimeOrNull
* toDateTimeTimeOrZero
* parseDateTimeBestEffort
* parseDateTimeBestEffortOrNull
* parseDateTimeBestEffortOrZero
Pick the one depending or your needs. Variants with 'or' don't throw exceptions on bad strings.
– 22、获取日期周、月、季度第一天
* toStartOfMonth( )
* toStartOfQuarter( )
* ( toMonday( 日期 + 1 ) - 1 )
– 23、cube
group by的一种扩展,生成的结果集包含了所选列中值的所有组合的所有聚合,结果是总数的2
的n次方,n为维度的个数;
– 24、rollup
意思为上卷,是group by子句的一种扩展,生成的结果集显示了所选列中值的某一层次结构的聚合,从右至左去掉维度进行小计。
– 25、totals
属于group by的一种,只计算合计