Oracle Covar_Pop, Covar_Samp
-- The query table shown below has data for x and y.  When
-- any value of x and/or y is null, that will be excluded
-- in computation of average and covariance as shown in
-- query table 2

-- [1] Query table

WITH q_stat AS 
 (SELECT 1 x, 2 y  FROM DUAL   UNION 
  SELECT 2 x, 6 y  FROM DUAL   UNION
  SELECT 9 x, 3 y  FROM DUAL   UNION
  SELECT 4 x, 7 y  FROM DUAL)
SELECT
 AVG(x) avg_x,
 AVG(y) avg_y,
 COVAR_POP(x ,y) covar_pop_xy,
 COVAR_SAMP(x ,y) covar_samp_xy
FROM  q_stat;
Oracle Covar_Pop, Covar_Samp Analysis
-- Oracle Covar_Pop, Covar_Samp formula
COVAR_POP = (SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1)/n)/n
          = (SUM(x * y) - SUM(y) * SUM(x)/4)/4
          = (SUM(1x2+2x6+9x3+4x7) - (2+6+3+7) x (1+2+9+4)/4)/4
          = (69 - 288/4)/4
          = -0.75

COVAR_SAMP = (SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1)/n)/n-1
           = (SUM(x * y) - SUM(y) * SUM(x)/4)/3
           = (69 - 288/4)/3
           = -1.0
Oracle Covar_Pop, Covar_Samp Analysis
 
Oracle Covar_Pop, Covar_Samp With NULL value Data
-- [2] Query table

WITH q_stat AS 
 (SELECT 1 x,    2 y  FROM DUAL   UNION 
  SELECT 2 x,    6 y  FROM DUAL   UNION
  SELECT 9 x, NULL y  FROM DUAL   UNION
  SELECT 4 x,    7 y  FROM DUAL)
SELECT
 AVG(x) avg_x,
 AVG(y) avg_y,
 ROUND(COVAR_POP(x ,y),4)  covar_pop_xy,
 ROUND(COVAR_SAMP(x ,y),4) covar_samp_xy
FROM  q_stat;
Oracle Covar_Pop, Covar_Samp Analysis With Null Value Data
-- Oracle Covar_Pop, Covar_Samp formula
-- Due to null value in the query table, the count (n)
-- becomes 3 for COVAR_POP and n-1 = 2 for COVAR_SAMP

COVAR_POP = (SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1)/n)/n
          = (SUM(x * y) - SUM(y) * SUM(x)/n)/n
          = (SUM(1x2+2x6+9xNULL+4x7)-(2+6+NULL+7)x(1+2+NULL+4)/3)/3
          = (42 - 15 x (7/3))/3
          = 2.3333

COVAR_SAMP = (SUM(x * y) - SUM(y) * SUM(x)/n)/n-1
           = (42 - 15 x 7/3)/2
           = 3.50
Oracle Covar_Pop, Covar_Samp Analysis With Null value data

Oracle Variance

Oracle registered trademark of Oracle Corporation.

Last Revised On: September 14, 2014

  1312