【sql】clickchouse别名使用，聚合函数报错情况

报错信息：

[EXCEPTION] Failed to do execute query. ru.yandex.clickhouse.except.ClickHouseUnknownException: ClickHouse exception, code: 1002, host: 140.210.90.141, port: 8123; Code: 184. DB::Exception: Aggregate function sum(student_num) is found inside another aggregate function in query: While processing sum(student_num) AS student_num. (ILLEGAL_AGGREGATION) (version 21.12.2.17 (official build))↵↵    

报错原因：clickhouse会对同级查询字段的别名进行复用，使用聚合函数时候会报错

解决方法：修改复用聚合函数别名

<--sql1：错误-->
SELECT  SUM(student_num) AS student_num, 
SUM(avg_score / student_num) AS EXPR1,
city AS city student_num 
FROM t1 GROUP BY city 

<--sql2：正确-->
SELECT  SUM(student_num) AS student_num1, <--此处修改别名即可-->
SUM(avg_score / student_num) AS EXPR1,
city AS city student_num 
FROM t1 GROUP BY city 

---扩展---

大多数数据库不会复用，可以使用上面的sql1

mysql中

mysql> select 1 as a ,a +1 as b;
ERROR 1054 (42S22): Unknown column 'a' in 'field list'

clickhouse

SELECT
    1 AS a,
    a + 1 AS b

┌─a─┬─b─┐
│ 1 │ 2 │
└───┴───┘

1 rows in set. Elapsed: 0.004 sec.