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