创建 0 和 1 之间的可重复随机数 (SQL Server 2008) [英] Creating Reproducible Random Numbers Between 0 and 1 (SQL Server 2008)

问题描述

我正在尝试弄清楚如何在 SQL Server 2008 中为我的一个表中的每条记录(行)分配一个介于 0 和 1 之间的唯一数字(值).我已经查看了 rand(checksum(newid())) 类型方法，这似乎是合适的，除了我需要我的一组值是可重现的(即，如果我多次运行相同的查询，我将获得相同的随机值).

I'm trying to figure out how to assign every record (row) in one of my tables a unique number (value) between 0 and 1 in SQL Server 2008. I have reviewed the rand(checksum(newid())) type methods and this appears to be appropriate with the exception that I need my set of values to be reproducible (i.e. if I run the same query multiple times I will get identical random values).

知道我该怎么做吗?

推荐答案

您将需要一次遍历行以获取每一行的不同随机值.为了确保每次都是相同的随机序列，选择一个固定的种子，并用该种子调用 RAND(seed) 一次.此后每次调用(在同一会话中)RAND() 都会为您提供该种子的下一个数字，并且始终为给定的种子生成相同的序列.

You will need to loop through the rows one at a time to get a different random value for each row. To make sure it is the same random sequence each time, pick a fixed seed, and call RAND(seed) with that seed once. Each call (in the same session) to RAND() after that will give you the next number in sequence for that seed, and will always produce the same sequence for a given seed.

进行循环的方法有很多种(例如使用游标)，但这里有一种方法可以帮助您入门.

There are different ways to do the looping (e.g. using a cursor), but here's one to get you started.

create table RandomValues
(
    Id  int,
    RandomValue float
)
go

-- Pick a value and always use the same one to get reproducible random numbers
declare @FixedSeed int
set @FixedSeed = 100

declare @Id int
declare @MaxId int

select @MaxId = MAX(Id), @Id = MIN(Id) from OriginalData

-- You don't need this value for anything, you just need a call to rand(@FixedSeed) 
-- to start the sequence with the fixed seed
declare @dummyseed float
set @dummyseed = rand(@FixedSeed)

while (@Id <= @MaxId)
  begin
    insert RandomValues
    select Id, rand()
    from   OriginalData
    where   Id = @Id

    -- Get the next Id from the original table
    select  @Id = MIN(Id)
    from    OriginalData
    where   Id > @Id
end

select od.*, rv.RandomValue 
from RandomValues rv
join OriginalData od on rv.Id = od.Id

这篇关于创建 0 和 1 之间的可重复随机数 (SQL Server 2008)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！