Big Query

Min Max Linear Regression

Dataset

Number of Students	Sales by Quarter
x	y
2	58
6	105
8	88
8	118
12	117
16	137
20	157
20	169
22	149
26	202

$$x_{max}=26,\space x_{min}=2, \space y_{max}=202, \space y_{min}=58$$

Slop

$$b_1=\frac{y_{max}-y_{min}}{x_{max} - x_{min}}=\frac{202-58}{26-2}=6$$

Intercept

$$b_0=y_{max} - b_1 x_{max}=202-6*26 = 46$$

$$b_0=y_{min} - b_1 x_{min}=58-6*2 = 46$$

Equation

$$\hat{y} = 46 + 6x$$

DECLARE XMax, XMin, YMax, YMin, B0, B1 FLOAT64;
SET (XMax, XMin, YMax, YMin) = (
  SELECT STRUCT(Max(NumberOfStudents), MIN(NumberOfStudents), MAX(SalesByQuarter), MIN(SalesByQuarter)) 
  FROM Shop.Sales
);
SET B1 = (YMax - YMin) / (XMax - XMin);
SET B0 = YMax - B1 * XMax;
#SELECT B1 , B0;
CREATE OR REPLACE TABLE Shop.MinMaxRegression AS
  SELECT B1, B0;

Predict Procedure

$$\hat{y} = b_1 * x + b_0$$

CREATE PROCEDURE Shop.PredictLG(X INT64)
BEGIN
  SELECT X * B1 + B0 FROM Shop.LG;
END;

Score R²

$$R^2=1-\frac{\sum{(y-\hat{y})^2}}{\sum{(y-\bar{y})^2}}$$

DECLARE YBar FLOAT64;
SET YBar = (SELECT AVG(SalesByQuarter) FROM Shop.Sales);
SELECT 1 - SUM(POWER(SalesByQuarter - (NumberOfStudents * B1 + B0), 2))/
  SUM(POWER((SalesByQuarter - YBar), 2))
 FROM Shop.Sales, Shop.LG;

Adjusted R²

$$Ajusted \space R^2 = 1 - \frac{n-1}{n-2}(1 - R^2)$$

Standard Error

$$S_{est}=\sqrt{\frac{\sum{(y-\hat{y})^2}}{n-2}}$$