-
ClickHouse学习笔记之SQL语句
简介
ClickHouse支持传统的MySQL中的结构化查询语言SQL，因此此处不会从头学SQL，而是记录ClickHouse中对SQL扩展的新特性。
Insert
基本和MySQL一致：
insert into table_name values(...), (...); insert into table_name select a, b, c from table2_name;- 1
- 2
Update和Delete
ClickHouse将
Update和Delete合称为Mutation查询，是Alter的一种。ClickHouse中的Mutation会导致放弃目标数据原有的分区，并重建分区，因此尽量做批量的变更，不要频繁进行小数据的更删，而且Mutation查询不支持事务。
Mutation语句分两步执行，同步执行的部分是第一步：新增数据或分区，并把旧分区打上失效标记；第二步是合并分区时，删除被标记的旧数据释放磁盘空间。删除
alter table t_order_smt delete where sku_id = 'sku_001';- 1
修改
alter table t_order_smt update total_amount = toDecimal32(2000.00, 2) where id = 102;- 1
查询
跟MySQL差别不大：支持子查询、支持
CTE(Common Table Expression，公用表语句)、支持Join、窗口函数(官方正在测试)、不支持自定义函数、group by增加了with rollup/with cube/with total以支持计算小计和总计。
ClickHouse中的Join不会使用缓存，因此即使是两条相同的Join语句，ClickHouse也会视为两条新的SQL。下面对支持小计和总计的语句进行测试
插入数据
scentos :) alter table t_order_mt delete where 1=1; ALTER TABLE t_order_mt DELETE WHERE 1 = 1 Query id: ee8fdaf7-13b1-4591-92ca-d5b8c39a9072 Ok. 0 rows in set. Elapsed: 0.003 sec. scentos :) insert into t_order_mt values :-] (101,'sku_001',1000.00,'2020-06-01 12:00:00'), :-] (101,'sku_002',2000.00,'2020-06-01 12:00:00'), :-] (103,'sku_004',2500.00,'2020-06-01 12:00:00'), :-] (104,'sku_002',2000.00,'2020-06-01 12:00:00'), :-] (105,'sku_003',600.00,'2020-06-02 12:00:00'), :-] (106,'sku_001',1000.00,'2020-06-04 12:00:00'), :-] (107,'sku_002',2000.00,'2020-06-04 12:00:00'), :-] (108,'sku_004',2500.00,'2020-06-04 12:00:00'), :-] (109,'sku_002',2000.00,'2020-06-04 12:00:00'), :-] (110,'sku_003',600.00,'2020-06-01 12:00:00'); INSERT INTO t_order_mt FORMAT Values Query id: 544f51e2-1437-495e-8dbd-46ce298d2c43 Ok. 10 rows in set. Elapsed: 0.004 sec. scentos :) with rollup： scentos :) select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with rollup; SELECT id, sku_id, sum(total_amount) FROM t_order_mt GROUP BY id, sku_id WITH ROLLUP Query id: 8b99ff06-e5b5-4e8b-bbab-1bc4bc6d18f3 ┌──id─┬─sku_id──┬─sum(total_amount)─┐ │ 110 │ sku_003 │ 600 │ │ 109 │ sku_002 │ 2000 │ │ 107 │ sku_002 │ 2000 │ │ 106 │ sku_001 │ 1000 │ │ 104 │ sku_002 │ 2000 │ │ 101 │ sku_002 │ 2000 │ │ 103 │ sku_004 │ 2500 │ │ 108 │ sku_004 │ 2500 │ │ 105 │ sku_003 │ 600 │ │ 101 │ sku_001 │ 1000 │ └─────┴─────────┴───────────────────┘ ┌──id─┬─sku_id─┬─sum(total_amount)─┐ │ 110 │ │ 600 │ │ 106 │ │ 1000 │ │ 105 │ │ 600 │ │ 109 │ │ 2000 │ │ 107 │ │ 2000 │ │ 104 │ │ 2000 │ │ 103 │ │ 2500 │ │ 108 │ │ 2500 │ │ 101 │ │ 3000 │ └─────┴────────┴───────────────────┘ ┌─id─┬─sku_id─┬─sum(total_amount)─┐ │ 0 │ │ 16200 │ └────┴────────┴───────────────────┘ 20 rows in set. Elapsed: 0.003 sec.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
计算
group by子句中从左往右的每个组合的小计，此处group by id, skull_id，那么会计算每个(id, skull_id)的小计、每个id的小计和合计with cube
scentos :) select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with cube; SELECT id, sku_id, sum(total_amount) FROM t_order_mt GROUP BY id, sku_id WITH CUBE Query id: 6c515fc3-0002-4d32-a99f-e569e0749a31 ┌──id─┬─sku_id──┬─sum(total_amount)─┐ │ 110 │ sku_003 │ 600 │ │ 109 │ sku_002 │ 2000 │ │ 107 │ sku_002 │ 2000 │ │ 106 │ sku_001 │ 1000 │ │ 104 │ sku_002 │ 2000 │ │ 101 │ sku_002 │ 2000 │ │ 103 │ sku_004 │ 2500 │ │ 108 │ sku_004 │ 2500 │ │ 105 │ sku_003 │ 600 │ │ 101 │ sku_001 │ 1000 │ └─────┴─────────┴───────────────────┘ ┌──id─┬─sku_id─┬─sum(total_amount)─┐ │ 110 │ │ 600 │ │ 106 │ │ 1000 │ │ 105 │ │ 600 │ │ 109 │ │ 2000 │ │ 107 │ │ 2000 │ │ 104 │ │ 2000 │ │ 103 │ │ 2500 │ │ 108 │ │ 2500 │ │ 101 │ │ 3000 │ └─────┴────────┴───────────────────┘ ┌─id─┬─sku_id──┬─sum(total_amount)─┐ │ 0 │ sku_003 │ 1200 │ │ 0 │ sku_004 │ 5000 │ │ 0 │ sku_001 │ 2000 │ │ 0 │ sku_002 │ 8000 │ └────┴─────────┴───────────────────┘ ┌─id─┬─sku_id─┬─sum(total_amount)─┐ │ 0 │ │ 16200 │ └────┴────────┴───────────────────┘ 24 rows in set. Elapsed: 0.004 sec.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
计算
group by子句中所有组合的小计，此处group by id, skull_id，那么会计算每个(id, skull_id)的小计、每个id的小计、每个skull_id的小计和合计。with totals
scentos :) select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with totals; SELECT id, sku_id, sum(total_amount) FROM t_order_mt GROUP BY id, sku_id WITH TOTALS Query id: 74a55dba-83d1-4365-9cf4-961cf5685a2e ┌──id─┬─sku_id──┬─sum(total_amount)─┐ │ 110 │ sku_003 │ 600 │ │ 109 │ sku_002 │ 2000 │ │ 107 │ sku_002 │ 2000 │ │ 106 │ sku_001 │ 1000 │ │ 104 │ sku_002 │ 2000 │ │ 101 │ sku_002 │ 2000 │ │ 103 │ sku_004 │ 2500 │ │ 108 │ sku_004 │ 2500 │ │ 105 │ sku_003 │ 600 │ │ 101 │ sku_001 │ 1000 │ └─────┴─────────┴───────────────────┘ Totals: ┌─id─┬─sku_id─┬─sum(total_amount)─┐ │ 0 │ │ 16200 │ └────┴────────┴───────────────────┘ 10 rows in set. Elapsed: 0.004 sec.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
计算
group by子句中的小计和合计，此处group by id, skull_id，那么会计算每个(id, skull_id)的小计和合计。alter操作
基本和MySQL一致。
新增字段：
alter table table_name add column col_name String after old_col1;- 1
修改字段类型：
alter table table_name modify column col_name String;- 1
修改字段名：
rename column old_name to new_name;- 1
删除字段：
alter table table_name drop column col_name;- 1
导出数据
[szc@scentos ~]$ clickhouse-client --query "select * from t_order_mt" --format CSVWithNames > ./clickhouse_result1.csv [szc@scentos ~]$ cat ./clickhouse_result1.csv "id","sku_id","total_amount","create_time" 106,"sku_001",1000,"2020-06-04 12:00:00" 107,"sku_002",2000,"2020-06-04 12:00:00" 108,"sku_004",2500,"2020-06-04 12:00:00" 109,"sku_002",2000,"2020-06-04 12:00:00" 101,"sku_001",1000,"2020-06-01 12:00:00" 101,"sku_002",2000,"2020-06-01 12:00:00" 103,"sku_004",2500,"2020-06-01 12:00:00" 104,"sku_002",2000,"2020-06-01 12:00:00" 110,"sku_003",600,"2020-06-01 12:00:00" 105,"sku_003",600,"2020-06-02 12:00:00"- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
更多格式参考官网。
-
相关阅读:
Amazon 消息订阅对接
MYSQL的存储过程
grid实现“品”字布局
区块链与哈希函数
React Native 工程基础建设
聊聊啥项目适合做自动化测试
华为常用命令
Iterable、Collection、List等接口
交换式网络捕获网络流量的方法
Nvidia显卡L40S学习：产品规格，常用名词解释
- 原文地址：https://blog.csdn.net/qq_37475168/article/details/126798116
