ClickHouse 中的数据查询以及各种子句

楔子

作为一款 OLAP 型的数据库，它的查询功能可谓是重中之重，而且我相信大家在绝大部分时间都在使用它的查询功能，事实上，在日常运转的过程中，数据查询也是 ClickHouse 的主要工作之一。ClickHouse 完全使用 SQL 作为查询语言，能够以 SELECT 查询语句的形式从数据库中选取数据，这也是它具备流行潜质的重要原因。虽然 ClickHouse 拥有优秀的查询性能，但是我们也不能滥用查询，掌握 ClickHouse 所支持的各种查询子句，并选择合理的查询形式是很有必要的。使用不恰当的 SQL 语句进行查询不仅会带来低性能，还可能导致不可预知的系统错误。

虽然在上面的示例中，我们已经见识过一些查询语句的用法，但那些都是为了演示效果简化后的代码，与真正的生产环境中的代码相差较大。例如在绝大部分场景中，都应该避免使用 SELECT * 来查询数据，因为通配符 * 对于采用列式存储的 ClickHouse 而言没有任何好处。假如面对一张拥有数百个列字段的数据表，下面这两条 SELECT 语句的性能可能会相差 100 倍之多：

SELECT * FROM table;
SELECT col FROM table;

使用通配符 * 和按列查询相比，性能可能相差 100 倍，另外 ClickHouse 对于 SQL 语句的解析是大小写敏感的，这意味着 SELECT a 和 SELECT A 表示的语义是不相同的，但关键字大小写不敏感，不过还是建议遵循规范使用大写。此外 ClickHouse 的类型也大小写敏感，比如：UInt8 不可以写成 uint8，String 不可以写成 string；还有大部分函数也是大小写敏感，这些函数都是 ClickHouse 独有的，或者说你在其它关系型数据库中见不到的，但是像 min、max、length、sum、count 等等这些在其它关系型库中也能看到的函数，在 ClickHouse 中则是大小写不敏感的。

下面介绍 ClickHouse 的查询语法，ClickHouse 支持的查询子句和我们平常使用的关系型数据库非常类似，但是在此基础上又提供了很多新的功能，我们来看一下。

虽然 ClickHouse 的查询是重中之重，但毕竟采用的是 SQL 语法，因此像什么如何起别名、比较运算符、条件运算符等等，这些比较基础的内容就不说了。

WITH 子句

ClickHouse 支持 CTE（Common Table Expression，公共表表达式），以增强查询语句的表达。例如下面的函数嵌套：

SELECT pow(pow(2, 2), 3); 
/*
┌─pow(pow(2, 2), 3)─┐
│                64 │
└───────────────────┘
*/

在改用 CTE 的形式后，可以极大地提高语句的可读性和可维护性，简化后的语句如下所示：

WITH pow(2, 2) AS a SELECT pow(a, 3); 
/*
┌─pow(a, 3)─┐
│        64 │
└───────────┘
*/

在 ClickHouse 中的 CTE 通过 WITH 子句表示，而语法格式显然很简单，想象一下编程语言中的变量定义，一般都类似于：

var = 表达式

在后续的的代码编写中，可以使用 var 来代替相应的表达式，而在 ClickHouse 中也是同理：

WITH 表达式 AS var

通过 WITH，我们即可在查询中使用 var 来代替表达式，而根据表达式的不同，可以有以下几种用法：

1. 表达式为常量

此时相当于为常量起了一个有意义的名字，这些名字能够在后续的查询子句中被直接访问。例如下面示例中的 start，被直接用在紧接着的 WHERE 子句中：

WITH 10 AS start
SELECT number FROM system.numbers -- 这是一张系统表
WHERE number > start LIMIT 5
/*
┌─number─┐
│     11 │
│     12 │
│     13 │
│     14 │
│     15 │
└────────┘
 */

如果没有 WITH 子句，那么直接把 start 换成 10 即可，只不过通过 WITH 我们给 10 这个常量起了一个有意义的名字。当然常量不仅是整数，字符串、浮点数、甚至数组都是可以的。

 

2. 表达式为函数调用

感觉此时就类似于替换，例如在下面的示例中，对 data_uncompressed_bytes 使用聚合函数求和后，又紧接着在 SELECT 子句中对其进行了格式化处理。

WITH SUM(data_uncompressed_bytes) AS bytes
SELECT database, formatReadableSize(bytes) AS format
FROM system.columns
GROUP BY database
ORDER BY bytes DESC
/*
┌─database─┬─format───┐
│ system   │ 5.32 GiB │
│ default  │ 0.00 B   │
└──────────┴──────────┘
*/

如果不使用 WITH 子句，那么 SELECT 里面出现的就是 formatReadableSize(SUM(data_uncompressed_bytes))，这样读起来不是很方便，所以使用 WITH 子句将里面的聚合函数调用起一个名字叫 bytes，那么后面的查询直接使用 bytes 即可。

 

3. 表达式为子查询

表达式也可以是一个子查询，例如在下面的示例中，借助子查询可以得出各 database 未压缩数据大小与数据总和大小的比例的排名：

-- SELECT sum(data_uncompressed_bytes) FROM system.columns 会得到一个数值
-- 因此本质上和表达式为常量是类似的，只不过多了一个计算的过程
WITH (SELECT sum(data_uncompressed_bytes) FROM system.columns) AS total_bytes
SELECT database, 
       (sum(data_uncompressed_bytes) / total_bytes) * 100 AS database_disk_usage
FROM system.columns
GROUP BY database
ORDER BY database_disk_usage DESC
/*
┌─database─┬─database_disk_usage─┐
│ system   │                 100 │
│ default  │                   0 │
└──────────┴─────────────────────┘
*/

使用 WITH 子句时有一点需要特别注意，表达式只能返回的数据不能超过 1 行，否则会抛出异常。我们举个栗子：

这里的 WITH AS 就类似于编程语言中的变量赋值，但你不可能让一个变量指代多个值，如果想这么做，那么就将这些值放在一个容器（列表、集合、字典等等）里面。同理，如果 WITH 的表达式返回了多行数据，那么可以将其变成一个数组：

-- 这里见到了一个函数 groupArray，我们可以把它当成是普通的聚合函数来理解
-- 类似于 sum，sum 是对同一组的元素进行求和，groupArray 是将同一组的元素组合成数组
WITH (SELECT groupArray(number) FROM numbers(10)) AS arr SELECT arr;
/*
┌─arr───────────────────┐
│ [0,1,2,3,4,5,6,7,8,9] │
└───────────────────────┘
*/

显然此时就没问题了，并且相比关系型数据库，ClickHouse 在行列转换的时候尤为方便。

 

4. 在子查询中重复使用WITH

在子查询中可以嵌套使用 WITH 子句，例如在下面的示例中，在计算出各 database 未压缩数据大小与数据总和的比例之后，又进行了取整函数的调用：

WITH round(database_disk_usage) AS database_disk_usage_v1
SELECT database, database_disk_usage, database_disk_usage_v1
FROM (
    -- 嵌套
    WITH (SELECT sum(data_uncompressed_bytes) FROM system.columns) AS total_bytes
    SELECT database, (sum(data_uncompressed_bytes) / total_bytes) * 100 AS database_disk_usage
    FROM system.columns GROUP BY database ORDER BY database_disk_usage DESC
)

虽然看起来有点复杂，但是不难理解，不考虑 WITH 的话，那么就是一个嵌套子查询：SELECT ... FROM (SELECT ... FROM)，只不过两个 SELECT 里面的 database_disk_usage_v1、total_bytes 是用 WITH 声明的变量。

而且我们看到如果子查询是作为一张表使用的，那么在关系型数据库中应该起一个别名，但在 ClickHouse 可以不用。

总的来说，WITH 子句就相当于起一个别名，如果你看某个表达式长得不顺眼，那么就可以使用 WITH 将它替换掉，就这么简单。而且一个 WITH 子句是可以为多个表达式起别名的，举个栗子：

WITH 1 AS a, 2 AS b SELECT a + b; 
/*
┌─plus(a, b)─┐
│          3 │
└────────────┘
*/

 

其它关系型数据库的 WITH 子句

ClickHouse 的 WITH 语句和其它的关系型数据库还是有很大差别的，比如 PostgreSQL。如果 PostgreSQL 的话，那么 AS 之后的结果是会被当成是一张表，举个例子：

-- 假设存在一张表叫 girls, 如果是 PostgreSQL 的话
WITH tmp AS (
	SELECT * FROM girls WHERE id < 100 
)
SELECT * FROM tmp WHERE age > 20;
-- 这么做的话, 在 PostgreSQL 中是完全正确的做法，此时的 tmp 就是 table 中 id 小于 100 的记录组成的结果集
-- 并且它可以作为一张临时表来使用
-- 我们这个示例比较简单, 但是当子查询比较复杂的时候, 通过将子查询当做一张临时表, 可以使查询逻辑更加清晰
-- 并且查询结束之后, 这张临时表也就不存在了
-- 上面这段代码和下面都是等价的
SELECT * FROM girls WHERE id < 100 AND age > 20;
SELECT * FROM (SELECT * FROM girls WHERE id < 100) tmp WHERE age > 20;

所以 ClickHouse 的 WITH 中的表达式必须只能有一行，它就等价于为某个复杂的表达式起一个别名，不可以放在 FROM 后面作为临时表来使用。而 PostgreSQL 的 WITH 中的表达式没有任何限制，可以返回任何数据，行数不限，并且可以当成临时表放在 FROM 后面。除此之外，ClickHouse 和 PostgreSQL 的 WITH 语句还有一处不同，那就是 ClickHouse 中别名在 AS 后面，而 PostgreSQL 中别名在 AS 前面。

注意：WITH 中的表达式如果是子查询，那么会提前计算好，在使用别名的时候使用的是已经计算好的结果。

问题来了，既然行数有限制，那列数有没有限制呢？我们试一下就知道了，首先创建一张 Memory 数据表，内容如下：

SELECT * FROM women
/*
┌─id─┬─name─────┬─age─┐
│  1 │ 古明地觉  │  17 │
│  2 │ 芙兰朵露  │ 144 │
│  3 │ 琪露诺    │  58 │
└────┴──────────┴─────┘
*/

然后我们来进行查询：

同理通过 WITH，我们还可以实现为字段起别名的效果，举个栗子：

不过这种做法显然没有太大意义，除非字段名太长了，想起一个短点儿的。

以上就是 WITH 子句，非常简单，核心就一句话：给表达式起别名，后续使用别名来对表达式进行替换。

FROM 子句

FROM 子句表示从何处读取数据，目前支持如下 3 种形式。

1. 从数据表中取数

SELECT name FROM people

 

2. 从子查询中取数

SELECT max_id FROM
(SELECT max(id) AS max_id FROM people)
-- 在其它关系型数据库中, 如果子查询作为一张表来使用, 那么必须要起一个别名
-- 但是在 ClickHouse 中不需要, 个人觉得这是个不错的决定，因为起别名我们又不用

 

3. 从表函数中取数

SELECT number FROM numbers(N) -- 会返回 0 到 N - 1

另外 FROM 关键字可以省略，我们在介绍 WITH 子句的时候多次省略 FROM，因为 SELECT 后面是标量，此时会从虚拟表中取值。在 ClickHouse 中，并没有数据库中常见的 DUAL 虚拟表，取而代之的是 system.one。举个栗子：

-- 等价于 SELECT 1, 2, 3 FROM system.one，
SELECT 1, 2, 3;  
/*
┌─1─┬─2─┬─3─┐
│ 1 │ 2 │ 3 │
└───┴───┴───┘
*/

SELECT 1 + 2, 2 * 3;  -- 此时可以当成计算器来使用
/*
┌─plus(1, 2)─┬─multiply(2, 3)─┐
│          3 │              6 │
└────────────┴────────────────┘
*/

SELECT [1, 2, 3], 'KOMEIJI SATORI';
/*
┌─[1, 2, 3]─┬─'KOMEIJI SATORI'─┐
│ [1,2,3]   │ KOMEIJI SATORI   │
└───────────┴──────────────────┘
*/

ARRAY JOIN 子句

ARRAY JOIN 子句允许在数据表的内部，与数组或嵌套类型的字段进行 JOIN 操作，从而将一行数组展开为多行。接下来让我们看看它的基础用法，首先新建一张包含 Array 数组字段的测试表：

CREATE TABLE t1 (
    title String,
    value Array(UInt8)
) ENGINE = Memory();

-- 然后写入数据
INSERT INTO t1 VALUES ('food', [1, 2, 3]), ('fruit', [3, 4]), ('meat', []);

-- 查询
SELECT * FROM t1;
/*
┌─title─┬─value───┐
│ food  │ [1,2,3] │
│ fruit │ [3,4]   │
│ meat  │ []      │
└───────┴─────────┘
*/

在一条 SELECT 语句中，只能存在一个 ARRAY JOIN（使用子查询除外），目前支持 INNER 和 LEFT 两种 JOIN 策略：

INNER ARRAY JOIN

ARRAY JOIN 在默认情况下使用的是 INNER JOIN 策略，例如下面的语句：

SELECT title, value FROM t1 ARRAY JOIN value;
/*
┌─title─┬─value─┐
│ food  │     1 │
│ food  │     2 │
│ food  │     3 │
│ fruit │     3 │
│ fruit │     4 │
└───────┴───────┘
*/

从查询结果可以发现，最终的数据基于 value 数组被展开成了多行，并且排除掉了空数组，同时会自动和其它字段进行组合（相当于按行合并）。在使用 ARRAY JOIN 时，如果还想访问展开前的数组字段，那么只需为原有的数组字段添加一个别名即可，例如：

-- 如果不给 ARRAY JOIN 后面的 value 起一个别名，那么 value 就是展开后的结果
-- 如果给 ARRAY JOIN 后面的 value 起一个别名 val，那么 value 就还是展开前的数组字段
-- 而 val 才是展开后的结果，所以再反过来，让 val 出现在 SELECT 中即可
SELECT title, value, val FROM t1 ARRAY JOIN value AS val;
/*
┌─title─┬─value───┬─val─┐
│ food  │ [1,2,3] │   1 │
│ food  │ [1,2,3] │   2 │
│ food  │ [1,2,3] │   3 │
│ fruit │ [3,4]   │   3 │
│ fruit │ [3,4]   │   4 │
└───────┴─────────┴─────┘
*/

我们看到 ClickHouse 的确是当之无愧的最强 OLAP 数据库，不单单是速度快，最重要的是，它提供的查询语法也很方便。如果你用过 Hive 的话，会发现这里特别像里面的 lateral view explode 语法。

LEFT ARRAY JOIN

ARRAY JOIN 子句支持 LEFT 连接策略，例如执行下面的语句：

SELECT title, value, val FROM t1 LEFT ARRAY JOIN value AS val;
/*
┌─title─┬─value───┬─val─┐
│ food  │ [1,2,3] │   1 │
│ food  │ [1,2,3] │   2 │
│ food  │ [1,2,3] │   3 │
│ fruit │ [3,4]   │   3 │
│ fruit │ [3,4]   │   4 │
│ meat  │ []      │   0 │
└───────┴─────────┴─────┘
*/

在改为 LEFT 连接查询后，可以发现，在 INNER JOIN 中被排除掉的空数组出现在了返回的结果集中。但此时的 val 是零值，所以 LEFT ARRAY JOIN 个人觉得不是很常用，一般都是用 ARRAY JOIN。

关于数组的一些骚操作

在关系型数据库里面我们一般都不太喜欢用数组，但是在 ClickHouse 中数组会用的非常多，并且操作起来非常简单。ClickHouse 里面提供了非常多的函数，用好了的话，就相当于分布式的 pandas。下面就先来看一下关于数组的一些函数，这里先介绍一部分，提前感受一下 ClickHouse 的强大，首先我们创建一张新表，并写入测试数据：

SELECT * FROM t2;
/*
┌─────────dt─┬─cash───────┐
│ 2020-01-01 │ [10,10,10] │
│ 2020-01-02 │ [20,20,20] │
│ 2020-01-01 │ [10,10,10] │
│ 2020-01-02 │ [20,20]    │
│ 2020-01-03 │ []         │
│ 2020-01-03 │ [30,30,30] │
└────────────┴────────────┘
*/

 

groupArray

这个函数已经出现过一次了，我们说它是把多行数据合并成一个数组，相当于是聚合函数的一种。

SELECT dt, groupArray(cash) FROM t2 GROUP BY dt;
/*
┌─────────dt─┬─groupArray(cash)────────┐
│ 2020-01-01 │ [[10,10,10],[10,10,10]] │
│ 2020-01-02 │ [[20,20,20],[20,20]]    │
│ 2020-01-03 │ [[],[30,30,30]]         │
└────────────┴─────────────────────────┘
*/

我们看到 groupArray 就等同于类似 count、sum 这样的聚合函数，将同一组的数据组合成一个新的数组。由于本来的元素就是数组，所以这里就是数组嵌套数组。

除了 groupArray 之外，还有一个 groupUniqArray，在组合的时候会对元素进行去重：

SELECT dt, groupUniqArray(cash) FROM t2 GROUP BY dt;
/*
┌─────────dt─┬─groupUniqArray(cash)─┐
│ 2020-01-01 │ [[10,10,10]]         │
│ 2020-01-02 │ [[20,20],[20,20,20]] │
│ 2020-01-03 │ [[],[30,30,30]]      │
└────────────┴──────────────────────┘
*/

我们看到 '2020-01-01' 这行数据被去重了。

 

arrayFlatten

从名字上应该能猜出来，直接看例子就明白了。

SELECT dt, 
       groupArray(cash),
       arrayFlatten(groupArray(cash)) FROM t2 GROUP BY dt;

我们在 groupArray(cash) 基础上又调用了 arrayFlatten：

相信该函数的作用显而易见的，就是将多个嵌套数组扁平化，另外这里的查询语句还可以美化一下：

-- 使用 WITH 子句，提前将 groupArray(cash) 起一个别名
WITH groupArray(cash) AS group_cash
SELECT dt, 
       group_cash,
       arrayFlatten(group_cash) FROM t2 GROUP BY dt;
       
-- 或者这么做
SELECT dt,
       groupArray(cash) AS group_cash,
       arrayFlatten(group_cash) FROM t2 GROUP BY dt;
-- 我们看到即使是在 SELECT 里面起的别名也是可以被使用的
-- 另外顺序也没有限制，比如下面的做法也是合法的
SELECT dt, 
       arrayFlatten(group_cash), 
       groupArray(cash) AS group_cash FROM t2 GROUP BY dt;

 

splitByChar

将字符串按照指定字符分割成数组：

SELECT splitByChar('^', 'komeiji^koishi');
/*
┌─splitByChar('^', 'komeiji^koishi')─┐
│ ['komeiji','koishi']               │
└────────────────────────────────────┘
*/

 

arrayJoin

该函数和 ARRAY JOIN 子句的作用非常类似：

 

arrayMap

对数组中的每一个元素都以相同的规则进行映射：

-- arrayMap(x -> x * 2, value) 表示将 value 中的每一个元素都乘以 2，然后返回一个新数组
-- 而 mapV 就是变换过后的新数组，直接拿来用即可
SELECT title, arrayMap(x -> x * 2, value) AS mapV, v
FROM t1 LEFT ARRAY JOIN mapV as v
/*
┌─title─┬─mapV────┬─v─┐
│ food  │ [2,4,6] │ 2 │
│ food  │ [2,4,6] │ 4 │
│ food  │ [2,4,6] │ 6 │
│ fruit │ [6,8]   │ 6 │
│ fruit │ [6,8]   │ 8 │
│ meat  │ []      │ 0 │
└───────┴─────────┴───┘
*/


-- 另外展开的字段也可以不止一个
SELECT title, 
       arrayMap(x -> x * 2, value) AS mapV, v,
       value, v_1
FROM t1 LEFT ARRAY JOIN mapV as v, value AS v_1
/*
┌─title─┬─mapV────┬─v─┬─value───┬─v_1─┐
│ food  │ [2,4,6] │ 2 │ [1,2,3] │   1 │
│ food  │ [2,4,6] │ 4 │ [1,2,3] │   2 │
│ food  │ [2,4,6] │ 6 │ [1,2,3] │   3 │
│ fruit │ [6,8]   │ 6 │ [3,4]   │   3 │
│ fruit │ [6,8]   │ 8 │ [3,4]   │   4 │
│ meat  │ []      │ 0 │ []      │   0 │
└───────┴─────────┴───┴─────────┴─────┘
*/

嵌套类型

在前面介绍数据定义时曾介绍过，嵌套数据类型的本质是数组，所以 ARRAY JOIN 也支持嵌套数据类型。接下来继续用一组示例说明，首先新建一张包含嵌套类型的测试表：

CREATE TABLE t3(
    title String,
    nested Nested
    (
        v1 UInt32,
        v2 UInt64
    )
) ENGINE = Log();

-- 接着写入测试数据
-- 在写入嵌套数据类型时，记得同一行数据中各个数组的长度需要对齐，而对多行数据之间的数组长度没有限制
INSERT INTO t3
VALUES ('food', [1, 2, 3], [10, 20, 30]),
       ('fruit', [4, 5], [40, 50]),
       ('meat', [], [])

对嵌套类型数据的访问，ARRAY JOIN 既可以直接使用字段列名：

也可以使用点访问符的形式：

-- nested 只有 v1 和 v2
-- 所以 ARRAY JOIN nested.v1, nested.v2 等价于 ARRAY JOIN nested
SELECT title, nested.v1, nested.v2 FROM t3 ARRAY JOIN nested.v1, nested.v2
/*
┌─title─┬─nested.v1─┬─nested.v2─┐
│ food  │         1 │        10 │
│ food  │         2 │        20 │
│ food  │         3 │        30 │
│ fruit │         4 │        40 │
│ fruit │         5 │        50 │
└───────┴───────────┴───────────┘
*/

嵌套类型也支持 ARRAY JOIN 部分嵌套字段：

SELECT title, nested.v1, nested.v2 FROM t3 ARRAY JOIN nested.v1
/*
┌─title─┬─nested.v1─┬─nested.v2──┐
│ food  │         1 │ [10,20,30] │
│ food  │         2 │ [10,20,30] │
│ food  │         3 │ [10,20,30] │
│ fruit │         4 │ [40,50]    │
│ fruit │         5 │ [40,50]    │
└───────┴───────────┴────────────┘
*/

可以看到，在这种情形下，只有被 ARRAY JOIN 的数组才会展开。

在查询嵌套类型时也能够通过别名的形式访问原始数组：

SELECT title, 
       nested.v1, nested.v2, 
       n.v1, n.v2  
from t3 ARRAY JOIN nested AS n;
/*
┌─title─┬─nested.v1─┬─nested.v2──┬─n.v1─┬─n.v2─┐
│ food  │ [1,2,3]   │ [10,20,30] │    1 │   10 │
│ food  │ [1,2,3]   │ [10,20,30] │    2 │   20 │
│ food  │ [1,2,3]   │ [10,20,30] │    3 │   30 │
│ fruit │ [4,5]     │ [40,50]    │    4 │   40 │
│ fruit │ [4,5]     │ [40,50]    │    5 │   50 │
└───────┴───────────┴────────────┴──────┴──────┘
*/

-- 所以 ARRAY JOIN nested 后面如果没有 AS，那么这个 nested.v1 和 nest.v2 就是展开后的值
-- 如果 ARRAY JOIN nested AS n，起了一个别名，那么 nested.v1 和 nest.v2 就是展开前值，也就是数组本身
-- 而 n.v1 和 n.v2 才是展开后的值
-- 另外 ARRAY JOIN nested AS n，这个 n 可以不使用
SELECT title, 
       nested.v1, nested.v2
from t3 ARRAY JOIN nested AS n;
/*
┌─title─┬─nested.v1─┬─nested.v2──┐
│ food  │ [1,2,3]   │ [10,20,30] │
│ food  │ [1,2,3]   │ [10,20,30] │
│ food  │ [1,2,3]   │ [10,20,30] │
│ fruit │ [4,5]     │ [40,50]    │
│ fruit │ [4,5]     │ [40,50]    │
└───────┴───────────┴────────────┘
*/

JOIN 子句

JOIN 子句可以对左右两张表的数据进行连接，这是最常用的查询子句之一，它的语法包含连接精度和连接类型两部分。目前 ClickHouse 支持的 JOIN 子句形式如图所示：

由上图可知，连接精度分为 ALL、ANY 和 ASOF 三种（准确的说是五种，还有 SEMI 和 ANTI，这两个过会再提），而连接类型也可分为外连接、内连接和交叉连接三种。

除此之外，JOIN 查询还可以根据其执行策略被划分为本地查询和远程查询。关于远程查询的内容放在后续章节进行说明，这里着重讲解本地查询。

连接精度

连接精度决定了 JOIN 查询在连接数据时所使用的策略，目前支持 ALL、ANY 和 ASOF 三种类型。如果不主动声明，则默认是 ALL。可以通过 join_default_strictness 配置参数修改默认的连接精度类型。

那么这个连接精度指的是啥呢？举个栗子就明白了。

SELECT * FROM tbl_1;
/*
┌─id─┬─code1─┬─count─┐
│  1 │ A001  │    30 │
│  2 │ A002  │    28 │
│  3 │ A003  │    32 │
└────┴───────┴───────┘
*/

SELECT * FROM tbl_2;
/*
┌─id─┬─code2─┬─count─┐
│  1 │ B001  │    35 │
│  1 │ B001  │    29 │
│  3 │ B003  │    31 │
│  4 │ B004  │    38 │
└────┴───────┴───────┘
*/

以上是用于测试的表数据，下面进行测试：

SELECT t1.id, t1.code1, t2.code2 
FROM tbl_1 AS t1 
ALL INNER JOIN tbl_2 AS t2
ON t1.id = t2.id;
/*
┌─id─┬─code1─┬─code2─┐
│  1 │ A001  │ B001  │
│  1 │ A001  │ B001  │
│  3 │ A003  │ B003  │
└────┴───────┴───────┘
*/
-- 一切正常，跟一般的关系型数据库是类似的，但如果将 ALL 改成 ANY
SELECT t1.id, t1.code1, t2.code2 
FROM tbl_1 AS t1 
ANY INNER JOIN tbl_2 AS t2
ON t1.id = t2.id;
/*
┌─id─┬─code1─┬─code2─┐
│  1 │ A001  │ B001  │
│  3 │ A003  │ B003  │
└────┴───────┴───────┘
*/

所以结论很清晰了，如果左表内的一行数据，在右表中有多行数据与之连接匹配，那么当连接精度为 ALL，会返回右表中全部连接的数据；当连接精度为 ANY，会仅返回右表中第一行连接的数据

这就是连接精度，没什么好稀奇的，不过在关系型数据库中则没有连接精度的概念，因为当出现这种情况，只有一个策略，那就是直接返回右表中匹配的全部数据。而在 ClickHouse 中，给了我们自由选择的权利。

除了 ALL 和 ANY 之外还有一个 ASOF，它是做什么的呢？首先无论 ALL 还是 ANY，在连接的时候必须是等值连接。比如上面的 t1.id = t2.id，如果改成 t1.id >= t2.id 就是错误的，如果是多个连接条件，那么这些连接条件都必须是等值连接。但 ASOF 表示模糊连接，也就是它允许你在等值连接的后面加上一个非等值连接，举个栗子：

SELECT t1.id, t1.code1, t2.code2, t1.count AS count1, t2.count AS count2
FROM tbl_1 AS t1 
ALL INNER JOIN tbl_2 AS t2
ON t1.id = t2.id;
/*
┌─id─┬─code1─┬─code2─┬─count1─┬─count2─┐
│  1 │ A001  │ B001  │     30 │     35 │
│  1 │ A001  │ B001  │     30 │     29 │
│  3 │ A003  │ B003  │     32 │     31 │
└────┴───────┴───────┴────────┴────────┘
*/

SELECT t1.id, t1.code1, t2.code2, t1.count AS count1, t2.count AS count2
FROM tbl_1 AS t1 
ASOF INNER JOIN tbl_2 AS t2
ON t1.id = t2.id AND t1.count > t2.count;
/*
┌─id─┬─code1─┬─code2─┬─count1─┬─count2─┐
│  1 │ A001  │ B001  │     30 │     29 │
│  3 │ A003  │ B003  │     32 │     31 │
└────┴───────┴───────┴────────┴────────┘
*/

SELECT t1.id, t1.code1, t2.code2, t1.count AS count1, t2.count AS count2
FROM tbl_1 AS t1 
ASOF INNER JOIN tbl_2 AS t2
ON t1.id = t2.id AND t1.count < t2.count;
/*
┌─id─┬─code1─┬─code2─┬─count1─┬─count2─┐
│  1 │ A001  │ B001  │     30 │     35 │
└────┴───────┴───────┴────────┴────────┘
*/

所以结论很清晰，如果连接精度为 ALL 或者 ANY，那么所有的连接条件必须为等值连接，如果出现了非等值连接则报错。而这两者的唯一区别就在于：

  • ALL：如果右表有多条数据匹配，返回所有的匹配的数据
  • ANY：如果右表有多条数据匹配，返回第一条匹配的数据

如果连接精度为 ASOF，那么允许在等值连接条件后面追加一个非等值连接，所以上面的 t1.id = t2.id 是等值连接，t1.count > t2.count 是非等值连接。但需要注意的是：使用非等值连接时，这个非等值可以是 >、>=、<、<=，但不能是 !=；并且对于 ASOF 而言，连接条件必须是等值连接和非等值连接的组合，两者缺一不可。

对于 ASOF 而言，如果右表中有多行数据匹配，只会返回第一行。

连接类型

连接类型就比较简单了，这个和关系型数据库是完全类似的。

-- 省略连接精度，默认为 ALL
-- 左连接
SELECT t1.id, t1.code1, t2.code2 
FROM tbl_1 t1 LEFT JOIN tbl_2 t2 
USING(id); -- 等价于 t1.id = t2.id
/*
┌─id─┬─code1─┬─code2─┐
│  1 │ A001  │ B001  │
│  1 │ A001  │ B001  │
│  2 │ A002  │       │
│  3 │ A003  │ B003  │
└────┴───────┴───────┘
*/

-- 右连接
SELECT t1.id, t1.code1, t2.code2 
FROM tbl_1 t1 RIGHT JOIN tbl_2 t2 
USING(id);
/*
┌─id─┬─code1─┬─code2─┐
│  1 │ A001  │ B001  │
│  1 │ A001  │ B001  │
│  3 │ A003  │ B003  │
└────┴───────┴───────┘
┌─id─┬─code1─┬─code2─┐
│  4 │       │ B004  │
└────┴───────┴───────┘
*/

-- 全连接
SELECT t1.id, t1.code1, t2.code2 
FROM tbl_1 t1 FULL JOIN tbl_2 t2 
USING(id);
/*
┌─id─┬─code1─┬─code2─┐
│  1 │ A001  │ B001  │
│  1 │ A001  │ B001  │
│  2 │ A002  │       │
│  3 │ A003  │ B003  │
└────┴───────┴───────┘
┌─id─┬─code1─┬─code2─┐
│  4 │       │ B004  │
└────┴───────┴───────┘
*/

和关系型数据库类似，但有一点区别，就是当没有与之匹配的记录时，会使用对应类型的空值进行补全，而不是 Null。这里没有指定连接精度，默认为 ALL，此外 LEFT / RIGHT / FULL JOIN 后面都可以加上一个 OUTER，不过也可以不加。最后是交叉连接，交叉连接直接会去笛卡尔积，不需要任何的连接条件。

SEMI 和 ANTI

我们之前说连接精度不止 ALL、ANY、ASOF 三种，还有 SEMI 和 ANTI，只不过这两个比较特殊，因为它们只能用在 LEFT JOIN 和 RIGHT JOIN 上面，所以我们单独介绍。

  • t1 SEMI LEFT JOIN t2 USING(id)：遍历 t1 中的 id，如果存在于 t2 中，则输出
  • t1 SEMI RIGHT JOIN t2 USING(id)：遍历 t2 中的 id，如果存在于 t1 中，则输出
  • t1 ANTI LEFT JOIN t2 USING(id)：遍历 t1 中的 id，如果不存在于 t2 中，则输出
  • t1 ANTI RIGHT JOIN t2 USING(id)：遍历 t2 中的 id，如果不存在于 t1 中，则输出

我们举个栗子：

ANTI 则与之类似，只不过它的策略是不出现才输出，可以自己尝试一下。另外可能有人发现，这个 SEMI 的功能貌似有些重复了，因为我们使用 ALL 和 ANY 完全可以取代。其实如果你用过 hive 的话，会发现 SEMI LEFT JOIN 和 ANTI LEFT JOIN 是 IN/EXISTS 的一种更加高效的实现：

-- 这种子查询应该非常常见了，查询一张表，而过滤的条件该表的某个字段的取值要出现在另一张表的某个字段中
SELECT id, code1 FROM tbl_1 WHERE id in (SELECT id FROM tbl_2);
/*
┌─id─┬─code1─┐
│  1 │ A001  │
│  3 │ A003  │
└────┴───────┘
*/

-- 而通过 SEMI LEFT JOIN 的话，效率会更高一些
SELECT t1.id, t1.code1 FROM tbl_1 t1 
SEMI LEFT JOIN tbl_2 t2 USING(id)
/*
┌─id─┬─code1─┐
│  1 │ A001  │
│  3 │ A003  │
└────┴───────┘
*/

-- ANTI 则是为了 NOT IN/EXISTS

两者的输出是一致的，所以 SEMI / ANTI LEFT JOIN 是为了 IN/EXISTS 这类场景而设计的，至于 SEMI RIGHT JOIN、ANTI RIGHT JOIN 就用的不是很多了。

Hive 里有一个 LEFT SEMI JOIN，单词顺序调换了一下，用途是类似的，不过它的局限性要比 ClickHouse 中的 SEMI LEFT JOIN 大很多。

-- Hive，这个 t2.xxx 只能出现在 ON 子句中用于连接，不可用在其它地方
t1 LEFT SEMI JOIN t2 ON t1.id = t2.id

-- ClickHouse，t2.xxx 除了可以出现在 ON 子句中，可以出现在 SELECT 子句中，WHERE 子句中
t1 SEMI LEFT JOIN t2 ON t1.id = t2.id

-- 举个栗子：
SELECT t1.id, t1.code1, t2.code2 
FROM tbl_1 t1 SEMI 
LEFT JOIN tbl_2 t2 
USING(id) where t2.code2 = 'B003'
/*
┌─id─┬─code1─┬─code2─┐
│  3 │ A003  │ B003  │
└────┴───────┴───────┘
*/

另外 Hive 里面只有 LEFT SEMI JOIN，没有其它的，但 ClickHouse 的选择就多了很多。

多表连接

在进行多张数据表的连接查询时，ClickHouse 会将它们转为两两连接的形式。我们首先再创建一张表：

然后对三张测试表进行连接查询：

SELECT t1.id, t1.code1, t2.code2, t3.code3
FROM tbl_1 AS t1 INNER JOIN tbl_2 AS t2 ON t1.id = t2.id
LEFT JOIN tbl_3 AS t3 ON t1.id = t3.id

在执行上述查询时，tbl_1 和 tbl_2 会先进行内连接，之后再将它们的结果集合 tbl_3 进行左连接。

另外 ClickHouse 也支持关联查询的语法，只不过会自动转成指定的连接查询，举个栗子：

-- 关联查询，如果没有 WHERE，那么三张表会做笛卡尔积
SELECT t1.id, t1.code1, t2.code2, t3.code3
FROM tbl_1 t1, tbl_2 t2, tbl_3 t3 
WHERE t1.id = t2.id AND t1.id = t3.id
/*
┌─t1.id─┬─t1.code1─┬─t2.code2─┬─t3.code3─┐
│     3 │ A003     │ B003     │ C003     │
└───────┴──────────┴──────────┴──────────┘
*/

以上就是关联查询，虽然也能实现，不过还是不推荐这种做法，因为此时连接条件和过滤条件都写在了 WHERE 子句里面，看起来会比较混乱。所以更推荐连接查询（ClickHouse 会自动转化），也就是 JOIN ON 的形式，此时 ON 后面写连接条件，而数据过滤条件写 WHERE 里面（当然我们这里不需要过滤）。

SELECT t1.id, t1.code1, t2.code2, t3.code3
FROM tbl_1 t1 INNER JOIN tbl_2 t2 ON t1.id = t2.id
INNER JOIN tbl_3 t3 ON t1.id = t3.id
/*
┌─t1.id─┬─t1.code1─┬─t2.code2─┬─t3.code3─┐
│     3 │ A003     │ B003     │ C003     │
└───────┴──────────┴──────────┴──────────┘
*/

注意事项

最后，还有两个关于 JOIN 查询的注意事项。

1. 关于性能

最后，还有两个关于 JOIN 查询的注意事项。为了能够优化 JOIN 查询性能，首先应该遵循左大右小的原则，即数据量小的表要放在右侧。这是因为在执行 JOIN 查询时，无论使用的是哪种连接方式，右表都会被全部加载到内存中与左表进行比较。

其次，JOIN 查询目前没有缓存的支持，这意味着每一次 JOIN 查询，即便是连续执行相同的 SQL，也都会生成一次全新的执行计划。如果应用程序会大量使用 JOIN 查询，则需要进一步考虑借助上层应用侧的缓存服务或使用 JOIN 表引擎来改善性能。

最后，如果是在大量维度属性补全的查询场景中，则建议使用字典代替 JOIN 查询。因为在进行多表的连接查询时，查询会转换成两两连接的形式，而这种滚雪球式的查询很可能带来性能问题。

2. 空值策略

在之前的介绍中，连接查询的空值（那些未被连接的数据）是由默认值填充的，这与其他数据库所采取的策略不同（由Null 填充）。连接查询的空值策略通过 join_use_nulls 参数指定的，默认为 0。当参数值为 0 时，空值由数据类型的默认值填充；而当参数值为 1 时，空值由 Null 填充。

WHERE 与 PREWHERE 子句

WHERE 子句基于条件表达式来实现数据过滤，如果过滤条件恰好是主键字段，则能够进一步借助索引过滤数据区间，从而加速查询，所以 WHERE 子句是一条查询语句能否启用索引的判断依据，前提是表引擎支持索引特性。

除了 WHERE，ClickHouse 还支持全新的 PREWHERE 子句，PREWHERE 目前只能用于 MegeTee 系列的表引擎，它可以看作对是 WHERE 的一种优化，其作用与 WHERE 相同，均是用来过滤数据。但它们的不同之处在于。使用 PREWHERE 时，首先只会读取 PREWHERE 指定的列字段数据，用于数据过滤的条件判断。待数据过滤之后再读取 SELECT 声明的列字段以补全其余属性。所以在一些场合下，PREWHERE 相比 WHERE 而言，处理的数据量更少，性能更高。

既然 WHERE 子句性能更优，那么是否需要将所有的 WHERE 子句都替换成 PREWHERE 子句呢？其实大可不必，因为 ClickHouse 实现了自我优化的功能，会在条件合适的情况下将 WHERE 替换为 PREWHERE。如果想开启这项特性，只需要将 optimize_move_to_prewhere 设置为 1 即可，当然默认就为 1，即开启状态。

但凡事也有例外，以下情形不会自动优化：

1）使用了常量表达式：

SELECT id, code FROM tbl WHERE 1 = 1

2）使用了默认值为 ALIAS 类型的字段：

-- 假设 code 的默认值类型是 ALIAS
SELECT id, code FROM tbl WHERE code = 'A000'

3）包含了 arrayJoin、globalIn、globalNotIn 或者 indexHint 查询的：

SELECT title, nested.v1, nested.v2 FROM tbl ARRAY JOIN nested WHERE nested.v1 = 1

4）SELECT 查询的列字段和 WHERE 谓词相同：

SELECT v3 FROM tbl WHERE v3 = 1

5）使用了主键字段：

SELECT id FROM tbl WHERE id = 'A000'

虽然在上述情形中 ClickHouse 不会自动将谓词移动到 PREWHERE，但仍然可以主动使用 PREWHERE。以主键字段为例，当使用 PREWHERE 进行主键查询时，首先会通过稀疏索引过滤数据区间（index_granularity 粒度），接着会读取 PREWHERE 指定的条件列进一步过滤，这样一来就有可能截掉数据区间的尾巴，从而返回低于 index_granularity 粒度的数据范围。但即便如此，相比其他场合移动谓词所带来的性能提升，这类效果还是比较有限的，所以目前 ClickHouse 在这类场合下仍然保持不移动的处理方式。

GROUP BY 子句

GROUP BY 又称聚合查询，是最常用的子句之一，它是让 ClickHouse 最凸显卓越性能的地方。在 GROUP BY 后声明的表达式，通常称为聚合键或者 Key，数据会按照聚合键进行聚合。ClickHouse 的聚合查询中，和关系型数据库也是类似的。

-- 只有聚合函数，可以省略 GROUP BY
SELECT sum(data_compressed_bytes) AS compressed,
       sum(data_uncompressed_bytes) AS undata_compressed_bytes
FROM system.parts;

-- SELECT 子句中的字段要么出现在 GROUP BY 子句中，要么出现在聚合函数中
SELECT table, count() FROM system.parts GROUP BY table;

-- 错误的语法，rows 既没有出现在 GROUP BY 中，也没有出现在聚合函数中
SELECT table, count(), rows() FROM system.parts GROUP BY table;

如果聚合键对应的列包含 Null 值，那么所有的 Null 会被归为同一组。

我们看到所有的 Null 被分为了一组，但是注意：count(字段) 不会把 Null 计算在内，所以直接 count() 就行。

比较简单，但除了上述特性之外，聚合查询还能配合 WITH ROLLUP、WITH CUBE、WITH TOTALS 三种修饰符获取额外的汇总信息。

WITH ROLLUP

测试数据如下：

以上是普通的 GROUP BY，没什么难的，然后看看它和 WITH ROLLUP 搭配会有什么效果：

我们注意到，多了四条数据，上面三条，就是按照 product、channel 汇总之后，再单独按 product 汇总，而此时会给对应的 channel 设为零值（这里是空字符串，关系型数据库中为 Null）。同理最后一条数据是全量汇总，不需要指定 product 和 channel，所以显示为 product 和 channel 都显示为零值。我们看到这就相当于按照 product 单独聚合然后再自动拼接在上面了，排好序，并且自动将 channel 赋值为零值，同理最后一条数据也是如此。当然我们也可以写多个语句，然后通过 UNION 也能实现上面的效果，有兴趣可以自己试一下。但是 ClickHouse 提供了 WITH ROLLUP 这个非常方便的功能，我们就要利用好它。

GROUP BY 子句加上 WITH ROLLUP 选项时，首先按照全部的分组字段进行分组汇总；然后从右往左依次去掉一个分组字段再进行分组汇总，被去掉的字段显示为零值；最后，将所有的数据进行一次汇总，所有的分组字段都显示为零值。

WITH CUBE

CUBE 代表立方体，它用于对分组字段进行各种可能的组合，能够产生多维度的交叉统计结果，CUBE 通常用于数据仓库中的交叉报表分析。

从以上结果可以看出，CUBE 返回了更多的分组数据，其中不仅包含了 ROLLUP 汇总的结果，还包含了相当于按照 channel 进行聚合的记录。因此随着分组字段的增加，CUBE 产生的组合将会呈指数级增长。

WITH TOTALS

WITH TOTALS 反而是最简单的，只包含一个全局汇总的结果。

HAVING 子句

HAVING 子句要和 GROUP BY 子句同时出现，不能单独使用，它能够在聚合计算之后实现数据的二次过滤。

对于上面的栗子，使用 WHERE 比使用 HAVING 的效率更高，因为 WHERE 等同于使用了谓词下推，在聚合之前就减少了数据过滤，从而减少了后续聚合时需要处理的数据量。

所以使用 HAVING 进行过滤，那么应该是对聚合之后的结果进行过滤。如果不是聚合之后的，那么使用 WHERE 就好，举个栗子：

因为 WHERE 的优先级大于 GROUP BY，所以如果按照聚合值进行统计，那么就必须要借助于 HAVING。

ORDER BY 子句

ORDER BY子句通过声明排序键来指定查询数据返回的顺序，通过先前的介绍我们知道，在 MergeTree 表引擎中也有 ORDER BY 参数用于指定排序键，那么这两者有何不同呢？在 MergeTree 中指定 ORDER BY 后，数据在各个分区内会按照其定义的规则排序，这是一种分区内的局部排序。如果在查询时数据跨越了多个分区，则它们的返回顺序是无法预知的，每一次查询返回的顺序都有可能不同。在这种情况下，如果需要数据总是能够按照期望的顺序范围，就需要借助 ORDER BY 子句来指定全局顺序。

ORDER BY 在使用时可以定义多个排序键，每个排序键后需紧跟 ASC（升序）或  DESC（降序）来确定排列顺序。如若不写，则默认为 ASC。例如下面的两条语句即是等价的：

SELECT * FROM tbl ORDER BY v1 ASC, v2 DESC;
SELECT * FROM tbl ORDER BY v1, v2 DESC;

数据首先会按照 v1 升序，如果 v1 字段中出现了相同的值，那么再按照 v2 降序。

然后是 Null 值的排序，目前 ClickHouse 有 Null 值最后和 Null 值最前两种策略，可以通过如下进行设置：

1. NULLS LAST

Null 值排在最后，无论升序还是降序，这也是默认的行为。

value -> NaN -> Null

 

2. NULLS FIRST

Null 值排在最后，无论升序还是降序。

NULL -> NaN -> value

经过测试不难发现，对于 NaN 而言，它总是跟在 Null 的身边。

LIMIT BY 子句

LIMIT BY 子句和大家常见的 LIMIT 有所不同，它运行于 ORDER BY 之后和 LIMIT 之前，它能够按照指定分组，最多返回前 n 行数据（少于 n 行则按照实际数量返回），常用于 TOP N 的查询场景。LIMIT BY 语法规则如下：

LIMIT n BY express

个人觉得这个 LIMIT BY 非常强大，我们举个栗子：

当然聚合之后没有排序，我们还可以排一下序：

SELECT
    product,
    channel,
    sum(amount) AS amount
FROM sales_data
GROUP BY
    product,
    channel
ORDER BY amount ASC
LIMIT 1 BY channel

此时会选择每个渠道对应的金额最高的数据，当然我们也可以 LIMIT 多条数据、也可以 BY 多个字段。这个功能可以说是非常常用了，我们平时使用的 LIMIT，一般是全局排序之后选择前 N 条数据，而这里的 LIMIT BY 是按照指定的字段分组，然后每一组选择前 N 条数据。

LIMIT BY 会从上往下在每个组中选择指定条数的数据，因此使用 LIMIT BY 应该同时指定 ORDER BY，否则拿出的数据没有太大意义，除非数据本身就是有序的。

当然 LIMIT BY 也可以指定偏移量，因为不一定从一条开始选择，而指定偏移量有两种方式：

  • LIMIT N OFFSET M BY ...
  • LIMIT M, N BY ...

LIMIT 子句

LIMIT 子句用于返回指定的前 N 行数据，常用于分页场景，它的三种语法形式如下：

LIMIT N
LIMIT N OFFSET M
LIMIT M, N

用法和 LIMIT BY 中的 LIMIT 一致，如果把 LIMIT BY 中的 BY 去掉，那么就变成了 LIMIT。比较简单，这里用一张图来介绍一下 LIMIT 和 LIMIT BY 之前的关系：

比较简单，但是在使用 LIMIT 的时候需要注意一点，如果数据跨越了多个分区，那么在没有使用 ORDER BY 指定全局顺序的情况下，每次 LIMIT 查询所返回的数据可能有所不同。如果对返回的数据的顺序比较敏感，则应搭配 ORDER BY 一起使用。

SELECT 和 DISTINCT 子句

SELECT 子句决定了一次查询语句最终能返回哪些字段或表达式，与直观感受不同，虽然 SELECT 位于 SQL 语句的起始位置，但它的执行的顺序却排在了上面介绍的所有子句的后面。在其它子句执行之后，SELECT 会将选取的字段或表达式作用于每行数据之上，如果使用 * 通配符，则会返回所有字段。但正如开篇所言，大多数情况下都不建议这么做，因为对于一款列式存储数据库而言，这绝对是劣势而不是优势（我们这里在学习的过程就不算了）。

在选择列字段时，ClickHouse 还为特定场景提供了一种基于正则查询的形式，例如下面会选择以 n 开头和包含字母 p 的字段：

SELECT COLUMNS('^n'), COLUMNS('p') FROM system.databases

DISTINCT 子句能够去除重复数据，使用场景也很广泛，很多人经常会拿它和 GROUP BY 进行对比：

虽然顺序不同，但显然结果集的内容是一致的，那么这两者之间有什么区别呢？如果观察它们的执行计划（后面会说）不难发现，DISTINCT 子句的执行计划会更加简单，与此同时，DISTINCT 也能够和 GROUP BY 搭配使用，所以它们是互补而不是互斥的关系。

另外，如果使用了 LIMIT 且没有 ORDER BY 子句，那么 DISTINCT 在满足条件时能够立即结束查询。假设我只需要去重之后的前三条数据，那么 GROUP BY 会对全体数据进行分组，然后再选择前三条；而 DISTINCT 在去重时发现已经有三条了，于是直接返回，后面的数据就不需要看了，因为看了也没意义，LIMIT 决定了只返回三条。

两个查询返回的结果集不一样，这是因为 GROUP BY 和 DISTINCT 处理数据的顺序不同。一开始我们就看到了，如果没有 LIMIT，那么两个结果集顺序不同，但内容是一样的，只是这里加了 LIMIT，所以相当于选择了相同内容的不同部分。

如果有 ORDER BY，那么会先执行 DISTINCT，再执行 ORDER BY。并且对于 Null 而言，如果有多个 Null，那么 DISTINCT 之后只会保留一个 Null。

UNION ALL 子句

UNION ALL 子句能够联合左右两边的两组子查询，将结果一并返回。在一次查询中可以声明多次 UNION ALL 以便联合多组查询，但 UNION ALL 不能直接使用其他子句（例如 ORDER BY、LIMIT 等），这些子句只能在它联合的子查询中使用。

SELECT name, v1 FROM union_v1
UNION ALL
SELECT title, v1 FROM union_v1

对于 UNION ALL 两侧的子查询有以下几点信息：首先，列字段的数量必须相同；其次，列字段的数据类型必须相同或相兼容；最后，列字段的名称可以不同，查询结果中的列名会以左边的子查询为准。

对于联合查询还有一点要说明，目前 ClickHouse 只支持 UNION ALL 子句，如果想得到 UNION DISTINCT 子句的效果，可以使用嵌套查询来变相实现，例如：

SELECT DISTINCT name FROM
(
    SELECT name, v1 FROM union_v1
    UNION ALL
    SELECT title, v1 FROM union_v1
)

SAMPLE 子句

SAMPLE 子句能够实现数据采样的功能，使查询仅返回采样数据而不是全部数据，从面有效减少查询负载。SAMPLE 子句的采样机制是一种幂等设计，也就是说在数据不发生变化的情况下，使用相同的采样规则总是能等返回相同的数据，所以这项特性非常适合在那些可以接受近似查询结果的场合使用。例如在数据量十分巨大的情况下，对查询时效性的要求大于准确性时就可以尝试使用 SAMPLE 子句。

SAMPLE 子句只能用于 MergeTree 系列引擎的数据表，并且要求在 CREATE TABLE 时声明 SAMPLE BY 表达式，例如：

CREATE TABLE hits_v1 (
    CounterID UInt64,
    EventDate Date,
    UserID UInt64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, intHash32(UserID))
-- SAMPLE BY 声明的表达式必须要包含在主键的声明中
SAMPLE BY intHash32(UserID)

SAMPLE BY 表示 hits_v1内的数据，可以按照 intHash32(UserID) 分布后的结果采样查询。但需要注意：SAMPLE BY 所声明的表达式必须同时包含在主键的声明内，并且选择的字段必须是 Int 类型，如果不是 ClickHouse 在建表的时候也不会报错，但查询的时候会出异常。

SAMPLE 子句目前支持如下 3 种用法：

1. SAMPLE factor

SAMPLE factor 表示按因子系数采样，其中 factor 表示采样因子，它的取值支持0~1 之间的小数。如果 factor 设置为 0 或者 1，则效果等同于不进行数据采样。

SELECT CounterID FROM hits_v1 SAMPLE 0.1

factor 也支持使用十进制的形式表述。

SELECT CounterID FROM hits_v1 SAMPLE 1 / 10

如果在进行统计查询时，为了得到最终的近似结果，需要将得到的直接结果乘以采样系数。例如想按照 0.1 的因子采样数据，则需要将统计结果放大 10 倍。

SELECT count() * 10 FROM hits_v1 SAMPLE 0.1

一种更为优雅的方法是借助虚拟字段 _sample_factor 来获取采样系数，并以此代替硬编码的形式，_sample_factor 可以发那会当前查询所对应的采样系数。

SELECT count() * any(_sample_factor) FROM hits_v1 SAMPLE 0.1

2. SAMPLE rows

SAMPLE rows 表示按样本数量采样，其中 rows 表示至少采样多少行数据，它的取值必须是大于 1 的整数。如果 rows 的取值大于表内数据的总行数，则效果等于 rows = 1，也就是不使用采样。

比如我们采样 10000 行数据：

SELECT count() FROM hits_v1 SAMPLE 10000;

虽然我们采样 10000 行，但是不一定就返回 10000 行，因为数据采样是一个近似范围，这是由于采样数据的最小粒度由 index_granularity 索引粒度所决定的。由此可知，设置一个小于索引粒度或者较小的 rows 没有什么意义，应该设置一个比较大的值。另外，同样可以使用虚拟字段 _sample_factor 来获取当前查询对应的采样系数。

4. SAMPLE factor OFFSET n

SAMPLE factor OFFSET n 表示按因子系数和偏移量采样，其中 factor 表示采样因子，n 表示偏移多少数据后才开始采样，它们两个的取值都是 0~1 之间的小数。例如下面的语句表示偏移量为 0.5 并按 0.4 的系数采样：

SELECT CounterID FROM hits_v1 SAMPEL 0.4 OFFSET 0.5

上述查询会从数据的二分之一处开始，按照 0.4 的系数采样数据：

如果在计算 OFFSET 偏移量后，按照 SAMPLE 比例采样出现了溢出，则数据会被自动截断。

当然这种做法也支持虚拟字段。

小结

以上就是 ClickHouse 关于查询方面的内容，可以肯定的是内容绝对不止这些，因为和关系型数据库重叠的部分这里自动省略或者一笔带过了，比如空值如何处理（nullif、coalesce）、IN 查询、LIKE 查询、什么是子查询、CASE WHEN 语句等等等等。如果大部分的关系型数据库都支持的语法，那么在 ClickHouse 中基本也是支持的。所以个人觉得有 MySQL 相关经验的话，至少在 ClickHouse 的查询方面，绝对是非常好上手的，没事多写一写就行。

接下来我会介绍 ClickHouse 中关于操作数组的函数，到时候也会刻意地融入更多的语法（这里介绍的，和没有介绍的）。因为 ClickHouse 中提供了大量的函数，通过这些函数 ClickHouse 在处理数据就能够变得所向披靡，但我们不可能一下全说完，这里就先拿数组开刀，因为它相对更复杂一些。

posted @ 2021-09-03 15:59  古明地盆  阅读(19484)  评论(2)  编辑  收藏  举报