{"id":956,"date":"2012-11-17T16:13:35","date_gmt":"2012-11-17T07:13:35","guid":{"rendered":"http:\/\/fujiitoshiki.com\/improvesociety\/?p=956"},"modified":"2017-04-27T12:51:06","modified_gmt":"2017-04-27T03:51:06","slug":"post-956","status":"publish","type":"post","link":"https:\/\/www.fujiitoshiki.com\/improvesociety\/?p=956","title":{"rendered":"T-SQL\u3067Fisher\u306e\u76f4\u63a5\u78ba\u7387\u691c\u5b9a\u3092\u884c\u3046"},"content":{"rendered":"

\u3000Fisher \u306e\u78ba\u7387\u306f\u30ab\u30c3\u30c8\u30aa\u30d5\u5024\u306e\u95a2\u6570\u3067\u3042\u308b\u3068\u4ee5\u524d\u66f8\u304d\u307e\u3057\u305f\uff0e\u4eca\u56de\u306f\u4e0b\u8a18\u306e\u30af\u30a8\u30ea\u3067 ROC \u66f2\u7dda\u3092\u63cf\u304d\uff0cFisher\u306e\u76f4\u63a5\u78ba\u7387\u691c\u5b9a\u3092\u884c\u3044\u307e\u3059\uff0eROC \u66f2\u7dda\u3067\u306f\u6a2a\u8ef8\u306b\u507d\u967d\u6027\u7387\u3092\u53d6\u308a\uff0c\u7e26\u8ef8\u306b\u611f\u5ea6\u3092\u53d6\u308a\u307e\u3059\uff0e\u30ab\u30c3\u30c8\u30aa\u30d5\u5024\u3092\u6a2a\u8ef8\u306b\u53d6\u308a\uff0cFisher \u306e\u78ba\u7387\u3092\u7e26\u8ef8\u306b\u30b0\u30e9\u30d5\u3092\u63cf\u304f\u3068\uff0c\u30b0\u30e9\u30d5\u306e\u6700\u5c0f\u5024\u306b\u8a72\u5f53\u3059\u308b\u30ab\u30c3\u30c8\u30aa\u30d5\u5024\u304c\u6c42\u3081\u308b\u3082\u306e\u3068\u306a\u308a\u307e\u3059\uff0e\u305f\u3060\u3057\uff0cp < 0.05 \u3067\u3042\u308b\u3053\u3068\u304c\u6761\u4ef6\u3067\u3059\uff0e<\/p>\n

\r\nCREATE TABLE [dbo].[T_DATA]\r\n    (    ID       nchar(8)         NOT NULL, PRIMARY KEY\r\n    ,    Test     decimal(4, 2)    NOT NULL\r\n    ,    Outcome  nchar(1)         NOT NULL\r\n    );\r\nGO\r\nALTER TABLE T_DATA ADD CONSTRAINT CK_Outcome CHECK (Outcome = '0' OR Outcome = '1');\r\nGO<\/pre>\n
\u3000\u4e0b\u8a18\u306e\u95a2\u6570\u306f\u968e\u4e57\u3092\u5bfe\u6570\u306e\u548c\u306b\u5909\u63db\u3057\u307e\u3059\uff0e<\/p>\n
\r\nCREATE FUNCTION [dbo].[LOG_FACT](\r\n    @SrcNumber FLOAT\r\n)\r\nRETURNS FLOAT\r\nBEGIN\r\n    DECLARE @DesNumber   FLOAT\r\n    SET @DesNumber = LOG(1)\r\n    WHILE @SrcNumber > 0\r\n    BEGIN\r\n        SET @DesNumber = @DesNumber + LOG(@SrcNumber)\r\n        SET @SrcNumber = @SrcNumber - 1\r\n    END\r\n    RETURN @DesNumber\r\nEND\r\nGO<\/pre>\n
\u3000\u4e0b\u8a18\u306e\u30b9\u30c8\u30a2\u30c9\u30d7\u30ed\u30b7\u30fc\u30b8\u30e3\u306f\u30ab\u30c3\u30c8\u30aa\u30d5\u5024\u3092\u5909\u6570\u5316\u3057\u3066 T_DATA \u304b\u3089\u30af\u30ed\u30b9\u8868\u3092\u4f5c\u6210\u3057\u307e\u3059\uff0e<\/p>\n
\r\nCREATE PROCEDURE [dbo].[sp_Cut_by_Test]\r\n    @CutOff  decimal(4, 2)\r\nAS\r\nBEGIN\r\nWITH Cross_Table AS\r\n(\r\n  SELECT COUNT(*) AS 'N'\r\n    ,    SUM(CASE WHEN T_DATA.Test <= @CutOff AND T_DATA.Outcome = '1' THEN 1 ELSE 0 END) AS 'a'\r\n    ,    SUM(CASE WHEN T_DATA.Test <= @CutOff AND T_DATA.Outcome = '0' THEN 1 ELSE 0 END) AS 'b'\r\n    ,    SUM(CASE WHEN T_DATA.Test >  @CutOff AND T_DATA.Outcome = '1' THEN 1 ELSE 0 END) AS 'c'\r\n    ,    SUM(CASE WHEN T_DATA.Test >  @CutOff AND T_DATA.Outcome = '0' THEN 1 ELSE 0 END) AS 'd'\r\n    ,    SUM(CASE WHEN                            T_DATA.Outcome = '1' THEN 1 ELSE 0 END) AS 'a+c'\r\n    ,    SUM(CASE WHEN                            T_DATA.Outcome = '0' THEN 1 ELSE 0 END) AS 'b+d'\r\n    ,    SUM(CASE WHEN T_DATA.Test <= @CutOff                          THEN 1 ELSE 0 END) AS 'a+b'\r\n    ,    SUM(CASE WHEN T_DATA.Test >  @CutOff                          THEN 1 ELSE 0 END) AS 'c+d'\r\n    FROM T_DATA\r\n)\r\n  SELECT @CutOff\r\n    ,    Cross_Table.[N]\r\n    ,    Cross_Table.[a]\r\n    ,    Cross_Table.[b]\r\n    ,    Cross_Table.\r\n    ,    Cross_Table.[d]\r\n    ,    Cross_Table.[a+c]\r\n    ,    Cross_Table.[b+d]\r\n    ,    Cross_Table.[a+b]\r\n    ,    Cross_Table.\r\n    ,    Cross_Table.[a]\/Cross_Table.[a+c] AS 'Sensitivity'\r\n    ,    Cross_Table.[d]\/Cross_Table.[b+d] AS 'Specificity'\r\n    ,    1 - Cross_Table.[d]\/Cross_Table.[b+d] AS 'FalsePositive'\r\n    FROM Cross_Table;\r\nEND\r\nGO<\/pre>\n
\u3000\u4e0b\u8a18\u306e\u30b9\u30c8\u30a2\u30c9\u30d7\u30ed\u30b7\u30fc\u30b8\u30e3\u306f\u30af\u30ed\u30b9\u8868\u304b\u3089 Fisher \u306e\u78ba\u7387\u3092\u6c42\u3081\u307e\u3059\uff0e@Start \u3068\u306f test \u306e\u6700\u5c0f\u5024\u3067\u3042\u308a\uff0c @End \u306f test \u306e\u6700\u5927\u5024\uff0c @Step \u306f @Start \u304b\u3089 @End \u307e\u3067\u306e\u5897\u5206\u306e\u3053\u3068\u3067\u3059\uff0e\u4f8b\u3048\u3070\u305d\u308c\u305e\u308c @Start \u3092 2.0 \u3068\u3057\uff0c@End \u3092 4.0 \u3068\u3057\uff0c@Step \u3092 0.1 \u3068\u3059\u308b\u306a\u3069\u3067\u3059\uff0e<\/p>\n
\r\nCREATE PROCEDURE [dbo].[FisherExactTest]\r\n    (    @Start  decimal(4, 2)\r\n    ,    @End    decimal(4, 2)\r\n    ,    @Step   decimal(4, 2)\r\n    )\r\nAS\r\nBEGIN\r\n    CREATE TABLE #Result\r\n    (    [CutOff]    decimal(4, 2)    NOT NULL\r\n    ,    N    int    NOT NULL\r\n    ,    a    int    NOT NULL\r\n    ,    b    int    NOT NULL\r\n    ,    c    int    NOT NULL\r\n    ,    d    int    NOT NULL\r\n    ,    [a+c]    int    NOT NULL\r\n    ,    [b+d]    int    NOT NULL\r\n    ,    [a+b]    int    NOT NULL\r\n    ,        int    NOT NULL\r\n    ,    Sensitivity    FLOAT    NOT NULL\r\n    ,    Specificity    FLOAT    NOT NULL\r\n    ,    FalsePositive    FLOAT    NOT NULL\r\n    )\r\n    DECLARE @CutOff  decimal(4, 2)\r\n    SET @CutOff = @Start\r\n    WHILE @CutOff <= @End\r\n    BEGIN\r\n       INSERT INTO #Result EXEC sp_Cut_by_Test @CutOff\r\n       SET @CutOff = @CutOff + @Step\r\n    END\r\n  SELECT CutOff\r\n    ,    N\r\n    ,    a\r\n    ,    b\r\n    ,    c\r\n    ,    d\r\n    ,    [a+c]\r\n    ,    [b+d]\r\n    ,    [a+b]\r\n    ,    [c+d]\r\n    ,    Sensitivity\r\n    ,    Specificity\r\n    ,    FalsePositive\r\n    ,    EXP(dbo.LOG_FACT([a+b])\r\n         +   dbo.LOG_FACT([c+d])\r\n         +   dbo.LOG_FACT([a+c])\r\n         +   dbo.LOG_FACT([b+d])\r\n         -   dbo.LOG_FACT(N)\r\n         -   dbo.LOG_FACT(CASE WHEN a = 0 THEN 1 ELSE a END)\r\n         -   dbo.LOG_FACT(CASE WHEN b = 0 THEN 1 ELSE b END)\r\n         -   dbo.LOG_FACT(CASE WHEN c = 0 THEN 1 ELSE c END)\r\n         -   dbo.LOG_FACT(CASE WHEN d = 0 THEN 1 ELSE d END)) AS [FisherExact_Test]\r\n    FROM #Result\r\n   ORDER BY CutOff\r\nEND<\/pre>\n
\u53c2\u7167\u8a18\u4e8b
\n\u5bfe\u6570\u3092\u7528\u3044\u3066Fisher\u306e\u76f4\u63a5\u78ba\u7387\u691c\u5b9a\u3092\u8a08\u7b97\u3059\u308b\u306b\u306f<\/a>
\n\u5468\u8fba\u5ea6\u6570\u304b\u3089\u30af\u30ed\u30b9\u8868\u3092\u4f5c\u6210\u3059\u308b\u306b\u306f<\/a>
\n\u5bfe\u6570\u306b\u3088\u308a\u968e\u4e57\u3092\u8a08\u7b97\u3059\u308b\u30b9\u30c8\u30a2\u30c9\u30d7\u30ed\u30b7\u30fc\u30b8\u30e3\u3092\u4f5c\u6210\u3059\u308b<\/a><\/p>\n