ClickHouse中的SELECT查询：与PostgreSQL的差异，PREWHERE，SAMPLE

ClickHouse中SELECT查询的详细指南，重点关注与PostgreSQL的差异。解释了PREWHERE（在读取列之前过滤，节省I/O），SAMPLE（概率采样，快速近似答案），FINAL（ReplacingMergeTree中的去重——何时需要以及为何避免），IN与子查询和JOIN的比较（在ClickHouse中IN更快），ANY/ALL修饰符，DISTINCT性能及替代方案（uniq，topK）。展示了输出格式：Pretty，JSON，CSV，JSONEachRow。提供了10个博彩分析就绪查询：按运动项目投注，按交易量排名靠前的玩家，按赔率计算的胜率，LTV，欺诈模式。列出了将SQL从PostgreSQL迁移到ClickHouse时的典型错误及具体修正示例。

By Sudonull Editorial Team • Published 2026-06-08 • Updated 2026-06-08

ClickHouse SELECT：哪些功能在PostgreSQL中不可用（以及哪些功能更好）

ClickHouse中的SELECT查询：在PostgreSQL十年后如何重塑思维

在使用了十年PostgreSQL后，当我开始接触ClickHouse时，我尝试运行一个熟悉的SELECT * FROM bets WHERE sport = 'football' ORDER BY created_at DESC LIMIT 10。它成功了，而且快得惊人——快得可疑。然后我了解了PREWHERE和SAMPLE——这些构造在PostgreSQL中由于行式存储而不存在，也不可能存在。

ClickHouse不仅仅是执行SQL——它为了列式架构重新思考了SQL。下面列出了所有让我查询出错（有时甚至影响生产环境）的差异。

1. 基本语法：熟悉的面孔，列式的内核

一个基本的SELECT看起来似曾相识：

-- 简单选择
SELECT user_id, amount, odds 
FROM betting.bets 
WHERE created_at >= today() - 7 
ORDER BY amount DESC 
LIMIT 100;

但差异从你查看EXPLAIN时开始。PostgreSQL会构建包含Seq Scan、Index Scan、Bitmap Heap Scan的计划。ClickHouse则显示读取的颗粒数和分区数。

关键区别： 在PostgreSQL中，SELECT *有时没问题（如果你需要几乎所有列）。在ClickHouse中，SELECT *会从磁盘读取所有列。如果你不需要30列中的20列——只列出你需要的列。我们通过这种方式节省了70%的磁盘I/O。

2. PREWHERE——PostgreSQL羡慕的优化

PREWHERE在列被解包和读取之前进行过滤。

-- 不使用PREWHERE（较慢）
SELECT user_id, amount, odds, sport
FROM betting.bets
WHERE outcome = 'win' AND amount > 1000;

-- 使用PREWHERE（更快）
SELECT user_id, amount, odds, sport
FROM betting.bets
PREWHERE outcome = 'win'
WHERE amount > 1000;

工作原理：

ClickHouse首先读取outcome列（磁盘上的一个文件）
过滤行，只保留win
仅针对过滤后的行，读取其余列
然后应用amount > 1000

ClickHouse何时自动应用PREWHERE： 如果你写WHERE outcome = 'win'，优化器可能会自动将轻量级条件移到PREWHERE。但对于复杂条件，我总是显式地写出来。

让我踩坑的地方： PREWHERE不适用于PRIMARY KEY中的列。ClickHouse仍然会先读取索引。不要试图优化已经很快的东西。

3. SAMPLE——0.1秒内的近似答案

在业务中，有时你会遇到这样的问题：“估算每小时的下注量，误差在5%以内。”绝对精度并不需要。

-- 10%随机行（SAMPLE 0.1）
SELECT 
    toHour(created_at) AS hour,
    count() * 10 AS estimated_total_bets
FROM betting.bets
SAMPLE 0.1
WHERE created_at >= now() - INTERVAL 1 HOUR
GROUP BY hour;

SAMPLE的物理工作原理： ClickHouse并非完全读取每个颗粒，而是每隔N个读取一个。这是因为磁盘上的数据没有打乱——在颗粒内部，数据是按ORDER BY排序的。

我使用SAMPLE的规则：

对于数十亿行的聚合——SAMPLE 0.01就足够了，精度在2-3%以内
不要用于精确计算（财务、支付）
仅当表创建时带有SAMPLE BY键（或ORDER BY）时才有效

4. FINAL——初学者的雷区

如果你使用ReplacingMergeTree（一种去重引擎），行可能有多个版本。FINAL强制ClickHouse在查询时合并它们。

-- 慢（但有时必要）
SELECT user_id, max(amount)
FROM betting.bets_replacing
FINAL
GROUP BY user_id;

我几乎从不使用FINAL的原因： 它会强制读取所有部分并在内存中合并。如果你有十亿行，查询会耗尽内存。

FINAL的替代方案：

使用argMax进行分组（推荐）
定期在后台执行OPTIMIZE TABLE ... FINAL
根本不使用ReplacingMergeTree

-- 替代FINAL
SELECT user_id, argMax(amount, version) AS last_amount
FROM betting.bets_replacing
GROUP BY user_id;

5. 子查询中的IN/NOT IN vs JOIN

在PostgreSQL中，JOIN通常比子查询快。在ClickHouse中，情况正好相反——带子查询的IN通常胜出。

-- ClickHouse中快
SELECT user_id, sum(amount)
FROM betting.bets
WHERE user_id IN (SELECT user_id FROM betting.fraud_users)
GROUP BY user_id;

-- 较慢（但更易读）
SELECT b.user_id, sum(b.amount)
FROM betting.bets b
JOIN betting.fraud_users f ON b.user_id = f.user_id
GROUP BY b.user_id;

为什么IN更快： ClickHouse将子查询转换为内存中的一组常量，并使用列式操作进行过滤。JOIN需要逐行匹配。

何时仍需要JOIN：

超过两个表
需要在SELECT中获取两个表的列
复杂的连接条件（不仅仅是等值）

6. ANY / ALL修饰符——早期遗留物

这些修饰符是为了与其他数据库管理系统兼容而存在的。我很少使用它们。

-- ANY：类似于分组的MIN
SELECT user_id, ANY(sport) AS any_sport
FROM betting.bets
GROUP BY user_id;

-- ALL：类似于MAX
SELECT user_id, ALL(amount) AS all_amounts  -- 所有金额的数组
FROM betting.bets
GROUP BY user_id;

但我更喜欢显式的聚合函数：min()、max()、groupArray()。

7. DISTINCT及其性能

ClickHouse中的SELECT DISTINCT比PostgreSQL快，但并非免费。

-- 所有唯一的运动项目
SELECT DISTINCT sport FROM betting.bets;

-- DISTINCT与ORDER BY
SELECT DISTINCT user_id, created_at
FROM betting.bets
ORDER BY created_at DESC
LIMIT 100;

内部原理： ClickHouse在内存中构建一个哈希表。如果你对具有十亿个唯一值的列运行DISTINCT——你会遇到内存不足。

我的建议：

不要使用SELECT DISTINCT user_id，而是使用GROUP BY user_id（结果相同）
对于近似唯一计数——使用uniq()和uniqHLL12()
对于前N个唯一值——使用topK()

8. FORMAT——按客户端需求输出

ClickHouse可以以数十种格式返回结果。我使用五种：

-- 人类可读（用于控制台）
SELECT * FROM bets LIMIT 3 FORMAT Pretty;

-- 紧凑（默认）
SELECT * FROM bets LIMIT 3 FORMAT PrettyCompact;

-- JSON用于API
SELECT * FROM bets LIMIT 3 FORMAT JSON;

-- 逐行JSON（解析时节省内存）
SELECT * FROM bets LIMIT 3 FORMAT JSONEachRow;

-- CSV用于Excel
SELECT * FROM bets LIMIT 3 FORMAT CSV;

# 在命令行中，你可以覆盖格式
clickhouse-client --format=JSON --query="SELECT * FROM bets LIMIT 3"

9. 博彩分析十大查询（生产就绪）

1. 今日按运动项目的投注

SELECT 
    sport,
    count() AS bets,
    sum(amount) AS total_staked,
    round(avg(odds), 2) AS avg_odds
FROM betting.bets
WHERE created_at >= today()
GROUP BY sport
ORDER BY total_staked DESC;

2. 本周交易量前十的玩家

SELECT 
    user_id,
    count() AS bets,
    sum(amount) AS total_staked,
    sumIf(amount * odds, outcome = 'win') AS total_won,
    round(total_won / total_staked, 4) AS roi
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY user_id
ORDER BY total_staked DESC
LIMIT 10;

3. 今日每小时投注量

SELECT 
    toHour(created_at) AS hour,
    count() AS bets,
    sum(amount) AS volume
FROM betting.bets
WHERE created_at >= today()
GROUP BY hour
ORDER BY hour;

4. 按赔率范围的胜率

SELECT 
    CASE 
        WHEN odds < 1.5 THEN '1.00-1.49'
        WHEN odds < 2.0 THEN '1.50-1.99'
        WHEN odds < 3.0 THEN '2.00-2.99'
        ELSE '3.00+'
    END AS odds_range,
    count() AS total_bets,
    countIf(outcome = 'win') AS wins,
    round(wins / total_bets, 4) AS win_rate
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY odds_range
ORDER BY odds_range;

5. 按星期几的最活跃小时

SELECT 
    toDayOfWeek(created_at) AS dow,
    toHour(created_at) AS hour,
    count() AS bets
FROM betting.bets
WHERE created_at >= today() - 30
GROUP BY dow, hour
ORDER BY dow, hour;

6. 按用户的平均投注和赔率（LTV）

SELECT 
    user_id,
    avg(amount) AS avg_bet,
    avg(odds) AS avg_odds,
    count() AS total_bets,
    now() - max(created_at) AS hours_since_last_bet
FROM betting.bets
GROUP BY user_id
HAVING total_bets > 100
ORDER BY avg_bet DESC
LIMIT 50;

7. 每小时近似唯一玩家数

SELECT 
    toStartOfHour(created_at) AS hour,
    uniq(user_id) AS unique_users_approx,
    uniqExact(user_id) AS unique_users_exact
FROM betting.bets
WHERE created_at >= today() - 1
GROUP BY hour
ORDER BY hour;

8. 每日支出和退款

SELECT 
    toDate(created_at) AS day,
    sum(amount) AS staked,
    sumIf(amount * odds, outcome = 'win') AS paid,
    sumIf(amount, outcome = 'void') AS refunded,
    round((paid + refunded) / staked, 4) AS net_hold_pct
FROM betting.bets
GROUP BY day
ORDER BY day DESC
LIMIT 30;

9. 串关 vs 单关

SELECT 
    bet_type,
    count() AS bets,
    avg(amount) AS avg_stake,
    avg(odds) AS avg_odds,
    avgIf(amount * odds, outcome = 'win') AS avg_payout
FROM betting.bets
GROUP BY bet_type;

10. 可疑模式用户（欺诈）

SELECT 
    user_id,
    count() AS bets_5min,
    stddevPop(amount) AS stake_variance,
    stddevPop(odds) AS odds_variance
FROM betting.bets
WHERE created_at >= now() - INTERVAL 5 MINUTE
GROUP BY user_id
HAVING bets_5min > 30 AND stake_variance < 1 AND odds_variance < 0.1;

10. 从PostgreSQL迁移SQL到ClickHouse时的常见错误

错误1：在子查询中使用SELECT *
在PostgreSQL中没问题。在ClickHouse中，它会在每一层读取所有列。

错误2：期望子查询中的ORDER BY持久化
在ClickHouse中，子查询不保证顺序，即使有ORDER BY。只在顶层排序。

错误3：相关子查询
ClickHouse对相关子查询的优化很差。将它们重写为JOIN或使用窗口函数。

-- 差（慢）
SELECT user_id, amount
FROM bets b1
WHERE amount = (SELECT max(amount) FROM bets b2 WHERE b2.user_id = b1.user_id);

-- 好（快）
SELECT user_id, max(amount) AS max_amount
FROM bets
GROUP BY user_id;

错误4：期望事务完整性
ClickHouse没有REPEATABLE READ。如果你在查询期间插入数据——你可能会看到部分数据。

错误5：不使用ALTER TABLE进行UPDATE和DELETE
在ClickHouse中，这些是突变，异步且重量级。不要用一个命令更新一百万行。

下一步

现在你知道如何编写ClickHouse的SELECT查询而不会遇到意外了。下一篇文章——高级聚合和窗口函数。

← 上一篇: 将数据加载到 ClickHouse：如何告别逐行插入，将数据摄入速度提升 500 倍
→ 下一篇: ClickHouse聚合函数：我是如何不再害怕uniqHLL12和quantileTDigest的

— Editorial Team

社区

暂无评论。

继续阅读

主题

数据库 SQL

常见问题

什么是ClickHouse中的PREWHERE，它与WHERE有何不同？

PREWHERE在其余列从磁盘解压和读取之前过滤数据。首先，只读取PREWHERE中的列，过滤行，然后仅对这些行读取其余列。这带来了2-10倍的加速。PostgreSQL由于行式存储而缺乏这种优化。

何时在ClickHouse中使用SAMPLE？

当2-5%的精度可接受时，用于快速近似答案：估算每小时总投注量，近似唯一用户数，百分位数分布。不要用于财务计算（支出，余额）。SAMPLE 0.1读取10%的随机颗粒。

为什么ClickHouse中的FINAL被认为有害？

FINAL读取表的所有部分并在内存中即时合并。在拥有十亿行的表上，使用FINAL的查询会因内存不足而崩溃。最好使用argMax(column, version) GROUP BY或定期在后台执行OPTIMIZE TABLE，而不是在查询中使用FINAL。

在ClickHouse中，IN与子查询和JOIN哪个更快？

IN与子查询更快。ClickHouse将子查询物化为内存中的一组常量，并应用列式过滤。JOIN需要逐行匹配和临时表。仅当需要两个表的列或复杂连接条件时才使用JOIN。

ClickHouse中的SELECT查询：与PostgreSQL的差异，PREWHERE，SAMPLE

ClickHouse中的SELECT查询：在PostgreSQL十年后如何重塑思维

1. 基本语法：熟悉的面孔，列式的内核

2. PREWHERE——PostgreSQL羡慕的优化

3. SAMPLE——0.1秒内的近似答案

4. FINAL——初学者的雷区

5. 子查询中的IN/NOT IN vs JOIN

6. ANY / ALL修饰符——早期遗留物

7. DISTINCT及其性能

8. FORMAT——按客户端需求输出

9. 博彩分析十大查询（生产就绪）

10. 从PostgreSQL迁移SQL到ClickHouse时的常见错误

下一步

社区

继续阅读

最近浏览

标签

主题

常见问题

ClickHouse中的SELECT查询：在PostgreSQL十年后如何重塑思维

1. 基本语法：熟悉的面孔，列式的内核

2. PREWHERE——PostgreSQL羡慕的优化

3. SAMPLE——0.1秒内的近似答案

4. FINAL——初学者的雷区

5. 子查询中的IN/NOT IN vs JOIN

6. ANY / ALL修饰符——早期遗留物

7. DISTINCT及其性能

8. FORMAT——按客户端需求输出

9. 博彩分析十大查询（生产就绪）

10. 从PostgreSQL迁移SQL到ClickHouse时的常见错误

下一步

社区

继续阅读

将数据加载到ClickHouse：批量、async_insert、监控

ClickHouse中的MergeTree：颗粒、部分和稀疏索引

ClickHouse 数据类型：分析完整参考

ClickHouse客户端和HTTP API：连接与首次查询

ClickHouse in Docker: 2分钟内启动分析

在Ubuntu 22.04和Debian 12上安装ClickHouse：指南

ClickHouse：为什么列式数据库管理系统能将分析速度提升100倍

自适应液态变压器：人工智能能效的突破

Alphabet筹集850亿美元：AI竞赛的求救信号还是企业自救？

xAI与美国政府合同——马斯克为0.42美元恐慌

最近浏览

标签

主题

常见问题

分享本文