Snippet Name: VARIANCE
Description: VARIANCE returns the variance of expr. You can use it as an aggregate or analytic function.
Oracle Database calculates the variance of expr as follows:
*
0 if the number of rows in expr = 1
*
VAR_SAMP if the number of rows in expr > 1
If you specify DISTINCT, then you can specify only the query_partition_clause of the analytic_clause. The order_by_clause and windowing_clause are not allowed.
This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.
Also see:
» RANK
» REGR_SLOPE
» VAR_SAMP
» VAR_POP
» SUM
» STDDEV_SAMP
» STDDEV_POP
» STDDEV
» ROW_NUMBER
» REGR_SYY
» REGR_SXY
» REGR_SXX
» REGR_R2
» REGR_INTERCEPT
» REGR_COUNT
» REGR_AVGY
» REGR_AVGX
» Number Functions: RATIO_TO_REPORT
» Number Functions: RANK
» PERCENTILE_DISC
» PERCENTILE_CONT
» PERCENT_RANK
» OVER PARTITION BY
» NTILE
» MIN
» MAX
» LEAD
» LAST_VALUE
» LAST
» LAG
Comment: (none)
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 02nd, 2009
|
VARIANCE([DISTINCT | ALL] <value>) OVER (<analytic_clause>)
--Aggregate Example
--The following example calculates the variance of
-- all salaries in the sample employees table:
SELECT VARIANCE(salary) "Variance"
FROM employees;
VARIANCE
----------
15283140.5
--Analytic Example
--The following example returns the cumulative variance of
-- salary values in Department 30 ordered by hire date.
SELECT last_name, salary, VARIANCE(salary)
OVER (ORDER BY hire_date) "Variance"
FROM employees
WHERE department_id = 30;
LAST_NAME SALARY VARIANCE
--------------- ---------- ----------
Raphaely 11000 0
Khoo 3100 31205000
Tobias 2800 21623333.3
Baida 2900 16283333.3
Himuro 2600 13317000
Colmenares 2500 11307000
|
