ClickHouseNotes

第 06 章：查询基础

zjc 于 2026-01-06 发布

这是《ClickHouse 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 本章把常用查询能力串起来：投影、过滤、聚合、窗口、排序、去重、格式输出和查询生命周期。目标是写出可预测成本的查询，而不只是得到结果。

6.1 SELECT 执行结构

SELECT city_id, count() AS pv
FROM analytics.events_local
WHERE event_date = today()
GROUP BY city_id
HAVING pv > 100
ORDER BY pv DESC
LIMIT 20;

概念执行顺序：

FROM -> WHERE -> GROUP BY -> 聚合 -> HAVING -> SELECT -> ORDER BY -> LIMIT

ClickHouse 会做多种优化，但写 SQL 时仍应先通过过滤条件减少扫描量。

6.2 查看查询成本

SELECT
    query_id,
    event_time,
    query_duration_ms,
    read_rows,
    read_bytes,
    result_rows,
    memory_usage,
    exception
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY event_time DESC
LIMIT 20;

关键指标：

指标 含义
read_rows 读取行数
read_bytes 读取字节数
result_rows 返回行数
query_duration_ms 总耗时
memory_usage 内存峰值
ProfileEvents 更细粒度事件

优化优先看扫描量和分区剪枝，而不是只看总耗时。

6.3 聚合

SELECT
    event_type,
    count() AS events,
    countDistinct(user_id) AS uv,
    round(sum(amount), 2) AS gmv,
    round(avg(amount), 2) AS avg_amount
FROM analytics.events_local
WHERE event_date = today()
GROUP BY event_type;

常用组合：

表达式 说明
count() 精确计数，通常比 count(*) 更符合习惯
sum(x) 求和，注意空组结果
min(x) / max(x) 极值
uniq(x) 近似去重
uniqExact(x) 精确去重
quantile(0.95)(x) 分位数
topK(10)(x) 高频值

6.4 条件与空值

SELECT
    if(amount > 0, 'paid', 'free') AS event_scope,
    multiIf(
        amount >= 1000, 'high',
        amount >= 100, 'middle',
        'low'
    ) AS amount_level,
    coalesce(city_name, 'unknown') AS city
FROM analytics.event_view;

逻辑条件：

SELECT count()
FROM analytics.events_local
WHERE event_date BETWEEN '2026-08-01' AND '2026-08-31'
  AND (city_id = 1 OR city_id = 2)
  AND event_type NOT IN ('spam');

6.5 子查询与 CTE

CTE：

WITH daily AS (
    SELECT
        event_date,
        city_id,
        count() AS pv
    FROM analytics.events_local
    WHERE event_date >= today() - 7
    GROUP BY event_date, city_id
)
SELECT
    city_id,
    sum(pv) AS week_pv,
    max(pv) AS max_day_pv
FROM daily
GROUP BY city_id
ORDER BY week_pv DESC;

CTE 提升可读性，但不等于自动缓存。复杂公共子查询是否能复用要结合执行计划确认。

6.6 窗口函数

SELECT
    event_date,
    city_id,
    pv,
    row_number() OVER (PARTITION BY event_date ORDER BY pv DESC) AS rn,
    rank() OVER (PARTITION BY event_date ORDER BY pv DESC) AS rk,
    sum(pv) OVER (PARTITION BY event_date) AS day_total
FROM (
    SELECT event_date, city_id, count() AS pv
    FROM analytics.events_local
    WHERE event_date >= today() - 3
    GROUP BY event_date, city_id
);

分组 TopN：

SELECT event_date, city_id, pv
FROM (
    SELECT *,
       row_number() OVER (PARTITION BY event_date ORDER BY pv DESC) AS rn
    FROM (
        SELECT event_date, city_id, count() AS pv
        FROM analytics.events_local
        GROUP BY event_date, city_id
    )
)
WHERE rn <= 10;

窗口函数通常在聚合结果上使用。直接对超大数据集开窗口会造成大量排序和内存消耗。

6.7 数组与漏斗

展开数组：

SELECT
    user_id,
    arrayJoin(event_ids) AS event_id
FROM analytics.user_events;

漏斗：

SELECT
    level,
    count() AS users
FROM (
    SELECT
        user_id,
        windowFunnel(86400)(
            toUnixTimestamp(event_time),
            event_type = 'view',
            event_type = 'cart',
            event_type = 'pay'
        ) AS level
    FROM analytics.events_local
    WHERE event_date = today()
    GROUP BY user_id
)
GROUP BY level
ORDER BY level;

留存：

SELECT
    sum(retention(event_type = 'install', event_date = today())) AS d1,
    sum(retention(event_type = 'install', event_date = today() + 1)) AS d2
FROM analytics.events_local;

漏斗和留存函数的窗口定义、事件顺序和时间语义必须在业务侧先明确。

6.8 关联查询

内连接：

SELECT e.event_id, c.city_name
FROM analytics.events_local AS e
INNER JOIN analytics.city_dim AS c
    ON e.city_id = c.city_id
WHERE e.event_date = today();

字典替代小维表：

SELECT
    event_id,
    dictGet('analytics.city_dict', 'city_name', toUInt64(city_id)) AS city_name
FROM analytics.events_local
WHERE event_date = today();

字典适合小而更新可控的维表，不适合频繁全量刷新的超大维度。

6.9 查询限制

用户级限制示例：

ALTER USER app_user
SETTINGS max_execution_time = 30, max_memory_usage = 2000000000;

语句级限制：

SELECT count()
FROM analytics.events_local
SETTINGS max_threads = 8, max_execution_time = 30;

常用保护：

参数 目的
max_execution_time 限制执行时间
max_memory_usage 限制单查询内存
max_rows_to_read 限制扫描行数
max_bytes_to_read 限制扫描字节
readonly 禁止写操作
max_result_rows 限制返回规模

6.10 结果格式

clickhouse-client --query "
SELECT number, number * 2
FROM system.numbers
LIMIT 3
FORMAT PrettyCompact
"

服务返回 JSON：

SELECT count()
FROM analytics.events_local
FORMAT JSONEachRow;

大结果集导出建议用 clickhouse-client 或对象存储，不要通过 HTTP 一次性拉取到应用内存。

6.11 查询故障排查

慢查询清单：

1. 是否有分区条件？
2. 条件是否作用在排序键前缀？
3. 是否扫描了大量不需要的列？
4. 是否高基数 group by 或精确去重？
5. 是否大表 JOIN？
6. 是否窗口函数作用在明细上？
7. 是否返回超大结果集？
8. 是否资源被其他查询占用？

查看运行中查询：

SELECT query_id, elapsed, read_rows, memory_usage, query
FROM system.processes
ORDER BY elapsed DESC;

取消查询：

KILL QUERY WHERE query_id = 'target-query-id';

本章小结

查询 ClickHouse 时，先让分区剪枝和排序键发挥作用，再选择合适聚合与去重函数。窗口函数、精确去重和大 JOIN 都是高成本能力，应配合过滤、预聚合、物化视图和资源限制使用。system.query_log 是性能治理的起点。

思考题

  1. 如何判断一个查询是否命中分区剪枝？
  2. uniq 和 uniqExact 如何选择？
  3. 窗口函数为什么常放在聚合之后？
  4. 字典 JOIN 适合什么维表？
  5. 你会给报表用户设置哪些查询限制？