S02046

S02046

Analytic functions are not allowed in WHERE clause, GROUP BY clause, or to be nested in aggregate functions. RefId: S02046

不允许在 WHERE 子句、GROUP BY 子句、聚合函数或自定义函数中应用分析函数。

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);

select id from t where max(val) over (partition by date rows 1 preceding) > 50;
// select id from t where max(val) over (partition by date rows between 1 preceding and current row) > 50 => Analytic functions are not allowed in WHERE clause, GROUP BY clause, or to be nested in aggregate functions. RefId:S02046

select id from t group by max(val) over (partition by date rows 1 preceding);
// select id from t group by max(val) over (partition by date rows between 1 preceding and current row) as max => Analytic functions are not allowed in WHERE clause, GROUP BY clause, or to be nested in aggregate functions. RefId:S02046

select min(max(val) over (partition by date rows 1 preceding)) from t;
// select [67108864] min(max(val) over (partition by date rows between 1 preceding and current row)) as min from t => Analytic functions are not allowed in WHERE clause, GROUP BY clause, or to be nested in aggregate functions. RefId:S02046

避免在 WHERE 子句、GROUP BY 子句、聚合函数或自定义函数中使用分析函数。需要改写查询语句以满足需求，参见下例：

select
    id
from
    t
where
    (
        exec
            max(val) over (partition by date rows 1 preceding)
        from
            t
    ) > 50; // 执行成功