ClickHouse学习小记--数据查询

作为一款OLAP分析型数据库，绝大部分时间内都在使用它的查询功能。ClickHouse完全使用SQL作为查询语言，能够以SELECT查询语句的形式从数据库中选取数据，这也是它具备流行潜质的重要原因。虽然ClickHouse拥有优秀的查询性能，但是我们也不能滥用查询，掌握ClickHouse所支持的各种查询子句，并选择合理的查询形式是很有必要的。
例如在绝大部分场景中，都应该避免使用SELECT 形式来查询数据，因为通配符对于采用列式存储ClickHouse而言没有任何好处。假如面对一张拥有数百个列字段的数据表，下面这两条SELECT语句的性能可能会相差100倍之多：

--使用通配符*与按列按需查询相比，性能可能相差100倍 
SELECT * FROM datasets.hits_v1;
SELECT WatchID FROM datasets.hits_v1;

ClickHouse对于SQL语句的解析是大小写敏感的，这意味着SELECT a和SELECT A表示的语义是不相同的。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]

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.1、定义变量

可以定义变量，这些变量能够在后续的查询子句中被直接访问。例如下面示例中的常量start，被直接用在紧接的WHERE子句中：

WITH 10 AS start SELECT number FROM system.numbers WHERE number > start LIMIT 5

number
11
12
13
14
15

1.2、调用函数

可以访问SELECT子句中的列字段，并调用函数做进一步的加工处理。例如在下面的示例中，对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
datasets	12.12 GiB
default	1.87 GiB
system	1.10 MiB
dictionaries	0.00 B

1.3、定义子查询

可以定义子查询。例如在下面的示例中，借助子查询可以得出各database未压缩数据大小与数据总和大小的比例的排名：

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中使用子查询时有一点需要特别注意，该查询语句只能返回一行数据，如果结果集的数据大于一行则会抛出异常。

1.4、在子查询中重复使用WITH

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

WITH (
  round(database_disk_usage) 
) AS database_disk_usage_v1 
SELECT database,database_disk_usage, database_disk_usage_v1 FROM 
(