19、ClickHouse企业中常见的25种用法

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的一种，只计算合计