clickhouse语法

官方中文文档

https://clickhouse.com/docs/zh/operations/

案例网页：

https://blog.csdn.net/qq_41070393/article/details/116783138

常用操作-- 修改表名

RENAME TABLE sem_energy_categor TO sem_energy_category;

-- 修改表中的字段类型

ALTER TABLE sem_energy_device_day_result MODIFY COLUMN day_value Decimal(18,4);  ALTER TABLE sem_energy_item_day_result MODIFY COLUMN hour_max_value Decimal(18,4);  ALTER TABLE sem_energy_item_day_result MODIFY COLUMN hour_min_value Decimal(18,4);  ALTER TABLE sem_energy_item_day_result MODIFY COLUMN mater_type Int32;

-- 插入数据

INSERT INTO `default`.sem_energy_item_day_result (id,energy_category_id,mater_type,start_day,end_day,day_value,hour_max_value,hour_min_value,unit_id,state,update_date,updater,creator,create_date) VALUES (1,1605133058837647362,1,'2022-12-01 00:00:00','2022-12-02 00:00:100.000，50.0000，50.0000，10000，1,NULL,NULL,1067246875800000001,'2022-12-23 12:53:45'), (2,1605133058837647362,1,'2022-12-02 00:00:00','2022-12-03 00:00:100.000，50.000，50.0000，50.00000，1,1,NULL,NULL,1067246875800000001,'2022-12-23 12:53:45'), (3,1605133058837647362,1,'2022-12-03 00:00:00','2022-12-04 00:00:100.000，50.0000，50.0000,1,1,NULL,NULL,1067246875800000001,'2022-12-23 12:53:45'), (4,1605133058837647362,1,'2022-12-04 00:00:00','2022-12-05 00:00:00',100.0000,50.000，50.0000，1.1NULL,NULL,1067246875800000001,'2022-12-23 12:53:45'), (5,1605132995977613314,1,'2022-12-05 00:00:00','2022-12-06 00:00:00',50.000,50.000,50.000,1,1,1,NULL,NULL,1067246875800000001,'2022-12-23 12:53:45'), (6,1605132995977613314,1,'2022-12-06 00:00:00','2022-12-07 00:00:50.0000，50.0000，50.0000，1，1，0000，NULL,NULL,1067246875800000001,'2022-12-23 12:53:45'), (7,1605132995977613314,1,'2022-12-07 00:00:00','2022-12-08 00:00:50.0000，50.0000，50.0000，1，1，0000，NULL,NULL,1067246875800000001,'2022-12-23 12:53:45'), (8,1605132995977613314,1,'2022-12-08 00:00:00','2022-12-09 00:00:50.0000，50.0000，50.0000，1，1，0000，NULL,NULL,1067246875800000001,'2022-12-23 12:53:45'), (9,1605133058837647362,2,'2022-12-09 00:00:00','2022-12-10 00:00:100,000,50,000,50,000,1,1,1,1,NULL,NULL,1067246875800000001,'2022-12-23 12:53:45'), (10,1605133058837647362,2,'2022-12-10 00:00:00','2022-12-11 00:00:100,000,50,000,50,000,1,1,1,1,NULL,NULL,1067246875800000001,'2022-12-23 12:53:45');

--修改数据

ALTER TABLE sem_energy_item_hour_result UPDATE id=1 where

--删除部分数据(where必带)

ALTER TABLE `default`.sem_energy_item_hour_result DELETE WHERE id=0 AND energy_category_id=0 AND mater_type=0 AND start_hour='' AND end_hour='' AND hour_value=0 AND unit_id=0 AND state=0 AND update_date='' AND updater=0 AND creator=0 AND create_date='';alter table sem_energy_item_day_result delete where id is not null;

-- 新建临时表

MERGE INTO sem_energy_item_hour_result AS tgtUSING SOURCE_TABLE AS srcWHEN MATCHEDTHEN UPDATE SETtgt.id=src.id, tgt.energy_category_id=src.energy_category_id, tgt.mater_type=src.mater_type, tgt.start_hour=src.start_hour, tgt.end_hour=src.end_hour, tgt.hour_value=src.hour_value, tgt.unit_id=src.unit_id, tgt.state=src.state, tgt.update_date=src.update_date, tgt.updater=src.updater, tgt.creator=src.creator, tgt.create_date=src.create_dateWHEN NOT MATCHEDTHEN INSERT (id, energy_category_id, mater_type, start_hour, end_hour, hour_value, unit_id, state, update_date, updater, creator, create_date)VALUES (src.id, src.energy_category_id, src.mater_type, src.start_hour, src.end_hour, src.hour_value, src.unit_id, src.state, src.update_date, src.updater, src.creator, src.create_date);

-- 查询数据

select * FROM sem_energy_item_day_result

-- SELECT 查询关键字-- ALL子句

SELECT ALL 和 SELECT 不带 DISTINCT 是一样的。select sum(hour_value) from sem_energy_item_hour_resultselect sum(all hour_value) from sem_energy_item_hour_result seihr select sum(distinct hour_value) from sem_energy_item_hour_result seihr2

-- array join

对于包含数组列的表来说，生成一个包含初始列中每个单独数组元素的新表是一个常见的操作，其他列的值将被重复显示。 这是 ARRAY JOIN 句子最基本的场景。

• https://clickhouse.com/docs/zh/sql-reference/statements/select/array-join

-- prewhere的句子

1、使用prewhere优化，首先只读取执行prewhere表达式所需的列。 然后读取运行其他查询所需的其他列，但只读取prewhere表达式所在的块 “true” 至少对于一些行。2、如果有很多块，prewhere表达式是 “false” 对于所有行和prewhere需要比查询的其他部分更少的列，通常允许从磁盘上读取更少的数据进行查询。select * FROM sem_energy_item_hour_result seihr prewhere seihr.id >0

-- sample

1. 数据的后半部分取出10％的样本: SAMPLE 1/10 OFFSET 1/2
2. 例子是所有数据的十分之一： SAMPLE 1/10
3. sample n : n是足够大的整数

-- with 语法

with '6' as mater_typeselect mater_type ,* FROM sem_energy_item_hour_result seihr WHERE seihr.id =1

SELECThello,*FROM(WITH ['hello'] AS helloSELECT hello)

与Clickhose时间函数对照表