ClickHouse查询语句详解

ClickHouse查询语句兼容大部分SQL语法，并且进行了更加丰富的扩展，查询语句模板如下：

[WITH expr_list|(subquery)]
SELECT [DISTINCT [ON (column1, column2, ...)]] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table (ON <expr_list>)|(USING <column_list>)
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH ROLLUP|WITH CUBE] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr]
[LIMIT [offset_value, ]n BY columns]
[LIMIT [n, ]m] [WITH TIES]
[SETTINGS ...]
[UNION  ...]
[INTO OUTFILE filename [COMPRESSION type] ]
[FORMAT format]

下面我们会逐一介绍各个子表达式，主要介绍和标准SQL不同的部分，如果某条语句执行报错，则可能是clickhouse版本不支持。

1. SELECT 语句

select 字段查询除了支持直接列名查询外，还支持一些特殊查询：

select col1, col2 from test_table;
select * from test_table;
select 1;   -- 和hive一样，相当于oracle dual表，实际是 select 1 from system.one
select count() from test_table; -- 统计表记录数量
select COLUMNS('a') FROM test_table; -- 基于re2库正则匹配字段名
SELECT COLUMNS('a'), COLUMNS('c'), toTypeName(COLUMNS('c')) FROM test_table; --嵌套在函数中使用
SELECT * APPLY(sum) FROM columns_transformers; -- 为所有列调用sum函数
SELECT * EXCEPT (i) from columns_transformers; -- 查询除 i 以外的其他列
SELECT COLUMNS('[jk]') APPLY(toString) APPLY(length) APPLY(max) from columns_transformers; -- 组合使用修饰符
SELECT * REPLACE(i + 1 AS i) EXCEPT (j) APPLY(sum) from columns_transformers; -- 组合使用修饰符

• 在统计全表数据时，可以使用count()，前面文章我们介绍过，MergeTree会把每个分区数据量记录在count.txt文件中，所以MergeTree统计全表记录数是很快的，如果是条件查询count，应使用count(主键字段)查询。
• COLUMNS表达式支持re2正则表达式匹配字段名，COLUMNS(‘a’) 表示查询所有列名含 "a"的列，COLUMNS也可以嵌套在函数中使用。
• 在select查询时，尽量避免使用 select * from，显式的指定要查的列，如果为了获取字段信息，应该使用 DESC TABLE test_table，而不是 select * from test_table limit 1。
• 如果是使用客户端查询样例数据，如使用 DBeaver 查询数据，一定要加 limit 子句，可以提高查询效率，而且节约内存。
• 使用 SELECT APPLY( ) FROM [db.]table_name 为所有返回字段调用某个函数，需要字段类型支持调用函数。
• 使用 SELECT EXCEPT ( col_name1 [, col_name2, col_name3, …] ) FROM [db.]table_name 去除不需要的查询字段，尤其是宽表中去掉少量不需要字段时很有用。

2. SETTINGS 语句

如果在多条查询中，只想对某条语句设置查询参数，则可以使用 SETTINGS 选项：

SELECT * FROM some_table SETTINGS optimize_read_in_order=1, cast_keep_nullable=1;

3. DISTINCT 语句

clickhouse distinct选项除了支持对指定列去重以外，还可以返回去重列以外的字段：

SELECT DISTINCT ON (a,b) * FROM t1; -- 基于a，b列去重，同时返回所有字段，返回的是随机一条记录（一般是第一条）

• 如果有多条为NULL的记录，则distinct只保留一条，换句话说在distinct查询中 NULL = NULL。
• distinct子句优先级高于limit n，即返回n个去重值以后查询结束，而不是n条记录的去重值。
• 使用group by也可以去重，但是distinct可以在处理时输出数据块，而无需等待整个查询完成运行。

4. EXCEPT / UNION / INTERSECT（差集、并集、交集）

SELECT number FROM numbers(1,10) EXCEPT SELECT number FROM numbers(3,6);
SELECT number FROM numbers(1,10) UNION ALL SELECT number FROM numbers(3,6);
SELECT number FROM numbers(1,10) UNION DISTINCT SELECT number FROM numbers(3,6);
SELECT number FROM numbers(1,10) INTERSECT SELECT number FROM numbers(3,6);

• EXCEPT（差集）在低版本clickhouse中不支持，使用前应先验证。EXCEPT 和 UNION 具有相同的优先级。
• UNION ALL 不会去重，返回所有结果集，UNION DISTINCT 返回结果集的去重结果，如果只使用 UNION，则会根据 union_default_mode 设置参数解析，union_default_mode 可以设置为 ALL 或者 DISTINCT（如：SET union_default_mode = ‘DISTINCT’），使用时最好显式指定 UNION ALL/DISTINCT 结果。
• UNION 结果是根据字段位置判断的，无论两条查询语句的字段名是否一致，即使字段一样，但是select时字段顺序不一样，结果也是按照位置UNION，clickhouse不会根据列名解析，这一点尤其要注意。
• 对于两条查询，如果一条是 non-Nullable 类型，另一条是 Nullable 类型，则UNION结果是 Nullable 类型。
• ORDER BY 和 LIMIT 的优先级比 UNION 高，所以如果需要对 UNION 的结果进行处理，需要使用子查询。
• INTERSECT（交集）不会对结果去重，且优先级高于UNION和EXCEPT子句。

5. GROUP BY 语句

• GROUP BY、ORDER BY 和 LIMIT BY 子句支持位置参数查询，索引位置从1开始，但是在查询之前要打开enable_positional_arguments设置（要确定使用的clickhouse版本支持），例如按照第一列和第二列排序：

  SET enable_positional_arguments = 1;
  SELECT * FROM positional_arguments ORDER BY 1,2;

• 对于 NULL，GROUP BY 也会当做一个值处理，例如：SELECT sum(x), y FROM t_null_big GROUP BY y 所有 y 为 NULL 的记录也会计算 sum(x) 并返回一条结果。

• clikhouse GROUP BY 语句同样支持组合 WITH ROLLUP/CUBE 使用，且后面也可以接 HAVING 子句。例如对于下面的表：

  ┌─year─┬─month─┬─day─┐
  │ 2019 │ 1 │ 5 │
  │ 2019 │ 1 │ 15 │
  │ 2020 │ 1 │ 5 │
  │ 2020 │ 1 │ 15 │
  │ 2020 │ 10 │ 5 │
  │ 2020 │ 10 │ 15 │
  └──────┴───────┴─────┘

执行：

SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP;

结果如下：

┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │    10 │  15 │       1 │
│ 2020 │     1 │   5 │       1 │
│ 2019 │     1 │   5 │       1 │
│ 2020 │     1 │  15 │       1 │
│ 2019 │     1 │  15 │       1 │
│ 2020 │    10 │   5 │       1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │     1 │   0 │       2 │
│ 2020 │     1 │   0 │       2 │
│ 2020 │    10 │   0 │       2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │     0 │   0 │       2 │
│ 202