google-bigquery - 如何在 BigQuery 中执行线性回归？

Question

BigQuery 有一些统计聚合函数，例如 STDDEV(X) 和 CORR(X, Y)，但它不提供直接执行线性回归的函数。

如何使用确实存在的函数计算线性回归？

Answer 1

编者编辑:请看next answer ，现在 BigQuery 原生支持线性回归。 --Fh

以下查询使用数值稳定且易于修改以适用于任何输入表的计算执行线性回归。它使用内置函数 CORR 生成最适合模型 Y = SLOPE * X + INTERCEPT 和 Pearson 相关系数的斜率和截距。

例如，我们使用公共(public)出生数据集将出生体重计算为怀孕持续时间的线性函数，按州分割。你可以写得更紧凑，但是我们使用了几层子查询来突出这些部分是如何组合在一起的。要将其应用于另一个数据集，您只需替换最里面的查询。

SELECT Bucket,
       SLOPE,
       (SUM_OF_Y - SLOPE * SUM_OF_X) / N AS INTERCEPT,
       CORRELATION
FROM (
    SELECT Bucket,
           N,
           SUM_OF_X,
           SUM_OF_Y,
           CORRELATION * STDDEV_OF_Y / STDDEV_OF_X AS SLOPE,
           CORRELATION
    FROM (
        SELECT Bucket,
               COUNT(*) AS N,
               SUM(X) AS SUM_OF_X,
               SUM(Y) AS SUM_OF_Y,
               STDDEV_POP(X) AS STDDEV_OF_X,
               STDDEV_POP(Y) AS STDDEV_OF_Y,
               CORR(X,Y) AS CORRELATION
        FROM (SELECT state AS Bucket,
                     gestation_weeks AS X,
                     weight_pounds AS Y
              FROM [publicdata.samples.natality])
        WHERE Bucket IS NOT NULL AND
              X IS NOT NULL AND
              Y IS NOT NULL
        GROUP BY Bucket));

与将 X 和 Y 的乘积相加然后取差和除法相比，使用 STDDEV_POP 和 CORR 函数提高了此查询的数值稳定性，但是如果您在表现良好的数据集上使用这两种方法，则可以验证它们是否产生相同的结果结果精度高。