-- Oracle Regression - Slope, Intercept Functions
-- The query table is non-null data and computation of Slope and Intercept 
-- is as computed in [1.1].   The query table [2] has a null value row 
-- and computation of Slope and Intercept  is as computed in [2.1].

-- [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,
 VAR_POP(y) var_pop_y,
 ROUND(REGR_SLOPE(x, y),4) regr_slope,
 ROUND(REGR_INTERCEPT(x, y),4) regr_intercept,
 REGR_COUNT(x ,y) regr_cnt
FROM q_stat;

-- [1.1] Slope, Intercept Formula

COVAR_POP = (SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1)/n)/n
          = (SUM(1x2+2x6+9x3+4x7) - (2+6+3+7) x (1+2+9+4)/4)/4
          = (69 - 288/4)/4 
          = -0.75

VAR_POP = SUM((expr-(SUM(expr)/COUNT(expr)))2)/COUNT(expr) = SUM((Xi - Avg)2)/n
        = SUM((2-4.5)2+(6-4.5)2+(3-4.5)2+(7-4.5)2)/4
        = (6.25 + 2.25 + 2.25 + 6.25)/4 
        = 4.25

REGR_SLOPE = COVAR_POP(expr1, expr2)/VAR_POP(expr2)
           = COVAR_POP(x, y)/VAR_POP(y)
           = -0.75/4.25
           = -0.1765

REGR_INTERCEPT = AVG(expr1) - REGR_SLOPE(expr1, expr2) * AVG(expr2)
               = AVG(x) - REGR_SLOPE(x, y) * AVG(y)
               = 4 - (-0.1765 * 4.5)
               = 4.7941

-- [2] Query Table with a Null value row

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,
 ROUND(VAR_POP(y),4) var_pop_y,
 ROUND(REGR_SLOPE(x, y),4) regr_slope,
 ROUND(REGR_INTERCEPT(x, y),4) regr_intercept,
 REGR_COUNT(x ,y) regr_cnt
FROM q_stat;

-- [2.1] Slope, Intercept Formula 
-- With a Null value, the third row having the Null value is skipped in
-- computation of average values as shown below, 
-- Avg(x) = (1+2+4)/3 = 2.3333 and
-- Avg(y) = (2+6+7)/3 = 5
-- with corresponding changes to slope and intercept computation

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

VAR_POP = SUM((expr-(SUM(expr)/COUNT(expr)))2)/COUNT(expr) = SUM((Xi - Avg)2)/n
        = SUM((2-5)2+(6-5)2+(Null-5)2+(7-5)2)/3
        = (9 + 1 + Null + 4)/3 
        = 14/3
        = 4.6667

REGR_SLOPE = COVAR_POP(expr1, expr2)/VAR_POP(expr2)
           = COVAR_POP(x, y)/VAR_POP(y)
           = 2.3333/4.6667
           = 0.5

REGR_INTERCEPT = AVG(expr1) - REGR_SLOPE(expr1, expr2) * AVG(expr2)
               = AVG(x) - REGR_SLOPE(x, y) * AVG(y)
               = (7/3) - 0.5 * 5
               = -0.1667