Clickhouse别名使用问题

发表于 更新于 分类于 阅读次数： Changyan： 本文字数： 1.8k 阅读时长 ≈ 3 分钟

Clickhouses别名使用问题

clickhouse 在查询中使用别名时可能会有下面的问题1。

1
2
3
$ SELECT avg(number) AS number, max(number) FROM numbers(10)

Aggregate function avg(number) is found inside another aggregate function in query.

If aliased expression contains aggregate function, alias should not be resolved inside another aggregate function.
If alias name clashes with the column name, the substitution of this alias should be cancelled.

原因是clickhouse的别名如果和某个列名相同，就会有上面的异常。可以通过添加下面了配置处理上述问题2.

1
2
SELECT avg(number) AS number, max(number) FROM numbers(10)
settings prefer_column_name_to_alias  = 1;

但是这个配置引入了新的问题3.

Prefer alias for ORDER BY after GROUP BY in case of set prefer_column_name_to_alias=1;
Clickhouse version 21.6 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
$ SELECT
    max(number) AS number,
    sum(number) AS sum
FROM numbers(100)
ORDER BY number ASC

Query id: 058b2cb1-7ea6-44de-b6a8-662ac4d949a8


0 rows in set. Elapsed: 0.002 sec.

Received exception from server (version 21.6.1):
Code: 184. DB::Exception: Received from localhost:9000. DB::Exception: Aggregate function max(number) is found inside another aggregate function in query: While processing max(number) AS number.

$ set prefer_column_name_to_alias=1;

$ SELECT
    max(number) AS number,
    sum(number) AS sum
FROM numbers(100)
ORDER BY number ASC

Query id: f8ca2417-9bba-42c6-9f12-42987a26e75d


0 rows in set. Elapsed: 0.004 sec.

Received exception from server (version 21.6.1):
Code: 215. DB::Exception: Received from localhost:9000. DB::Exception: Column `number` is not under aggregate function and not in GROUP BY: While processing number ASC.

clickhouse 社区又提出了新的pr4处理上述问题:

Add more options to prefer_column_name_to_alias setting. This is for #24237

  1. set prefer_column_name_to_alias=1 —> prefer column over alias in all sub clauses;
  2. set prefer_column_name_to_alias=2 —> prefer column over alias in all sub clauses before and include group by;
  3. set prefer_column_name_to_alias=3 —> prefer column over alias in all sub clauses before group by;

参考