如何在 sql 查询中获得更好的随机化？

发布于 2024-11-07 05:54:11 字数 401 浏览 0 评论 0原文

我正在尝试获得从 0 到 359.9 的随机方位。

SET bearing = FLOOR((RAND() * 359.9));

我可以在同一个 while 循环中调用运行此请求的过程，紧接着下一个。不幸的是，随机化似乎一点也不独特。例如

结果

358.07
359.15
357.85

我了解随机化的工作原理，并且我知道由于我快速调用同一函数，用于生成随机数的刻度彼此非常接近。

在任何其他情况下，我会在调用之间等待几毫秒或重新初始化我的 Random 对象（例如在 C# 中），这会极大地改变我的随机性。然而，我不想在这种情况下等待。

如何在不等待的情况下增加随机性？

原文

I am attempting to get a random bearing, from 0 to 359.9.

SET bearing = FLOOR((RAND() * 359.9));

I may call the procedure that runs this request within the same while loop, immediately one after the next. Unfortunately, the randomization seems to be anything but unique. e.g.

Results

358.07
359.15
357.85

I understand how randomization works, and I know because of my quick calls to the same function, the ticks used to generate the random number are very close to one another.

In any other situation, I would wait a few milliseconds in between calls or reinit my Random object (such as in C#), which would greatly vary my randomness. However, I don't want to wait in this situation.

How can I increase randomness without waiting?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

居里长安 2024-11-14 05:54:11

我了解随机化的工作原理，并且我知道由于我对同一函数的快速调用，用于生成随机数的刻度彼此非常接近。

这不太正确。人们遇到麻烦的地方是，当他们用当前时间重复重新播种随机数生成器时，由于他们做得很快，所以时间是相同的，最终他们用相同的种子重新播种 RNG。这导致 RNG 每次重新播种时都会输出相同的数字序列。

重要的是，我所说的“相同”是指完全相同。 RNG 要么返回相同的序列，要么返回完全不同的序列。 “接近”的种子不会产生“相似”的序列。您要么得到相同的序列，要么得到完全不同的序列。

正确的解决方案不是错开重新播种的时间，而是实际上停止为 RNG 重新播种。您只需为 RNG 播种一次。

无论如何，那既不在这里也不在那里。 MySQL 的 RAND() 函数不需要显式播种。当您不带参数调用 RAND() 时，系统会为您处理播种，这意味着您可以重复调用它而不会出现问题。调用它的频率没有时间限制。

实际上你的 SQL 看起来很好。事实上，你的帖子中缺少一些东西。由于您正在调用 FLOOR()，因此您得到的结果应该始终是整数。您不可能从该作业中获得分数结果。您应该看到如下所示的积分结果：

这就是我通过重复运行 SELECT FLOOR(RAND() * 359.9) 得到的结果。

此外，无论如何，RAND() 永远不会返回 1.0。其范围为 0 ≤ RAND() < 1.0。使用 360 与 359.9 相比是安全的：

SET bearing = FLOOR(RAND() * 360);

I understand how randomization works, and I know because of my quick calls to the same function, the ticks used to generate the random number are very close to one another.

That's not quite right. Where folks get into trouble is when they re-seed a random number generator repeatedly with the current time, and because they do it very quickly the time is the same and they end up re-seeding the RNG with the same seed. This results in the RNG spitting out the same sequence of numbers each time it is re-seeded.

Importantly, by "the same" I mean exactly the same. An RNG is either going to return an identical sequence or a completely different one. A "close" seed won't result in a "similar" sequence. You will either get an identical sequence or a totally different one.

The correct solution to this is not to stagger your re-seeds, but actually to stop re-seeding the RNG. You only need to seed an RNG once.

Anyways, that is neither here nor there. MySQL's RAND() function does not require explicit seeding. When you call RAND() without arguments the seeding is taken care of for you meaning you can call it repeatedly without issue. There's no time-based limitation with how often you can call it.

Actually your SQL looks fine as is. There's something missing from your post, in fact. Since you're calling FLOOR() the result you get should always be an integer. There's no way you'll get a fractional result from that assignment. You should see integral results like this: