ROC曲線の閾値を求めるストアドプロシージャまたはインラインテーブル値関数をSQL Serverで定義する

　前回は感度と特異度をユーザー定義のスカラー値関数として定義した．今回はそれを利用して閾値を求める．

ROC曲線

　そもそも閾値とは感度と特異度のバランスが最も取れた検査値のことである．ROC曲線の点(0, 1) からの距離が最短であることと同義である．三平方の定理から，直角三角形では直角を挟む辺の二乗の和が斜辺の二乗に等しいことを利用する．

ストアドプロシージャ

　下記コードをクエリウィンドウで実行する．ユーザー定義関数（ここでは Sensitivity および Specificity）の前にdbo.を書き加えておかないと，「組み込み関数名として認識されません」というエラーが出る．

ALTER PROCEDURE [dbo].[Temperature_Death] 
	@Temperature int
AS
BEGIN
SELECT	@Temperature										AS '閾値'
,	SUM(CASE WHEN E.[傷病程度：死亡] > 0 AND T.日最高気温 >= @Temperature THEN 1 ELSE 0 END)	AS a
,	SUM(CASE WHEN E.[傷病程度：死亡] = 0 AND T.日最高気温 >= @Temperature THEN 1 ELSE 0 END)	AS b
,	SUM(CASE WHEN E.[傷病程度：死亡] > 0 AND T.日最高気温 <  @Temperature THEN 1 ELSE 0 END)	AS c
,	SUM(CASE WHEN E.[傷病程度：死亡] = 0 AND T.日最高気温 <  @Temperature THEN 1 ELSE 0 END)	AS d
,	SUM(CASE WHEN E.[傷病程度：死亡] > 0 THEN 1 ELSE 0 END)					AS 'a+c'
,	SUM(CASE WHEN E.[傷病程度：死亡] = 0 THEN 1 ELSE 0 END)					AS 'b+d'
,	SUM(CASE WHEN T.日最高気温 >= @Temperature THEN 1 ELSE 0 END)				AS 'a+b'
,	SUM(CASE WHEN T.日最高気温 <  @Temperature THEN 1 ELSE 0 END)				AS 'c+d'
,	COUNT(*)										AS N
,	dbo.Sensitivity(SUM(CASE WHEN E.[傷病程度：死亡] > 0 AND T.日最高気温 >= @Temperature THEN 1 ELSE 0 END)
,	SUM(CASE WHEN E.[傷病程度：死亡] > 0 THEN 1 ELSE 0 END))				AS 'Sensitivity'
,	dbo.Specificity(SUM(CASE WHEN E.[傷病程度：死亡] = 0 AND T.日最高気温 <  @Temperature THEN 1 ELSE 0 END)
,	SUM(CASE WHEN E.[傷病程度：死亡] = 0 THEN 1 ELSE 0 END))				AS 'Specificity'
,	1 - dbo.Specificity(SUM(CASE WHEN E.[傷病程度：死亡] = 0 AND T.日最高気温 <  @Temperature THEN 1 ELSE 0 END)
,	SUM(CASE WHEN E.[傷病程度：死亡] = 0 THEN 1 ELSE 0 END))				AS '1-Specificity'
,	1 - dbo.Sensitivity(SUM(CASE WHEN E.[傷病程度：死亡] > 0 AND T.日最高気温 >= @Temperature THEN 1 ELSE 0 END)
,	SUM(CASE WHEN E.[傷病程度：死亡] > 0 THEN 1 ELSE 0 END))				AS '1-Sensitivity'
,	POWER(1 - dbo.Specificity(SUM(CASE WHEN E.[傷病程度：死亡] = 0 AND T.日最高気温 <  @Temperature THEN 1 ELSE 0 END)
,	SUM(CASE WHEN E.[傷病程度：死亡] = 0 THEN 1 ELSE 0 END)), 2)
+	POWER(1 - dbo.Sensitivity(SUM(CASE WHEN E.[傷病程度：死亡] > 0 AND T.日最高気温 >= @Temperature THEN 1 ELSE 0 END)
,	SUM(CASE WHEN E.[傷病程度：死亡] > 0 THEN 1 ELSE 0 END)), 2)			AS 'Radius Squared'
FROM	dbo.T_HeatStroke	AS E
INNER JOIN	dbo.T_MaxTemperature	AS T
ON	E.日付 = T.年月日
AND	E.都道府県コード = T.都道府県コード
END

　ストアドプロシージャを実行するには下記のように記述する．

USE HeatStrokeDB
GO
EXEC	dbo.Temperature_Death @Temperature = 10
GO

インラインテーブル値関数

　あるいは下記のようにしても良い．

• 3行目CREATE FUNCTIONで関数名を定義する
• 5行目 () 内で引数とそのデータ型を定義する
• 7行目RETURNS TABLEで戻り値のデータ型をテーブル型に指定している
• 9行目のRETURN以下の () 内に関数の本体を記述する

USE	HeatStrokeDB
GO
CREATE FUNCTION UDF_Temperature_Death 
(	
	@Temperature int
)
RETURNS TABLE 
AS
RETURN 
(
SELECT	@Temperature										AS '閾値'
,	SUM(CASE WHEN E.[傷病程度：死亡] > 0 AND T.日最高気温 >= @Temperature THEN 1 ELSE 0 END)	AS a
,	SUM(CASE WHEN E.[傷病程度：死亡] = 0 AND T.日最高気温 >= @Temperature THEN 1 ELSE 0 END)	AS b
,	SUM(CASE WHEN E.[傷病程度：死亡] > 0 AND T.日最高気温 <  @Temperature THEN 1 ELSE 0 END)	AS c
,	SUM(CASE WHEN E.[傷病程度：死亡] = 0 AND T.日最高気温 <  @Temperature THEN 1 ELSE 0 END)	AS d
,	SUM(CASE WHEN E.[傷病程度：死亡] > 0 THEN 1 ELSE 0 END)					AS 'a+c'
,	SUM(CASE WHEN E.[傷病程度：死亡] = 0 THEN 1 ELSE 0 END)					AS 'b+d'
,	SUM(CASE WHEN T.日最高気温 >= @Temperature THEN 1 ELSE 0 END)				AS 'a+b'
,	SUM(CASE WHEN T.日最高気温 <  @Temperature THEN 1 ELSE 0 END)				AS 'c+d'
,	COUNT(*)										AS N
,	dbo.Sensitivity(SUM(CASE WHEN E.[傷病程度：死亡] > 0 AND T.日最高気温 >= @Temperature THEN 1 ELSE 0 END)
,	SUM(CASE WHEN E.[傷病程度：死亡] > 0 THEN 1 ELSE 0 END))				AS 'Sensitivity'
,	dbo.Specificity(SUM(CASE WHEN E.[傷病程度：死亡] = 0 AND T.日最高気温 <  @Temperature THEN 1 ELSE 0 END)
,	SUM(CASE WHEN E.[傷病程度：死亡] = 0 THEN 1 ELSE 0 END))				AS 'Specificity'
,	1 - dbo.Specificity(SUM(CASE WHEN E.[傷病程度：死亡] = 0 AND T.日最高気温 <  @Temperature THEN 1 ELSE 0 END)
,	SUM(CASE WHEN E.[傷病程度：死亡] = 0 THEN 1 ELSE 0 END))				AS '1-Specificity'
,	1 - dbo.Sensitivity(SUM(CASE WHEN E.[傷病程度：死亡] > 0 AND T.日最高気温 >= @Temperature THEN 1 ELSE 0 END)
,	SUM(CASE WHEN E.[傷病程度：死亡] > 0 THEN 1 ELSE 0 END))				AS '1-Sensitivity'
,	POWER(1 - dbo.Specificity(SUM(CASE WHEN E.[傷病程度：死亡] = 0 AND T.日最高気温 <  @Temperature THEN 1 ELSE 0 END)
,	SUM(CASE WHEN E.[傷病程度：死亡] = 0 THEN 1 ELSE 0 END)), 2)
+	POWER(1 - dbo.Sensitivity(SUM(CASE WHEN E.[傷病程度：死亡] > 0 AND T.日最高気温 >= @Temperature THEN 1 ELSE 0 END)
,	SUM(CASE WHEN E.[傷病程度：死亡] > 0 THEN 1 ELSE 0 END)), 2)			AS 'Radius Squared'
FROM	dbo.T_HeatStroke	AS E
INNER JOIN	dbo.T_MaxTemperature	AS T
ON	E.日付 = T.年月日
AND	E.都道府県コード = T.都道府県コード
)
GO

　関数を実行するには下記のように記述する．

USE HeatStrokeDB
GO
SELECT	*	FROM	dbo.UDF_Temperature_Death(10)

結果

　上記のストアドプロシージャまたは関数を実行した結果をグラフで示す．横軸に閾値，縦軸にRadius Squaredを取る．点(0, 1)からの距離距離が最小であるとは，点(0, 1)を中心とし，ROC曲線と接する円の半径が最小であるという意味でもある．その意味で縦軸の名前をRadius Squaredと定義している．32℃で最小値を取っており，閾値として最適であることがわかる．

　最高気温および蒸気圧について，軽症，中等症，重症，死亡の重症度ごとに閾値をプロットすると下図のようになる．最高気温，蒸気圧ともに重症度が上昇するにつれてその閾値が上昇していくことが分かる．