二乗平均平方根誤差で回帰モデルの当てはまりを検討する

　二乗平均平方根誤差とは英語では Root Mean Squared Error (RMSE) と書く．真値と予測値との乖離（誤差）を二乗し，その平均値をとり，その平方根を求めた値のことである．非負の値を取り，0に近いほど優れたモデルであることを示唆する．

　今回使用するのはe-Statからの社会疫学的指標を加えて熱中症搬送人員数を分析するで使用したデータベースである．先の記事では回帰モデルを評価する指標が必要との認識であった．

クエリ

　下記クエリを実行する．二乗平均平方根誤差の他，絶対平均誤差も計算している．

USE HeatStrokeDB
GO
WITH	CTE_MOVING_AVERAGE(date, pref, Avg10, Avg20, Avg30)
AS(
SELECT
    D.年月日
,	D.都道府県
,	AVG(D.日平均気温) 
		OVER (PARTITION BY D.都道府県
			  ORDER BY D.年月日 ASC
			  ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS '移動平均10日間'
,	AVG(D.日平均気温) 
		OVER (PARTITION BY D.都道府県
			  ORDER BY D.年月日 ASC
			  ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS '移動平均20日間'
,	AVG(D.日平均気温) 
		OVER (PARTITION BY D.都道府県
			  ORDER BY D.年月日 ASC
			  ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS '移動平均30日間'
FROM	dbo.T_DailyAvgTemp	AS D
)
SELECT	SQRT(CAST(AVG(POWER(CAST(EXP(-22.2
+	0.3141 * M.日最高気温
-	0.1068 * CTE_MOVING_AVERAGE.Avg30
+	0.09617 * V.日平均蒸気圧
+	LOG(P.総人口)
+	0.0001203 * A.[ルームエアコン【台】]
-	3.301 * G.年間収入のジニ係数
+	0.00009799 * I.[光熱・水道費【円】]
-	0.00000291 * I.[実収入【円】]
+	0.01777 * L.第１次産業就業者比率*100
-	0.005739 * L.第２次産業就業者比率*100
+	0.00002997 * PA.H9101_都市公園数
-	0.1554 * (PA.[H920502_都市緑地面積【ｈａ】]/dbo.T_AREA.[B1102_総面積【ｈａ】])*100
-	0.004013 * (CAST(NA.[B2101_自然公園面積【ｈａ】] AS float)	
/	CAST(dbo.T_AREA.[B1102_総面積【ｈａ】] AS float))*100	
+	0.02068 * NA.[B2102_都道府県立自然公園数【箇所】]
-	0.0000005096 * W.[生活保護被保護実人員【人】]
)	AS int)
-	H.[搬送人員（計）],2)) AS float))	AS RootMeanSquaredError
,
	AVG(CAST(ABS(CAST(EXP(-22.2
+	0.3141 * M.日最高気温
-	0.1068 * CTE_MOVING_AVERAGE.Avg30
+	0.09617 * V.日平均蒸気圧
+	LOG(P.総人口)
+	0.0001203 * A.[ルームエアコン【台】]
-	3.301 * G.年間収入のジニ係数
+	0.00009799 * I.[光熱・水道費【円】]
-	0.00000291 * I.[実収入【円】]
+	0.01777 * L.第１次産業就業者比率*100
-	0.005739 * L.第２次産業就業者比率*100
+	0.00002997 * PA.H9101_都市公園数
-	0.1554 * (PA.[H920502_都市緑地面積【ｈａ】]/dbo.T_AREA.[B1102_総面積【ｈａ】])*100
-	0.004013 * (CAST(NA.[B2101_自然公園面積【ｈａ】] AS float)	
/	CAST(dbo.T_AREA.[B1102_総面積【ｈａ】] AS float))*100	
+	0.02068 * NA.[B2102_都道府県立自然公園数【箇所】]
-	0.0000005096 * W.[生活保護被保護実人員【人】]
)	AS int)
-	H.[搬送人員（計）])	AS float))	AS MeanAbsoluteError

FROM	dbo.T_MaxTemperature	AS M
INNER	JOIN	CTE_MOVING_AVERAGE
ON	CTE_MOVING_AVERAGE.date=M.年月日
AND	CTE_MOVING_AVERAGE.pref=M.都道府県
INNER	JOIN	dbo.T_HeatStroke	AS H
ON	M.都道府県コード = H.都道府県コード
AND	M.年月日 = H.日付
INNER	JOIN	dbo.T_VaporPressure	AS V
ON	M.都道府県コード=V.都道府県コード
AND	M.年月日=V.年月日
INNER	JOIN	dbo.T_Population	AS P
ON	M.都道府県コード = P.都道府県コード
AND	YEAR(M.年月日) = P.調査年
INNER	JOIN	dbo.T_AirCon	AS A
ON	M.都道府県コード = LEFT(A.都道府県コード, 2)
AND	CASE WHEN YEAR(M.年月日) >= 2014 THEN 2014 ELSE
	CASE WHEN YEAR(M.年月日) >= 2009 THEN 2009 ELSE	2004 END END = A.調査年
INNER	JOIN	dbo.T_Gini	AS G
ON	M.都道府県コード = LEFT(G.都道府県コード, 2)
AND	CASE WHEN	YEAR(M.年月日) >= 2014 THEN 2014 ELSE 2009 END = G.調査年
INNER	JOIN	dbo.T_Income	AS I
ON	M.都道府県コード = LEFT(I.都道府県コード, 2)
AND	CASE WHEN YEAR(M.年月日) >= 2014 THEN 2014 ELSE
	CASE WHEN YEAR(M.年月日) >= 2009 THEN 2009 ELSE 2004 END END = I.調査年
INNER	JOIN	dbo.T_LaborForce	AS L
ON	CASE WHEN YEAR(M.年月日) >= 2020 THEN 2020 ELSE
	CASE WHEN YEAR(M.年月日) >= 2015 THEN 2015 ELSE
	CASE WHEN YEAR(M.年月日) >= 2010 THEN 2010 ELSE 2005 END END END = L.調査年
AND	M.都道府県コード = LEFT(L.都道府県コード, 2)
INNER	JOIN	dbo.T_PARKS	AS PA
ON	M.都道府県コード = LEFT(PA.都道府県コード, 2)
AND	CASE WHEN	YEAR(M.年月日) >= 2021 THEN 2021 ELSE YEAR(M.年月日) END = PA.調査年
INNER	JOIN	dbo.T_NaturePark	AS NA
ON	CASE WHEN YEAR(M.年月日) >= 2021 THEN 2021 ELSE YEAR(M.年月日) END = NA.調査年
AND	M.都道府県コード = LEFT(NA.都道府県コード, 2)
INNER	JOIN	dbo.T_AREA
ON	M.都道府県コード = LEFT(dbo.T_AREA.都道府県コード, 2)
AND	CASE WHEN	YEAR(M.年月日) >= 2021 THEN 2021 ELSE YEAR(M.年月日) END = dbo.T_AREA.調査年
INNER	JOIN	dbo.T_Wellfare	AS W
ON	M.都道府県コード = LEFT(W.都道府県コード,2)
AND	YEAR(M.年月日) = W.調査年

(1 行処理されました)

結果

RootMeanSquaredError	MeanAbsoluteError
8.71779788708135	3.83666406162778

　筆者の作成したモデルは都道府県別の熱中症搬送人員数の予測と実際をEXCELの組み合わせグラフで描くで公開した．このモデルも同様にRMSEを求めてみると下記のようになった．

USE HeatStrokeDB;
GO
SELECT	
	SQRT(AVG(CAST(POWER(H.[搬送人員（計）]
-	CAST(EXP(- 20.50 
			+ 0.2781  * T.日最高気温
			+ 0.07366 * V.日平均蒸気圧
			- 0.9236  * 1/(1 + EXP(16 - 2 * MONTH(T.年月日)))
			- 0.1485  * LOG(P.人口密度)
			+ LOG(P.[65歳以上人口]))
	AS int), 2) AS float)))	AS RootMeanSquaredError
FROM	dbo.T_HeatStroke AS H
INNER JOIN	dbo.T_MaxTemperature	AS T
ON	H.都道府県コード = T.都道府県コード
AND	H.日付 = T.年月日
INNER JOIN	dbo.T_VaporPressure	AS V
ON	H.都道府県コード = V.都道府県コード
AND	H.日付 = V.年月日
INNER JOIN	dbo.T_PopulationDensity	AS P
ON	H.都道府県コード = P.都道府県コード
AND	YEAR(H.日付) = P.調査年

RootMeanSquaredError
8.63938639489863

まとめ

　熱中症搬送人員数の予測値と真値の誤差を評価するのに二乗平均平方根誤差と絶対平均誤差を使って評価した．社会疫学的指標を変数として投入するよりも筆者作成のモデルのほうが若干当てはまりが良くなった．