CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

-- 查询文件结构
SELECT table, name, path, active, * FROM `system`.parts WHERE table = '<table name>' 

-- 示例
CREATE TABLE emp_replacingmergetree_2
(
    emp_id     UInt16,
    name       String,
    work_place String,
    age        UInt8,
    depart     String,
    salary     Decimal32(2)
) ENGINE = ReplacingMergeTree()
ORDER BY (emp_id, name)  -- 注意排序key是两个字段
PRIMARY KEY emp_id       -- 主键是一个字段
PARTITION BY work_place
; 

CREATE TABLE emp_collapsingmergetree
(
    emp_id     UInt16 COMMENT '员工id',
    name       String COMMENT '员工姓名',
    work_place String COMMENT '工作地点',
    age        UInt8 COMMENT '员工年龄',
    depart     String COMMENT '部门',
    salary     Decimal32(2) COMMENT '工资',
    sign       Int8
) ENGINE = CollapsingMergeTree(sign)
ORDER BY (emp_id, name)
PARTITION BY work_place
;

CREATE TABLE emp_versioned
(
    emp_id     UInt16 COMMENT '员工id',
    name       String COMMENT '员工姓名',
    work_place String COMMENT '工作地点',
    age        UInt8 COMMENT '员工年龄',
    depart     String COMMENT '部门',
    salary     Decimal32(2) COMMENT '工资',
    sign       Int8,
    version    Int8
) ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY (emp_id, name)
PARTITION BY work_place
;

CREATE TABLE emp_summingmergetree
(
    emp_id     UInt16 COMMENT '员工id',
    name       String COMMENT '员工姓名',
    work_place String COMMENT '工作地点',
    age        UInt8 COMMENT '员工年龄',
    depart     String COMMENT '部门',
    salary     Decimal32(2) COMMENT '工资'
) ENGINE = SummingMergeTree(salary)
ORDER BY (emp_id, name)
PRIMARY KEY emp_id
PARTITION BY work_place
;

-- 使用雾化视图示例,另外一种直接指定表引擎为AggregatingMergeTree
-- 基础明细表
CREATE TABLE emp_mergetree_base
(
    emp_id     UInt16 COMMENT '员工id',
    name       String COMMENT '员工姓名',
    work_place String COMMENT '工作地点',
    age        UInt8 COMMENT '员工年龄',
    depart     String COMMENT '部门',
    salary     Decimal32(2) COMMENT '工资'
) ENGINE = MergeTree()
ORDER BY (emp_id, name)
PARTITION BY work_place
;

-- 聚合物化视图，使用 AggregatingMergeTree 作为表引擎
CREATE MATERIALIZED VIEW view_emp_agg
    ENGINE = AggregatingMergeTree()
        PARTITION BY emp_id
        ORDER BY (emp_id, name)
AS SELECT emp_id,
       name,
       sumState(salary) AS salary
FROM emp_mergetree_base
GROUP BY emp_id, name;

-- 向基础表插入数据
INSERT INTO emp_mergetree_base
VALUES (1, 'tom', '上海', 25, '技术部', 20000),
       (1, 'tom', '上海', 26, '人事部', 10000);

-- 从基础明细表查询明细数据
SELECT * FROM emp_mergetree_base;

-- 从物化视图查询聚合数据
SELECT emp_id, name, sumMerge(salary) AS salary
FROM view_emp_agg
GROUP BY emp_id, name;

[WITH expr_list|(subquery)]
SELECT [DISTINCT] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table (ON <expr_list>)|(USING <column_list>)
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr]
[LIMIT [offset_value, ]n BY columns]
[LIMIT [n, ]m] [WITH TIES]
[UNION ALL ...]
[INTO OUTFILE filename]
[FORMAT format]

-- 在WITH子句中定义一个变量并赋值，然后在SELECT子句中通过别名使用该变量
with '2014-03-17' as dt \
select count(1) from hits_v1 where EventDate = dt;


-- 在WITH子句中定义一个函数，然后在SELECT子句中通过别名使用该函数
with round(Duration / 60)as duration_minutes \
select StartDate, max(duration_minutes) as max_duration_minutes from visits_v1 \
group by StartDate, Duration \
order by max_duration_minutes desc \
limit 10;


-- 在WITH子句中定义一个子查询，然后在SELECT子句中通过别名使用该子查询
-- 该子查询只能返回一行数据
with ( \
  select sum(Duration) from visits_v1 \
) as total_duration \
select StartDate, sum(Duration) / total_duration as duration_percentage from visits_v1 \
group by StartDate, Duration \
limit 10;

CREATE TABLE tutorial.hits_v1 \ 
( 
  ...
)
ENGINE = MergeTree() \ 
PARTITION BY toYYYYMM(EventDate) \ 
ORDER BY (CounterID, EventDate, intHash32(UserID)) \ 
SAMPLE BY intHash32(UserID) \ 
SETTINGS index_granularity = 8192;


-- 按比例采样
-- 采样结果记录数
select count(1) from hits_v1 sample 0.1
-- 采样数据，默认限制返回10000条
select CounterID, UserID, EventDate, EventTime  from hits_v1 sample 0.1
-- 采样数据，限制返回10条
select CounterID, UserID, EventDate, EventTime  from hits_v1 sample 0.1 limit 10


-- 按记录数采样
-- 采样记录数较小时，采样结果数据为0条
select count(1) from hits_v1 sample 100
-- 采样记录数大过索引粒度时，采样结果数据记录数接近采样记录数
elect count(1) from hits_v1 sample 10000
-- 采样数据，默认限制返回10000条
select CounterID, UserID, EventDate, EventTime  from hits_v1 sample 20000
-- 采样数据，限制返回10条
select CounterID, UserID, EventDate, EventTime  from hits_v1 sample 20000 limit 10


-- 按比例和偏移量采样，类似于按比例采样
select CounterID, UserID, EventDate, EventTime  from hits_v1 sample 0.1 offset 0.3 limit 10

-- optimize_move_to_prewhere为1时，表示开始PREWHERE自动优化
select name, value from system.settings where name like '%prewhere%'

----------------------------------------- list类型 ---------------------------------------
-- 不使用ARRAY JOIN
select WatchID, RefererCategories from hits_v1 where WatchID = 4944118417295196513

-- 结果：
┌─────────────WatchID─┬─RefererCategories─┐
│ 4944118417295196513 │ [6,98,456,8586]   │
└─────────────────────┴───────────────────┘


-- 使用ARRAY JOIN
select WatchID, RefererCategories \
from hits_v1 \
array join RefererCategories \
where WatchID = 4944118417295196513;

-- 结果：
─────────────WatchID─┬─RefererCategories─┐
│ 4944118417295196513 │                 6 │
│ 4944118417295196513 │                98 │
│ 4944118417295196513 │               456 │
│ 4944118417295196513 │              8586 │
└─────────────────────┴───────────────────┘


----------------------------------------- nested类型 ---------------------------------------
-- 不使用ARRAY JOIN
select WatchID, ParsedParams.Key1, ParsedParams.Key2 from hits_v1 where WatchID = 5024825574842900819

-- 结果：
┌─────────────WatchID─┬─ParsedParams.Key1───────────┬─ParsedParams.Key2───┐
│ 5024825574842900819 │ ['gen_timestamp','Toolbar'] │ ['group','true" /'] │
└─────────────────────┴─────────────────────────────┴─────────────────────┘


-- 使用ARRAY JOIN
select WatchID, ParsedParams.Key1, ParsedParams.Key2 \
from hits_v1 \
array join ParsedParams \
where WatchID = 5024825574842900819;

-- 结果：
┌─────────────WatchID─┬─ParsedParams.Key1─┬─ParsedParams.Key2─┐
│ 5024825574842900819 │ gen_timestamp     │ group             │
│ 5024825574842900819 │ Toolbar           │ true" /           │
└─────────────────────┴───────────────────┴───────────────────┘

CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory;
INSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21), (2, 22), (3, 31);

-- 排序
SELECT * FROM limit_by ORDER BY id, val
┌─id─┬─val─┐
│  1 │  10 │
│  1 │  11 │
│  1 │  12 │
│  2 │  20 │
│  2 │  21 │
│  2 │  22 │
│  3 │  31 │
└────┴─────┘

-- 分类排序，再在每个分组内取前2条记录
SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id
┌─id─┬─val─┐
│  1 │  10 │
│  1 │  11 │
│  2 │  20 │
│  2 │  21 │
│  3 │  31 │
└────┴─────┘

-- 注意，与LIMIT 2的不同
SELECT * FROM limit_by ORDER BY id, val LIMIT 2
┌─id─┬─val─┐
│  1 │  10 │
│  1 │  11 │
└────┴─────┘

-- 输出到当前目录
-- 默认格式为TSV
-- 注意文件名必须用单引号来括起来，且不能用双引号括起来，否则会报错：Expected string literal
-- 目录下不能存在同名文件，否则会报错
select WatchID, JavaEnable, EventDate  from hits_v1 limit 10 into outfile 'test.tsv'

-- 设置格式为CSV，CSV需要为全大小
select WatchID, JavaEnable, EventDate  from hits_v1 limit 10 into outfile 'out.csv' format CSV

SELECT
   *
FROM imdb.roles
  JOIN imdb.actors_dictionary
  ON imdb.roles.actor_id = imdb.actors_dictionary.id

INSERT INTO helloworld.my_first_table (user_id, message, timestamp, metric) VALUES
    (101, 'Hello, ClickHouse!',                                 now(),       -1.0    ),
    (102, 'Insert a lot of rows per batch',                     yesterday(), 1.41421 ),
    (102, 'Sort your data based on your commonly-used queries', today(),     2.718   ),
    (101, 'Granules are the smallest chunks of data read',      now() + 5,   3.14159 )

ALTER TABLE [<database>.]<table> UPDATE <column> = <expression> WHERE <filter_expr>

-- 示例
ALTER TABLE website.clicks
UPDATE visitor_id = getDict('visitors', 'new_visitor_id', visitor_id)
WHERE visit_date < '2022-01-01'

ALTER TABLE [<database>.]<table> DELETE WHERE <filter_expr>

-- 示例
ALTER TABLE clicks ON CLUSTER main_cluster WHERE visit_date < '2022-01-02 15:00:00' AND page_id = '573'

SET allow_experimental_lightweight_delete = true;

DELETE FROM hits WHERE Title LIKE '%hello%';

-- 1）创建Projection
ALTER TABLE [db].name ADD PROJECTION name ( SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY] )

-- 2）删除Projection
ALTER TABLE [db].name DROP PROJECTION name

-- 3）手动触发物化（首次创建Projection时使用，否则只有新插入数据会自动物化）
ALTER TABLE [db.]table MATERIALIZE PROJECTION name IN PARTITION partition_name

-- 4）删除Projection磁盘文件，但不删除说明（类似Mysql的truncate）
ALTER TABLE [db.]table CLEAR PROJECTION name IN PARTITION partition_name

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] [TO[db.]name] 
[ENGINE = engine] [POPULATE] AS SELECT ...

CREATE MATERIALIZED VIEW order_mv1
ENGINE=SummingMergeTree
PARTITION BY toYYYYMMDD(order_date) ORDER BY (id,order_date)
AS SELECT
id,
order_date,
sum(pay_number) as number,
sum(pay_amount) as amount
FROM order_detail
WHERE order_date > '2021-08-14'
GROUP BY id,order_date;