Вопрос по sql-server – Как сгенерировать случайное число для каждой строки в TSQL Select?

279

Мне нужно другое случайное число для каждой строки в моей таблице. Следующий, казалось бы, очевидный код использует одно и то же случайное значение для каждой строки.

<code>SELECT table_name, RAND() magic_number 
FROM information_schema.tables 
</code>

Я хотел бы получить INT или FLOAT из этого. В остальной части истории я собираюсь использовать это случайное число для создания случайного смещения даты относительно известной даты, например, Смещение 1-14 дней от даты начала.

Это для Microsoft SQL Server 2000.

@ Rory Задайте это как новый вопрос, это привлечет больше внимания. (Мой ответ будет заключаться в использовании фиксированных таблиц случайных чисел, например. Например, этот известный стандартный набор случайных чисел: Rand.org / Пабы / monograph_reports / MR1418 / index.html ) MatthewMartin
RAND был введен в 2005 году, этот вопрос был задан в 2009 году, в каких организациях все еще использовался SQL 2000, потому что это была первая версия, достаточно хорошая, чтобы использовать ее вечно. MatthewMartin
Есть ли решение, которое не использует NEWID ()? Я хочу иметь возможность генерировать ту же последовательность случайных чисел для данного семени. Rory MacLeod
Если вы ищете очень равномерное распределение случайных чисел в наборе данных, я добавил ответ ниже, который можно использовать при определенных обстоятельствах и которого я не видел в Интернете. Это не проблема, люди. Trevor
Посмотрите @RAND (Transact-SQL) AminM

Ваш Ответ

17   ответов
440

SQL Server - установить случайные числа на основе с очень подробным объяснением.

Подводя итог, следующий код генерирует случайное число от 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 равен Много больше ... настолько, что в большинстве случаев смещение почти невозможно обнаружить. Однако об этом следует знать, если вы обнаружите, что делаете это для серьезного кода безопасности.

На этой связанной странице было решение: ABS (CHECKSUM (NewId ()))% 14 MatthewMartin
% 14 вернет числа от 0 до 13 CoderDennis
@ Деннис Палмер, просто добавь 1 KM.
Мы только что обнаружили гениальную ошибку с этим. Поскольку контрольная сумма возвращает целое число, а диапазон целого числа от -2 ^ 31 (-2 147 483 648) до 2 ^ 31-1 (2 147 483 647), функция abs () может возвращать ошибку переполнения, если результат равен точно 2 147 483 648. ! Шансы, очевидно, очень малы, около 1 на 4 миллиарда, однако мы каждый день запускали их по таблице строк ~ 1,8 млрд, так что это происходило примерно раз в неделю! Исправление - привести контрольную сумму к bigint до пресса. EvilPuppetMaster
Я думаю, что это должно сказать «равномерное распределение», а не «нормализованное распределение» - каждое число одинаково вероятно, это не кривая колокола. «Нормализованный» имеет конкретное математическое значение. AnotherParker
87

е число.

Я бы предложил использовать конвертировать varbinary,newid()) в качестве начального аргумента:

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

newid() гарантированно будет возвращать разные значения при каждом вызове, даже в пределах одного и того же пакета, поэтому использование его в качестве начального числа приведет к тому, что rand () выдаст другое значение каждый раз.

Отредактировано, чтобы получить случайное целое число от 1 до 14.

Как вы получаете число из guid или varbinary? Я обновлю вопрос, чтобы указать, что я надеюсь на целое число. MatthewMartin
Вы умножаете это на число и напишите это :), так что если вы хотите пять цифр, умножьте на 100000 и преобразуйте в целое число. Уродливо, но достаточно просто. Jeremy Smyth
Как дальнейшее дополнение - это даст вамвплоть д пять цифр - если вы хотите заполнить его нулями, вам нужно будет использовать тип данных char и использовать replicate для заполнения нулями до 5 цифр. Jeremy Smyth
Если вы используете функцию потолка вместо пола, вам не нужно добавлять 1. PopeDarren
2

Это так же просто, как:

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
64
RAND(CHECKSUM(NEWID()))

исключая. Если используется в выборе, поскольку начальное значение изменяется для каждой строки, оно генерирует новое случайное число для каждой строки (однако не гарантируется, что будет генерироваться уникальное число для каждой строки).

Пример в сочетании с верхним пределом 10 (производит числа 1 - 10):

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

Документация по Transact-SQL:

CAST(): https: //docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sqRAND(): http: //msdn.microsoft.com/en-us/library/ms177610.aspCHECKSUM(): http: //msdn.microsoft.com/en-us/library/ms189788.aspNEWID(): https: //docs.microsoft.com/en-us/sql/t-sql/functions/newid-transact-sq
32

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

"+ 1" - включить значения верхней границы (9999 для предыдущего примера)

Верхняя граница является исключительной для этого метода, поэтому, если вы хотите включить верхнее число, вам нужно сделатьFLOOR(RAND(CHECKSUM(NEWID()))*(10000-1000)+1000) vaindil
Спасибо, vaindil, обновленный ответ, чтобы включить его Vova
11

если оно используется в запросе SELECT таблицы. То же самое относится, если вы используете семя для функции Rand. Альтернативный способ сделать это, используя это:

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

Получил информацию отВо, которая очень хорошо объясняет проблему.

4

используя начальное значение в RAND (seedInt). RAND () будет выполняться только один раз для каждого оператора, поэтому каждый раз вы видите одно и то же число.

Simplest! Хотя значения кажутся гораздо более разбросанными, используются цифры из середины, например,RIGHT(CONVERT(BIGINT, RAND(RecNo) * 1000000000000), 2) (примечание: я вижуRIGHT неявно преобразуетBIGINT вCHAR, но если быть строгим, у тебя был бы другойCONVERT там). Doug_Ivison
15

но этот ответ не был предоставлен ранее, и, надеюсь, он будет полезен для тех, кто находит эти результаты через поисковую систему.

С SQL Server 2008 была введена новая функция,CRYPT_GEN_RANDOM(8), который использует CryptoAPI для создания криптографически сильного случайного числа, возвращаемого какVARBINARY(8000). Вот страница документации:https: //docs.microsoft.com/en-us/sql/t-sql/functions/crypt-gen-random-transact-sq

Чтобы получить случайное число, вы можете просто вызвать функцию и привести ее к нужному типу:

select CAST(CRYPT_GEN_RANDOM(8) AS bigint)

или получитьfloat между -1 и +1, вы можете сделать что-то вроде этого:

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

чтобы оно каждый раз генерировало «одинаковые» случайные данные, вы можете сделать следующее:

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

3. Прежде чем выбирать ваши данные, запустите функцию rand (), а затем используйте UDF в вашем операторе select.

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
5

которое вы можете передать в качестве начального числа в функцию RAND?

Чтобы получить целое число от 1 до 14, я думаю, это сработает:

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

чтобы это было целое число, но любой случайный уникальный идентификатор, вы можете использоватьnewid()

SELECT table_name, newid() magic_number 
FROM information_schema.tables
2

с которой я иногда сталкиваюсь с выбранным «Ответом», заключается в том, что распределение не всегда равномерное. Если вам нужно очень равномерное распределение случайных чисел от 1 до 14 среди большого количества строк, вы можете сделать что-то вроде этого (моя база данных имеет 511 таблиц, так что это работает. Если у вас меньше строк, чем у диапазона случайных чисел, это не работает Что ж)

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

Этот тип противоположен нормальным случайным решениям в том смысле, что он поддерживает последовательность чисел и рандомизирует другой столбец.

Помните, у меня есть 511 таблиц в моей базе данных (что относится только к тому, что мы выбираем из информационной_схемы). Если я беру предыдущий запрос и помещаю его во временную таблицу #X, а затем запускаю этот запрос для полученных данных:

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

Я получаю этот результат, показывая, что мое случайное число ОЧЕНЬ равномерно распределено по множеству строк:

4
Ссылка на мертвую :( Какие-либо копии, которые могут быть включены в ответ? jocull
Он кладетRAND() в вид, ставитSELECT этого представления в функцию, а затем вызывает функцию из любого места. Умная Doug_Ivison
Я опубликовал решение, которое решает проблему точно так же, как в связанной статье, но здесь, в этом блоге, прямо как ответ пять постов назад! Никто не называл меня умн завистливое лицо хе-хе Mitselplik
4
select round(rand(checksum(newid()))*(10)+20,2)

round даст максимум два знака после запятой.

Если тебе нужны отрицательные числа, ты можешь сделать это с помощью

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

Тогда минимальное значение будет -60, а максимальное - -50.

2

newid ()

или, возможно, это select binary_checksum (newid ())

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

1
    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;
Это был бы лучший ответ, если бы ты мог объяснить, что делает код и почему ... arnt
Извините, если я плохо объяснил, ichak khoury
Извини @ Арнт, у нас здесь две функции CTDE_GENERATE_32_BIT_KEY, который генерирует 32-битный буквенно-цифровой ключ (может быть увеличен до более или менее), а другой называется CTDE_GENERATE_PUBLIC_KEY, который вызывает первую функцию и возвращает открытый ключ 32 бита, или вы можете вернуть закрытый ключ 16 бита ... вам просто нужно вызвать выберите dbo.CTDE_GENERATE_PUBLIC_KEY () в качестве открытого ключа; логика заключается в том, что мы выбираем один символ из списка буквенно-цифровых символов 32 раза и объединяем их вместе, чтобы получить случайный буквенно-цифровой ключ. после исследования. ichak khoury
Ницца. Это объяснение делает его намного лучшим ответом. (Кто-то пометил его для удаления; я проголосовал за то, чтобы оставить его открытым, и оставил этот комментарий для вас.) arnt
спасибо @ arnt ichak khoury

Похожие вопросы