《ClickHouse原理解析与应用实践》读书笔记（6）

开始学习《ClickHouse原理解析与应用实践》，写博客作读书笔记。

本文全部内容都来自于书中内容，个人提炼。

第7章-第8章： 

《ClickHouse原理解析与应用实践》读书笔记（5）_Aiky哇的博客-CSDN博客表引擎介绍：· MergeTree系列表引擎介绍。· 外部存储类型的表引擎、内存类型的表引擎、日志类型表引擎、接口类型的表引擎和其他类型的表引擎介绍。https://aikysay.blog.csdn.net/article/details/125587096

第9章　数据查询

真正的生产环境中，在绝大部分场景中，都应该避免使用SELECT * 形式来查询数据，因为通配符*对于采用列式存储的ClickHouse而言没有任何好处。

ClickHouse目前支持的查询子句如下所示：

[WITH expr |(subquery)]
SELECT [DISTINCT] expr
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE expr]
[[LEFT] ARRAY JOIN]
[GLOBAL] [ALL|ANY|ASOF] [INNER | CROSS | [LEFT|RIGHT|FULL [OUTER]] ] JOIN (subquery)|table ON|USING columns_list
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr] [WITH ROLLUP|CUBE|TOTALS]
[HAVING expr]
[ORDER BY expr]
[LIMIT [n[,m]]
[UNION ALL]
[INTO OUTFILE filename]
[FORMAT format]
[LIMIT [offset] n BY columns]

 方括号包裹的查询子句表示其为可选项，只有SELECT子句是必须的。

9.1 WITH子句

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

SELECT pow(pow(2, 2), 3)

在改用CTE的形式后：

WITH pow(2, 2) AS a SELECT pow(a, 3)

CTE通过WITH子句表示，目前支持以下四种用法。

1.定义变量

可以定义变量，这些变量能够在后续的查询子句中被直接访问。

WITH 10 AS start
SELECT number FROM system.numbers
WHERE number > start
LIMIT 5
┌number─┐
│ 11 │
│ 12 │
│ 13 │
│ 14 │
│ 15 │
└─────┘

2.调用函数

可以访问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───┐
│ datasets │ 12.12 GiB │
│ default │ 1.87 GiB │
│ system │ 1.10 MiB │
│ dictionaries │ 0.00 B │
└─────────┴───────┘

例子中，data_uncompressed_bytes使用聚合函数求和后，又紧接着在SELECT子句中对其进行了格式化处理。

3.定义子查询

可以定义子查询。

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─┐
│ datasets │ 85.15608638238845 │
│ default │ 13.15591656190217 │
│ │ │
│ system │ 0.007523354055850406 │
│ dictionaries │ 0 │
└──────────┴──────────────┘

 WITH使用子查询语句，只能返回一行数据，如果结果集的数据大于一行则会抛出异常。

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

在子查询中可以嵌套使用WITH子句

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.colum
    GROUP BY database
    ORDER BY database_disk_usage DESC
)
┌─database────┬───database_disk_usage─┬─database_disk_usage_v1───┐
│ datasets │ 85.15608638238845 │ 85 │
│ default │ 13.15591656190217 │ 13 │
│ system │ 0.007523354055850406 │ 0 │
└─────────┴───────────────┴─────────────────┘

9.2 FROM子句

 支持三种形式：

1. 从数据表中获取数据。
2. 从子查询中获取数据。
3. 从表函数中获取数据。（比如SELECT number FROM numbers(5)）

FROM关键字可以省略，此时会从虚拟表中取数。在ClickHouse 中，并没有数据库中常见的DUAL虚拟表，取而代之的是system.one。

-- 以下两条查询等价
SELECT 1
SELECT 1 FROM system.one
┌─1─┐
│ 1 │
└───┘

在FROM子句后，可以使用Final修饰符，可以触发合并，但是会降低性能。

9.3 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 Key声明的表达式必须也包含在主键的声明中
SAMPLE BY intHash32(UserID)

例子表示按照intHash32(UserID)分布后的结果采样查询。

SAMPLE BY需要注意：

1. SAMPLE BY所声明的表达式必须同时包含在主键的声明内；
2. Sample Key必须