Oracle - Use analytic function inside aggregate function

Question

I have a table DATE_VALUE like this:

  Date        Value

  ----        -----

01/01/2012    1.5 

02/01/2012    1.7 

03/01/2012    1.3

04/01/2012    2.1

05/01/2012    3.4

I want to calculate variance between differences of value between 2 consecutive dates. However this simple query does not work:

select variance(lead( value,1) OVER (order by date) - value)
 from DATE_VALUE

I got an error:

ORA-30483: window functions are not allowed here 30483. 00000 - "window functions are not allowed here" *Cause: Window functions are allowed only in the SELECT list of a query. And, window function cannot be an argument to another window or group function.

The query works fine if I move the variance function out of the query:

select variance(difvalue) from (
  select lead( value,1) OVER (order by rundate) - value  as difvalue
  from DATE_VALUE 
);

I wonder if there is any way to modify the query such that there is no sub-query used?

Multisync · Accepted Answer · 2014-11-13T05:18:49.503

From Oracle reference:

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

Aggregate functions are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.

If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view.

So you cannot put analytic functions inside aggregate functions because aggregate functions are performed before analytic (but you can use aggregate functions inside analytic functions).

P.S. What's wrong with subqueries by the way?

Oracle - Use analytic function inside aggregate function

1 Answers1