query - Pseudo Random Repeatable Sort in SQL Server(no NEWID() y no RAND())

sql server limit pagination

Question

Me gustaría ordenar aleatoriamente un resultado de manera repetible para fines tales como la búsqueda. Para este NEWID () es demasiado aleatorio porque los mismos resultados no se pueden volver a obtener. El orden por Rand (semilla) sería ideal ya que con la misma semilla se obtendría la misma colección aleatoria. Desafortunadamente, el estado de Rand () se restablece con cada fila, ¿alguien tiene una solución?

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

Tenga en cuenta que probé Rand (ID) pero que acaba de ser ordenado. Aparentemente Rand (n) <Rand (n + 1)

Answer 1

Crear un hash puede consumir mucho más tiempo que crear un número aleatorio sembrado.

Para obtener más variación en la producción de RAND ([semilla]), necesita hacer que la [semilla] también varíe significativamente. Posiblemente tal como ...

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

El uso de una constante garantiza la replicabilidad que solicitó. Pero tenga cuidado con el resultado de (id * 9999) que causa un desbordamiento si espera que su tabla crezca lo suficiente ...

Answer 2

Después de leer, este es un método aceptado.

Select Rand(@seed) -- now rand is seeded
Select *, 0 * id + Rand() as r from temp order by r

Tener id en la expresión hace que se reevalúe en cada fila. Pero multiplicarlo por 0 asegura que no afecta el resultado del rand.

¡Qué forma tan horrible de hacer las cosas!

Answer 3

Puede usar un valor de cada fila para volver a evaluar la función rand:

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

al agregar la ID, se asegura que el rand se vuelva a sellar para cada fila. Pero para un valor de semilla siempre obtendrá la misma secuencia de filas (siempre que la tabla no cambie)

Answer 4

Esto me ha funcionado bien en el pasado, y puede aplicarse a cualquier tabla (simplemente atornillar en la cláusula ORDER BY):

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

Answer 5

Aprovechando la sugerencia de hash de gkrogers, esto funciona muy bien. ¿Alguna idea sobre el rendimiento?

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

EDITAR: Tenga en cuenta que la declaración de @seed como su uso en la consulta podría reemplazarse con un parámetro o con una constante int si se usa SQL dinámico. (la declaración de @int en una forma TSQL no es necesaria)

Answer 6

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

Este tipo de obras Aunque el resultado de checksum () no me parece tan aleatorio. La documentación de MSDN indica:

[...], no recomendamos usar CHECKSUM para detectar si los valores han cambiado, a menos que su aplicación pueda tolerar ocasionalmente la pérdida de un cambio. Considere usar HashBytes en su lugar. Cuando se especifica un algoritmo hash MD5, la probabilidad de que HashBytes devuelva el mismo resultado para dos entradas diferentes es mucho menor que la de CHECKSUM.

Pero puede ser más rápido.

Answer 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