Pseudo Random Repeatable Sort в 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([email protected]) 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 (п + 1)

Ответ 1

Построение хешеров 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([email protected] 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

EDIT: Обратите внимание: объявление @seed, используемое в запросе, может быть заменено параметром или константой int, если используется динамический SQL. (объявление @int в методе TSQL не требуется)

Ответ 2

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

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

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

Ответ 3

Создание хеша может быть гораздо более трудоемким, чем создание случайного числа посещенных.

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

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

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

Ответ 4

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

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

[...], мы не рекомендуем использовать CHECKSUM для определения того, изменились ли значения, если ваше приложение не может терпеть время от времени без изменений. Вместо этого используйте HashBytes. Когда задан алгоритм хеширования MD5, вероятность того, что HashBytes вернет тот же результат для двух разных входов, намного ниже, чем у CHECKSUM.

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

Ответ 5

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

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

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

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

Какой ужасный способ сделать что-то!

Ответ 6

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

SELECT *
FROM MY_TABLE
ORDER BY  
  (SELECT ABS(CAST(NEWID() AS BINARY(6)) % 1000) + 1);

Ответ 7

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