S02047

S02047

Analytic functions cannot be used with CONTEXT BY or PIVOT BY. RefId: S02047

由于 DolphinDB 中的 CONTEXT BY 和 PIVOT BY 是独有的语句，不属于标准 SQL 语句。分析函数尚未定义与 CONTEXT BY 、PIVOT BY 连用时的语义，因此，在查询语句中同时使用分析函数和 CONTEXT BY 或 PIVOT 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 lead(val) over (partition by date) from t group by date, val;
/*
date       val                 lead               
---------- ------------------- -------------------
2020.01.01 73.599999999999994  102.099999999999994
2020.01.01 102.099999999999994                    
2020.01.02 33.399999999999998              
*/       

select cume_dist() over () from t context by id;
select cume_dist() over (range between unbounded preceding and unbounded following) as cume_dist from t context by id => Analytic functions cannot be used with CONTEXT BY or PIVOT BY. RefId:S02047

需要根据查询预期修改 SQL 语句。例如：

如果预期结果按 CONTEXT BY 表达式分组，可以这样写：

select
    cume_dist
from
    (
        select
            id,
            cume_dist() over ()
        from
            t
    )
context by
    id

如果预期分析函数计算时按 CONTEXT BY 表达式分组，直接使用分析函数语法即可：

select
    cume_dist() over (partition by id)
from
    t