BigQueryMLなるものが登場し、すこしだけ世間が賑わって(?)ますが、この記事ではそれよりも原始的な最小二乗法をBigQueryで実装する方法を紹介します。
測定値のペア($x_i$ , $y_i$)が $n$ 個数あったときに、それらを $y=ax+b$ という直線でフィッティングすることを考えます。
ある測定値と直線との間の誤差 $e_i$ を以下の式で定義します。
e_i = \left\{ y_i - (ax_i + b) \right\} ^2
このとき全測定値に対する誤差の合計 $E$ は、以下の式で与えられます。
E = \sum_{i=1}^{n} e_i = \sum_{i=1}^{n} \left\{ y_i - (ax_i + b) \right\} ^2
$a$ と $b$ を適切に設定することでこの $E$ を最小化することを考えます。
$E$ を $a$ と $b$ で偏微分した結果が0と等しくなるという方程式を解析的に解き、以下を得ます。
a=\frac{\displaystyle n\sum_{i=1}^n x_iy_i-\sum_{i=1}^n x_i\sum_{i=1}^n y_i}{\displaystyle n\sum_{i=1}^n x^2_i-\left( \sum_{i=1}^n x_i \right)^2}
b=\frac{\displaystyle \sum_{i=1}^n x^2_i\sum_{i=1}^n y_i-\sum_{i=1}^n x_iy_i\sum_{i=1}^n x_i}{\displaystyle n\sum_{i=1}^n x^2_i-\left( \sum_{i=1}^n x_i \right)^2}
この式を愚直に使っても良いのですが、BigQueryに入力するのが大変なので、シンプルな式に変形します。
a = \frac{\mathrm{Cov}(x, y)}{\sigma_x^2}
b = \mu_y - a\mu_x
ただし、
$\mu_x, \mu_y$ は $x_i$ と $y_i$ の平均
$\sigma_x^2$ は $x_i$ の分散
$\mathrm{Cov}(x, y)$ は $x_i$ と $y_i$ の共分散です。
あとはここまでくれば、BigQueryのSQLで簡単に表現できそうです。
BigQueryには標本分散を求める式と不偏分散を求める式が別にあるので、その点注意が必要です。
select COVAR_POP(x, y) / VAR_POP(x) as a, AVG(y) - COVAR_POP(x, y) / VAR_POP(x) * AVG(x) as b from data