sql-server - 按重量百分比选择随机元素

Question

假设我有如下表格格式，我如何根据 Microsoft SQL Server 中函数/存储过程的百分比选择随机用户。

请注意，它应该是一个通用函数，可以处理行中的任何随机数据。

对于正常的 javascript 操作等，我可以为此找到库，但我无法在 SQL Server 中为此找到内置的存储过程/函数。

user  percentage
-----------------
a     0.1
b     0.3
c     0.4
d     0.2

Answer 1

找到每个用户的每个概率区间，然后使用 RAND() 找到您的加权选择。

DECLARE @Random FLOAT = RAND()

;WITH Odds AS
(
    SELECT
        V.*
    FROM
        (VALUES
        ('A', 0.1),
        ('B', 0.3),
        ('C', 0.4),
        ('D', 0.2)
        ) V (UserCode, Percentage)
),
OddIntervals AS
(
    SELECT
        O.*,
        OddStart = SUM(O.Percentage) OVER (ORDER BY O.UserCode) - O.Percentage,
        OddsEnd = SUM(O.Percentage) OVER (ORDER BY O.UserCode)
    FROM
        Odds AS O
)
SELECT
    O.*
FROM
    OddIntervals AS O
WHERE
    @Random > O.OddStart AND
    @Random <= O.OddsEnd

OddsIntervals 如下所示:

UserCode    Percentage  OddStart    OddsEnd
A           0.1         0.0         0.1
B           0.3         0.1         0.4
C           0.4         0.4         0.8
D           0.2         0.8         1.0

RAND()不返回 1，因此最后一个间隔可能会有点不利。

---

如果您需要多次执行此操作，可以使用以下脚本生成 N 个随机数(我用了 4 秒生成 100k 个值)。您必须在每一行提供不同的种子以获得不同的 RAND() 结果，因此我使用 NEWID() 作为种子。您只需将 #Odds 表替换为您的表即可执行。

DECLARE @AmountRandomValues INT = 100000

IF OBJECT_ID('tempdb..#RandomValues') IS NOT NULL
    DROP TABLE #RandomValues

SELECT TOP (@AmountRandomValues)
    RandomValue = RAND(CONVERT(VARBINARY, NEWID()))
INTO 
    #RandomValues
FROM 
    sys.columns AS s1
    CROSS JOIN sys.columns AS s2

IF OBJECT_ID('tempdb..#Odds') IS NOT NULL
    DROP TABLE #Odds

CREATE TABLE #Odds (
    UserCode CHAR(1),
    Percentage DECIMAL(3,2))

INSERT INTO #Odds (
    UserCode,
    Percentage)
VALUES
    ('A', 0.1),
    ('B', 0.3),
    ('C', 0.4),
    ('D', 0.2)

;WITH OddIntervals AS
(
    SELECT
        O.*,
        OddStart = SUM(O.Percentage) OVER (ORDER BY O.UserCode) - O.Percentage,
        OddsEnd = SUM(O.Percentage) OVER (ORDER BY O.UserCode)
    FROM
        #Odds AS O
)
SELECT
    R.RandomValue,
    O.*
FROM
    #RandomValues AS R
    INNER JOIN OddIntervals AS O ON 
        R.RandomValue > O.OddStart AND
        R.RandomValue <= O.OddsEnd

对于 100k 的值，例如几次运行的用户选择量如下:

第一次运行:

UserCode    Amount
A           10222
B           29883
C           39738
D           20157

第二次运行:

UserCode    Amount
A           10064
B           29794
C           40061
D           20081

第三次运行:

UserCode    Amount
A           10030
B           29960
C           40261
D           19749

您可以看到这与他们的赔率非常一致。