Clickhouse常用查询语句

1./clickhouse-client --host xxx.xxx.xxx.xxx --port 9000 --user 'yyy' --password 'zzzzz'

1use databases;
2select * from table;
3show create table;

1##按照数据量倒序
2SELECT database, formatReadableSize(on_disk) FROM (SELECT database, sum(bytes_on_disk) AS on_disk FROM system.parts  GROUP BY database)  ORDER BY on_disk DESC;
3
4SELECT database, formatReadableSize(sum(bytes_on_disk)) AS on_disk FROM system.parts GROUP BY database ORDER BY on_disk DESC;

1select database, table, sum(rows) as "总行数",　    formatReadableSize(sum(data_uncompressed_bytes)) as "原始大小",　    formatReadableSize(sum(data_compressed_bytes)) as "压缩大小",　    2*CEILING(sum(data_compressed_bytes)/(10*1024*1024*1024*1024)/0.7)*3+3 as "计算单元数",　    2*CEILING(sum(data_compressed_bytes)/(10*1024*1024*1024*1024)/0.7)*2+3 as "存储单元数",　    round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 0) "压缩率"　from system.parts group by database,table order by sum(data_compressed_bytes) desc limit 100;
2
3
4select database, table, sum(rows) as "总行数",　    formatReadableSize(sum(data_uncompressed_bytes)) as "原始大小",　    formatReadableSize(sum(data_compressed_bytes)) as "压缩大小",　    round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 0) "压缩率"　from system.parts group by database,table order by sum(data_compressed_bytes) desc limit 100;

1SELECT * FROM TABLE FORMAT Vertical

 1## 查询近几小时
 2SELECT stactics_date FROM table where subtractHours(stactics_date, 7) > toDate(now());
 3
 4## 查询今天
 5SELECT stactics_date FROM table where toDate(stactics_date) = today();
 6
 7## 查询昨天
 8SELECT stactics_date FROM table where toDate(stactics_date) = yesterday();
 9
10##查询近几天
11SELECT stactics_date FROM table where subtractDays(stactics_date, 7) > toDate(now());
12
13## 查询本周
14SELECT stactics_date FROM table where stactics_date BETWEEN subtractDays(stactics_date, toDayOfWeek(stactics_date)) and subtractDays(stactics_date, toDayOfWeek(tactics_date) + 7);
15
16##查询本月
17SELECT stactics_date FROM table where toMonth(tactics_date) = toMonth(now());
18
19##按照自定义时间段（小时维度）
20SELECT stactics_date FROM table where stactics_date BETWEEN yesterday() and subtractHours(stactics_date, 1);
21
22##按照自定义时间段（字符时间格式加减天数）
23SELECT stactics_date FROM table where stactics_date BETWEEN
24DATE_SUB( str_to_date({startTime},'YYYY-MM-DD HH24:MI:SS'), INTERVAL 1 DAY )
25AND DATE_SUB( str_to_date({endTime},'YYYY-MM-DD HH24:MI:SS'), INTERVAL 1 DAY );
26
27## 字符串日期自定义时间
28SELECT COUNT(*) from table  where parseDateTimeBestEffort(import_date) BETWEEN parseDateTimeBestEffort('20220831') AND parseDateTimeBestEffort('20230217');
29
30## 每天时间段内
31SELECT stactics_date FROM table where toYYYYMMDDhhmmss(tactics_date)%1000000 BETWEEN '123000' and '160000';
32
33## 按小时分组
34SELECT 
35	stactics_name,
36	stactics_id,
37	concat(toString(toHour(stactics_date)), '-', toString(toHour(stactics_date) + 1), '点') stacticsDate,count(*) stacticsCount
38FROM table GROUP BY stactics_name,	stactics_id,toHour(stactics_date);
39
40## 按天分组
41SELECT stactics_name,stactics_id,toDate(stactics_date) stacticsDate,count(*) stacticsCount
42FROM table GROUP BY stactics_name,stactics_id,toDate(stactics_date);