Clickhouse别名使用问题
Clickhouses别名使用问题
clickhouse 在查询中使用别名时可能会有下面的问题1。
1 | $ SELECT avg(number) AS number, max(number) FROM numbers(10) |
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 | SELECT avg(number) AS number, max(number) FROM numbers(10) |
但是这个配置引入了新的问题3.
Prefer alias for ORDER BY after GROUP BY in case of set prefer_column_name_to_alias=1;
Clickhouse version 21.6
1 | $ SELECT |
clickhouse 社区又提出了新的pr4处理上述问题:
Add more options to prefer_column_name_to_alias setting. This is for #24237
- set prefer_column_name_to_alias=1 —> prefer column over alias in all sub clauses;
- set prefer_column_name_to_alias=2 —> prefer column over alias in all sub clauses before and include group by;
- set prefer_column_name_to_alias=3 —> prefer column over alias in all sub clauses before group by;