-- 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 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
-- [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 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
69983