分组取每组前n条
数据分析分组展示N行想必大家都不陌生,clickhouse也很方便的提供了相关的查询语法:LIMIT n BY express
,我们以系统表为例,用到的属性有数据库名、表名、表大小。
-- 数据库分组取每个库下最大的三个表
SELECT database,
table,
formatReadableSize(sum(data_compressed_bytes)) AS bytes
FROM system.parts
GROUP BY database,table
ORDER BY database,bytes DESC
limit 3 by database
┌─database───────────────┬─table──────────────────────────┬─bytes──────┐
│ default │ insert_test_76 │ 9.93 MiB │
│ default │ insert_test_77 │ 9.92 MiB │
│ default │ insert_test_67 │ 9.42 MiB │
│ system │ trace_log │ 8.31 MiB │
│ system │ metric_log │ 74.76 MiB │
│ tutorial │ hdfs2ch │ 5.32 MiB │
│ tutorial │ visits_v1 │ 310.43 MiB │
│ tutorial │ hits_v1 │ 1.18 GiB │
...
结果集会从每个分组中取前三条、如果像system库表容量没有三条则只展示存在的。
分组、汇总同时计算
数据分析时大家是不是常常做了分组统计后、又要再做一个汇总统计。如果这两个才行能合并在一起是不是很方便。ck中我们只需要在sql中加上with totals
就能实现这种效果了。还是以系统表为例。
SELECT database,
formatReadableSize(SUM(bytes_on_disk)),
COUNT(table)
FROM system.parts
GROUP BY database
WITH TOTALS
┌─database───────────────┬─formatReadableSize(SUM(bytes_on_disk))─┬─COUNT(table)─┐
│ tutorial │ 1.71 GiB │ 4 │
│ system │ 285.54 MiB │ 228 │
│ default │ 32.23 GiB │ 8893 │
│ test │ 1.14 GiB │ 12 │
│ default1 │ 69.57 MiB │ 232 │
│ test_base │ 1.55 GiB │ 3 │
│ test_base_report_view │ 843.83 MiB │ 7 │
│ test_base_view │ 131.55 MiB │ 2 │
└────────────────────────┴────────────────────────────────────────┴──────────────┘
Totals:
┌─database─┬─formatReadableSize(SUM(bytes_on_disk))─┬─COUNT(table)─┐
│ │ 37.93 GiB │ 9381 │
└──────────┴────────────────────────────────────────┴──────────────┘
8 rows in set. Elapsed: 1.163 sec. Processed 9.38 thousand rows, 4.38 MB (8.06 thousand rows/s., 3.77 MB/s.)
表重命名
之前在网上查clickhouse的表重命名需要从A表插入B表,其实ck也是支持表移动的。RENAME可以修改数据表的名称,如果将原始数据库与目标数据库设为不同,那么就可以实现数据表在 两个数据库之间移动的效果。
例如在下面的例子中,testcol_v1从default默认数据库被移动到了db_test 数据库,同时数据表被命名为testcol_v2:
RENAME TABLE default.testcol_v1 TO db_test.testcol_v2
需要注意的是,数据表的移动只能在单个节点的范围内。换言之,数据表移动的目标数据库和原始数据 库必须处在同一个服务节点内,不能是集群中的远程节点。
日期类型函数
常见的获取当前日期、月初、季度、年份格式转换都是可以在官网-时间日期函数中找到。另外还有一个好用是函数parseDateTimeBestEffort,可以直接很多格式如纯数字类型$YYYYMMDD直接转换时间。
select parseDateTimeBestEffort('20210101');
┌─parseDateTimeBestEffort('20210101')─┐
│ 2021-01-01 00:00:00 │
└─────────────────────────────────────┘
SELECT parseDateTimeBestEffort('12/12/2020 12:12:57') AS parseDateTimeBestEffort;
┌─parseDateTimeBestEffort─┐
│ 2020-12-12 12:12:57 │
└─────────────────────────┘
添加修改多个字段
配置一个或多个用逗号分隔的动作。每个动作是对某个列实施的操作行为。
-- 添加两列
ALTER TABLE test1 ADD COLUMN f1 String, ADD COLUMN f2 String;
ALTER TABLE test1
ADD COLUMN `f1` String, ADD COLUMN `f2` String
Query id: e6452480-faca-440e-b61f-aa517fb41e04
Ok.
0 rows in set. Elapsed: 0.011 sec.
-- 删除两列
ALTER TABLE test1 drop COLUMN f1 , drop COLUMN f2 ;
ALTER TABLE test1
DROP COLUMN f1, DROP COLUMN f2
Query id: 80c0cdc9-27de-4435-9850-a238088594d0
Ok.
0 rows in set. Elapsed: 0.014 sec.