Генерация случайных значений из равномерного распределения с установкой начального числа в T-SQL

Question

Генерация случайных значений из равномерного распределения с установкой начального числа в T-SQL

Я хотел бы создать случайное значение из равномерного распределения со средним значением =0 и стандартным отклонением =1 для каждой строки данной таблицы данных в T-SQL. Кроме того, я хотел бы установить начальное значение для обеспечения воспроизводимости анализа. Вот идеи, которые не сработали:

Используя функцию RAND() с объявленным числом не выполняет эту задачу: одно и то же случайное значение генерируется для каждой строки набора данных.
Такое решение:
SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber]

также не решает проблему, поскольку она не воспроизводима.

РЕДАКТИРОВАТЬ:

Производительность имеет значение, поскольку в моей таблице сотни миллионов записей.

10

sql-server t-sql random random-seed

Источник

user4931020 01 мар '17 в 22:56

5 ответов

Другие вопросы по тегам sql-server t-sql random random-seed

user357429 09 мар '17 в 11:32 2017-03-09 11:32 · Answer 1 · 2017-03-09 11:32

Главный вопрос здесь, ИМХО, как вы видите "повторяемость"? Или по-другому спросить: что "движет" случайностью? Я могу представить решение, которое будет привязывать одно и то же случайное число к каждой записи для каждого прогона, пока данные не меняются. Тем не менее, что вы ожидаете, если данные изменятся?

Для удовольствия я провел следующие тесты на (не очень представительной) тестовой таблице с 1 миллионом строк:

-- seed
SELECT Rand(0)

-- will show the same random number for EVERY record
SELECT Number, blah = Convert(varchar(100), NewID()), random = Rand()
  INTO #test
  FROM master.dbo.fn_int_list(1, 1000000)

CREATE UNIQUE CLUSTERED INDEX uq0_test ON #test (Number)

SET NOCOUNT ON

GO
DECLARE @start_time datetime = CURRENT_TIMESTAMP,
        @c_number int

-- update each record (one by one) and set the random number based on 'the next Rand()' value
-- => the order of the records drives the distribution of the Rand() value !

-- seed
SELECT @c_number = Rand(0) 

-- update 1 by 1 
DECLARE cursor_no_transaction CURSOR LOCAL STATIC
    FOR SELECT Number
          FROM #test
         ORDER BY Number
OPEN cursor_no_transaction 
FETCH NEXT FROM cursor_no_transaction INTO @c_number
WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE #test 
           SET random = Rand()
         WHERE Number = @c_number

        FETCH NEXT FROM cursor_no_transaction INTO @c_number
    END
CLOSE cursor_no_transaction 
DEALLOCATE cursor_no_transaction 

PRINT 'Time needed (no transaction) : ' + Convert(nvarchar(100), DateDiff(ms, @start_time, CURRENT_TIMESTAMP)) + ' ms.'

SELECT _avg = AVG(random), _stdev = STDEV(random) FROM #test

GO

DECLARE @start_time datetime = CURRENT_TIMESTAMP,
        @c_number int

BEGIN TRANSACTION

-- update each record (one by one) and set the random number based on 'the next Rand()' value
-- => the order of the records drives the distribution of the Rand() value !

-- seed
SELECT @c_number = Rand(0) 

-- update 1 by 1 but all of it inside 1 single transaction
DECLARE cursor_single_transaction CURSOR LOCAL STATIC
    FOR SELECT Number
          FROM #test
         ORDER BY Number
OPEN cursor_single_transaction 
FETCH NEXT FROM cursor_single_transaction INTO @c_number
WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE #test 
           SET random = Rand()
         WHERE Number = @c_number

        FETCH NEXT FROM cursor_single_transaction INTO @c_number
    END
CLOSE cursor_single_transaction 
DEALLOCATE cursor_single_transaction 

COMMIT TRANSACTION

PRINT 'Time needed (single transaction) : ' + Convert(nvarchar(100), DateDiff(ms, @start_time, CURRENT_TIMESTAMP)) + ' ms.'

SELECT _avg = AVG(random), _stdev = STDEV(random) FROM #test

GO

DECLARE @start_time datetime = CURRENT_TIMESTAMP

-- update each record (single operation), use the Number column to reseed the Rand() function for every record
UPDATE #test 
    SET random = Rand(Number)

PRINT 'Time needed Rand(Number) : ' + Convert(nvarchar(100), DateDiff(ms, @start_time, CURRENT_TIMESTAMP)) + ' ms.'

SELECT _avg = AVG(random), _stdev = STDEV(random) FROM #test

GO

DECLARE @start_time datetime = CURRENT_TIMESTAMP

-- update each record (single operation), use 'a bunch of fields' to reseed the Rand() function for every record
UPDATE #test 
    SET random = Rand(BINARY_CHECKSUM(Number, blah))

PRINT 'Time needed Rand(BINARY_CHECKSUM(Number, blah)) : ' + Convert(nvarchar(100), DateDiff(ms, @start_time, CURRENT_TIMESTAMP)) + ' ms.'

SELECT _avg = AVG(random), _stdev = STDEV(random) FROM #test

Результаты более или менее ожидаемые:

Time needed (no transaction) : 24570 ms.
_avg                   _stdev
---------------------- ----------------------
0.499630943538644      0.288686960086461

Time needed (single transaction) : 14813 ms.
_avg                   _stdev
---------------------- ----------------------
0.499630943538646      0.288686960086461

Time needed Rand(Number) : 1203 ms.
_avg                   _stdev
---------------------- ----------------------
0.499407423620328      0.291093824839539

Time needed Rand(BINARY_CHECKSUM(Number, blah)) : 1250 ms.
_avg                   _stdev
---------------------- ----------------------
0.499715398881586      0.288579510523627

Все они "повторяемы", вопрос в том, означает ли "повторяемость" то, что вы хотите, чтобы это значило здесь. Я придерживался AVG() и STDEV(), чтобы получить общее представление о распределении, я оставлю это на ваше усмотрение, чтобы увидеть, действительно ли они соответствуют требованиям (и если нет, как улучшить это =)

ИМХО, 1,2 секунды для 1 миллиона строк звучат неплохо для 1 миллиона строк. Тем не менее, если ваша таблица содержит дополнительные столбцы, она займет больше места и, следовательно, займет больше времени!

Надеюсь, это поможет вам начать...

user1043031 07 мар '17 в 00:27 2017-03-07 00:27 · Answer 2 · 2017-03-07 00:27

Функция Rand() может быть заполнена в начале, передав ей целочисленное начальное значение. Если вы сделаете это один раз, прежде чем генерировать какие-либо случайные числа, последовательность случайных чисел будет повторяться. Генерация значений по отдельности гарантирует, что функция Rand() возвращает числа в последовательности. Далее будет получено равномерное распределение n псевдослучайных чисел со средним значением =0 и стандартным отклонением =1:

    DECLARE @Mean    FLOAT = 0.0; 
    DECLARE @stDev   FLOAT = 1.0; 
    DECLARE @n   INT = 100000;   -- count of random numbers to generate
    DECLARE @U   TABLE(x FLOAT); -- table of random numbers

    DECLARE @SEED    INT = 123456;    -- seed to ensure list is reproducible
    SELECT RAND(@Seed);

    SET NOCOUNT ON;
    BEGIN TRAN
    DECLARE @x INT = 0; -- counter
    WHILE @x < @n
      BEGIN
      INSERT INTO @U (x)
        SELECT @Mean + (2 * SQRT(3) * @stDev) * (RAND() - 0.5)
      SET @x = @x + 1;
      END;
    COMMIT

-- Check the results    
    SELECT * from @U;

    SELECT AVG([@U].x) AS mean,
        STDEV([@U].x) AS stDev
        FROM @U;

Вместо вставки во временную таблицу в цикле while, вы можете перебирать записи в вашей существующей таблице с помощью курсора и выполнять обновление для каждой записи. Как упоминалось в комментариях, производительность может быть проблемой, но она отвечает требованиям "равномерное распределение со средним значением =0 и стандартным отклонением =1" и "воспроизводимость". Принцип работы функции Rand() вызывает обновление "1 на 1".

Ниже представлен альтернативный вариант, который будет иметь гораздо лучшую производительность (должен выполняться менее чем за 2 секунды с 1 миллионом строк) с заменой функции Rand(). Это позволяет обновлять записи в одном UPDATE но опирается на уникальный числовой ID поле в вашей таблице и обновляет поле с именем RandomNumber, Функция Rand() заменяется на ( (ID * @SEED ) % 1000 ) / 1000 что, вероятно, может быть улучшено.

DECLARE @Mean    FLOAT = 0.0; 
DECLARE @stDev   FLOAT = 1.0; 
DECLARE @SEED numeric(18,0)    = 1234567890.0;    -- seed to ensure list is reproducible

SET NOCOUNT ON;
BEGIN TRAN
UPDATE TestTable
   set Randomnumber = @Mean + (2 * SQRT(3) * @stDev) * (( (ID * @SEED ) % 1000 ) / 1000 - 0.5) 
COMMIT
-- Check the results    
SELECT AVG(RandomNumber) AS mean,
    STDEV(RandomNumber ) AS stDev
    FROM TestTable;

user7500066 07 мар '17 в 02:26 2017-03-07 02:26 · Answer 3 · 2017-03-07 02:26

DECLARE @userReportId BIGINT
SET @userReportId = FLOOR(RAND()*(10000000000000-1) + 1);

1

Источник

user7500066 07 мар '17 в 02:26

user5089204 10 мар '17 в 08:13 2017-03-10 08:13 · Answer 4 · 2017-03-10 08:13

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

Следующее предложение заполнит физическую таблицу (добавьте индексы!) Позицией и случайным числом.

Используйте этот список с простым соединением, чтобы соединить каждую строку со случайным числом.

Каждый вызов будет привязывать одно и то же случайное число к данной строке.

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

Это должно быть довольно быстро...

CREATE TABLE dbo.MyRepeatableRandoms(CurrentPosition BIGINT,RandomNumber BIGINT);
GO
DECLARE @CountOfNumbers INT=5; --set a fitting max count here
WITH Tally AS
(
 SELECT TOP(@CountOfNumbers) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Nr 
 FROM master..spt_values 
      CROSS JOIN master..spt_values X 
      CROSS JOIN master..spt_values Y
)
INSERT INTO dbo.MyRepeatableRandoms
SELECT Nr,CAST(CAST(NEWID() AS VARBINARY(8)) AS BIGINT) FROM Tally;

--Use this list with a simple join to bind it to the rows of your table
SELECT * FROM dbo.MyRepeatableRandoms ORDER BY CurrentPosition;

--Re-Position the list
WITH UpdateableCTE AS
(
 SELECT ROW_NUMBER() OVER(ORDER BY A.posOrder) AS NewPos
       ,CurrentPosition
 FROM dbo.MyRepeatableRandoms
 CROSS APPLY(SELECT NEWID() AS posOrder) AS A
)
UPDATE UpdateableCTE SET CurrentPosition=NewPos;

--The same random numbers at new positions
SELECT * FROM MyRepeatableRandoms ORDER BY CurrentPosition;

GO
DROP TABLE dbo.MyRepeatableRandoms

Результат

    RandomNumber
1   -1939965404062448822
2   2786711671511266125
3   -3236707863137400753
4   -6029509773149087675
5   7815987559555455297

После повторного позиционирования

    RandomNumber
1   7815987559555455297
2   -1939965404062448822
3   2786711671511266125
4   -6029509773149087675
5   -3236707863137400753

user256196 11 мар '17 в 01:42 2017-03-11 01:42 · Answer 5 · 2017-03-11 01:42

Вот близкое приближение, которое является чистым, простым SQL:

select iif(rand(rand(id)) < .5, -1, 1) * sqrt(1 - exp(-1.27323954474*rand(id)*rand(id) * 
  (1 + 0.0586276296*rand(id)*rand(id)) / (1 + 0.0886745239*rand(id)*rand(id))))
from mytable

Я выбрал id столбец как семя, но вы можете выбрать любой столбец, который будет наиболее подходящим для вас. т.е. изменить rand(id) в rand(some_other_column) как вам нравится.

Эта формула основана на этом математическом приближении.