当前位置: 澳门新濠3559 > 数据库 > 正文

澳门新濠3559sql server:Monty Hall problem (蒙提霍尔问题

时间:2019-11-08 05:43来源:数据库
----------------------------------------------------------------------- Auxiliry Table of Numbers 数字辅助表----------------------------------------------------------------------- Listing 4-8: Creating and Populating Auxiliary Table of
---------------------------------------------------------------------
-- 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

澳门新濠3559,澳门新濠3559sql server:Monty Hall problem (蒙提霍尔问题)。  

编辑:数据库 本文来源:澳门新濠3559sql server:Monty Hall problem (蒙提霍尔问题

关键词: