Clickhouse 简单SQL优化以及特殊数据类型使用场景

一. SQL 查询上的优化点

1. 案例1利用好引擎表可以 PREWHER的优势

select big.id, big.time, small.code, small.value
from smalltable small INNER JOIN bigtable big on small.id=big.id
where big.time>‘2020-05-23’ and small.code in(‘a’,‘b’,‘c’);

smalltable为TinyLog引擎表
bigtable为MergeTree引擎表

遍历右表的每一条记录，然后和左表关联，NESTED JOIN
右表是小表。

改写：
select big.id, big.time, small.code, small.value
from bigtable big INNER JOIN smalltable small on small.id=big.id
where big.time>‘2020-05-23’ and small.code in(‘a’,‘b’,‘c’);

PREWHERE，自动where转移为PREWHER，条件，只适用于MERGETREE引擎

select big.id, big.time, small.code, small.value
from bigtable big INNER JOIN (select * from smalltable where small.code in(‘a’,‘b’,‘c’)) small on small.id=big.id
where big.time>‘2020-05-23’ ;

2. 案例2 ,函数在join前操作 /将小表存字典操作

select formatDateTime(ns.date, ‘%Y%m%d’) as dayid, ns.prd_id, m.name,count() as cnt
from crm_prd_inst ns left join dim_code_map m on(ns.code = m.code)
group by dayid, ns.prd_id, m.name;

date格式: 2020-03-31，通过formatDateTime函数转换为：20200331
dim_code_map表是维度表，主键为code。

改写1：
select t1.dayid, t1.prd_id, t2.name, t1.cnt from
(
select formatDateTime(ns.date, ‘%Y%m%d’) as dayid, ns.prd_id, ns.code,count() as cnt
from crm_prd_inst ns
group by dayid, ns.prd_id, ns.code
) t1 left join dim_code_map t2 on(t1code = t2.code)

改写2：

select formatDateTime(t1.date, ‘%Y%m%d’) as dayid, t1.prd_id, t2.name, t1.cnt from
(
select ns.date, ns.prd_id, ns.code,count() as cnt
from crm_prd_inst ns
group by ns.date, ns.prd_id, ns.code
) t1 left join dim_code_map t2 on(t1code = t2.code)

改写3：使用字典
将表数据作为字典的数据源，字典名称dic_dim_code

select formatDateTime(t1.date, ‘%Y%m%d’) as dayid, t1.prd_id, dictGet(‘default.dic_dim_code’, ‘name’, t1.code) name, t1.cnt from
from
(
select ns.date, ns.prd_id, ns.code,count() as cnt
from crm_prd_inst ns
group by ns.date, ns.prd_id, ns.code
) t1

二. 相关特殊数据类型. Tuple ,数组 ,负数

1. tunple

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 │
└────┴────┘

三. 特殊的操作符

1. 比较大小

（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)。

2. 时间操作之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 │
└───────────┴────────────┴──────────┴───────────┴─────────────┴─────────────┘

3. 时间操作之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 │
└─────────────────────┴────────────────────────────────────────────────────────┘

4.运算符

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

 三目运算符
元算法：
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 │
└───┴─────┘

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 │
└────────────┘


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

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

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                   │
└──────────────────────────────────┘