问题 SQL Server中的伪随机可重复排序（不是NEWID（）而不是RAND（））

我想以可重复的方式对结果进行随机排序，以实现分页等目的。对于这个NEWID（）太随机，因为无法重新获得相同的结果。兰德（种子）的排序将是理想的，因为相同的种子会产生相同的随机集合。不幸的是，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）

2097

2018-01-19 16:20

起源

答案:

建立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

编辑：注意，如果使用动态SQL，则在查询中使用的@seed声明可以替换为参数或使用常量int。（无需以TSQL方式声明@int）

2018-01-19 16:42

接受，这是唯一对我有用的方法 - ccook

我可以问你是否尝试过我的方法？它不需要任何额外的变量或存储过程。 - JosephStyons

我只是尝试过，不幸的是它并没有提供可靠性。上面的声明只是为了提供一个完整的环境来复制问题。临时表是具有代表性的。 - ccook

答案:

建立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

编辑：注意，如果使用动态SQL，则在查询中使用的@seed声明可以替换为参数或使用常量int。（无需以TSQL方式声明@int）

2018-01-19 16:42

接受，这是唯一对我有用的方法 - ccook

我可以问你是否尝试过我的方法？它不需要任何额外的变量或存储过程。 - JosephStyons

我只是尝试过，不幸的是它并没有提供可靠性。上面的声明只是为了提供一个完整的环境来复制问题。临时表是具有代表性的。 - ccook

您可以使用每行中的值来重新评估rand函数：

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

添加ID可确保每行重新连接rand。但对于种子值，您将始终返回相同的行序列（前提是表不会更改）

2018-01-19 16:28

谢谢JayArr。我确实试过这个，但不幸的是它最终被提升了。显然，第一个随机值是可以预测的。我也将用该说明更新问题。 - ccook

创建哈希比创建种子随机数要费时更多。

为了获得RAND（[种子]）输出的更多变化，你需要使[种子]显着变化。可能如......

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

使用常量可确保您要求的可复制性。但要小心（id * 9999）导致溢出的结果，如果你希望你的表变得足够大......

2018-01-19 18:45

我认为这与上面的问题相同，其中值是递增的。 - ccook

SELECT RAND（9999 * 1），RAND（9999 * 2），RAND（9999 * 3），RAND（9999 * 4），RAND（9999 * 5）0.899884439852407 0.0861955322535983 0.27250661186434 0.458817691475082 0.645128771085824不是增量的，但不是真的随机的...... - MatBailie

你正在运行这个数据库？ SQL2008？ - ccook

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

这种作品。虽然checksum（）的输出看起来并不是随机的。该 MSDN文档状态：

[...]，我们不建议使用CHECKSUM来检测值是否已更改，除非您的应用程序可以容忍偶尔错过更改。请考虑使用HashBytes。当指定MD5哈希算法时，HashBytes为两个不同输入返回相同结果的概率远低于CHECKSUM。

但可能会更快。

2018-01-30 15:36

有趣，怎么样？ - ccook

澄清了我的答案。但无论如何，这是你已经提出的解决方案。有点。 - dummy

做了一些阅读后，这是一种可接受的方法。

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

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

在表达式中使用id会导致每行重新评估它。但是将它乘以0可确保它不会影响兰特的结果。

多么可怕的做事方式！

2018-01-19 16:45

它能为您提供帮助吗？我得到一个恒定的r列。它还会产生额外的结果。也许它必须在一个sp？虽然疯了，哇。 - ccook

这在SQL Server 2005中似乎不适用于我。 - Jonas Lincoln

在我的测试中（在SQL Server 2008 R2上）， RAND() 只评估过一次。它在什么条件下评估每行不同的东西？ - binki

这对我来说效果很好，它可以应用于任何表（只需在ORDER BY子句上使用）：

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

2018-01-23 20:28

它随机排序，然而，它不会产生可重复的结果。与newid（）的顺序相同的行为？ - ccook

对不起，我没有正确地阅读这个问题。 - JosephStyons

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

2017-09-24 23:17

问题 SQL Server中的伪随机可重复排序（不是NEWID（）而不是RAND（））

答案:

答案:

热门问题