问如何在REGR_中模拟线性回归( BigQuery )函数？

EN

CREATE TEMPORARY FUNCTION NonNullX(y FLOAT64, x FLOAT64)
RETURNS FLOAT64
AS (IF(x IS NOT NULL AND y IS NOT NULL, x, NULL));

CREATE TEMPORARY FUNCTION NonNullY(y FLOAT64, x FLOAT64)
RETURNS FLOAT64
AS (IF(x IS NOT NULL AND y IS NOT NULL, y, NULL));

CREATE TEMPORARY FUNCTION NonNullCount(y FLOAT64, x FLOAT64)
RETURNS INT64
AS (IF(x IS NOT NULL AND y IS NOT NULL, 1, 0));

CREATE TEMPORARY FUNCTION REGR_R2_COEF(y_var FLOAT64, x_var FLOAT64, xy_corr FLOAT64)
RETURNS FLOAT64
AS (IF(x_var = 0, NULL, IF(y_var = 0 AND x_var <> 0, 1, POWER(xy_corr, 2))));

SELECT k, 
       AVG(NonNullX(y, x)) AS REGR_AVGX,
       AVG(NonNullY(y, x)) AS REGR_AVGY,
       SUM(NonNullCount(y, x)) AS REGR_COUNT,
       AVG(NonNullY(y, x))-(COVAR_POP(NonNullY(y, x), NonNullX(y, x))/VAR_POP(NonNullX(y, x)))*AVG(NonNullX(y, x)) AS REGR_INTERCEPT,
       REGR_R2_COEF(VAR_POP(NonNullY(y, x)), VAR_POP(NonNullX(y, x)), CORR(NonNullY(y, x), NonNullX(y, x))) AS REGR_R2,
       COVAR_POP(NonNullY(y, x), NonNullX(y, x))/VAR_POP(NonNullX(y, x)) AS REGR_SLOPE,
       SUM(NonNullCount(y, x)) * VAR_POP(NonNullX(y, x)) AS REGR_SXX,
       SUM(NonNullCount(y, x)) * COVAR_POP(NonNullY(y, x), NonNullX(y, x)) AS REGR_SXY,
       SUM(NonNullCount(y, x)) * VAR_POP(NonNullY(y, x)) AS REGR_SYY
FROM Temp.SimpleRegressionData
GROUP BY k;

CREATE TEMPORARY FUNCTION REGR_R2_COEF(y_var FLOAT64, x_var FLOAT64, xy_corr FLOAT64)
RETURNS FLOAT64
AS (IF(x_var = 0, NULL, IF(y_var = 0 AND x_var <> 0, 1, POWER(xy_corr, 2))));

SELECT k, 
       AVG(x) AS REGR_AVGX,
       AVG(y) AS REGR_AVGY,
       SUM(1) AS REGR_COUNT,
       AVG(y)-(COVAR_POP(y, x)/VAR_POP(x))*AVG(x) AS REGR_INTERCEPT,
       REGR_R2_COEF(VAR_POP(y), VAR_POP(x), CORR(y, x)) AS REGR_R2,
       COVAR_POP(y, x)/VAR_POP(x) AS REGR_SLOPE,
       SUM(1) * VAR_POP(x) AS REGR_SXX,
       SUM(1) * COVAR_POP(y, x) AS REGR_SXY,
       SUM(1) * VAR_POP(y) AS REGR_SYY
FROM Temp.SimpleRegressionData
WHERE x IS NOT NULL AND Y IS NOT NULL
GROUP BY k;