Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
Functions, 22 of 121
For information on syntax and semantics, see "Analytic Functions".
COVAR_SAMP
returns the sample covariance of a set of number pairs. You can use it as an aggregate or analytic function.
Both expr1 and expr2 are number expressions. Oracle applies the function to the set of (expr1 , expr2) pairs after eliminating all pairs for which either expr1 or expr2 is null. Then Oracle makes the following computation:
(SUM(expr1 * expr2) - SUM(expr1) * SUM(expr2) / n) / (n-1)
where n is the number of (expr1 , expr2) pairs where neither expr1 nor expr2 is null.
The function returns a value of type NUMBER
. If the function is applied to an empty set, it returns null.
See "COVAR_POP".
The following example calculates cumulative sample covariance of the amount of sales and sale profits in 1998.
SELECT s_year, s_month, s_day, COVAR_POP(s_amount, s_profit) OVER (ORDER BY s_month, s_day) AS CUM_COVP, COVAR_SAMP(s_amount, s_profit) OVER (ORDER BY s_month, s_day) AS CUM_COVS FROM sales WHERE s_year=1998 ORDER BY s_year, s_month, s_day; S_YEAR S_MONTH S_DAY CUM_COVP CUM_COVS ---------- ---------- ---------- ---------- ---------- 1998 6 5 0 1998 6 9 4940952.6 7411428.9 1998 6 9 4940952.6 7411428.9 1998 6 10 5281752.33 7042336.44 1998 8 21 6092799.46 7615999.32 1998 8 25 4938283.61 5761330.88 1998 8 25 4938283.61 5761330.88 1998 8 26 4612074.09 5270941.82 1998 11 9 4556799.53 5063110.59 1998 11 9 4556799.53 5063110.59 1998 11 10 4014833.65 4379818.52 1998 11 10 4014833.65 4379818.52 1998 11 11 3747965.53 4060295.99
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|