clickhouse（十、 特殊函数应用 ）

分组取每组前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.