Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
Functions, 116 of 121
For information on syntax and semantics, see "Analytic Functions".
VAR_POP
returns the population variance of a set of numbers after discarding the nulls in this set. You can use it as both an aggregate and analytic function.
The expr is a number expression, and the function returns a value of type NUMBER
. If the function is applied to an empty set, it returns null. The function makes the following calculation:
(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)
The following example returns the population variance of the salaries in the EMP table:
SELECT VAR_POP(sal) FROM emp; VAR_POP(SAL) ------------ 1298434.31
The following example calculates the cumulative population and sample variances of the monthly sales in 1998:
SELECT s_month, VAR_POP(SUM(s_amount)) OVER (ORDER BY s_month), VAR_SAMP(SUM(s_amount)) OVER (ORDER BY s_month) FROM sales WHERE s_year =1998 GROUP BY s_month; S_MONTH VAR_POP(SU VAR_SAMP(S ---------- ---------- ---------- 6 0 8 440588496 881176992 11 538819892 808229838
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|