ClickHouse学习笔记八数据查询

作为一款 OLAP 分析型数据库，数据查询是 ClickHouse 的主要工作。ClickHouse 完全使用 SQL 作为查询语言。

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]

⚠️ ClickHouse 对 SQL 大小写敏感，SELECT a 和 SELECT A 语义不同。

1.1 WITH 子句

CTE（Common Table Expression，公共表表达式），支持四种用法。

1. 定义变量

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

2. 调用函数

WITH SUM(data_uncompressed_bytes) AS bytes
SELECT database, formatReadableSize(bytes) AS format
FROM system.columns
GROUP BY database
ORDER BY bytes DESC;

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;

⚠️ WITH 中的子查询只能返回一行数据。

4. 嵌套使用 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.columns
    GROUP BY database
);

1.2 FROM 子句

支持三种取数形式：

1. 从数据表取数： SELECT WatchID FROM hits_v1

2. 从子查询取数： SELECT MAX(WatchID) FROM (SELECT MAX(WatchID) AS MAX_WatchID FROM hits_v1)

3. 从表函数取数： SELECT number FROM numbers(5)

FROM 可省略，从 system.one 取数：

SELECT 1;  -- 等价于 SELECT 1 FROM system.one;

FINAL 修饰符：配合 CollapsingMergeTree 等在查询过程中强制合并，会降低性能。

1.3 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 intHash32(UserID);

⚠️ SAMPLE BY 表达式必须包含在 ORDER BY 主键中；Sample Key 必须是 Int 类型。

1. SAMPLE factor（按因子采样）

SELECT CounterID FROM hits_v1 SAMPLE 0.1;
SELECT CounterID FROM hits_v1 SAMPLE 1/10;
-- 统计时乘以采样系数
SELECT count() * 10 FROM hits_v1 SAMPLE 0.1;
-- 使用虚拟字段 _sample_factor
SELECT count() * any(_sample_factor) FROM hits_v1 SAMPLE 0.1;

2. SAMPLE rows（按行数采样）

SELECT count() FROM hits_v1 SAMPLE 10000;

采样粒度由 index_granularity 决定，设置过小的 rows 值无意义。

3. SAMPLE factor OFFSET offset

SELECT CounterID FROM hits_v1 SAMPLE 1/10 OFFSET 3/10;

1.4 ARRAY JOIN 子句

对数组进行展开：

SELECT URL, SearchPhrase
FROM hits_v1
ARRAY JOIN SearchPhrase
WHERE URL = 'https://example.com';

LEFT ARRAY JOIN 保留空数组的行。

1.5 JOIN 子句

[GLOBAL] [ALL|ANY|ASOF] [INNER | CROSS | LEFT|RIGHT|FULL [OUTER]] JOIN table ON|USING columns_list

1.5.1 连接精度

1.5.2 连接类型

INNER、LEFT、RIGHT、FULL OUTER、CROSS（笛卡儿积）。

1.5.3 多表连接

SELECT ... FROM t1
JOIN t2 USING (id)
JOIN t3 USING (id);

1.5.4 注意事项

• JOIN 前应先过滤（WHERE 写在子查询中）

• 右表数据量不宜过大

• 分布式 JOIN 用 GLOBAL 修饰符

1.6 WHERE 与 PREWHERE 子句

-- PREWHERE 适用于列较多的表
SELECT * FROM hits_v1 PREWHERE CounterID = 101500;

1.7 GROUP BY 子句

WITH ROLLUP

生成各级小计：

SELECT City, Browser, count()
FROM visits
GROUP BY City, Browser WITH ROLLUP;

输出：(City, Browser)、(City, ALL)、(ALL, ALL)

WITH CUBE

生成所有维度组合：

SELECT City, Browser, count()
FROM visits
GROUP BY City, Browser WITH CUBE;

输出：(City, Browser)、(City, ALL)、(ALL, Browser)、(ALL, ALL)

WITH TOTALS

结果末尾附加总计行：

SELECT City, count()
FROM visits
GROUP BY City WITH TOTALS;

1.8 HAVING 子句

在 GROUP BY 之后过滤聚合结果：

SELECT City, count() AS cnt
FROM visits
GROUP BY City
HAVING cnt > 100;

1.9 ORDER BY 子句

支持 ASC（默认）和 DESC，支持 NULLS FIRST / NULLS LAST。

1.10 LIMIT BY 子句

按字段分组后每组只取前 N 行：

SELECT City, WatchID
FROM hits
ORDER BY City, EventTime DESC
LIMIT 10 BY City;

1.11 LIMIT 子句

LIMIT [offset,] n

1.12 SELECT 子句

支持 SELECT DISTINCT 去重。

1.13 DISTINCT 子句

SELECT DISTINCT CounterID FROM hits;

1.14 UNION ALL 子句

合并两个结果集（只支持 UNION ALL，不支持 UNION）：

SELECT 1 AS x UNION ALL SELECT 2 AS x;

1.15 查看 SQL 执行计划

EXPLAIN [AST|SYNTAX|PLAN|HEADER|INDEXES|PIPELINE|ANALYZE] SELECT ...;