Clickhouse常用的sql

--- 文档
--- https://help.aliyun.com/document_detail/209912.html?spm=a2c4g.186971.0.i2
--- https://clickhouse.com/docs/en/sql-reference/statements/alter/projection?spm=a2c4g.209170.0.0.13d62936v7yNlC

--- 用MaterializeMySQL去同步数据库
CREATE DATABASE `test_kingdee_bridge` ENGINE = MaterializeMySQL('localhost:8621', 'your_db_name', 'user', 'pwd');
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializeMySQL('host:port', 'database', 'user', 'password') 
[SETTINGS...]

--- 在表的末尾添加一列
ALTER TABLE mp_appdata.terminal_card ADD COLUMN test_period Nullable(DateTime) DEFAULT 'test_period';
ALTER TABLE mp_appdata.terminal_card ADD COLUMN get_status_time Nullable(DateTime) DEFAULT 'get_status_time';
ALTER TABLE mp_appdata.terminal_card ADD COLUMN update_status_time Nullable(DateTime) DEFAULT 'update_status_time';
ALTER TABLE mp_appdata.terminal_card RENAME COLUMN package_name TO package_info;


--- 设置超时时间
set GLOBAL on cluster default max_execution_time=3600;


--- 查看 system.query_log 错误日志
select event_time,query,exception from `system`.query_log 
where length(exception)>0 and query like '%connect_operators_monthly_flow_202209_view%'
order by event_time desc


--- 给已有的表加上 投影；增加其查询的性能
ALTER TABLE mp_connect_test.connect_operators_monthly_flow_202209_view ADD PROJECTION to_iccid_proj
( 
    SELECT iccid
    ORDER BY iccid
);
--- 对于历史数据，需要手动触发物化(数据量大的话，需要一段时间才能完成)
alter table mp_connect_test.connect_operators_monthly_flow_202209_view MATERIALIZE PROJECTION to_iccid_proj;


--- 查询投影创建的情况(is_done=1 表示历史数据投影初始化完成)
SELECT
    table,
    mutation_id,
    command,
    is_done
FROM system.mutations AS m
WHERE is_done = 1 and table='connect_operators_monthly_flow_202209_view'

--- 常用的时间函数
select 
	toString(toYYYYMMDDHHMMSS(now()),
	toString(toYYYYMM(now() - INTERVAL 1 Month)),     -- 获取1个月前的日期
	toString(toYYYYMMDD(now() - INTERVAL 1 Day)),     -- 获取3天前的日期
	toString(toYYYYMMDD(toDateTime(today())-3600*24*3)),   -- 获取3天前的日期
	toDateTime('2019-10-01') as stime,
	toDateTime('2019-10-01')-3600*24 as ftime,
	toString(toYYYYMMDD(yesterday())) as yesstr,
	concat(toString(toDateTime('2019-10-01 00:00:00')+3600*24*30*1),' ~ ',toString(toDateTime('2019-10-01 00:00:00')+3600*24*30*2)),
	toDateTime('2019-10-01 00:00:00') as stime,
	toDateTime('2019-10-01 00:00:00')+3600*24*30 as etime,
	toStartOfDay(toDateTime(now())),	-- 获取一天的开始
	toStartOfDay(toDateTime(now()))-3600*24,
	block_time,
	block_time-3600*24,
	SUBSTRING(toString(block_time),1,10), -- 截取年月日
	toDateTime(SUBSTRING(toString(block_time),1,10)) AS ftime,
	toDateTime(SUBSTRING(toString(block_time),1,10))+3600*24*15 AS ltime,
	toString(toQuarter(now())) as quarter_flag,
	toString(toYear(now())) as year_flag
	toMonday(toDateTime(now()))			-- 获取当前日期的周一
from etherdata.dwd_com_eth_eoa_action
where block_time>='2022-07-15';			-- 时间是分区的,可快速查询

select 
	toStartOfMonth(now()) as mth_start_day,
	date_sub(DAY, 1, toStartOfMonth(date_add(MONTH, 1, now()))) as mth_end_day,
	toStartOfMonth(now()) + INTERVAL 14 Day as mth_mid_day,
	toDateTime(now()-3600),  -- 表示获取上一秒
	now(),
	yesterday(),
	toStartOfDay(toDateTime(now())) as currentday_00_time,	
	toStartOfDay(toDateTime(now()))-3600*24 as yesterday_00_time,      --- 昨日0点
	toStartOfDay(toDateTime(now()))-3600*24*7 as last7day_00_time,     --- 7日前的0点
	toStartOfDay(toDateTime(now()))-3600*24*15 as last15day_00_time,   --- 15日前的0点
	toStartOfDay(toDateTime(now()))-3600*24*30 as last30day_00_time,    --- 30日前的0点
	toYYYYMMDD(toDateTime(now())),
	toYYYYMMDD(toDateTime(now())-3600*24)
  

--- 查看版本号
SELECT version(),now();	

--- 表示百分数
select concat(toString(round(39/6160*100,2)),'%') as percent

--- 类型转换
select cast (terminal_card.code as Decimal128(0)),toInt128('12050297429519378238736993207'),'12050297429519378238736993207'

--- 列传行
--- 将字符数组转化成指定分割符号的字符串
select 
	contract_address,
	groupArray(`from`),
    arrayStringConcat(flatten(groupArray(from)),'&') as t, 
	groupUniqArray(`from`)
from ct_addr_msg group by contract_address
--- 与metabase日期传唤有关
where action ='mix_withdraw' [[and toDate(toString(toYYYYMMDD(block_time)))={{cdate}}]] 
where pt_d=toDate32(parseDateTimeBestEffort(toString({{cdate}})))]] --20220808


--- concat 与 arrayStringConcat 且套组合
select sku_id,
       cday,
       shop_group_name,
       arrayStringConcat(flatten(groupArray(msg)),' | ') as aggmsg
from(
    select sku_id,cday,shop_group_name,concat(shop_name,'=',qty) as msg
    from (
        select 
        	sku_id,
        	cday,
        	toString(sum(qty)) as qty,
        	shop_group_name,
        	shop_name 
        from OUTC f group by shop_group_name,shop_name,sku_id,cday
    )f
)f group by shop_group_name,sku_id,cday
    

-- 通过设置相应日志表的TTL来清理，CH日志(删除日志)
SELECT
    table AS table_name,
    sum(rows) AS row_num,
    formatReadableSize(sum(data_uncompressed_bytes)) AS org_size,
    formatReadableSize(sum(data_compressed_bytes)) AS compress_size,
    round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS compress_ratio
FROM system.parts
--WHERE database='default'
--where table_name='query_log'
GROUP BY table order by sum(rows) desc;

-- 清空多余的日志
truncate table system.query_thread_log

-- 日志设置 TTL 失效时间
ALTER TABLE `system`.query_log MODIFY TTL event_time + toIntervalWeek(2);
ALTER TABLE `system`.query_thread_log MODIFY TTL event_time + toIntervalWeek(2);
ALTER TABLE `system`.trace_log MODIFY TTL event_time + toIntervalWeek(2);
ALTER TABLE `system`.part_log MODIFY TTL event_time + toIntervalWeek(2);


alter table system.query_log delete where event_date < '2022-01-01';
ALTER TABLE `system`.query_thread_log MODIFY TTL event_date + toIntervalMonth(1);
alter table `system`.query_log modify ttl event_date + interval 2 week;
desc `system`.query_log
select * from system.tables where name='query_log';
select * from system.query_log;



--- 字符串常用函数(判断字符吃否被包含)
select 
	case when position(action, ' ')>0 then splitByString(' ',action)[0] else '' end as project_symbol,
	splitByString(' ',action),
	position(action, ' '),
	pair_address
from t1

select * from system.materialize_mysql where countSubstrings(lasted_error_msg,'Cannot read all data')>0

-- 删除视图
drop view mp_connect_test.conn_tst_view
truncate mp_connect_test.connect_operators_monthly_flow_202209_view
truncate mp_connect_test.conn_tst_view

-- 字符切分
select iccid,splitByChar(',',flow_group_id) from mp_connect_test.connect_card_group_info;

--查询同步日志报错信息
select * from system.materialize_mysql;

--增加JDBC连接时间
jdbc:clickhouse://cc-wz9692n2vr8516py8.public.clickhouse.ads.aliyuncs.com:8123/?socket_timeout=7200000&send_timeout=3600&receive_timeout=3600&distributed_ddl_task_timeout=4800

-- 确定唯一值加个final关键字(在物化视图上也可以直接添加final做到相同的效果)
select * from mp_connect_test.conn_tst_view final where iccid='3'

select f1.iccid,
       f1.modify_time,
       case when f2.flow_group_id is null then 0 else f2.flow_group_id 
       end as flow_group_id
from mp_connect_test.connect_operators_monthly_flow_202209_view f1 final 
left join mp_connect_test.connect_card_group_info f2 final on f1.iccid = f2.iccid




--增加索引
ALTER TABLE mp_connect_test.connect_operators_monthly_flow_test
ADD INDEX id  iccid TYPE minmax GRANULARITY 3



-- 建表，建view时 允许字段为空Nullable
CREATE MATERIALIZED VIEW `mp-meter`.meter_base_meter_time_mv
(
  iccid String ,
  start_time Nullable(DateTime),
  end_time Nullable(DateTime)
)
ENGINE ReplacingMergeTree
PRIMARY KEY (iccid)  
ORDER BY (iccid) 
POPULATE AS
select terminal_id as iccid, 
     min(start_time) as start_time, 
     max(end_time) as end_time 
from `mp-meter`.meter_base_meter 
where is_delete = 0 and status in ( 0, 1, 2, 3 ) and meter_type in ( 1, 2 ) and meter_object = 1  
group by terminal_id 


--- 一般建表语句 
CREATE TABLE dev_mp_appdata.terminal_card_usage
(
    `iccid` String,
    `create_date` String,
    `type` Nullable(Int32),
    `real_usage` Nullable(DECIMAL(64,2)),
    `principal_id` Nullable(Int64),
    `ch_data_version` UInt64
)
ENGINE = ReplacingMergeTree()
ORDER BY tuple(iccid, create_date)
SETTINGS index_granularity = 8192;