ClickHouse SELECT查询深度解析

ClickHouse SELECT查询深度解析

ClickHouse ClickHouse® 是一个免费的大数据分析型数据库管理系统。 项目地址: https://gitcode.com/gh_mirrors/cli/ClickHouse

概述

SELECT查询是ClickHouse中最核心的数据检索操作，它允许用户从数据库中提取数据。ClickHouse的SELECT语句功能强大且灵活，支持多种高级特性和优化选项，能够满足各种复杂的数据分析需求。

基础语法结构

ClickHouse的SELECT查询语法非常丰富，基本结构如下：

SELECT [DISTINCT] 列表达式
FROM 数据源
[WHERE 条件]
[GROUP BY 分组表达式]
[HAVING 分组后条件]
[ORDER BY 排序表达式]
[LIMIT 限制数量]

所有子句都是可选的，只有SELECT后的表达式列表是必需的。

核心子句详解

SELECT子句

SELECT子句指定要返回的列或表达式，支持以下特性：

1. 通配符(*): 返回所有列（不推荐在生产环境使用）
2. 表达式计算: 可以对列进行数学运算和函数处理
3. 动态列选择: 使用COLUMNS('正则表达式')匹配列名
4. 别名(AS): 为列或表达式指定别名

示例：

-- 基本查询
SELECT column1, column2 FROM table

-- 使用表达式
SELECT column1 * 2 AS doubled FROM table

-- 动态列选择
SELECT COLUMNS('^user_.*') FROM user_profiles

FROM子句

FROM子句指定数据来源，支持：

• 表名（可带数据库前缀）
• 子查询
• 表函数（如numbers(), url()等）

WHERE与PREWHERE

WHERE子句用于过滤行数据，而PREWHERE是ClickHouse特有的优化特性：

• PREWHERE先于WHERE执行，适合过滤掉大量数据
• 对于列式存储，PREWHERE能显著减少IO
• 简单条件适合放在PREWHERE中

GROUP BY分组

GROUP BY支持多种高级分组方式：

• WITH ROLLUP: 生成多层次汇总
• WITH CUBE: 生成所有可能的组合汇总
• WITH TOTALS: 添加总计行

JOIN连接

ClickHouse支持多种JOIN类型：

• INNER/LEFT/RIGHT/FULL JOIN
• CROSS JOIN
• SEMI/ANTI JOIN
• 特殊的ASOF JOIN（近似连接）
• GLOBAL JOIN（分布式查询优化）

LIMIT限制

LIMIT子句控制返回行数，支持：

• 简单限制：LIMIT 10
• 带偏移量：LIMIT 20, 10
• BY子句：LIMIT 5 BY category

高级特性

WITH子句（CTE）

公共表表达式(CTE)可以创建临时结果集，提高复杂查询的可读性：

WITH sales_summary AS (
    SELECT product, SUM(amount) AS total
    FROM sales
    GROUP BY product
)
SELECT * FROM sales_summary WHERE total > 1000

SAMPLE采样

ClickHouse支持高效的数据采样：

-- 采样10%的数据
SELECT * FROM table SAMPLE 0.1

WINDOW窗口函数

支持标准SQL窗口函数：

SELECT 
    date,
    revenue,
    SUM(revenue) OVER (PARTITION BY month ORDER BY date)
FROM sales

极端值计算

通过设置extremes=1可以获取结果列的最小值和最大值：

SELECT * FROM table SETTINGS extremes=1

性能优化建议

1. **避免使用SELECT ***: 明确指定需要的列
2. 合理使用PREWHERE: 对高选择性条件优先使用
3. 注意JOIN顺序: 把小表放在JOIN右侧
4. 使用适当的采样: 大数据集分析时可考虑采样
5. 设置内存限制: 防止复杂查询耗尽内存

实际应用示例

电商数据分析

WITH user_orders AS (
    SELECT 
        user_id,
        COUNT() AS order_count,
        SUM(amount) AS total_spent
    FROM orders
    WHERE order_date > now() - INTERVAL 30 DAY
    GROUP BY user_id
)
SELECT 
    u.user_id,
    u.name,
    o.order_count,
    o.total_spent,
    o.total_spent / o.order_count AS avg_order_value
FROM users u
JOIN user_orders o ON u.user_id = o.user_id
ORDER BY total_spent DESC
LIMIT 100

时间序列分析

SELECT
    toStartOfHour(event_time) AS hour,
    count() AS events,
    avg(duration) AS avg_duration,
    quantile(0.95)(duration) AS p95_duration
FROM events
WHERE event_date = today()
GROUP BY hour
ORDER BY hour

总结

ClickHouse的SELECT查询提供了丰富的数据检索能力，从简单的数据提取到复杂的分析操作都能高效支持。通过合理利用其特有的优化特性如PREWHERE、SAMPLE等，可以在海量数据场景下获得卓越的查询性能。掌握这些功能可以帮助数据分析师和工程师更好地利用ClickHouse处理各种数据分析任务。

ClickHouse ClickHouse® 是一个免费的大数据分析型数据库管理系统。 项目地址: https://gitcode.com/gh_mirrors/cli/ClickHouse