如何为tsql select中的每一行生成随机数?

看一看 SQL Server - Set based random numbers 这有一个非常详细的解释。

总而言之,以下代码生成一个介于0和13之间的随机数,包括一个标准化分布:

ABS(CHECKSUM(NewId())) % 14

要更改范围,只需更改表达式末尾的数字。如果您需要同时包含正数和负数的范围,请格外小心。如果你做错了,可以把数字0加倍。

房间里数学迷的一个小警告:这个代码中有一个非常轻微的偏差。 CHECKSUM() 结果在整个SQL int数据类型范围内是一致的,或者至少在我(编辑器)测试显示的范围内是一致的。但是,当checksum()在该范围的最顶端生成一个数字时,会有一些偏差。任何时候,当您得到一个介于最大可能整数和最大整数之前所需范围(本例中为14)大小的最后一个精确倍数之间的数字时,这些结果将优先于最后一个14的倍数所不能产生的范围的其余部分。

例如,假设int类型的整个范围只有19。19是可以容纳的最大整数。当checksum()结果为14-19时,这些结果对应于结果0-5。这些数字是 沉重地 优于6-13,因为checksum()生成它们的可能性是前者的两倍。这很容易用视觉来演示。下面是我们假想整数范围的全部可能结果集:

Checksum Integer: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Range Result:     0 1 2 3 4 5 6 7 8 9 10 11 12 13  0  1  2  3  4  5

你可以在这里看到,产生一些数字的机会比其他数字更多:偏见。值得庆幸的是,int类型的实际范围是 许多的 更大的。。。如此之多以至于在大多数情况下,偏差几乎是不可检测的。但是,如果您发现自己这样做是为了认真的安全代码,那么应该注意这一点。

当在一个批处理中多次调用时,rand()返回相同的数字。

我建议使用convert( varbinary , newid() )作为种子论点:

SELECT table_name, 1.0 + floor(14 * RAND(convert(varbinary, newid()))) magic_number 
FROM information_schema.tables

NWID() 保证每次调用时都返回不同的值,即使是在同一批中,因此将其用作种子将提示rand()每次提供不同的值。

编辑后得到从1到14的随机整数。

RAND(CHECKSUM(NEWID()))

上面将生成一个介于0和1之间的(伪)随机数,不包括。如果在select中使用,因为每行的seed值都会发生变化,所以它将为每行生成一个新的随机数(但不保证每行生成一个唯一的数字)。

与10的上限(产生数字1-10)组合时的示例:

CAST(RAND(CHECKSUM(NEWID())) * 10 as INT) + 1

Transact-SQL文档:

1. CAST() : https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql
2. RAND() : http://msdn.microsoft.com/en-us/library/ms177610.aspx
3. CHECKSUM() : http://msdn.microsoft.com/en-us/library/ms189788.aspx
4. NEWID() : https://docs.microsoft.com/en-us/sql/t-sql/functions/newid-transact-sql

随机数生成介于1000和9999之间(含1000和9999):

FLOOR(RAND(CHECKSUM(NEWID()))*(9999-1000+1)+1000)

“+1”-包括上限值(前一个示例为9999)

回答旧问题,但之前没有提供此答案,希望这对通过搜索引擎找到此结果的人有用。

在SQL Server 2008中,引入了一个新的函数, CRYPT_GEN_RANDOM(8) ,它使用CryptoAPI生成一个加密的强随机数,返回为 VARBINARY(8000) . 以下是文档页面: https://docs.microsoft.com/en-us/sql/t-sql/functions/crypt-gen-random-transact-sql

因此,要得到一个随机数,只需调用函数并将其强制转换为必要的类型:

select CAST(CRYPT_GEN_RANDOM(8) AS bigint)

或者得到一个 float 在-1和+1之间,您可以这样做:

select CAST(CRYPT_GEN_RANDOM(8) AS bigint) % 1000000000 / 1000000000.0

rand()函数将生成相同的随机数(如果在表选择查询中使用)。如果对rand函数使用seed,则同样适用。另一种方法是:

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber]

信息来源 here 这很好地解释了这个问题。

您是否在每一行中都有一个整数值作为种子传递给rand函数?

为了得到1到14之间的整数,我相信这是可行的:

FLOOR( RAND(<yourseed>) * 14) + 1

如果需要保留种子以便每次生成“相同”的随机数据,可以执行以下操作:

1。创建返回select rand()的视图

if object_id('cr_sample_randView') is not null
begin
    drop view cr_sample_randView
end
go

create view cr_sample_randView
as
select rand() as random_number
go

2。创建从视图中选择值的UDF。

if object_id('cr_sample_fnPerRowRand') is not null
begin
    drop function cr_sample_fnPerRowRand
end
go

create function cr_sample_fnPerRowRand()
returns float
as
begin
    declare @returnValue float
    select @returnValue = random_number from cr_sample_randView
    return @returnValue
end
go

三。在选择数据之前,输入rand()函数,然后在select语句中使用udf。

select rand(200);   -- see the rand() function
with cte(id) as
(select row_number() over(order by object_id) from sys.all_objects)
select 
    id,
    dbo.cr_sample_fnPerRowRand()
from cte
where id <= 1000    -- limit the results to 1000 random numbers

尝试在rand(seedint)中使用种子值。rand()对于每条语句只执行一次,这就是为什么每次看到相同的数字的原因。

如果您不需要它是一个整数,而是任何随机唯一标识符,那么您可以使用 newid()

SELECT table_name, newid() magic_number 
FROM information_schema.tables

您需要为每一行调用rand()。这是一个很好的例子

https://web.archive.org/web/20090216200320/http://dotnet.org.za/calmyourself/archive/2007/04/13/sql-rand-trap-same-value-per-row.aspx

select round(rand(checksum(newid()))*(10)+20,2)

这里的随机数在20到30之间。 round 最多两位小数。

如果你想要负数,你可以用

select round(rand(checksum(newid()))*(10)-60,2)

则最小值为-60,最大值为-50。

选择 newid()

或者可能是这个选择二进制校验和(newid())

我有时对所选的“答案”有一个问题,那就是分布并不总是均匀的。如果您需要在许多行中随机分布1-14,那么您可以这样做(我的数据库有511个表,所以这是有效的)。如果行数少于随机数范围,则这不会很好地工作):

SELECT table_name, ntile(14) over(order by newId()) randomNumber 
FROM information_schema.tables

这种方法与正态随机解相反,即它保持数字的顺序,并随机化另一列。

记住,我的数据库中有511个表(这只与我们从信息模式中选择的B/C相关)。如果我接受上一个查询并将其放入临时表中,然后对结果数据运行此查询:

select randomNumber, count(*) ct from #X
group by randomNumber

我得到了这个结果,显示我的随机数在许多行中非常均匀地分布:

select ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) as [Randomizer]

一直为我工作

    DROP VIEW IF EXISTS vwGetNewNumber;
    GO
    Create View vwGetNewNumber
    as
    Select CAST(RAND(CHECKSUM(NEWID())) * 62 as INT) + 1 as NextID,
    'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'as alpha_num;

    ---------------CTDE_GENERATE_PUBLIC_KEY -----------------
    DROP FUNCTION IF EXISTS CTDE_GENERATE_PUBLIC_KEY;  
    GO
    create function CTDE_GENERATE_PUBLIC_KEY()
    RETURNS NVARCHAR(32)
    AS 
    BEGIN
        DECLARE @private_key NVARCHAR(32);
        set @private_key = dbo.CTDE_GENERATE_32_BIT_KEY();
        return @private_key;
    END;
    go

---------------CTDE_GENERATE_32_BIT_KEY -----------------
DROP FUNCTION IF EXISTS CTDE_GENERATE_32_BIT_KEY;  
GO
CREATE function CTDE_GENERATE_32_BIT_KEY()
RETURNS NVARCHAR(32)
AS 
BEGIN
    DECLARE @public_key NVARCHAR(32);
    DECLARE @alpha_num NVARCHAR(62);
    DECLARE @start_index INT = 0;
    DECLARE @i INT = 0;
    select top 1 @alpha_num = alpha_num from vwGetNewNumber;
        WHILE @i < 32
        BEGIN
          select top 1 @start_index = NextID from vwGetNewNumber;
          set @public_key = concat (substring(@alpha_num,@start_index,1),@public_key);
          set @i = @i + 1;
        END;
    return @public_key;
END;
    select dbo.CTDE_GENERATE_PUBLIC_KEY() public_key;

这就像:

DECLARE @rv FLOAT;
SELECT @rv = rand();

这将把0-99之间的随机数放入一个表中:

CREATE TABLE R
(
    Number int
)

DECLARE @rv FLOAT;
SELECT @rv = rand();

INSERT INTO dbo.R
(Number)
    values((@rv * 100));

SELECT * FROM R