抽奖概率互斥算法
问题：实现抽奖
- 红包奖品共计14个，每个奖品仅限获得1次，不可重复获得同一种红包
| 道具名称 | 概率 | |
|---|---|---|
| 第1个特殊礼包 | 1.00% | 抽奖次数消耗3次以上才可抽中此道具 |
| 第1个普通礼包 | 10.00% | 每被抽中一个道具后，将该道具的概率平均分摊在未抽中的道具概率上 |
| 第2个普通礼包 | 10.00% | 每被抽中一个道具后，将该道具的概率平均分摊在未抽中的道具概率上 |
| 第3个普通礼包 | 10.00% | 同上 |
| 第4个普通礼包 | 8.00% | ... |
| 第5个普通礼包 | 8.00% | ... |
| 第6个普通礼包 | 8.00% | ... |
| 第7个普通礼包 | 8.00% | ... |
| 第8个普通礼包 | 8.00% | ... |
| 第9个普通礼包 | 5.00% | ... |
| 第10个普通礼包 | 5.00% | ... |
| 第11个普通礼包 | 8.00% | ... |
| 第12个普通礼包 | 8.00% | ... |
| 第13个普通礼包 | 3.00% |
解答
思路
根据概率和总权重，生成权重范围，生成随机数
总权重（正整数），权重范围，随机数
例：总权重是100
A的概率是10%，其权重范围1~10
B的概率是90%，其权重范围11~100
随机数：根据总权重生成，此例也就是100以内的随机数，若总权重为N（正整数），随机数为N内的随机数
目前的问题：总权重为多少
演算
进行概率推算
\[\begin{matrix}
第n个礼包 & 第一次抽奖概率 & 第二次抽奖概率 & 第三次抽奖概率\\
1· & 0.01 & 0.01 & 0.01 \\
1 & 0.1 & 0.1+\frac{0.1}{12} & [0.1+\frac{0.1}{12}]+\frac{0.1+\frac{0.1}{12}}{11}\\
2 & 0.1 & 被抽中 & 0\\
3 & 0.1 & 0.1+\frac{0.1}{12} & 被抽中\\
4 & 0.08 & 0.08+\frac{0.1}{12} & [0.08+\frac{0.1}{12}]+\frac{0.1+\frac{0.1}{12}}{11}\\
5 & 0.08 & 0.08+\frac{0.1}{12} & []+\frac{0.1+\frac{0.1}{12}}{11}\\
6 & 0.08 & 0.08+\frac{0.1}{12} & ...\\
7 & 0.08 & 0.08+\frac{0.1}{12} & ...\\
8 & 0.08 & 0.08+\frac{0.1}{12} & ...\\
9 & 0.05 & 0.05+\frac{0.1}{12} & ...\\
10 & 0.05 & 0.05+\frac{0.1}{12} & ...\\
11 & 0.08 & 0.08+\frac{0.1}{12} & ...\\
12 & 0.08 & 0.08+\frac{0.1}{12} & ...\\
13 & 0.03 & 0.03+\frac{0.1}{12} & ...\\
\end{matrix}
\]
使用公式进行抽象
当前抽奖概率是X1，被抽礼包概率从X2~X13，可得：
\[\begin{matrix}
第n次& 概率公式 & 通分\\
(1) & X_{1} & X_{1}=X_{1}\\
(2) & [X_{1}]+\frac{X_{2}}{12}
& [X_{1}]+\frac{X_{2}}{12}
=\frac{12X_{1}+X_{2}}{12}\\
(3) & [X_{1}+\frac{X_{2}}{12}]+\frac{X_{3}+\frac{X_{2}}{12}}{11}
& [X_{1}+\frac{X_{2}}{12}]+\frac{X_{2}}{12*11}+\frac{X_{3}}{11}
= \frac{12*11X_{1}+12X_{2}+12X_{3}}{12*11}\\
(4) & [X_{1}+\frac{X_{2}}{12}+\frac{X_{3}+\frac{X_{2}}{12}}{11}]+\frac{X_{4}+\frac{X_{2}}{12}+\frac{X_{3}+\frac{X_{2}}{12}}{11}}{10}
& [X_{1}+\frac{X_{2}}{12}+\frac{X_{2}}{12*11}+\frac{X_{3}}{11}]
+\frac{11X_{2}+X_{2}}{12*11*10}+\frac{X_{3}}{11*10}+\frac{X_{4}}{10}
= \frac{12*11*10X_{1}+...}{12*11*10}\\\\
(5) & ... & ...\\
(6) & ... & ...\\
... & ... & ...\\
(13) & ...& \frac{...}{12*11*10*9*8*7*6*5*4*3*2*1}=\frac{...}{12!}
\end{matrix}
\]
由于总权重为正整除，不允许出现小数，推算出 总权重 = 12！= 479001600
实际情况-踩坑
由以上推算可得：总权重 = 12！= 479001600
注意到系数，使用公式进行抽象中X1~X13为系数，数值范围从0.03~0.1，以下为示：
\[\begin{matrix}
假设 & 第n次抽奖 & 第1个普通礼包(未抽中) & 其礼包权重为： & \frac{0.1*12!}{12*11*10*···*n}\\
假设 & 第n次抽奖 & 第4个普通礼包(未抽中) & 其礼包权重为： & \frac{0.08*12!}{12*11*10*···*n}\\
假设 & 第n次抽奖 & 第9个普通礼包(未抽中) & 其礼包权重为： & \frac{0.05*12!}{12*11*10*···*n}\\
假设 & 第n次抽奖 & 第13个普通礼包(未抽中)& 其礼包权重为： & \frac{0.03*12!}{12*11*10*···*n}\\
\end{matrix}
\]
0.03~0.1的系数，会影响礼包权重出现小数的情况，所以：总权重为 = 12！*100
具体实现（SQL SERVER）
已知信息
- 总权重 = 12！*100
- 礼包权重 = 总权重 * 概率
- 权重范围 通过礼包权重生成
- 随机数从Random（1,12！）
表设计
礼包信息表
USE [LotteryDB]
GO
/****** Object: Table [dbo].[Packages] Script Date: 2021/1/21 13:56:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Packages](
[Id] [int] IDENTITY(1,1) NOT NULL, --ID，自增
[Description] [nvarchar](max) NULL, --描述
[Probability] [decimal](18, 2) NOT NULL,--概率
[Limit] [int] NOT NULL, --限制，特殊礼包为3，普通礼包为0
[CreatedTime] [datetime] NOT NULL, --创建，时间
CONSTRAINT [PK_dbo.Packages] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
| Id | Description | Probability | Limit | CreatedTime |
|---|---|---|---|---|
| 1 | 第1个特殊礼包 | 0.01 | 3 | 2021/1/19 11:14:18 |
| 2 | 第1个普通礼包 | 0.1 | 0 | 2021/1/19 11:14:18 |
| 3 | 第2个普通礼包 | 0.1 | 0 | 2021/1/19 11:14:18 |
| 4 | 第3个普通礼包 | 0.1 | 0 | 2021/1/19 11:14:18 |
| 5 | 第4个普通礼包 | 0.08 | 0 | 2021/1/19 11:14:18 |
| 6 | 第5个普通礼包 | 0.08 | 0 | 2021/1/19 11:14:18 |
| 7 | 第6个普通礼包 | 0.08 | 0 | 2021/1/19 11:14:18 |
| 8 | 第7个普通礼包 | 0.08 | 0 | 2021/1/19 11:14:18 |
| 9 | 第8个普通礼包 | 0.08 | 0 | 2021/1/19 11:14:18 |
| 10 | 第9个普通礼包 | 0.05 | 0 | 2021/1/19 11:14:18 |
| 11 | 第10个普通礼包 | 0.05 | 0 | 2021/1/19 11:14:18 |
| 12 | 第11个普通礼包 | 0.08 | 0 | 2021/1/19 11:14:18 |
| 13 | 第12个普通礼包 | 0.08 | 0 | 2021/1/19 11:14:18 |
| 14 | 第13个普通礼包 | 0.03 | 0 | 2021/1/19 11:14:18 |
礼包权重范围表
USE [NewYearCongratulateDB]
GO
/****** Object: Table [dbo].[Packages_Probability] Script Date: 2021/1/21 14:01:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Packages_Probability](
[UserId] [bigint] NOT NULL, --用户信息唯一标识
[PackageId] [int] NOT NULL, --礼包Id
[Index] [int] NOT NULL, --第n次抽奖，从1~14
[Order] [int] NOT NULL, --礼包排序，从1~14
[Description] [nvarchar](max) NULL, --礼包描述
[Limit] [int] NOT NULL, --礼包限制
[Probability] [decimal](18, 2) NOT NULL,--礼包概率
[TotalWeightsNow] [bigint] NOT NULL, --当前权重
[WeightsStart] [bigint] NOT NULL, --权重范围起
[WeightsEnd] [bigint] NOT NULL, --权重范围止
[LastUsed] [bit] NOT NULL, --本次抽奖是否抽中
[Used] [bit] NOT NULL, --是否已抽中
[CreatedTime] [datetime] NOT NULL, --创建时间
[WeightsLog] [nvarchar](max) NULL, --当前权重记录
CONSTRAINT [PK_dbo.PackagesProbability] PRIMARY KEY CLUSTERED
(
[UserId] ASC, --用户信息唯一标识
[PackageId] ASC, --礼包Id
[Index] ASC --礼包排序，从1~14
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
第一次抽奖数据
| UserId | PackageId | Index | Order | Description | Limit | Probability | TotalWeightsNow | WeightsStart | WeightsEnd | LastUsed | Used | CreatedTime | WeightsLog |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 20210001 | 1 | 1 | 1 | 第1个特殊礼包 | 3 | 0.01 | 479001600 | 1 | 479001600 | 0 | 0 | 2021/1/21 11:18:06 | 479001600+0 |
| 20210001 | 2 | 1 | 2 | 第1个普通礼包 | 0 | 0.1 | 4790016000 | 479001601 | 5269017600 | 0 | 0 | 2021/1/21 11:18:06 | 4790016000+0 |
| 20210001 | 3 | 1 | 3 | 第2个普通礼包 | 0 | 0.1 | 4790016000 | 5269017601 | 10059033600 | 0 | 0 | 2021/1/21 11:18:06 | 4790016000+0 |
| 20210001 | 4 | 1 | 4 | 第3个普通礼包 | 0 | 0.1 | 4790016000 | 10059033601 | 14849049600 | 0 | 0 | 2021/1/21 11:18:06 | 4790016000+0 |
| 20210001 | 5 | 1 | 5 | 第4个普通礼包 | 0 | 0.08 | 3832012800 | 14849049601 | 18681062400 | 0 | 0 | 2021/1/21 11:18:06 | 3832012800+0 |
| 20210001 | 6 | 1 | 6 | 第5个普通礼包 | 0 | 0.08 | 3832012800 | 18681062401 | 22513075200 | 0 | 0 | 2021/1/21 11:18:06 | 3832012800+0 |
| 20210001 | 7 | 1 | 7 | 第6个普通礼包 | 0 | 0.08 | 3832012800 | 22513075201 | 26345088000 | 0 | 0 | 2021/1/21 11:18:06 | 3832012800+0 |
| 20210001 | 8 | 1 | 8 | 第7个普通礼包 | 0 | 0.08 | 3832012800 | 26345088001 | 30177100800 | 0 | 0 | 2021/1/21 11:18:06 | 3832012800+0 |
| 20210001 | 9 | 1 | 9 | 第8个普通礼包 | 0 | 0.08 | 3832012800 | 30177100801 | 34009113600 | 1 | 1 | 2021/1/21 11:18:06 | 3832012800+0 |
| 20210001 | 10 | 1 | 10 | 第9个普通礼包 | 0 | 0.05 | 2395008000 | 34009113601 | 36404121600 | 0 | 0 | 2021/1/21 11:18:06 | 2395008000+0 |
| 20210001 | 11 | 1 | 11 | 第10个普通礼包 | 0 | 0.05 | 2395008000 | 36404121601 | 38799129600 | 0 | 0 | 2021/1/21 11:18:06 | 2395008000+0 |
| 20210001 | 12 | 1 | 12 | 第11个普通礼包 | 0 | 0.08 | 3832012800 | 38799129601 | 42631142400 | 0 | 0 | 2021/1/21 11:18:06 | 3832012800+0 |
| 20210001 | 13 | 1 | 13 | 第12个普通礼包 | 0 | 0.08 | 3832012800 | 42631142401 | 46463155200 | 0 | 0 | 2021/1/21 11:18:06 | 3832012800+0 |
| 20210001 | 14 | 1 | 14 | 第13个普通礼包 | 0 | 0.03 | 1437004800 | 46463155201 | 47900160000 | 0 | 0 | 2021/1/21 11:18:06 | 1437004800+0 |
第二次抽奖数据
| UserId | PackageId | Index | Order | Description | Limit | Probability | TotalWeightsNow | WeightsStart | WeightsEnd | LastUsed | Used | CreatedTime | WeightsLog |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 20210001 | 1 | 2 | 1 | 第1个特殊礼包 | 3 | 0.01 | 479001600 | 1 | 479001600 | 0 | 0 | 2021/1/21 11:19:17 | 479001600+0+0 |
| 20210001 | 2 | 2 | 2 | 第1个普通礼包 | 0 | 0.1 | 5109350400 | 479001601 | 5588352000 | 0 | 0 | 2021/1/21 11:19:17 | 4790016000+0+319334400 |
| 20210001 | 3 | 2 | 3 | 第2个普通礼包 | 0 | 0.1 | 5109350400 | 5588352001 | 10697702400 | 0 | 0 | 2021/1/21 11:19:17 | 4790016000+0+319334400 |
| 20210001 | 4 | 2 | 4 | 第3个普通礼包 | 0 | 0.1 | 5109350400 | 10697702401 | 15807052800 | 0 | 0 | 2021/1/21 11:19:17 | 4790016000+0+319334400 |
| 20210001 | 5 | 2 | 5 | 第4个普通礼包 | 0 | 0.08 | 4151347200 | 15807052801 | 19958400000 | 1 | 1 | 2021/1/21 11:19:17 | 3832012800+0+319334400 |
| 20210001 | 6 | 2 | 6 | 第5个普通礼包 | 0 | 0.08 | 4151347200 | 19958400001 | 24109747200 | 0 | 0 | 2021/1/21 11:19:17 | 3832012800+0+319334400 |
| 20210001 | 7 | 2 | 7 | 第6个普通礼包 | 0 | 0.08 | 4151347200 | 24109747201 | 28261094400 | 0 | 0 | 2021/1/21 11:19:17 | 3832012800+0+319334400 |
| 20210001 | 8 | 2 | 8 | 第7个普通礼包 | 0 | 0.08 | 4151347200 | 28261094401 | 32412441600 | 0 | 0 | 2021/1/21 11:19:17 | 3832012800+0+319334400 |
| 20210001 | 9 | 2 | 9 | 第8个普通礼包 | 0 | 0.08 | 0 | 0 | 0 | 0 | 1 | 2021/1/21 11:19:17 | 3832012800+0 |
| 20210001 | 10 | 2 | 10 | 第9个普通礼包 | 0 | 0.05 | 2714342400 | 32412441601 | 35126784000 | 0 | 0 | 2021/1/21 11:19:17 | 2395008000+0+319334400 |
| 20210001 | 11 | 2 | 11 | 第10个普通礼包 | 0 | 0.05 | 2714342400 | 35126784001 | 37841126400 | 0 | 0 | 2021/1/21 11:19:17 | 2395008000+0+319334400 |
| 20210001 | 12 | 2 | 12 | 第11个普通礼包 | 0 | 0.08 | 4151347200 | 37841126401 | 41992473600 | 0 | 0 | 2021/1/21 11:19:17 | 3832012800+0+319334400 |
| 20210001 | 13 | 2 | 13 | 第12个普通礼包 | 0 | 0.08 | 4151347200 | 41992473601 | 46143820800 | 0 | 0 | 2021/1/21 11:19:17 | 3832012800+0+319334400 |
| 20210001 | 14 | 2 | 14 | 第13个普通礼包 | 0 | 0.03 | 1756339200 | 46143820801 | 47900160000 | 0 | 0 | 2021/1/21 11:19:17 | 1437004800+0+319334400 |
存储过程设计
更新礼包概率
USE [LotteryDB]
GO
/****** Object: StoredProcedure [dbo].[RefreshPackage_Probability] Script Date: 2021/1/21 11:47:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: WenzhengLi
-- Create date: 2020-12-23
-- Description: 更新礼包概率（权重范围）
-- =============================================
ALTER PROCEDURE [dbo].[RefreshPackage_Probability]
@UserId BIGINT ,
@TotalWeights BIGINT OUT,
@INDEX INT OUT --第几次抽奖
AS
BEGIN
BEGIN TRY
SET NOCOUNT ON;
SET @TotalWeights = 47900160000; --总权重 12!*100
SET @INDEX = 1; --第几次抽奖
DECLARE @PackageId INT --礼包ID
DECLARE @CountNow INT = 1; --普通抽奖剩余个数
DECLARE @WeightsNow BIGINT = 0 --总权重
DECLARE @LastWeights BIGINT = 0; --上次抽奖的权重（平分使用）
DECLARE @DataOrder INT= 0; --当前数据排序
DECLARE @InitWeights BIGINT= 0; --权重范围使用
--1.第几次抽奖 赋值@INDEX，插入本次抽奖数据
IF(EXISTS (SELECT TOP(1) * FROM [NewYearCongratulateDB].dbo.Packages_Probability
WHERE UserId = @UserId))
BEGIN
--非首次抽奖 取上次抽奖数据
SELECT @INDEX = MAX([Index]) + 1
FROM [NewYearCongratulateDB].dbo.Packages_Probability
WHERE UserId = @UserId
INSERT INTO [dbo].Packages_Probability
([UserId]
,[Order]
,[PackageId]
,[Index]
,[Description]
,[Limit]
,[Probability]
,[TotalWeightsNow]
,[WeightsStart]
,[WeightsEnd]
,[LastUsed]
,[Used]
,[CreatedTime]
,[WeightsLog])
SELECT @UserId,
A.[Order],
A.[PackageId],
@INDEX, --[Index]
A.[Description],
A.[Limit],
A.[Probability], --[Probability_Prep]
A.[TotalWeightsNow],
0, --[WeightsStart],
0, --[WeightsEnd],
0, --[LastUsed]
A.[Used],
GETDATE(),
A.[WeightsLog]
FROM [NewYearCongratulateDB].[dbo].Packages_Probability AS A
WHERE A.[UserId] = @UserId
AND A.[Index] = @INDEX - 1
ORDER BY A.[Order]
END
ELSE
BEGIN
--首次抽奖
INSERT INTO [dbo].Packages_Probability
([UserId]
,[Order]
,[PackageId]
,[Index]
,[Description]
,[Limit]
,[Probability]
,[TotalWeightsNow]
,[WeightsStart]
,[WeightsEnd]
,[LastUsed]
,[Used]
,[CreatedTime]
,[ModifiedTime]
,[WeightsLog])
SELECT @UserId,
@GameAreaId,
A.Id,
A.[PackageId],
@INDEX, --[Index]
A.[Description],
A.[Limit],
A.[Probability], --[Probability_Prep]
A.[Probability] * @TotalWeights, --[TotalWeightsNow],
0, --[WeightsStart],
0, --[WeightsEnd],
0, --[LastUsed]
0, --[Used],
GETDATE(),
CONVERT(NVARCHAR,CONVERT(BIGINT,A.[Probability] * @TotalWeights)) --整数乘分数会出现 xxx.00
FROM [NewYearCongratulateDB].[dbo].Packages AS A
ORDER BY A.Id
END
--2.核对本次抽奖数据（是否使用，当前权重数）
--赋值 @LastWeights；@CountNow；
BEGIN TRAN
IF(@INDEX = 1)
BEGIN
--首次抽奖不需要核对数据
SET @LastWeights = 0
END
ELSE
BEGIN
--核对数据 当前权重数（仅更新普通礼包 [Limit] = 0）
UPDATE Packages_Probability
SET [TotalWeightsNow] = 0
WHERE UserId = @UserId
AND [Index] = @INDEX
AND [Limit] = 0
AND [Used] = 1
--赋值@LastWeights
SELECT TOP 1 @LastWeights = [TotalWeightsNow]
FROM Packages_Probability
WHERE UserId = @UserId
AND [Index] = @INDEX - 1
AND [LastUsed] = 1
AND [Limit] = 0
END
--赋值@CountNow
SELECT @CountNow = COUNT(*)
FROM Packages_Probability
WHERE UserId = @UserId
AND [Index] = @INDEX
AND [Used] = 0
AND [Limit] = 0
--3.特殊情况-分母为0
--赋值@CountNow出现分母为0的情况
--分母为0置换为：分母为1，分子为零
--特殊礼包权重增大，
IF(@CountNow = 0)
BEGIN
SET @LastWeights = 0
SET @CountNow = 1
END
--3.特殊情况
--最后一次抽奖，限制礼包尚未抽中
IF(@INDEX = 14 AND (EXISTS (SELECT 1 FROM Packages_Probability
WHERE [UserId] = @UserId
AND [Index] = @INDEX
AND [Limit] = 3
AND [Used] = 0)))
BEGIN
UPDATE Packages_Probability
SET [TotalWeightsNow] = @TotalWeights
WHERE UserId = @UserId
AND [Index] = @INDEX
AND [Limit] = 3
END
--4.更新当前权重
UPDATE Packages_Probability
SET [TotalWeightsNow] = [TotalWeightsNow] + @LastWeights/@CountNow,
[WeightsLog] = [WeightsLog] + '+' + CONVERT(NVARCHAR,@LastWeights/@CountNow)
WHERE UserId = @UserId
AND [Index] = @INDEX
AND [Limit] = 0
AND [Used] = 0
--4.游标赋值[WeightsStart],[WeightsEnd]
DECLARE Package_Cur CURSOR
FOR SELECT [PackageId],
[TotalWeightsNow],
[Order]
FROM Packages_Probability
WHERE [UserId] = @UserId
AND [Index] = @INDEX
ORDER BY [Order]
OPEN Package_Cur
FETCH NEXT FROM Package_Cur
INTO @PackageId,@WeightsNow,@DataOrder
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@WeightsNow = 0)
UPDATE dbo.Packages_Probability
SET [WeightsStart] = 0 ,
[WeightsEnd] = 0,
[ModifiedTime] = GETDATE()
WHERE UserId = @UserId
AND [Index] = @INDEX
AND [Order] = @DataOrder
ELSE
UPDATE dbo.Packages_Probability
SET [WeightsStart] = @InitWeights + 1 ,
[WeightsEnd] = @InitWeights + @WeightsNow,
[ModifiedTime] = GETDATE()
WHERE UserId = @UserId
AND [Index] = @INDEX
AND [Order] = @DataOrder
SET @InitWeights = @InitWeights + @WeightsNow;
FETCH NEXT FROM Package_Cur
INTO @PackageId,@WeightsNow,@DataOrder--提取第一行数据并存入定义的变量中
END
CLOSE Package_Cur
DEALLOCATE Package_Cur
SET NOCOUNT OFF;
COMMIT
RETURN 0;
END TRY
BEGIN CATCH
ROLLBACK;
INSERT INTO ProcedureErrorLogs
( ErrorNumber ,
ErrorSeverity ,
ErrorState ,
ErrorProcedure ,
ErrorLine ,
ErrorMessage ,
CreatedTime
)
VALUES ( ERROR_NUMBER() ,
ERROR_SEVERITY() ,
ERROR_STATE() ,
ERROR_PROCEDURE() ,
ERROR_LINE() ,
ERROR_MESSAGE() ,
GETDATE()
);
RETURN -10;
END CATCH
END
抽奖
USE [NewYearCongratulateDB]
GO
/****** Object: StoredProcedure [dbo].[GetPackages] Script Date: 2021/1/21 14:47:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: lwz
-- Create date: 2020-12-22
-- Description: 抽奖
-- =============================================
ALTER PROCEDURE [dbo].[GetPackages]
@UserId BIGINT
@Msg NVARCHAR(128) OUT
AS
BEGIN
BEGIN TRY
SET NOCOUNT ON;
--1.检索
DECLARE @PackageCount INT --礼包数
DECLARE @TotalProbability DECIMAL(18, 5); --礼包概率
DECLARE @TotalCount INT --总抽奖次数
DECLARE @UsedCount INT --已抽奖次数
--更新抽奖次数
EXEC [dbo].[XXXXXXX] @UserId ,@TotalCount OUTPUT ,@UsedCount OUTPUT
--检索礼包信息
SELECT @PackageCount = COUNT(*) FROM Packages
IF(@PackageCount = 0)
BEGIN
SET @Msg = '礼包信息为空'
RETURN -1;
END
SELECT @TotalProbability = ISNULL(SUM(Probability),0) FROM dbo.Packages
IF ( @TotalProbability <> 1 )
BEGIN
SET @Msg = '请检查礼包概率'
RETURN -2;
END
--检索抽奖次数
IF (@TotalCount - @UsedCount) <= 0
BEGIN
SET @Msg = '暂无可抽奖次数'
RETURN -3;
END
IF (@UsedCount >= 14)
BEGIN
SET @Msg = '抽奖次数已达上限'
RETURN -4;
END
--2.更新-礼包概率范围
DECLARE @TotalWeightsNow BIGINT --总权重
DECLARE @INDEX INT --第几次概率范围
EXEC [dbo].[RefreshPackage_Probability] @UserId ,@TotalWeightsNow OUTPUT,@INDEX OUTPUT
--3.生成随机数，匹配礼包概率范围
DECLARE @RandNum BIGINT = 0; --随机数
DECLARE @PackagesId INT; --礼包ID
DECLARE @PacDes NVARCHAR(300); --礼包描述
DECLARE @Limit INT; --礼包次数
DECLARE @PackagesUsed BIT; --是否使用
--抽中限制礼包 限制次数 大于 已抽奖次数 时，重新抽奖
WHILE (EXISTS (SELECT TOP(1) * FROM [Packages_Probability]
WHERE UserId = @UserId
AND [Index] = @INDEX
AND [Used] = 0))
BEGIN
--SET @RandNum = ABS(CHECKSUM(NEWID()) % @TotalWeightsNow) + 1;
--SELECT CONVERT(BIGINT,ABS(CHECKSUM(NEWID()) % 4.790016)*10000000000+1)
SET @RandNum = CONVERT(BIGINT,ABS(CHECKSUM(NEWID()) % 4.790016)*10000000000+1)
select @RandNum
SELECT @PackagesId = PackageId ,
@PacDes = Description ,
@Limit = [Limit],
@PackagesUsed = [Used]
FROM dbo.Packages_Probability
WHERE UserId = @UserId
AND @RandNum >= WeightsStart
AND @RandNum <= WeightsEnd
AND @INDEX = [Index]
--select @Limit,@UsedCount,@PackagesUsed
--BREAK;
IF ((@Limit >= @UsedCount AND @Limit = 3) OR @PackagesUsed = 1)
CONTINUE;
ELSE
BREAK;
END
--4.生成发送礼包数据
--5.发送礼包
EXEC [dbo].[XXXXX] @UserId
SET @Msg = @PackagesId;
SET NOCOUNT OFF;
RETURN 0;
END TRY
BEGIN CATCH
INSERT INTO ProcedureErrorLogs
( ErrorNumber ,
ErrorSeverity ,
ErrorState ,
ErrorProcedure ,
ErrorLine ,
ErrorMessage ,
CreatedTime
)
VALUES ( ERROR_NUMBER() ,
ERROR_SEVERITY() ,
ERROR_STATE() ,
ERROR_PROCEDURE() ,
ERROR_LINE() ,
ERROR_MESSAGE() ,
GETDATE()
);
RETURN -10;
END CATCH
END
扩展（踩坑）
Q：SQL SERVER 生成随机数 10000以内
A：
-
使用数学函数
SQL SERVER Mathmatical 函数方法释义 返回类型 RAND() 返回一个介于 0 到 1（不包括 0 和 1）之间的伪随机 float 值。 float FLOOR() 返回小于或等于指定数值表达式的最大整数。 返回与 参数相同的类型。CEILING() 返回大于或等于指定数值表达式的最小整数。 返回与 参数相同的类型。CAST() 将表达式由一种数据类型转换为另一种数据类型。 具体使用：
SELECT CAST(FLOOR(RAND() * 10000) AS INT ) --1445 --8852 --779 --9566 select CAST(CEILING(RAND() * 10000) AS INT) --5566 --7318 --3659 --2842 -
使用系统函数
SQL SERVER 系统函数 函数方法释义 返回类型 NEWID() 创建 uniqueidentifier 类型的唯一值。 格式为“xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx”，其中每个 x 是 0-9 或 a-f 范围内的一个十六进制数 CHECKSUM() 函数返回按照表的某一行或一组表达式计算出来的校验和值。 int ABS() 返回指定数值表达式的绝对值（正值）的数学函数。 返回与 参数相同的类型。CAST ( expression AS data_type [ ( length ) ] ) 将表达式由一种数据类型转换为另一种数据类型 返回转换为 data_type 的 expression。 CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) 将表达式由一种数据类型转换为另一种数据类型 返回转换为 data_type 的 expression。 SELECT ABS(CHECKSUM(NEWID()) % 10000) + 1
注意：存储过程实现时，采用的第二种方式系统函数的方式进行实现，出现了以下冲突
CHECKSUM（）返回值为int类型
总权重=12！*100=47900160000 （47,900,160,000）类型为bigint
所以要通过CONVERT(BIGINT,ABS(CHECKSUM(NEWID()) % 4.790016)*10000000000+1)生成随机数
| 数据类型 | 范围 | 存储 |
|---|---|---|
| bigint | -2^63 (-9,223,372,036,854,775,808) 到 2^63-1 (9,223,372,036,854,775,807) | 8 字节 |
| int | -2^31 (-2,147,483,648) 到 2^31-1 (2,147,483,647) | 4 个字节 |
| smallint | -2^15 (-32,768) 到 2^15-1 (32,767) | 2 字节 |
| tinyint | 0 到 255 | 1 字节 |
