Псевдо произволно повторяемо сортиране в 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)


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

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

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
ИЗБЕРЕТЕ RAND(9999 * 1), RAND(9999 * 2), RAND(9999 * 3), RAND(9999 * 4), RAND(9999 * 5) 0,899884439852407 0,0861955322535983 0,27250661186434 0,4588176914 75082 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

Този вид работи. Въпреки че изходът от checksum() не ми изглежда толкова случаен. В документацията на MSDN се посочва:

[...], ние не препоръчваме да използвате CHECKSUM, за да откриете дали стойностите са се променили, освен ако вашето приложение може да толерира от време на време липса на промяна. Помислете вместо това да използвате 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