Псевдослучайная повторяемая сортировка в SQL Server (не NEWID () и не RAND ())
Я хотел бы случайным образом отсортировать результат повторяемым образом для таких целей, как разбиение на страницы. Для этого NEWID () слишком случайный, так как те же результаты не могут быть получены повторно. Заказ по Rand (семя) был бы идеальным, так как с тем же семенем в результате получился бы тот же случайный сбор. К сожалению, состояние 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)
Ответы 7
Вы можете использовать значение из каждой строки для повторной оценки функции rand:
Select *, Rand(@seed + id) as r from temp order by r
добавление идентификатора гарантирует, что ранд будет повторно заполнен для каждой строки. Но для значения seed вы всегда получите ту же последовательность строк (при условии, что таблица не изменится)
Основываясь на предложении хэша 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 не требуется)
Могу я спросить, пробовали ли вы мой метод? Он не требует никаких дополнительных переменных или хранимой процедуры.
Я только что попробовал, но, к сожалению, надежности не дало. Приведенные выше объявления предназначены только для того, чтобы предоставить полную среду для воспроизведения проблемы. Временная таблица должна быть репрезентативной.
После некоторого чтения это приемлемый метод.
Select Rand(@seed) -- now rand is seeded
Select *, 0 * id + Rand() as r from temp order by r
Наличие id в выражении заставляет его переоценивать каждую строку. Но умножение его на 0 гарантирует, что это не повлияет на результат rand.
Какой ужасный образ действий!
Это сработало для вас? Я получаю постоянный столбец r. Это также дает дополнительный результат. Может быть, это должно быть внутри sp? хотя безумие, вау.
В моем тесте (на SQL Server 2008 R2) RAND() оценивается только один раз. При каких условиях он когда-либо оценивает что-то другое в каждой строке?
Создание хэша может занять гораздо больше времени, чем создание случайного числа с начальным значением.
Чтобы получить больше вариаций в нашем вводе RAND ([seed]), вам нужно также значительно изменить [seed]. Возможно, такие как ...
SELECT
*,
RAND(id * 9999) AS [r]
FROM
temp
ORDER BY
r
Использование константы обеспечивает требуемую воспроизводимость. Но будьте осторожны с результатом (id * 9999), вызывающим переполнение, если вы ожидаете, что ваша таблица станет достаточно большой ...
Я думаю, что здесь возникает та же проблема, что и выше, где значения увеличиваются.
SELECT RAND (9999 * 1), RAND (9999 * 2), RAND (9999 * 3), RAND (9999 * 4), RAND (9999 * 5) 0,899884439852407 0,0861955322535983 0,27250661186434 0,4588 17691475082 0,645128Не 7710858 , но и не совсем случайный ...
С какой базой данных вы работаете? SQL2008?
Это хорошо сработало для меня в прошлом, и его можно применить к любой таблице (просто закрепите предложение ORDER BY):
SELECT *
FROM MY_TABLE
ORDER BY
(SELECT ABS(CAST(NEWID() AS BINARY(6)) % 1000) + 1);
Сортировка выполняется случайным образом, но не дает повторяемых результатов. Такое же поведение, как у order by newid ()?
SELECT *, checksum(id) AS r FROM table ORDER BY r
Такого рода работы. Хотя вывод контрольной суммы () мне не кажется таким уж случайным. В Документация MSDN говорится:
[...], we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change. Consider using HashBytes instead. When an MD5 hash algorithm is specified, the probability of HashBytes returning the same result for two different inputs is much lower than that of CHECKSUM.
Но может быть быстрее.
Немного прояснил свой ответ. Но это решение, которое вы уже придумали. Что-то вроде.
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 *, NEWID() r
from temp order by r
drop table temp
Спасибо, JayArr. Я пробовал это сделать, но, к сожалению, это привело к вознесению. По-видимому, первое случайное значение довольно предсказуемо. Я также обновлю вопрос этой запиской.