Clickhouse 基础知识二(基本命令、复合数据类型、TTL、窗口函数以及Array Join)

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)) ;

https://www.cnblogs.com/aresxin/p/clickhouse-ttl.html

 

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 等连接