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:00',100.0000,50.0000,50.0000,1,1,NULL,NULL,1067246875800000001,'2022-12-23 12:53:45'),
	 (2,1605133058837647362,1,'2022-12-02 00:00:00','2022-12-03 00:00:00',100.0000,50.0000,50.0000,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:00',100.0000,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.0000,50.0000,1,1,NULL,NULL,1067246875800000001,'2022-12-23 12:53:45'),
	 (5,1605132995977613314,1,'2022-12-05 00:00:00','2022-12-06 00:00:00',50.0000,50.0000,50.0000,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:00',50.0000,50.0000,50.0000,1,1,NULL,NULL,1067246875800000001,'2022-12-23 12:53:45'),
	 (7,1605132995977613314,1,'2022-12-07 00:00:00','2022-12-08 00:00:00',50.0000,50.0000,50.0000,1,1,NULL,NULL,1067246875800000001,'2022-12-23 12:53:45'),
	 (8,1605132995977613314,1,'2022-12-08 00:00:00','2022-12-09 00:00:00',50.0000,50.0000,50.0000,1,1,NULL,NULL,1067246875800000001,'2022-12-23 12:53:45'),
	 (9,1605133058837647362,2,'2022-12-09 00:00:00','2022-12-10 00:00:00',100.0000,50.0000,50.0000,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:00',100.0000,50.0000,50.0000,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 tgt
USING SOURCE_TABLE AS src
WHEN MATCHED
THEN UPDATE SET
tgt.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_date
WHEN NOT MATCHED
THEN 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_result
select 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_type select mater_type ,* FROM sem_energy_item_hour_result seihr WHERE seihr.id =1

SELECT hello, * FROM ( WITH ['hello'] AS hello SELECT hello )

mysql和Clickhouse时间函数对照表