SQL Server で線形回帰するためのSQL コード生打ち

メモです。

PostgreSQLあたりだとLinear regression (線形回帰) の関数があるのですが、SQL Serverにはそういう関数がありません。SSASだとできるらしいのですがSSRSで使いたいだけなのでSSASを設定するのもめんどくさい。ということで大学生の実験以来、線形回帰をSQLで手計算しました(-"-;;。

test というテーブルでx,yというコラム名があったときのa,b (slope, intercept)は以下になります。

SELECT
bunshi_a/bunbo_a AS a
,avg_y - bunshi_a/bunbo_a*avg_x AS b
FROM
(SELECT
SUM((x - avg_x)*(y - avg_y)) AS bunshi_a
,SUM((x - avg_x)*(x - avg_x)) AS bunbo_a
,avg_x ,avg_y
FROM
(SELECT
x,y
,(SELECT Avg(CAST(x AS REAL)) from test) AS avg_x
,(SELECT Avg(CAST(y AS REAL)) from test) AS avg_y
FROM test
) src1 /* シグマ内の必要な要素を一列に準備 */
GROUP BY avg_x,avg_y
) src2 /* 最後の計算の一歩手前 */

実際にはtestテーブルのx,yはエラー値を除去したビューかサブクエリにすると良いでしょう。
出てきたa,bから、またtestテーブルのx,yの横に回帰した値とか追加しようとすると6重サブクエリとかになりますな…。数百行なら大丈夫だろうけど。

タグ：線形回帰 BI SQL SQL Server linear regression