clickhouse教程-09

最新推荐文章于 2024-04-15 02:52:52 发布

nzch

最新推荐文章于 2024-04-15 02:52:52 发布

阅读量2k

点赞数 1

分类专栏： clickhouse

本文链接：https://blog.csdn.net/qq_28764557/article/details/109066379

版权

clickhouse 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

---9-1---

WITH只在查询的时候是有效的，不作为对象储存。

查询1：

查询2:

with定义子查询，这个查询只能返回一行数据，但是可以有多个列。

查询3:子查询不可以使用主查询的，反之可以的。

查询4:

总结：


示例1： 定义常量表达式变量
WITH '2019-08-01 15:23:00' AS timestamp
SELECT 
    number, 
    timestamp
FROM system.numbers
LIMIT 2

┌─number─┬─timestamp───────────┐
│      0 │ 2019-08-01 15:23:00 │
│      1 │ 2019-08-01 15:23:00 │
└────────┴─────────────────────┘
示例2：函数表达式

WITH sum(bytes) AS s
SELECT 
    formatReadableSize(s), 
    table
FROM system.parts
GROUP BY table
ORDER BY s ASC


示例3： 在WITH子句中定义子查询，这里需注意子查询只能返回一行数据。
这个例子将返回TOP10的大表：

WITH 
    (
        SELECT sum(bytes)
        FROM system.parts
        WHERE active
    ) AS total_disk_usage
SELECT 
    (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage, 
    table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10

示例4：WITH子句的作用范围
1）、子查询中不能使用主查询的WITH子句的结果，反之可以
WITH '2020-05-01 15:23:00' AS timestamp
SELECT 
    number, 
    date
FROM 
(
    SELECT 
        number, 
        toDate(timestamp) AS date
    FROM system.numbers
    LIMIT 10
)
上面语句尝试在子查询中引用住查询WITH子句的timestamp字段， 执行这段代码将抛出异常：
Code: 47. DB::Exception: Received from localhost:9001. DB::Exception: Missing columns: 'timestamp' while processing query: 'SELECT number, toDate(timestamp) AS date FROM system.numbers LIMIT 10', required columns: 'number' 'timestamp', source columns: 'number'.


下面的查询可以正常工作：
select date, number
from
(
with '2020-05-01 15:23:00' AS timestamp
select number, toDate(timestamp) as date from system.numbers limit 10
)


2）、主查询不可以使用子查询内部WITH子句的结果
select timestamp, number
from
(
with '2020-05-01 15:23:00' AS timestamp
select number, toDate(timestamp) as date from system.numbers limit 10
)
上面的查询尝试在主查询中使用子查询WITH子句的timestamp字段，执行这段代码将抛出异常：
Code: 47. DB::Exception: Received from localhost:9001. DB::Exception: Missing columns: 'timestamp' while processing query: 'SELECT timestamp, number FROM (WITH '2020-05-01 15:23:00' AS timestamp SELECT number, toDate(timestamp) AS date FROM system.numbers LIMIT 10)', required columns: 'timestamp' 'number', source columns: 'date' 'number'.

正确的使用方式是将WITH子句的结果作为SELECT的列返回：
SELECT 
    timestamp, 
    number
FROM 
(
    WITH '2020-05-01 15:23:00' AS timestamp
    SELECT 
        number, 
        toDate(timestamp) AS date, 
        timestamp
    FROM system.numbers
    LIMIT 10
)

只能是一行数据但是可以包含多个列。

关于with和from:https://blog.csdn.net/vkingnew/article/details/107088370

---9-2---

总结：

示例1：从表中查询数据
SELECT * FROM system.numbers LIMIT 5


示例2： 从子查询查询数据
SELECT number FROM ( SELECT number FROM system.numbers LIMIT 5 )


示例3：从表函数查询数据
SELECT * FROM numbers(0, 5)

---9-3---

只是查询一部分数据的。

要比inidex_granularity(8192)大很多。

例子：

SAMPLE支持如下三种语法：
1. SAMPLE k
k的取值范围在0到1之间。

SELECT
    Title,
    count() * 10 AS PageViews
FROM hits_v1
SAMPLE 0.1
WHERE
    CounterID = 34
GROUP BY Title
ORDER BY PageViews DESC LIMIT 1000

这个示例按照10%的比例采样数据并进行聚合计算，因此聚合计数的结果要放大十倍，这里手工乘以了一个采样系数10。
采样系数的值也可以从表的虚拟列_sample_factor获取，这个虚拟列在建表的时候创建，在查询的时候动态计算值。 


SELECT 
    factor, 
    count() * factor AS cnt
FROM 
(
    SELECT 
        CounterID, 
        _sample_factor AS factor
    FROM hits_v1
    SAMPLE 1 / 10
)
GROUP BY factor

┌─factor─┬─────cnt─┐
│     10 │ 8398890 │
└────────┴─────────┘


2. SAMPLE n

指定行数采样数据。

SELECT count() FROM hits_v1 SAMPLE 100000

┌─count()─┐
│   87591 │
└─────────┘


当指定行数采样数据，用户并不知道采样的百分比，因此无法手工设置采样系数系数的值，但可通过表的虚拟列_sample_factor动态生成采样的值。


示例1： 计算页面访问次数
SELECT sum(PageViews * _sample_factor) FROM visits_v1 SAMPLE 10000000

SELECT sum(PageViews) FROM visits_v1 ;

┌─sum(multiply(PageViews, _sample_factor))─┐
│                                  6754136 │
└──────────────────────────────────────────┘
示例2： 计算访问数
SELECT sum(_sample_factor) FROM visits_v1 SAMPLE 1000000
SELECT count() FROM visits_v1

┌─sum(_sample_factor)─┐
│  1651842.9757234894 │
└─────────────────────┘

示例3：计算会话平均持续时间
Note：这里不需要使用采样系数计算平均值。
SELECT avg(Duration) FROM visits_v1 SAMPLE 10000000

┌──────avg(Duration)─┐
│ 361.43725409203375 │
└────────────────────┘


3. SAMPLE k OFFSET m

根据采样比例和偏移比例采样数据， 这里的k和m都是0到1之间的数字，其中k表示采样的比例，m表示偏移多少比例的数据才开始采样。

示例1：
SAMPLE 1/10
这个例子将采样10%的数据：
[++------------------]

示例2：
SAMPLE 1/10 OFFSET 1/2
这个例子，查询会从数据的中间开始采样，采样10%的数据。
[----------++--------]


SELECT 
    factor, 
    count() * factor AS cnt
FROM 
(
    SELECT 
        CounterID, 
        _sample_factor AS factor
    FROM hits_v1
    SAMPLE 1 / 10 OFFSET 5 / 10
)
GROUP BY factor

┌─factor─┬─────cnt─┐
│     10 │ 8954720 │
└────────┴─────────┘


Note：这里的OFFSET不能设置的过大，否则可能会因为采样的数据不足导致最终的计算结果偏差很大。 
例如：SAMPLE 5 / 10 OFFSET 9 / 10， 这个表示从数据偏移90%的位置采样50%的数据，但是从数据偏移90%的位置最多只能采集10%的数据。
SAMPLE 0.5采集的数据：
[++++++++++----------]
SAMPLE 0.5 OFFSET 0.9采集的数据：
[------------------++]

关于sample:https://blog.csdn.net/vkingnew/article/details/107090352

---9-4---

下面通过示例来演示ARRAY JOIN的使用。
创建表和生成测试数据：
drop table arrays_test;
CREATE TABLE arrays_test
(
    s String,
    arr Array(UInt8)   
) ENGINE = Memory;

INSERT INTO arrays_test
VALUES ('Hello', [1,2]), ('World', [3,4,5]), ('Goodbye', []);
SELECT *
FROM arrays_test

┌─s───────┬─arr─────┐
│ Hello   │ [1,2]   │
│ World   │ [3,4,5] │
│ Goodbye │ []      │
└─────────┴─────────┘



示例1： ARRAY JOIN
SELECT 
    s, 
    arr
FROM arrays_test
ARRAY JOIN arr

┌─s─────┬─arr─┐
│ Hello │   1 │
│ Hello │   2 │
│ World │   3 │
│ World │   4 │
│ World │   5 │
└───────┴─────┘
从这个例子可以看到， 每一行拆分为多行，每一行拆分后的行数为数组的大小。即一行裂变为多行，裂变后的行数为该行数组列的数组大小。
这里JOIN结果不包含空数组的行。


示例2： LEFT ARRAY JOIN
SELECT 
    s, 
    arr
FROM arrays_test
LEFT ARRAY JOIN arr 

┌─s───────┬─arr─┐
│ Hello   │   1 │
│ Hello   │   2 │
│ World   │   3 │
│ World   │   4 │
│ World   │   5 │
│ Goodbye │   0 │
└─────────┴─────┘

这里JOIN结果包含空数组的行，数组的元素设置为默认的0。





当在ARRAY JOIN子句中为数组指定别名，则使用别名访问数组中元素， 通过原始名称访问数组本身。
SELECT 
    s, 
    arr, 
    a
FROM arrays_test
ARRAY JOIN arr AS a

┌─s─────┬─arr─────┬─a─┐
│ Hello │ [1,2]   │ 1 │
│ Hello │ [1,2]   │ 2 │
│ World │ [3,4,5] │ 3 │
│ World │ [3,4,5] │ 4 │
│ World │ [3,4,5] │ 5 │
└───────┴─────────┴───┘


用户可使用别名实现与外部数组的JOIN。例如：
SELECT s, arr_external FROM arrays_test ARRAY JOIN [1, 2, 3] AS arr_external

┌─s───────┬─arr_external─┐
│ Hello   │            1 │
│ Hello   │            2 │
│ Hello   │            3 │
│ World   │            1 │
│ World   │            2 │
│ World   │            3 │
│ Goodbye │            1 │
│ Goodbye │            2 │
│ Goodbye │            3 │
└─────────┴──────────────┘


ARRAY JOIN中可指定多个数组，数组之间使用逗号分隔， 每个数组的大小必须相等。 多个数组的ARRAY JOIN不会产生笛卡尔积，他们之间的元素是按行一一对应和按行拆分的。
SELECT 
    s, 
    arr, 
    a, 
    num, 
    mapped
FROM arrays_test
ARRAY JOIN 
    arr AS a, 
    arrayEnumerate(arr) AS num, 
    arrayMap(x -> (x + 1), arr) AS mapped

┌─s─────┬─arr─────┬─a─┬─num─┬─mapped─┐
│ Hello │ [1,2]   │ 1 │   1 │      2 │
│ Hello │ [1,2]   │ 2 │   2 │      3 │
│ World │ [3,4,5] │ 3 │   1 │      4 │
│ World │ [3,4,5] │ 4 │   2 │      5 │
│ World │ [3,4,5] │ 5 │   3 │      6 │
└───────┴─────────┴───┴─────┴────────┘


 arrayEnumerate的使用示例：
SELECT 
    s, 
    arr, 
    a, 
    num, 
    arrayEnumerate(arr)
FROM arrays_test
ARRAY JOIN 
    arr AS a, 
    arrayEnumerate(arr) AS num

┌─s─────┬─arr─────┬─a─┬─num─┬─arrayEnumerate(arr)─┐
│ Hello │ [1,2]   │ 1 │   1 │ [1,2]               │
│ Hello │ [1,2]   │ 2 │   2 │ [1,2]               │
│ World │ [3,4,5] │ 3 │   1 │ [1,2,3]             │
│ World │ [3,4,5] │ 4 │   2 │ [1,2,3]             │
│ World │ [3,4,5] │ 5 │   3 │ [1,2,3]             │
└───────┴─────────┴───┴─────┴─────────────────────┘


嵌套数据类型ARRAY JOIN和数组类似，下面通过示例来演示嵌套类型ARRAY JOIN的使用。 
创建嵌套类型的表并插入测试数据：
CREATE TABLE nested_test
(
    s String,
    nest Nested(
    x UInt8,
    y UInt32)
) ENGINE = Memory;

INSERT INTO nested_test
VALUES ('Hello', [1,2], [10,20]), ('World', [3,4,5], [30,40,50]), ('Goodbye', [], []);
SELECT * FROM nested_test

┌─s───────┬─nest.x──┬─nest.y─────┐
│ Hello   │ [1,2]   │ [10,20]    │
│ World   │ [3,4,5] │ [30,40,50] │
│ Goodbye │ []      │ []         │
└─────────┴─────────┴────────────┘


执行ARRAY JOIN：
SELECT s, nest.x, nest.y FROM nested_test ARRAY JOIN nest;

┌─s─────┬─nest.x─┬─nest.y─┐
│ Hello │      1 │     10 │
│ Hello │      2 │     20 │
│ World │      3 │     30 │
│ World │      4 │     40 │
│ World │      5 │     50 │
└───────┴────────┴────────┘
下面语句执行结果和上面示例一致，可简单理解为两个数组的ARRAY JOIN：
SELECT s, nest.x, nest.y FROM nested_test ARRAY JOIN nest.x, nest.y;

┌─s─────┬─nest.x─┬─nest.y─┐
│ Hello │      1 │     10 │
│ Hello │      2 │     20 │
│ World │      3 │     30 │
│ World │      4 │     40 │
│ World │      5 │     50 │
└───────┴────────┴────────┘


下面是语句， 只和嵌套接收的x字段做JOIN， 因此嵌套结构的y字段不会被拆分：
SELECT s, nest.x, nest.y FROM nested_test ARRAY JOIN nest.x;

┌─s─────┬─nest.x─┬─nest.y─────┐
│ Hello │      1 │ [10,20]    │
│ Hello │      2 │ [10,20]    │
│ World │      3 │ [30,40,50] │
│ World │      4 │ [30,40,50] │
│ World │      5 │ [30,40,50] │
└───────┴────────┴────────────┘


使用别名：
SELECT s, n.x, n.y, nest.x, nest.y FROM nested_test ARRAY JOIN nest AS n;

┌─s─────┬─n.x─┬─n.y─┬─nest.x──┬─nest.y─────┐
│ Hello │   1 │  10 │ [1,2]   │ [10,20]    │
│ Hello │   2 │  20 │ [1,2]   │ [10,20]    │
│ World │   3 │  30 │ [3,4,5] │ [30,40,50] │
│ World │   4 │  40 │ [3,4,5] │ [30,40,50] │
│ World │   5 │  50 │ [3,4,5] │ [30,40,50] │
└───────┴─────┴─────┴─────────┴────────────┘
arrayEnumerate使用：
SELECT s, n.x, n.y, nest.x, nest.y, num FROM nested_test ARRAY JOIN nest AS n, arrayEnumerate(nest.x) AS num;

┌─s─────┬─n.x─┬─n.y─┬─nest.x──┬─nest.y─────┬─num─┐
│ Hello │   1 │  10 │ [1,2]   │ [10,20]    │   1 │
│ Hello │   2 │  20 │ [1,2]   │ [10,20]    │   2 │
│ World │   3 │  30 │ [3,4,5] │ [30,40,50] │   1 │
│ World │   4 │  40 │ [3,4,5] │ [30,40,50] │   2 │
│ World │   5 │  50 │ [3,4,5] │ [30,40,50] │   3 │
└───────┴─────┴─────┴─────────┴────────────┴─────┘

arrayEnumerate:https://blog.csdn.net/jarry_cm/article/details/106114863

---9-5---

测试数据集：
drop table t_first;
drop table t_second;
drop table t_third;
create table t_first(id String, area_id String, score UInt8) ENGINE=TinyLog;
create table t_second(id String, name String, age UInt8) ENGINE=TinyLog;
create table t_third(area_id String, city String) ENGINE=TinyLog;


insert into t_first values('id001', '025', 100);
insert into t_first values('id002', '0551', 90);
insert into t_first values('id003', '010', 80);

insert into t_second values('id001', 'xiaohe', 22);
insert into t_second values('id002', 'xiaojiang', 23);
insert into t_second values('id003', 'xiaohai', 34);


insert into t_third values('025', '南京');
insert into t_third values('0551', '合肥');
insert into t_third values('010', '北京');

数据：
SELECT * FROM t_first
┌─id────┬─area_id─┬─score─┐
│ id001 │ 025     │   100 │
│ id002 │ 0551    │    90 │
│ id003 │ 010     │    80 │
└───────┴─────────┴───────┘

SELECT * FROM t_second
┌─id────┬─name──────┬─age─┐
│ id001 │ xiaohe    │  22 │
│ id002 │ xiaojiang │  23 │
│ id003 │ xiaohai   │  34 │
└───────┴───────────┴─────┘

SELECT * FROM t_third
┌─area_id─┬─city─┐
│ 025     │ 南京 │
│ 0551    │ 合肥 │
│ 010     │ 北京 │
└─────────┴──────┘


使用JOIN ON语法关联：
SELECT 
    t1.id, 
    t1.area_id, 
    t2.name, 
    t2.age, 
    t3.city
FROM t_first AS t1
LEFT JOIN t_second AS t2 ON t1.id = t2.id
LEFT JOIN t_third AS t3 ON t1.area_id = t3.area_id

┌─t1.id─┬─t1.area_id─┬─t2.name───┬─t2.age─┬─t3.city─┐
│ id001 │ 025        │ xiaohe    │     22 │ 南京    │
│ id002 │ 0551       │ xiaojiang │     23 │ 合肥    │
│ id003 │ 010        │ xiaohai   │     34 │ 北京    │
└───────┴────────────┴───────────┴────────┴─────────┘
使用JOIN USING关联：
SELECT 
    t1.id, 
    t1.area_id, 
    t2.name, 
    t2.age
FROM t_first AS t1
LEFT JOIN t_second AS t2 USING id;
Note: USING只支持两张表之间的关联。


多表之间逗号分隔的JOIN：
SELECT t1.id, t1.area_id, t2.name, t2.age, t3.city
FROM t_first AS t1, t_second AS t2, t_third AS t3
WHERE t1.id = t2.id AND t1.area_id = t3.area_id

---9-6---

关于left join和right join：https://www.cnblogs.com/bad-robot/p/9788959.html




准备测试数据：
drop table t_left;
drop table t_right;
create table t_left(id String, name String, ev_time DateTime, event String) ENGINE=TinyLog;
create table t_right(id String, name String, ev_time DateTime, event String) ENGINE=TinyLog;

insert into t_left values
('1', 'xiaojiang', '2020-03-25 23:25:55', 'event_left_1'),
('2', 'xiaojiang', '2020-03-25 23:25:55', 'event_left_2_1')
('2', 'xiaojiang', '2020-03-25 23:27:50', 'event_left_2_2')
('3', 'xiaojiang', '2020-03-25 23:26:22', 'event_left_3');

insert into t_right values
('1', 'xiaojiang', '2020-03-25 23:25:20', 'event_right_1_1'),
('1', 'xiaojiang', '2020-03-25 23:25:55', 'event_right_1_2')
('2', 'xiaojiang', '2020-03-25 23:25:55', 'event_right_2_1')
('2', 'xiaojiang', '2020-03-25 23:26:55', 'event_right_2_2')
('2', 'xiaojiang', '2020-03-25 23:27:55', 'event_right_2_3');


1 ALL
如果右表具有多个匹配的行， ClickHouse将从匹配的行创建笛卡尔积。这是SQL中的标准JOIN行为。
t_left表的数据：
SELECT * FROM t_left
┌─id─┬─name──────┬─────────────ev_time─┬─event──────────┐
│ 1  │ xiaojiang │ 2020-03-25 23:25:55 │ event_left_1   │
│ 2  │ xiaojiang │ 2020-03-25 23:25:55 │ event_left_2_1 │
│ 2  │ xiaojiang │ 2020-03-25 23:27:50 │ event_left_2_2 │
│ 3  │ xiaojiang │ 2020-03-25 23:26:22 │ event_left_3   │
└────┴───────────┴─────────────────────┴────────────────┘
t_right表的数据：

SELECT * FROM t_right
┌─id─┬─name──────┬─────────────ev_time─┬─event───────────┐
│ 1  │ xiaojiang │ 2020-03-25 23:25:20 │ event_right_1_1 │
│ 1  │ xiaojiang │ 2020-03-25 23:25:55 │ event_right_1_2 │
│ 2  │ xiaojiang │ 2020-03-25 23:25:55 │ event_right_2_1 │
│ 2  │ xiaojiang │ 2020-03-25 23:26:55 │ event_right_2_2 │
│ 2  │ xiaojiang │ 2020-03-25 23:27:55 │ event_right_2_3 │
└────┴───────────┴─────────────────────┴─────────────────┘
ALL关联示例：
SELECT 
    t1.id AS id, 
    t1.event AS event1, 
    t2.event AS event2
FROM t_left AS t1
ALL LEFT JOIN t_right AS t2 ON t1.id = t2.id
ORDER BY id ASC

┌─id─┬─event1─────────┬─event2──────────┐
│ 1  │ event_left_1   │ event_right_1_1 │
│ 1  │ event_left_1   │ event_right_1_2 │
│ 2  │ event_left_2_1 │ event_right_2_1 │
│ 2  │ event_left_2_1 │ event_right_2_2 │
│ 2  │ event_left_2_1 │ event_right_2_3 │
│ 2  │ event_left_2_2 │ event_right_2_1 │
│ 2  │ event_left_2_2 │ event_right_2_2 │
│ 2  │ event_left_2_2 │ event_right_2_3 │
│ 3  │ event_left_3   │                 │
└────┴────────────────┴─────────────────┘


2 ANY 
如果右表具有多个匹配行，则仅连接找到的第一个行。如果右表只有一个匹配行，则使用ANY和ALL关键字的查询结果是相同的。
SELECT 
    t1.id AS id, 
    t1.event AS event1, 
    t2.event AS event2
FROM t_left AS t1
ANY LEFT JOIN t_right AS t2 ON t1.id = t2.id
ORDER BY id ASC

┌─id─┬─event1─────────┬─event2──────────┐
│ 1  │ event_left_1   │ event_right_1_1 │
│ 2  │ event_left_2_1 │ event_right_2_1 │
│ 2  │ event_left_2_2 │ event_right_2_1 │
│ 3  │ event_left_3   │                 │
└────┴────────────────┴─────────────────┘


3 ASOF  
用于连接不完全匹配的序列。

用于ASOF JOIN的表必须具有满足有序序列的特性的列， 这些数据类型包括：UInt32、UInt64、Float32、Float64、Date和DateTime。
ASOF JOIN不支持JOIN引擎表。


ASOF JON ...ON语法：
SELECT expressions_list
FROM table_1
ASOF LEFT JOIN table_2
ON equi_cond AND closest_match_cond

ASOF JON支持任意个（1个或以上）相等条件和有且仅一个的不等匹配条件（最接近的匹配条件）。
不等匹配条件（最接近的匹配条件）支持：>、>=、< 和 <=。

例如：
SELECT count() FROM table_1 ASOF LEFT JOIN table_2 ON table_1.a == table_2.b AND table_2.t <= table_1.t
ASOF JOIN ... USING的语法：
SELECT expressions_list
FROM table_1
ASOF JOIN table_2
USING (equi_column1, ... equi_columnN, asof_column)

ASOF JOIN使用equi_columnX进行相等条件连接，使用asof_column进行最接近的匹配，匹配条件为table_1.asof_column >= table_2.asof_column。asof_column是USING子句中最后一个字段。从ASOF JOIN ...USING语法，我们可以看出它的使用不如ASOF JOIN ...ON灵活，但是语句相对简单。


┌─id─┬────────ev_time──────┬─event_left─────┬    ┬───id─┬───────ev_time───────┬─event_right─────┐
│ 1  │ 2020-03-25 23:25:55 │ event_left_1   │    │ 1    │ 2020-03-25 23:25:20 │ event_right_1_1 │
│ 2  │ 2020-03-25 23:25:55 │ event_left_2_1 │    │ 1    │ 2020-03-25 23:25:55 │ event_right_1_2 │
│ 2  │ 2020-03-25 23:27:50 │ event_left_2_2 │    │ 2    │ 2020-03-25 23:25:55 │ event_right_2_1 │
└────┴─────────────────────┴────────────────┴    │ 2    │ 2020-03-25 23:26:55 │ event_right_2_2 │
                                                 │ 2    │ 2020-03-25 23:27:55 │ event_right_2_3 │
                                                 ┴──────┴─────────────────────┴─────────────────┘
例如上面的数据， 使用id作为相等条件， ev_time为不等条件， 这里以id=1且left.ev_time>=right.ev_time为例， 右侧id=1虽然两条记录都满足event_left>=event_right，但是很明显右侧的2020-03-25 23:25:55和左侧的2020-03-25 23:25:55更接近，因此右侧只匹配ev_time为2020-03-25 23:25:55的记录。

注意返回的是最接近的记录的。


ASOF JOIN ... ON示例：
SELECT 
    a.id, 
    a.ev_time AS ev_time_left, 
    b.ev_time AS ev_time_right, 
    a.event AS event_left, 
    b.event AS event_right
FROM t_left AS a
ASOF INNER JOIN t_right AS b ON (a.id = b.id) AND (a.name = b.name) AND (a.ev_time >= b.ev_time)


┌─id─┬────────ev_time_left─┬───────ev_time_right─┬─event_left─────┬─event_right─────┐
│ 1  │ 2020-03-25 23:25:55 │ 2020-03-25 23:25:55 │ event_left_1   │ event_right_1_2 │
│ 2  │ 2020-03-25 23:25:55 │ 2020-03-25 23:25:55 │ event_left_2_1 │ event_right_2_1 │
│ 2  │ 2020-03-25 23:27:50 │ 2020-03-25 23:26:55 │ event_left_2_2 │ event_right_2_2 │
└────┴─────────────────────┴─────────────────────┴────────────────┴─────────────────┘


ASOF JOIN ... USING示例：

SELECT 
    a.id, 
    a.ev_time AS ev_time_left, 
    b.ev_time AS ev_time_right, 
    a.event AS event_left, 
    b.event AS event_right
FROM t_left AS a
ASOF INNER JOIN t_right AS b USING (id, name, ev_time)

┌─id─┬────────ev_time_left─┬───────ev_time_right─┬─event_left─────┬─event_right─────┐
│ 1  │ 2020-03-25 23:25:55 │ 2020-03-25 23:25:55 │ event_left_1   │ event_right_1_2 │
│ 2  │ 2020-03-25 23:25:55 │ 2020-03-25 23:25:55 │ event_left_2_1 │ event_right_2_1 │
│ 2  │ 2020-03-25 23:27:50 │ 2020-03-25 23:26:55 │ event_left_2_2 │ event_right_2_2 │
└────┴─────────────────────┴─────────────────────┴────────────────┴─────────────────┘


ASOF Summary：
（1）、 不能使用多个不等条件。
（2）、可使用多个相等条件。
（3）、如果有多个满足条件的记录，只会返回最接近的记录。

其他的：

USING的用法：字段最后是不等，前面是相等。

---9-7---

空值的处理：

（1）、空值的填充
默认情况下， 空值使用对应数据类型的默认值填充。


测试数据生成：
drop table t_null_left;
drop table t_null_right;
create table t_null_left(id Nullable(UInt8), name String) ENGINE=TinyLog;
create table t_null_right(id Nullable(UInt8), score UInt8) ENGINE=TinyLog;

insert into t_null_left values
(1, 'xiaohe')
(2, 'xiaojiang')
;

insert into t_null_right values
(1, 88)
;

关联的两张表的数据如下：
SELECT * FROM t_null_left

┌─id─┬─name──────┐
│  1 │ xiaohe    │
│  2 │ xiaojiang │
└────┴───────────┘


SELECT * FROM t_null_right

┌─id─┬─score─┐
│  1 │    88 │
└────┴───────┘


两表左关联：
SELECT 
    t1.id, 
    t1.name, 
    t2.score
FROM t_null_left AS t1
LEFT JOIN t_null_right AS t2 ON t1.id = t2.id

┌─id─┬─name──────┬─score─┐
│  1 │ xiaohe    │    88 │
│  2 │ xiaojiang │     0 │
└────┴───────────┴───────┘
从关联的输出可以看出，左侧表（t_null_left）id为2的记录在右侧表(t_null_right)中没有与之关联的记录， 这里的score填充了UInt的默认值0。
用户可设置join_use_nulls参数，将空值填充为NULL。join_use_nulls设置默认为0，填充相应数据类型的默认值。
设置join_use_nulls示例：

ᴺᵁᴸᴸ │

SET join_use_nulls = 1;

SELECT t1.id, t1.name, t2.score
FROM t_null_left AS t1
LEFT JOIN t_null_right AS t2 ON t1.id = t2.id

┌─id─┬─name──────┬─score─┐
│  1 │ xiaohe    │    88 │
│  2 │ xiaojiang │  ᴺᵁᴸᴸ │
└────┴───────────┴───────┘

当设置了join_use_nulls，空值使用NULL填充。


（2）、NULL值关联

如果量表关联的key只要有一个为NULL，则该行不会进行JOIN。
准备数据：
drop table t_null_left;
drop table t_null_right;
create table t_null_left(id Nullable(UInt8), name String) ENGINE=TinyLog;
create table t_null_right(id Nullable(UInt8), score UInt8) ENGINE=TinyLog;

insert into t_null_left values
(1, 'xiaohe')
(NULL, 'xiaojiang')
;

insert into t_null_right values
(1, 88)
(NULL, 90)
;

两表的数据：
SELECT * FROM t_null_left;

┌───id─┬─name──────┐
│    1 │ xiaohe    │
│ ᴺᵁᴸᴸ │ xiaojiang │
└──────┴───────────┘

SELECT * FROM t_null_right;

┌───id─┬─score─┐
│    1 │    88 │
│ ᴺᵁᴸᴸ │    90 │
└──────┴───────┘


两表关联：
SELECT 
    t1.id, 
    t1.name, 
    t2.score
FROM t_null_left AS t1
INNER JOIN t_null_right AS t2 ON t1.id = t2.id;

┌─id─┬─name───┬─score─┐
│  1 │ xiaohe │    88 │
└────┴────────┴───────┘

---9-8---

1. 验证PREWHERE读取数据的大小
（1）、关闭PREWHERE的自动转化
SET optimize_move_to_prewhere = 0
（2）、使用WHERE查询
SELECT UserID, WatchID, Title FROM hits_v1 WHERE WatchID = 9126318258181481721;

┌─────────────UserID─┬─────────────WatchID─┬─Title───────────────────────────────────────────────────────────┐
│ 610708775678702928 │ 9126318258181481721 │ вышивка в Москве - Образовать фото мочия - Почта Mail.Ru: Силва │
└────────────────────┴─────────────────────┴─────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.094 sec. Processed 8.87 million rows, 908.92 MB (94.08 million rows/s., 9.64 GB/s.) 



（3）、使用PREWHERE的查询
SELECT UserID, WatchID, Title FROM hits_v1 PREWHERE WatchID = 9126318258181481721;

┌─────────────UserID─┬─────────────WatchID─┬─Title───────────────────────────────────────────────────────────┐
│ 610708775678702928 │ 9126318258181481721 │ вышивка в Москве - Образовать фото мочия - Почта Mail.Ru: Силва │
└────────────────────┴─────────────────────┴─────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.017 sec. Processed 8.87 million rows, 70.99 MB (533.89 million rows/s., 4.27 GB/s.) 
对比两种查询， WHERE读取了908.92 MB数据， PREWHERE只读取了70.99 MB数据，PREWHERE的耗时也明显更少。


2. 验证PREWHERE条件的自动转移
该示例要确保optimize_move_to_prewhere设置为1，即默认设置。
执行语句：

SELECT UserID, WatchID, Title FROM hits_v1 PREWHERE WatchID = 9126318258181481721;
查看server的日志，可以观察到WHERE条件转移到PREWHERE子句。


2020.03.26 17:01:02.784134 [ 73 ] {cd075c5e-45d8-4b64-bb99-fcc183c29b1c} <Debug> executeQuery: (from 127.0.0.1:37418) SELECT UserID, WatchID, Title FROM hits_v1 WHERE WatchID = 9126318258181481721
2020.03.26 17:01:02.785055 [ 73 ] {cd075c5e-45d8-4b64-bb99-fcc183c29b1c} <Debug> InterpreterSelectQuery: MergeTreeWhereOptimizer: condition "WatchID = 9126318258181481721" moved to PREWHERE
2020.03.26 17:01:02.785603 [ 73 ] {cd075c5e-45d8-4b64-bb99-fcc183c29b1c} <Debug> tutorial.hits_v1 (SelectExecutor): Key condition: unknown
...
2020.03.26 17:01:02.788193 [ 73 ] {cd075c5e-45d8-4b64-bb99-fcc183c29b1c} <Debug> executeQuery: Query pipeline:
Union
 Expression × 20
  Expression
   MergeTreeThread

日志在哪里：

---9-9---

1. NULL值处理
ClickHouse将NULL视为一个特殊的值进行聚合，即在聚合时列中的所有NULL值归为同一组。
下面通过具体的示例演示NULL值的使用。
准备数据：
drop table t_null;
create table t_null(key Nullable(UInt8), value UInt8) ENGINE=TinyLog;
insert into t_null values
(2, 1) (NULL, 2) (2, 3) (3,3) (NULL, 3);
数据查看：
SELECT * FROM t_null

┌──key─┬─value─┐
│    2 │     1 │
│ ᴺᵁᴸᴸ │     2 │
│    2 │     3 │
│    3 │     3 │
│ ᴺᵁᴸᴸ │     3 │
└──────┴───────┘
基于key的sum聚合：
SELECT key, sum(value) AS sum FROM t_null GROUP BY key;

┌──key─┬─sum─┐
│    2 │   4 │
│    3 │   3 │
│ ᴺᵁᴸᴸ │   5 │
└──────┴─────┘


2. WITH TOTALS修饰符
当指定WITH TOTALS修饰符，聚合查询将计算所有行的总计数。
SELECT 
    UserID, 
    count(*) AS cnt
FROM hits_v1
GROUP BY UserID
    WITH TOTALS
HAVING count() > 10
LIMIT 3

┌──────────────UserID─┬─cnt─┐
│  349463948327386591 │ 139 │
│ 3479937375504503991 │ 145 │
│ 3873537644544582407 │ 920 │
└─────────────────────┴─────┘

Extremes:
┌─UserID─┬─────cnt─┐
│      0 │ 8640288 │
└────────┴─────────┘



3. WITH ROLLUP修饰符
ROLLUP用于分组统计，用于计算每个分组以及上层分组的合计。
构造数据：
drop table t_rollup;
create table t_rollup(prov String, city String, num UInt64) ENGINE=TinyLog;
insert into t_rollup values
('anhui', 'tongling', 123) ('anhui', 'tongling', 100) ('anhui', 'tongling', 200)
('anhui', 'hefei', 100) ('anhui', 'tongling', 200) ('anhui', 'tongling', 300)
('jiangsu', 'nanjing', 10) ('jiangsu', 'nanjing', 20) ('jiangsu', 'nanjing', 30)
('jiangsu', 'suzhou', 5) ('jiangsu', 'suzhou', 10) ('jiangsu', 'suzhou', 15)
;
查看数据：
SELECT * FROM t_rollup ORDER BY prov, city;

┌─prov────┬─city─────┬─num─┐
│ anhui   │ hefei    │ 100 │
│ anhui   │ tongling │ 123 │
│ anhui   │ tongling │ 100 │
│ anhui   │ tongling │ 200 │
│ anhui   │ tongling │ 200 │
│ anhui   │ tongling │ 300 │
│ jiangsu │ nanjing  │  10 │
│ jiangsu │ nanjing  │  20 │
│ jiangsu │ nanjing  │  30 │
│ jiangsu │ suzhou   │   5 │
│ jiangsu │ suzhou   │  10 │
│ jiangsu │ suzhou   │  15 │
└─────────┴──────────┴─────┘
WITH ROLLUP聚合：
SELECT prov, city, sum(num) AS sum FROM t_rollup GROUP BY prov, city WITH ROLLUP;

┌─prov────┬─city─────┬─sum─┐
│ anhui   │ hefei    │ 100 │
│ jiangsu │ nanjing  │  60 │
│ jiangsu │ suzhou   │  30 │
│ anhui   │ tongling │ 923 │
└─────────┴──────────┴─────┘
┌─prov────┬─city─┬──sum─┐
│ jiangsu │      │   90 │
│ anhui   │      │ 1023 │
└─────────┴──────┴──────┘
┌─prov─┬─city─┬──sum─┐
│      │      │ 1113 │
└──────┴──────┴──────┘
上面的 ROLLUP类似于如下语句：
SELECT prov, city, sum(num) AS sum FROM t_rollup GROUP BY prov, city
UNION ALL
SELECT prov, NULL, sum(num) AS sum FROM t_rollup GROUP BY prov, NULL
UNION ALL
SELECT NULL, NULL, sum(num) AS sum FROM t_rollup GROUP BY NULL, NULL;


4. WITH CUBE修饰符
CUBE翻译成中文是立方体，用于聚合统计时，表示每个维度之间自由组合计算聚合值。
例如当需要对维度(A,B,C)计算CUBE的聚合，则会生成如下维度的组合：(A,B,C)、(A,B)、(A,C)、(B,C)、(A)、(B)、(C)和全表聚合。
使用示例：
SELECT prov, city, sum(num) AS sum FROM t_rollup GROUP BY prov, city WITH CUBE;

┌─prov────┬─city─────┬─sum─┐
│ anhui   │ hefei    │ 100 │
│ jiangsu │ nanjing  │  60 │
│ jiangsu │ suzhou   │  30 │
│ anhui   │ tongling │ 923 │
└─────────┴──────────┴─────┘
┌─prov────┬─city─┬──sum─┐
│ jiangsu │      │   90 │
│ anhui   │      │ 1023 │
└─────────┴──────┴──────┘
┌─prov─┬─city─────┬─sum─┐
│      │ nanjing  │  60 │
│      │ suzhou   │  30 │
│      │ hefei    │ 100 │
│      │ tongling │ 923 │
└──────┴──────────┴─────┘
┌─prov─┬─city─┬──sum─┐
│      │      │ 1113 │
└──────┴──────┴──────┘

文档：

---9-10---

示例：
生成测试数据：
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);
查看表数据：
SELECT * FROM limit_by

┌─id─┬─val─┐
│  1 │  10 │
│  1 │  11 │
│  1 │  12 │
│  2 │  20 │
│  2 │  21 │
└────┴─────┘
LIMIT BY查询，从每个数据块的开头最多返回2行记录：
SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id;

┌─id─┬─val─┐
│  1 │  10 │
│  1 │  11 │
│  2 │  20 │
│  2 │  21 │
└────┴─────┘
LIMIT BY查询，从每个数据块的开头跳过1个数据块，最多返回2行记录：

SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id;

┌─id─┬─val─┐
│  1 │  11 │
│  1 │  12 │
│  2 │  21 │
└────┴─────┘
上面的语句，也可以改写为：
SELECT
 * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id;

下面的查询返回每个domain和device_type的前5个referrer，最多返回100行：
SELECT
    domainWithoutWWW(URL) AS domain,
    domainWithoutWWW(REFERRER_URL) AS referrer,
    device_type,
    count() cnt
FROM hits
GROUP BY domain, referrer, device_type
ORDER BY cnt DESC
LIMIT 5 BY domain, device_type
LIMIT 100

mysql的分页：https://blog.csdn.net/bandaoyu/article/details/89844673

---9-11---

示例：
生成测试数据：
drop table t_order_by;
create table t_order_by(x UInt8, y Nullable(Int64), factor UInt8, z Nullable(Float32)) ENGINE=TinyLog;

insert into t_order_by values
(1, null, 1, 3.2)
(2, 2, 1,4.9)
(3, 3, 1,2.3)
(4, 0, 0,8.9)
(5, 7, 1,10.2)
(6, 6, 1,20.8)
(7, 18, 1, null)
(8, 0, 0, NAN)
(9, null, 1, 28.1)
(10, 29, 1, NAN)
;
查看数据：
SELECT * FROM t_order_by

┌──x─┬────y─┬─factor─┬────z─┐
│  1 │ ᴺᵁᴸᴸ │      1 │  3.2 │
│  2 │    2 │      1 │  4.9 │
│  3 │    3 │      1 │  2.3 │
│  4 │    0 │      0 │  8.9 │
│  5 │    7 │      1 │ 10.2 │
│  6 │    6 │      1 │ 20.8 │
│  7 │   18 │      1 │ ᴺᵁᴸᴸ │
│  8 │    0 │      0 │  nan │
│  9 │ ᴺᵁᴸᴸ │      1 │ 28.1 │
│ 10 │   29 │      1 │  nan │
└────┴──────┴────────┴──────┘
排序：
SELECT y / factor AS r FROM t_order_by ORDER BY r ASC NULLS FIRST

┌────r─┐
│ ᴺᵁᴸᴸ │
│ ᴺᵁᴸᴸ │
│  nan │
│  nan │
│    2 │
│    3 │
│    6 │
│    7 │
│   18 │
│   29 │
└──────┘
SELECT y / factor AS r FROM t_order_by ORDER BY r ASC NULLS LAST

┌────r─┐
│    2 │
│    3 │
│    6 │
│    7 │
│   18 │
│   29 │
│  nan │
│  nan │
│ ᴺᵁᴸᴸ │
│ ᴺᵁᴸᴸ │
└──────┘

---9-12---

示例：
创建测试数据：
drop table col_names;
CREATE TABLE col_names (aa Int8, ab Int8, bc Int) ENGINE = TinyLog;
insert into col_names values (1,2,3);
查询1：
SELECT COLUMNS('a') FROM col_names

┌─aa─┬─ab─┐
│  1 │  2 │
└────┴────┘
查询2：
SELECT  COLUMNS('a'),  COLUMNS('b') FROM col_names

┌─aa─┬─ab─┬─ab─┬─bc─┐
│  1 │  2 │  2 │  3 │
└────┴────┴────┴────┘
用于函数的查询：
Note： 在toTypeName函数中应用COLUMNS('c')，此时COLUMNS('c')只能返回一列，否则会抛出异常。
SELECT COLUMNS('a'), COLUMNS('c'), toTypeName(COLUMNS('c')) FROM col_names


Note：COLUMNS('regexp')必须要匹配表中列，否则将抛出异常。
SELECT COLUMNS('a1') FROM col_names

Received exception from server (version 20.1.4):
Code: 51. DB::Exception: Received from localhost:9001. DB::Exception: Empty list of columns in SELECT query.

---9-13---

下面通过示例说明DISTINCT与ORDER BY的执行顺序。
生成测试数据：
drop table t_distinct;
create table t_distinct (x Int8, y Int8) ENGINE=TinyLog;
insert into t_distinct values (1, 1) (2, 2) (3, 3) (2, 4);
查看数据：
SELECT * FROM t_distinct

┌─x─┬─y─┐
│ 1 │ 1 │
│ 2 │ 2 │
│ 3 │ 3 │
│ 2 │ 4 │
└───┴───┘

1  1
2  2
3  3

3
2
1


使用ORDER BY的去重：
SELECT DISTINCT x FROM t_distinct ORDER BY y DESC

┌─x─┐
│ 3 │
│ 2 │
│ 1 │
└───┘


我们通过反证法说明ORDER BY和DISTINCT的执行顺序：
如果ORDER BY先执行，则排序后的数据如下：
┌─x─┬─y─┐
│ 2 │ 4 │
│ 3 │ 3 │
│ 2 │ 2 │
│ 1 │ 1 │
└───┴───┘
然后针对x列去重：
┌─x─┬─y─┐
│ 2 │ 4 │
│ 3 │ 3 │
│ 1 │ 1 │
└───┴───┘
选择x列：
┌─x─┬
│ 2 │
│ 3 │
│ 1 │
└───┴
这个分析结果跟我们实际上我们通过DITINCT去重后的结果不一致。

---9-14---

---9-15---

示例：
drop table t_users_1;
drop table t_users_2;
create table t_users_1(id String, name String, age UInt8) ENGINE=Log;
create table t_users_2(user_id String, user_name String, age Nullable(UInt8)) ENGINE=Log;

insert into t_users_1 values('id001', 'xiaohe', 11);

insert into t_users_2 values('id001', 'xiaohe', 11) ('id002', 'xiaojiang', 22);

查看数据：
SELECT * FROM t_users_1
┌─id────┬─name───┬─age─┐
│ id001 │ xiaohe │  11 │
└───────┴────────┴─────┘

--------------------------------------------

SELECT * FROM t_users_2
┌─user_id─┬─user_name─┬─age─┐
│ id001   │ xiaohe    │  11 │
│ id002   │ xiaojiang │  22 │
└─────────┴───────────┴─────┘
使用UNION ALL查询：
SELECT id, name, age FROM t_users_1
UNION ALL
SELECT user_id, user_name, age FROM t_users_2
;

┌─id────┬─name──────┬─age─┐
│ id001 │ xiaohe    │  11 │
│ id002 │ xiaojiang │  22 │
└───────┴───────────┴─────┘
┌─id────┬─name───┬─age─┐
│ id001 │ xiaohe │  11 │
└───────┴────────┴─────┘

---9-16---

例子没看

---9-17---

创建测试数据：
drop table hits_test;
create table hits_test(UserID String, EventDate Date) ENGINE=Log;


insert into hits_test values
('id001', '2020-03-27')
('id002', '2020-03-27')
('id001', '2020-03-27')
('id003', '2020-03-27')
('id001', '2020-03-28')
('id002', '2020-03-28')
('id003', '2020-03-28')
('id001', '2020-03-28')
('id002', '2020-03-28')
('id003', '2020-03-28')
('id004', '2020-03-28')
('id005', '2020-03-28')
('id004', '2020-03-28')
('id006', '2020-03-28')
('id002', '2020-03-29')
('id003', '2020-03-29')
('id003', '2020-03-29')
('id001', '2020-03-29')
('id002', '2020-03-29')
('id005', '2020-03-29')
('id007', '2020-03-29')
;
查看数据：
SELECT * FROM hits_test

┌─UserID─┬──EventDate─┐
│ id001  │ 2020-03-27 │
│ id002  │ 2020-03-27 │
│ id001  │ 2020-03-27 │
│ id003  │ 2020-03-27 │
│ id001  │ 2020-03-28 │
│ id002  │ 2020-03-28 │
│ id003  │ 2020-03-28 │
│ id001  │ 2020-03-28 │
│ id002  │ 2020-03-28 │
│ id003  │ 2020-03-28 │
│ id004  │ 2020-03-28 │
│ id005  │ 2020-03-28 │
│ id004  │ 2020-03-28 │
│ id006  │ 2020-03-28 │
│ id002  │ 2020-03-29 │
│ id003  │ 2020-03-29 │
│ id003  │ 2020-03-29 │
│ id001  │ 2020-03-29 │
│ id002  │ 2020-03-29 │
│ id005  │ 2020-03-29 │
│ id007  │ 2020-03-29 │
└────────┴────────────┘

在2020-03-27访问过网站的用户，统计他们在每天的网站浏览量占比。
SELECT 
    EventDate, 
    avg(UserID IN 
    (
        SELECT UserID
        FROM hits_test
        WHERE EventDate = toDate('2020-03-27')
    )) AS ratio
FROM hits_test
GROUP BY EventDate
ORDER BY EventDate ASC;


┌──EventDate─┬──────────────ratio─┐
│ 2020-03-27 │                  1 │
│ 2020-03-28 │                0.6 │
│ 2020-03-29 │ 0.7142857142857143 │
└────────────┴────────────────────┘


2. NULL处理


drop table t_null;
create table t_null(x UInt8, y Nullable(UInt8)) ENGINE=Log;
insert into t_null values(1, NULL) (2, 3);

查看数据：
SELECT * FROM t_null;

┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │    3 │
└───┴──────┘
IN操作的查询：
SELECT * FROM t_null WHERE y IN (NULL, 3);

┌─x─┬─y─┐
│ 2 │ 3 │
└───┴───┘
所有与NULL运算的结果返回false。

---9-18---

1. Tuple相关操作

测试数据：
drop table t_tuple;
create table t_tuple(id String, addr Tuple(UInt16,String,String)) ENGINE=TinyLog;
insert into t_tuple values('id001', tuple(556, 'anhui', 'anqing'));
select * from t_tuple;

select id, addr.1, addr.2 from t_tuple;

┌─id────┬─tupleElement(addr, 1)─┬─tupleElement(addr, 2)─┐
│ id001 │                   556 │ anhui                 │
└───────┴───────────────────────┴───────────────────────┘


select id, tupleElement(addr, 1) as area_code from t_tuple;
┌─id────┬─area_code─┐
│ id001 │       556 │
└───────┴───────────┘

2. 数组相关操作
测试数据：
drop table t_arr;
create table t_arr(id String, hobby Array(String)) ENGINE=TinyLog;
insert into t_arr values('id001', ['football', 'basketball', 'movie']);
insert into t_arr values('id001', ['eat', 'sleep', 'play', 'ride']);
select * from t_arr;
数组和元组的元素的 索引都是从1开始的。a[N]和函数arrayElement(a, N)的使用。
xiaochen :) select id, hobby[1] as first_hobby from t_arr;

┌─id────┬─first_hobby─┐
│ id001 │ football    │
│ id001 │ eat         │
└───────┴─────────────┘

xiaochen :) select id, arrayElement(hobby,1) as first_hobby from t_arr;

┌─id────┬─first_hobby─┐
│ id001 │ football    │
│ id001 │ eat         │
└───────┴─────────────┘


3. 负数
select -2 as a, negate (2) as b;
┌──a─┬──b─┐
│ -2 │ -2 │
└────┴────┘

4 比较操作符
（1）、相等
a = b 
a == b
等价函数：equals(a, b)

（2）不相等
a != b
a <> b
等价函数：notEquals(a, b)

（3）、不等比较
a <= b， 等价函数：lessOrEquals(a, b)
a >= b ， 等价函数：greaterOrEquals(a, b)
a < b ， 等价函数：less(a, b)
a > b ， 等价函数：greater(a, b)

（4）、like匹配

a LIKE s， 等价函数：like(a, b)
a NOT LIKE s， 等价函数：notLike(a, b)

（5）、区间比较
a BETWEEN b AND c， 等价于： a >= b AND a <= c
a NOT BETWEEN b AND c ， 等价于：a < b OR a > c 。

5. 数据集操作符
a IN ... ： 等价函数 in(a, b)。
a NOT IN ... ： 等价函数 notIn(a, b)。
a GLOBAL IN ... ： 等价函数 globalIn(a, b)。
a GLOBAL NOT IN ... ： 等价函数 globalNotIn(a, b)。

6. 时间操作之EXTRACT
从给定的日期提取一个时间片段， 支持Date和DateTime数据类型。例如可以从给定的日期检索月或者秒等。
使用toYear、toMonth等也可实现类似功能。

EXTRACT(part FROM date);
part参数指定要解析的时间片段名称， 可选的片段名称如下：
DAY — 月份的日，Possible  valaues: 1–31.
MONTH — 月份， Possible values: 1–12.
YEAR — 年.
SECOND —秒. Possible values: 0–59.
MINUTE — 分钟. Possible values: 0–59.
HOUR — 小时. Possible values: 0–23.

SELECT EXTRACT(DAY FROM toDate('2017-06-15'));
SELECT EXTRACT(MONTH FROM toDate('2017-06-15'));
SELECT EXTRACT(YEAR FROM toDate('2017-06-15'));

CREATE TABLE test.Orders
(
    OrderId UInt64,
    OrderName String,
    OrderDate DateTime
)
ENGINE = Log;

INSERT INTO test.Orders VALUES (1, 'Jarlsberg Cheese', toDateTime('2008-10-11 13:23:44'));

SELECT
    toYear(OrderDate) AS OrderYear,
    toMonth(OrderDate) AS OrderMonth,
    toDayOfMonth(OrderDate) AS OrderDay,
    toHour(OrderDate) AS OrderHour,
    toMinute(OrderDate) AS OrderMinute,
    toSecond(OrderDate) AS OrderSecond
FROM test.Orders;

┌─OrderYear─┬─OrderMonth─┬─OrderDay─┬─OrderHour─┬─OrderMinute─┬─OrderSecond─┐
│      2008 │         10 │       11 │        13 │          23 │          44 │
└───────────┴────────────┴──────────┴───────────┴─────────────┴─────────────┘


7. 时间操作之INTERVAL
在Date和DateTime数据类型的值使用Interval类型的值做算术运算操作。
Interval类型： - SECOND - MINUTE - HOUR - DAY - WEEK - MONTH - QUARTER - YEAR 。
不同类型的interval不能合并。例如不能使用表达式："INTERVAL 4 DAY 1 HOUR"。 
示例：
SELECT now() AS current_date_time, current_date_time + INTERVAL 4 DAY + INTERVAL 3 HOUR
┌───current_date_time─┬─plus(plus(now(), toIntervalDay(4)), toIntervalHour(3))─┐
│ 2020-03-21 12:08:50 │                                    2020-03-25 15:08:50 │
└─────────────────────┴────────────────────────────────────────────────────────┘

8. 逻辑运算
逻辑非：NOT a ，等价于not(a) 。
逻辑与：a AND b， 等价于and(a, b)。
逻辑或： a OR b， 等价于or(a, b) 。

9. 三目运算符
元算法：
a ? b : c
等价函数：  if(a, b, c)
 如果b或C是arrayJoin（）函数，则无论“ a”条件如何，都将复制每一行。
select 1 as a, a>1?'a>1':'a<=1' as rs ;
┌─a─┬─rs───┐
│ 1 │ a<=1 │
└───┴──────┘

select 2 as a, a>1?'a>1':'a<=1' as rs ;
┌─a─┬─rs──┐
│ 2 │ a>1 │
└───┴─────┘

10. case when 表达式
CASE [x]
    WHEN a THEN b
    [WHEN ... THEN ...]
    [ELSE c]
END


SELECT case number when 3 then 111 when 5 then 222 when 7 then 333 else number end as new_number FROM system.numbers LIMIT 10;
┌─new_number─┐
│          0 │
│          1 │
│          2 │
│        111 │
│          4 │
│        222 │
│          6 │
│        333 │
│          8 │
│          9 │
└────────────┘


11. 创建数组和元组
数组 [x1, ...]  等价函数： array(x1, ...)
元组 (x1, x2, ...)  等价函数： tuple(x2, x2, ...)

select (1,1,2);
select [1,2];

13. NULL判断
支持两种操作符： IS NULL 或 IS NOT NULL 。
（1）、IS NULL
对于 Nullable类型的值， 返回0或1。

（2）、IS NOT NULL
对于 Nullable类型的值， 返回0或1。

SELECT * FROM t_null WHERE y IS NOT NULL

13. 连接运算符
s1 || s2 等价函数： concat(s1, s2)
select 'id001'||':'||'football';
┌─concat('id001', ':', 'football')─┐
│ id001:football                   │
└──────────────────────────────────┘

---9-19---

nzch

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
2
评论
clickhouse教程-09

---9-1---总结：示例1：定义常量表达式变量WITH '2019-08-01 15:23:00' AS timestampSELECT number, timestampFROM system.numbersLIMIT 2┌─number─┬─timestamp───────────┐│ 0 │ 2019-08-01 15:23:00 ││ 1 │ 2019-08-01 15:23:00 │└────────┴──────..
复制链接

扫一扫