Clickhouse基础知识⼆（基本命令、复合数据类型、TTL、窗⼝函数以
及ArrayJoin）
1、基本命令
  select bar(number,0,4) from numbers(4);
select now();
数据导⼊：cat t.tsv| clickhouse-client --query "insert into t from tsv"
数据导出：clickhouse-client --query "select * from t" > t.tsv
执⾏多条sql:clickhouse-client -h 192.168.8.101 --multiquery --query="select 10;select 20;select 30;"
clickhouse-client  --host 192.168.8.101 --port 9000 --user default --password --database default --query "select version();select now();" --multiquery --time
2、查看数据类型和函数
select * from system.data_type_families ;
select * from  system.functions;
3、复合数据类型
Clickhouse提供了4种复合数据类型：
数组Array：在同⼀数组内可以包含多种数据类型，但是数据类型必须要兼容；定义表的时候数组需要明确指定元素类型
eg:select [10,200,2000.0,null] as x,toTypeName(x);
元祖tuple: Tuple 元祖类型由1--N个元素组成，每个元素之间允许设置不同的数据类型，且彼此之间要求兼容；在表定义的时候元组也需要明确指定数据类型，且数据要严格匹配。
eg:select tuple(100,'027','wuhan',now()) as x,toTypeName(x);
枚举Enum：枚举固定使⽤（String，Int）这种kV键值对的形式定义数据;1.Key 和Value是不允许重复的，要保证唯⼀性;2.Key 和Value的值都不允许为Null，但是Key允许为空字符串。            eg：create table Enum（Season Enum8 （Spring=1，Summer=2，Fall=3，Winter=4））ENGINE=Memory;
insert into Enum values('Summer');
嵌套Nest：嵌套类型本质是⼀种多维数组的结构。嵌套表中的每个字段都是⼀个数组，并且⾏与⾏之间的数组长度⽆须对齐
eg:create table nested(username String,age UInt8,sex UInt8,address Nested(id UInt8,addr String)) Engine=Memory;
insert into nested values('clickhouse',4,1,[100,101,102],['Russia Moscow','China Beijing','China Wuhan']);
select address.id,address.addr from nested;
4、特殊数据类型
Nullable：Nullable并不能算是⼀种独⽴的数据类型，它更像是⼀种辅助的修饰符，需要与基础数据类型搭配⼀起使⽤，表⽰某个基础数据类型可以使Null值
Nullable类型使⽤注意：
1.只能和基本数据类型使⽤，不能和复合数据类型配合使⽤，不能作为索引字段。
2.慎⽤Nullable类型，包括Nullable的数据表，不然会使查询和写⼊性能变慢。
因为在Clickhouse每个字段的数据会存储为Column.bin⽂件中，若⼀个列字段被Nullable类型修饰之后会额外
字符串函数注册登录⽣成⼀个Column.null.bin⽂件专门保存Null值。即在读取和写⼊数据的时候需要额外的⽂件操作。
5、TTL定时器
在MergeTree中，可以为某个列字段或者整张表设置TTL。当时间达到时，若列字段级别的TTL 则会删除这⼀列的数据；
若表级别的TTL则会删除整张表的数据；若同时设置了列级别的和表级别的TTL则以先到期的为准
TTL time_column + interval 3 DAY
表⽰数据存活的时间为time_column 时间的3天之后。
INTERVAL⽀持的操作：second，minute，hour，day，week，month，quarter，year。
列级别的TTL:
CREATE TABLE t_column_ttl
(
`id` UInt64 COMMENT 'Primary key',
`create_time` Datetime,
`product_desc` String TTL create_time + toIntervalSecond(10),
`product_type` UInt8 TTL create_time + toIntervalSecond(10)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY id
表级别的TTL:
create table t_table_ttl(
id UInt64 comment '主键',
create_time Datetime comment '创建时间',
product_desc String  comment '产品描述' TTL create_time + interval 10 minute,
product_type UInt8 )
engine=MergeTree
partition by toYYYYMM(create_time)
order by create_time
TTL create_time  + INTERVAL 1 MONTH ,
create_time + INTERVAL 1 WEEK TO VOLUME 'default',
create_time + INTERVAL 2 WEEK TO DISK 'default';
TTL表级别测试：
CREATE TABLE orders2
(
`city_id` String,
`city_name` varchar(64),
`users` int,
`orders` int,
`amount` decimal(22, 2),
`createtime` datetime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(createtime)
PRIMARY KEY city_id
ORDER BY (city_id)
TTL createtime + INTERVAL 1 MINUTE ;
ALTER TABLE orders2 MODIFY SETTING merge_with_ttl_timeout = 60;
insert into orders2 values('gz','⼴州',1,1,2.23,now()) ,
('sh','上海',2,2,2.23,(now()-interval 1 minute)),('bj','北京',3,3,3.23,(now()-interval 2 minute)) ;
6、窗⼝函数(window functions)
1.窗⼝函数：
Online Anallytical Processing，联机分析处理，可以对数据库数据进⾏实时分析处理
2.标准SQL语法：
分析函数 over（partition by 列名 order by 列名）
eg: avg(column) over partition by column order by column
3.分析函数分类：
聚合类
avg(列名)、sum(列名)、count(列名)、max(列名)、min(列名)
排名类
row_number() 按照值排序时产⽣⼀个⾃增编号，不会重复
rank() 按照值排序时产⽣⼀个⾃增编号，值相等时会重复，会产⽣空位
dense_rank() 按照值排序时产⽣⼀个⾃增编号，值相等时会重复，不会产⽣空位
注意：排名类分析函数不需要任何参数。
其他类
lag(列名，往前的⾏数,[⾏数为null时的默认值，不指定为null])
lead(列名，往后的⾏数,[⾏数为null时的默认值，不指定为null])
ntile(n) ⽤于将分组数据按照顺序切分成n⽚，返回当前切⽚值，如果切⽚不均匀，默认增加第⼀个切⽚的分布。
7、Array JOIN ⼦句允许在数据表的内部，与数组或者嵌套的字段进⾏JOIN操作，从⽽将⼀⾏数据变多⾏ LEFT Array JOIN ：左连接
Array JOIN ： inner join 等连接

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。