S02045

DolphinDB allows the use of column aliases in the group by clause. When a query uses both an analytic function and a group by clause, the system checks if the partition by and order by clauses reference the original columns specified in group by or expressions involving these columns, such as val+1. If these clauses reference a column that is not included in group by or is the alias of the original column, this error occurs. For example:

id = `XOM`GS`AAPL
val = 102.1 33.4 73.6
date = 2020.01.01 2020.01.02 2020.01.01
t = table(id, val, date);

// The 'id' column referenced in the order by clause is not included in group by 
select min(max(val)) over (partition by date order by id) from t group by date => The PARTITION/ORDER BY column 'id' must be included in the GROUP BY clause.
// The partition by clause references the alias of the 'date' column
select min(max(val)) over (partition by id) from t group by date as id => The PARTITION/ORDER BY column 'id' must be included in the GROUP BY clause.

When a query uses both an analytic function and a group by clause, ensure that the partition by and order by clauses reference the original columns specified by group by.

select min(max(val)) over (partition by date) from t group by date
/* output
date       min                
---------- -------------------
2020.01.01 102.099999999999994
2020.01.02 33.399999999999998 
*/