ClickHouse 数据查询

查询注意

避免使用 SELECT * 查询

WITH

WITH 子句只能返回一行数据
定义变量
1

WITH 10 AS var_name SELECT ...

调用函数

1

WITH SUM(column_name) AS with_name SELECT ...

定义子查询

1
2
3
4


WITH (
    SELECT ...
) AS with_name
SELECt ...

WITH 子句可在子查询中嵌套使用

FROM

支持表、表函数和子查询
可用 FINAL 修饰以强制合并，会降低性能，应尽量避免使用

SAMPLE

返回采样数据，减少查询负载，适用于近似查询
只能用于 MergeTree 系列引擎表，且声明了 SAMPLE BY 抽样表达式
虚拟字段 _sample_factor 是采样系数

不采样

1
2
3


SELECT ... SAMPLE 0
-- 或
SELECT ... SAMPLE 1

SAMPLE factor，factor 是采样因子，取值 0~1

1
2
3


SELECT ... SAMPLE 0.1
-- 或者
SELECT ... SAMPLE 1/10

SAMPLE rows，采样近似行数，必须大于 1
1

SELECT ... SAMPLE 10000
SAMPLE factor OFFSET n，偏移 n*100% 的数据量后才开始按 factor 因子采样，取值都在 0~1
1

SELECT ... SAMPLE 0.4 OFFSET 0.5

ARRAY JOIN

允许在数据表内部，与数组或嵌套字段进行 JOIN 操作，操作时把数组或嵌套字段拆成多行

支持 INNER 和 LEFT，默认 INNER

1
2


SELECT ... FROM table_name ARRAY JOIN column_name AS alias_name
SELECT ... FROM table_name LEFT ARRAY JOIN column_name AS alias_name

JOIN

连接精度

ALL: 默认，左表的每行数据，在右表中有多行连接匹配，返回右表全部连接数据
ANY: 左表的每行数据，在右表中有多行连接匹配，返回右表第一行连接数据

ASOF: 增加模糊连接条件，对应字段必须是整数、浮点数和日期这类有序数据类型

1
2


SELECT ... FROM table_a ASOF INNER JOIN table_b USING(key_1, key_2)
-- key_1 字段是 join key，key_2 是模糊连接条件字段

连接类型

INNER: 内连接，返回交集部分
OUTER: 外链接
- LEFT: 左表数据全部返回，右表匹配则返回，不匹配则填充相应字段的默认值
- RIGHT: 与 LEFT 相反
- FULL: 先 LEFT，右表剩下的数据再 RIGHT
CROSS: 交叉连接，返回笛卡儿积

JOIN 查询优化

左大右小，小表放右侧，右表会被加载到内存中
JOIN 查询无缓存，应用可考虑实现查询缓存
大量维度属性补全时，建议使用字典表代替 JOIN 查询

USING 语法简写

1

SELECT ... FROM table_1 INNTER JOIN table_2 USING key_1

PREWHERE

只能用于 MergeTree 系列表引擎
与 WHERE 不同之处:
- 只读取 PREWHERE 指定的列字段，条件过滤
- 根据过滤好的数据再读取 SELECT 指定的列字段
clickhouse 会在合适条件下自动把 WHERE 替换成 PREWHERE

GROUP BY

WITH ROLLUP，按聚合键从右向左上卷数据，基于聚合函数依次生成分组小计和总计

1
2
3
4


SELECT table, name, SUM(bytes_on_disk) FROM system.parts
    GROUP BY table,name
    WITH ROLLUP
    ORDER BY table

WITH CUBE，基于聚合键之间的所有组合生成小计信息

1
2
3
4


SELECT ...
    GROUP BY key1,key2,key3, ...
    WITH CUBE
    ...

WITH TOTALS，常规聚合完成后，增加一行对所有数据的汇总统计

1
2
3
4


SELECT ...
    GROUP BY key1
    WITH TOTALS
    ...

HAVING

必须与 GROUP BY 配合使用，把聚合结果二次过滤
1

SELECT ... GROUP BY ... HAVING ...

ORDER BY

默认 ASC(升序)
NULLS LAST，默认，其他值 -> NaN -> NULL
NULLS FIRST，NULL -> NaN -> 其他值

LIMIT BY

返回指定分组的最多前 n 行数据
1

LIMIT n BY key1,key2 ...

支持 OFFSET

1
2
3


LIMIT n OFFSET m BY key1,key2 ...
-- 简写
LIMIT m,n BY key1,key2 ...

LIMIT

返回指定的前 n 行数据

1
2
3


LIMIT n
LIMIT n OFFSET m
LIMIT m,n

推荐搭配 ORDER BY，保证全局顺序

SELECT

查询正则匹配的列字段

1

SELECT COLUMNS('^n'), COLUMNS('p') FROM system.databases

DISTINCT

去重
先 DISTINCT 后 ORDER BY

UNION ALL

联合左右两边的子查询，一并返回结果，可多次声明使用联合多组查询
1

SELECT c1, c2 FROM t1 UNION ALL SELECT c3, c4 FROM t2
两边列字段数量必须一样，类型兼容，查询结果列名以左侧为准

SQL 执行计划

设置日志到 DEBUG 或 TRACE 级别，可查看 SQL 执行日志
SQL 需真正执行后才有日志，如果查询量大，推荐 LIMIT
不要用 SELECT * 查询
尽可能利用索引，避免全表扫描

文章目录