S02047

context by and pivot by are unique features in DolphinDB and serve as an extension to the ANSI SQL. The syntax of using an analytic function with context by or pivot by is not defined. Therefore, this error occurs when a query uses an analytic function with context by or pivot by. 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);

select cume_dist() over () from t context by id => Analytic functions cannot be used with CONTEXT BY or PIVOT BY.

The combined use of an analytic function and other clauses defined in the analytic function syntax triggers no error. The following script showcases the combined usage of an analytic function and the partition by clause:

select lead(val) over (partition by date) from t group by date, val;
/* output
date       val                 lead               
---------- ------------------- -------------------
2020.01.01 73.599999999999994  102.099999999999994
2020.01.01 102.099999999999994                    
2020.01.02 33.399999999999998      
 */ 

Restructure the SQL query according to the expected query results. For example:

• To apply the analytic function to the result of each context by group, use the function in the subquery and then use context by in the main query:

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

• To apply the analytic function to each context by group, directly use the analytic function with partitioned by:

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