COVAR_SAMP

Syntax

For information on syntax and semantics, see "Analytic Functions".

Purpose

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 Also: "Aggregate Functions".

Aggregate Example

See "COVAR_POP".

Analytic Example

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