sql server:Monty Hall problem (蒙提霍尔问题)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198

--------------------------------------------------------------------- -- Auxiliry Table of Numbers 数字辅助表 ---------------------------------------------------------------------   -- Listing 4-8: Creating and Populating Auxiliary Table of Numbers SET NOCOUNT ON; USE AdventureWorks; GO IF OBJECT_ID('dbo.Nums') IS NOT NULL   DROP TABLE dbo.Nums; GO CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY); DECLARE @max AS INT, @rc AS INT; SET @max = 1000000; SET @rc = 1;   INSERT INTO Nums VALUES(1); WHILE @rc * 2 <= @max BEGIN   INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;   SET @rc = @rc * 2; END   INSERT INTO dbo.Nums   SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max; GO   -- Naive Solution Returning an Auxiliary Table of Numbers DECLARE @n AS BIGINT; SET @n = 1000000;   WITH Nums AS (   SELECT 1 AS n   UNION ALL   SELECT n + 1 FROM Nums WHERE n < @n ) SELECT n FROM Nums OPTION(MAXRECURSION 0); GO   -- Optimized Solution 1 DECLARE @n AS BIGINT; SET @n = 1000000;   WITH Base AS (   SELECT 1 AS n   UNION ALL   SELECT n + 1 FROM Base WHERE n < CEILING(SQRT(@n)) ), Expand AS (   SELECT 1 AS c   FROM Base AS B1, Base AS B2 ), Nums AS (   SELECT ROW_NUMBER() OVER(ORDER BY c) AS n   FROM Expand ) SELECT n FROM Nums WHERE n <= @n OPTION(MAXRECURSION 0); GO   -- Optimized Solution 2 DECLARE @n AS BIGINT; SET @n = 1000000;   WITH L0   AS(SELECT 1 AS c UNION ALL SELECT 1), L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B), L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B), L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B), L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B), L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B), Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5) SELECT n FROM Nums WHERE n <= @n; GO   -- Listing 4-9: UDF Returning an Auxiliary Table of Numbers IF OBJECT_ID('dbo.fn_nums') IS NOT NULL   DROP FUNCTION dbo.Nums; GO CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE AS RETURN   WITH   L0   AS(SELECT 1 AS c UNION ALL SELECT 1),   L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),   L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),   L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),   L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),   L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),   Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)   SELECT n FROM Nums WHERE n <= @n; GO   -- Test function SELECT * FROM dbo.fn_nums(10) AS F; GO   --蒙提霍尔问题 --https://math.ucsd.edu/~crypto/Monty/montybg.html --Steve Kass   --用T-SQL(2005)模拟蒙提霍尔问题 --访问：https://math.ucsd.edu/~crypto/Monty/montybg.html --可以找到该问题的描述 WITH T0 AS (     SELECT     --prize_door 是概率相同的门，1，2，3     1+ABS(BINARY_CHECKSUM(NEWID()))%3 AS prize_door     FROM dbo.Nums     WHERE n<=100000 --尝试的次数     --任何表都行，只要不要太小 ), T1 AS ( SELECT prize_door,     --your_door 是概率相同的门，1，2，3     1+ABS(BINARY_CHECKSUM(NEWID()))%3 AS your_door     FROM T0 ), T2 AS (     SELECT     prize_door,     your_door,     CASE     WHEN prize_door<>your_door THEN 6-prize_door-your_door     ELSE SUBSTRING(REPLACE('123',RIGHT(your_door,1),''),1+ABS(BINARY_CHECKSUM(NEWID()))%2,1)     END AS open_door     FROM T1 ), T3 AS (     SELECT prize_door,your_door,open_door,         -- other door 是你最初没有选择的仍然关闭的门     6-your_door-open_door AS other_door     FROM T2 ), T4 AS (     SELECT COUNT(CASE WHEN prize_door=your_door                 THEN 'don''t switch' END) AS staying_wins,             COUNT(CASE WHEN prize_door=other_door                 THEN 'do switch' END) AS switching_wins,             COUNT(*) AS trials     FROM T3 ) SELECT trials,CAST(100.0*staying_wins/trials     AS DECIMAL(5,2)) AS staying_winsPercent,     CAST(100.0*switching_wins/trials     AS DECIMAL(5,2)) AS switching_winsPercent     FROM T4; GO   ---T-SQL Simulator for Monty Hall Paradox declare @counter int = 0 declare @maxGames int = 1000 declare @randomPrizeDoor tinyint declare @randomChoosenDoor tinyint declare @randomOpenedDoor tinyint   -- Games declare @games table (     GameId int not null identity(1, 1),     PrizeDoor tinyint not null,     ChoosenDoor tinyint not null,     HostOpensDoor tinyint not null,     ResultIfYouStay as case when PrizeDoor = ChoosenDoor then 1 else 0 end,     ResultIfYouSwitch as case when PrizeDoor = ChoosenDoor then 0 else 1 end )   while @counter < @maxGames begin     -- Hosts put a prize behind random door 1-3     SELECT @randomPrizeDoor = ABS(CAST(NEWID() AS binary(6)) %3) + 1 FROM sysobjects       -- Player randomly selects one door 1-3     SELECT @randomChoosenDoor = ABS(CAST(NEWID() AS binary(6)) %3) + 1 FROM sysobjects       -- Host shows one door where there is no prize     SELECT TOP 1 @randomOpenedDoor = Door     FROM (select 1 as Door union all select 2 union all select 3) T     WHERE T.Door not in (@randomPrizeDoor, @randomChoosenDoor)       insert into @games(PrizeDoor, ChoosenDoor, HostOpensDoor)     select @randomPrizeDoor, @randomChoosenDoor, @randomOpenedDoor     set @counter = @counter + 1 end   select 1.0 * sum(ResultIfYouStay) / @maxGames as ChancesToWinIfYouStay, 1.0 * sum(ResultIfYouSwitch) / @maxGames as ChancesToWinIfYouSwitch from @games GO

　　https://www.codeproject.com/Articles/16179/The-Monty-Hall-Problem-C-Solution

https://www.codeproject.com/Articles/30473/Monty-Hall-Paradox-Illustrated