sql - Oracle - 在聚合函数中使用分析函数

Question

我有一个像这样的表 DATE_VALUE:

  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

我想计算两个连续日期之间的值差之间的方差。然而，这个简单的查询不起作用:

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

我遇到了一个错误:

ORA-30483: 此处不允许窗口函数30483. 00000 - “此处不允许窗口函数”*原因:窗口函数只允许在查询的 SELECT 列表中使用。 而且，窗口函数不能作为另一个窗口或组的参数 功能。

如果我将方差函数从查询中移出，查询将正常工作:

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

我想知道是否有任何方法可以修改查询以便不使用子查询？

Answer 1

来自 Oracle 引用资料:

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.

因此您不能将分析函数放在聚合函数中，因为聚合函数在分析之前执行(但您可以在分析函数中使用聚合函数)。

附言顺便说一句，子查询有什么问题？