Псевдослучайная повторяющаяся сортировка в SQL Server (не NEWID() и не RAND())

Я хотел бы случайным образом сортировать результат повторяемым образом для таких целей, как пейджинг. Для этого NEWID() слишком случайный в том смысле, что одни и те же результаты не могут быть получены повторно. Заказ по Rand(seed) был бы идеальным, так как с одним и тем же семенем получится одна и та же случайная коллекция. К сожалению, состояние Rand() сбрасывается с каждой строкой, у кого-нибудь есть решение?

declare @seed as int;
set @seed = 1000;

create table temp (
id int,
date datetime)

insert into temp (id, date) values (1,'20090119')
insert into temp (id, date) values (2,'20090118')
insert into temp (id, date) values (3,'20090117')
insert into temp (id, date) values (4,'20090116')
insert into temp (id, date) values (5,'20090115')
insert into temp (id, date) values (6,'20090114')

-- re-seeds for every item
select *, RAND(), RAND(id+@seed) as r from temp order by r
--1 2009-01-19 00:00:00.000 0.277720118060575   0.732224964471124
--2 2009-01-18 00:00:00.000 0.277720118060575   0.732243597442382
--3 2009-01-17 00:00:00.000 0.277720118060575   0.73226223041364
--4 2009-01-16 00:00:00.000 0.277720118060575   0.732280863384898
--5 2009-01-15 00:00:00.000 0.277720118060575   0.732299496356156
--6 2009-01-14 00:00:00.000 0.277720118060575   0.732318129327415
-- Note how the last column is +=~0.00002

drop table temp

-- interestingly this works:
select RAND(@seed), RAND()
--0.732206331499865 0.306382810665955

Обратите внимание, я попробовал Rand(ID), но оказалось, что это отсортировано. Очевидно Rand(n) ‹ Rand(n+1)


sql random sql-server tsql paging
person ccook    schedule 19.01.2009    source источник

Ответы (6)


arrow_upward
15
arrow_downward

Построение предложения хеширования gkrogers отлично работает. Есть мысли о производительности?

declare @seed as int;
set @seed = 10;

create table temp (
id int,
date datetime)

insert into temp (id, date) values (1,'20090119')
insert into temp (id, date) values (2,'20090118')
insert into temp (id, date) values (3,'20090117')
insert into temp (id, date) values (4,'20090116')
insert into temp (id, date) values (5,'20090115')
insert into temp (id, date) values (6,'20090114')

-- re-seeds for every item
select *, HASHBYTES('md5',cast(id+@seed as varchar)) r
from temp order by r
--1 2009-01-19 00:00:00.000 0x6512BD43D9CAA6E02C990B0A82652DCA
--5 2009-01-15 00:00:00.000 0x9BF31C7FF062936A96D3C8BD1F8F2FF3
--4 2009-01-16 00:00:00.000 0xAAB3238922BCC25A6F606EB525FFDC56
--2 2009-01-18 00:00:00.000 0xC20AD4D76FE97759AA27A0C99BFF6710
--3 2009-01-17 00:00:00.000 0xC51CE410C124A10E0DB5E4B97FC2AF39
--6 2009-01-14 00:00:00.000 0xC74D97B01EAE257E44AA9D5BADE97BAF

drop table temp

РЕДАКТИРОВАТЬ: обратите внимание, что объявление @seed при использовании в запросе может быть заменено параметром или константой int, если используется динамический SQL. (объявление @int в стиле TSQL не требуется)

person ccook    schedule 19.01.2009
comment
Могу я спросить, пробовали ли вы мой метод? Он не требует никаких дополнительных переменных или хранимой процедуры. - person JosephStyons; 23.01.2009
comment
Я только что попробовал, к сожалению, это не обеспечило надежности. Приведенные выше объявления предназначены только для предоставления полной среды для воспроизведения проблемы. Временная таблица должна быть репрезентативной. - person ccook; 24.01.2009

arrow_upward
1
arrow_downward

Вы можете использовать значение из каждой строки для переоценки функции rand:

Select *, Rand(@seed + id) as r from temp order by r

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

person Jack Ryan    schedule 19.01.2009
comment
Спасибо JayArr. Я пробовал это, но, к сожалению, это заканчивается вознесением. По-видимому, первое случайное значение достаточно предсказуемо. Я также обновлю вопрос с этой заметкой. - person ccook; 19.01.2009

arrow_upward
1
arrow_downward

Создание хэша может занять гораздо больше времени, чем создание случайного числа.

Чтобы получить больше вариаций в нашем значении RAND([seed]), вам также нужно значительно изменить [seed]. Возможно такие как...

SELECT
    *,
    RAND(id * 9999)    AS [r]
FROM
   temp
ORDER BY
   r

Использование константы обеспечивает воспроизводимость, о которой вы просили. Но будьте осторожны с результатом (id * 9999), вызывающим переполнение, если вы ожидаете, что ваша таблица станет достаточно большой...

person MatBailie    schedule 19.01.2009
comment
Я думаю, что это вызывает ту же проблему, что и выше, где значения являются инкрементными. - person ccook; 19.01.2009
comment
Select Rand (9999 * 1), Rand (9999 * 2), Rand (9999 * 3), Rand (9999 * 4), Rand (9999 * 5) 0.899884439852407 0.086195532407 0.0861955322535983 0.27250661186434 0.458817691475082 0.645128771085824 не инкрементно, но не очень случайно же... - person MatBailie; 20.01.2009
comment
С какой базой данных вы работаете? SQL2008? - person ccook; 21.01.2009

arrow_upward
1
arrow_downward 

SELECT *, checksum(id) AS r FROM table ORDER BY r

Это работает. Хотя вывод контрольной суммы() не выглядит для меня таким уж случайным. В документации MSDN говорится:

[...], мы не рекомендуем использовать КОНТРОЛЬНУЮ СУММУ для определения того, изменились ли значения, если только ваше приложение не допускает случайного пропуска изменений. Вместо этого рассмотрите возможность использования HashBytes. Когда указан хеш-алгоритм MD5, вероятность того, что HashBytes вернет один и тот же результат для двух разных входных данных, намного ниже, чем у CHECKSUM.

Но может быть и быстрее.

person dummy    schedule 30.01.2009
comment
Немного уточнил мой ответ. Но это решение, которое вы уже придумали в любом случае. Вроде. - person dummy; 02.02.2009

arrow_upward
0
arrow_downward

После некоторого чтения это принятый метод.

Select Rand(@seed) -- now rand is seeded

Select *, 0 * id + Rand() as r from temp order by r

Наличие id в выражении приводит к тому, что оно пересчитывается в каждой строке. Но умножение на 0 гарантирует, что это не повлияет на результат rand.

Какой ужасный способ делать вещи!

person Jack Ryan    schedule 19.01.2009
comment
Это сработало для вас? Я получаю постоянный столбец r. Это также генерирует дополнительный результат. Может быть, это должно быть внутри sp? безумие однако, вау. - person ccook; 19.01.2009
comment
В моем тесте (на SQL Server 2008 R2) RAND() оценивается только один раз. При каких условиях он когда-либо оценивает что-то другое в каждой строке? - person binki; 03.02.2015

arrow_upward
-1
arrow_downward

Это хорошо работало для меня в прошлом, и его можно применить к любой таблице (просто закрепите предложение ORDER BY):

SELECT *
FROM MY_TABLE
ORDER BY  
  (SELECT ABS(CAST(NEWID() AS BINARY(6)) % 1000) + 1);
person JosephStyons    schedule 23.01.2009
comment
Он сортируется случайным образом, однако не дает повторяющихся результатов. То же поведение, что и при заказе с помощью newid()? - person ccook; 24.01.2009