创建按点数加权的随机选择，SQL

Question

我有一张奖品抽奖的获奖者表，每个获奖者都在一年中获得了一些分数。共有1300个注册用户，点数在50至43 000之间。我需要能够选择一个随机的赢家，这是直截了当的，但我面临的挑战是建立一个逻辑，每一分都可以算作进入奖品抽奖的入场券。会很感激你的帮助。

约翰

Answer 1

您的脚本看起来与此类似：

脚本1：

DECLARE @Name   varchar(100),
@Points int,
@i      int


DECLARE Cursor1 CURSOR FOR SELECT Name, Points FROM Table1

OPEN Cursor1
FETCH NEXT FROM Cursor1
INTO @Name, @Points
WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @i = 0

  WHILE @i < @Points
   BEGIN
     INSERT INTO Table2 (Name)
     VALUES (@Name)
     SET @i = @i + 1
   END

FETCH NEXT FROM Cursor1 INTO @Name, @Points
 END
DEALLOCATE Cursor1

我已经创建了一个只有一个名称和点列(varchar(100)和int)的表( Table1 )，我创建了一个游标来查看Table1中的所有记录，然后循环遍历这些点，然后将每个记录插入到另一个表(Table2)中。

然后根据Point列导入名称。

脚本2：

DECLARE @Name   varchar(100),
        @Points int,
        @i      int,
        @Count  int


CREATE TABLE #temptable(
UserEmailID nvarchar(200),
Points int)

DECLARE Cursor1 CURSOR FOR SELECT UserEmailID, Points FROM Table1_TEST

OPEN Cursor1
FETCH NEXT FROM Cursor1
INTO @Name, @Points
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @i = 0
    WHILE @i < @Points
        BEGIN
            INSERT INTO #temptable (UserEmailID, Points)
            VALUES (@Name, @Points)
            SET @i = @i + 1
        END


FETCH NEXT FROM Cursor1 INTO @Name, @Points
END
DEALLOCATE Cursor1

SELECT * FROM #temptable
DROP TABLE #temptable

在Script2中，我根据请求将结果导入临时表。该脚本现在运行在Table1中的每个记录中，并根据Table1中的点数将个人、UserEmailID和点导入临时表。

因此，如果John总共有3分，而Sarah 2，脚本将导入Johns UserEmailID 3次到TEMP表，2次导入Sarah。

如果在TEMP表上应用随机选择器，那么它将随机选择一个个体。约翰显然有更好的机会赢，因为他在临时表上有3张记录，而萨拉只有2张。

假设Johns UserEmailID为1，Sarah为2:则临时表的输出为：

UserEmailID   |  Points
1             | 3
1             | 3
1             | 3
2             | 2
2             | 2

如果你需要澄清的话请告诉我。希望这能有所帮助。

Answer 2

可以使用以下方法进行加权绘制：

• 计算积分的累积和。
• 除以积分总数，得到0到1之间的值。
• 原始数据中的每一行都有一个范围，如[0，0.1)，[0.1，0.3]，0.3，1
• 计算一个随机数，并选择值落在该范围内的行。

下面是这种方法的标准‘is：

with u as (
      select u.*,
             coalesce(lead(rangestart) over (order by points) as rangeend, 1)
      from (select u.*,
                   sum(points*1.0) over (order by points) / sum(points) over () as rangestart
            from users u
           ) u
     ),
     r as (
      select random() as rand
     )
select u.*
from u
where r.rand between rangestart and rangeend;

除了使用窗口函数(在许多情况下可以由相关子查询处理)外，确切的格式还取决于随机数生成器对于查询(例如Server，无论查询中调用的次数有多频繁)返回一个值还是不确定(例如在其他数据库中)是否是确定性的。此方法只需要随机数生成器的一个值，因此它将与任何一种方法一起工作。

Answer 3

这个解决方案也适用于分数点/权值。它创建一个助手表usersum。

create table user (id int primary key, points float);
insert into user values (1, 0.5), (2, 0), (3, 1);

create table usersum (id int primary key, pointsum float);
insert into usersum
select id, (select sum(points) from user b where b.id <= a.id)
from user a;

set @r = rand() * (select max(pointsum) from usersum);
select @r, usersum.* from usersum where pointsum >= @r order by id limit 1;

http://sqlfiddle.com/#!2/ae539e/1