Oracle - use analytic function inside aggregate function
I have a DATE_VALUE table, for example:
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 the difference between cost values between two consecutive dates. However, this simple query doesn't work:
select variance(lead( value,1) OVER (order by date) - value)
from DATE_VALUE
An error appeared:
ORA-30483 window functions are not allowed here 30483. 00000 - "window functions" are not allowed here * Reason. Window functions are only allowed in the SELECT list of the query. And a window function cannot be an argument for another window or group function.
The request works fine if I move the reject function out of the request:
select variance(difvalue) from (
select lead( value,1) OVER (order by rundate) - value as difvalue
from DATE_VALUE
);
I wonder if there is a way to change the query in such a way that no subquery is used?
source to share
From Oracle Help:
Analytic functions are the last set of operations performed in a query, with the exception of the final ORDER BY clause. Everything is attached and all WHERE, GROUP BY and HAVING are completed before the analytic functions are processed. Therefore, analytic functions can only appear in or ORDER BY.
Aggregate functions are commonly used with a GROUP BY clause in a SELECT, where Oracle Database divides the rows of the queried table or view into groups. In a query containing a GROUP BY clause, the picklist items can be aggregated functions, GROUP BY expressions, constants, or expressions associated with one of them. oracle applies 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 the aggregate function in the select list to all rows in the requested table or view.
Thus, you cannot inject analytic functions inside aggregate functions, because aggregate functions are executed before analysis (but you can use aggregated functions inside analytic functions).
PS What's wrong with subqueries, by the way?
source to share