---------------------------------------------------------------------

-- 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