如何为 TSQL Select 中的每一行生成随机数? [英] How do I generate random number for each row in a TSQL Select?

问题描述

我的表格中的每一行都需要一个不同的随机数.以下看似显而易见的代码对每一行使用相同的随机值.

SELECT table_name, RAND() magic_numberFROM information_schema.tables

我想从中获得 INT 或 FLOAT.故事的其余部分是我将使用这个随机数从已知日期创建一个随机日期偏移量，例如从开始日期偏移 1-14 天.

这适用于 Microsoft SQL Server 2000.

解决方案

看一看 SQL Server - Set based random numbers 有非常详细的解释.

总而言之，以下代码生成了一个介于 0 和 13 之间且分布均匀的随机数:

ABS(CHECKSUM(NewId())) % 14

要更改范围，只需更改表达式末尾的数字即可.如果您需要一个包含正数和负数的范围，请格外小心.如果你做错了，可能会重复计算数字 0.

给房间里的数学狂的一个小警告:这段代码有一个非常轻微的偏差.CHECKSUM() 生成的数字在整个 sql Int 数据类型范围内都是一致的，或者至少与我的(编辑器)测试可以显示的一样接近.但是，当 CHECKSUM() 在该范围的最顶端产生一个数字时，会有一些偏差.任何时候您在最大可能整数和该最大整数之前的所需范围大小的最后一个精确倍数(在本例中为 14)之间得到一个数字时，这些结果将优先于范围的其余部分，而不能从最后一个 14 的倍数.

举个例子，假设 Int 类型的整个范围只有 19.19 是您可以容纳的最大可能整数.当 CHECKSUM() 结果为 14-19 时，这些对应于结果 0-5.与 6-13 相比，这些数字非常受到青睐，因为 CHECKSUM() 生成它们的可能性是其两倍.直观地演示这一点更容易.以下是我们的虚整数范围的整个可能结果集:

<前>校验和整数:0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19范围 结果:0 1 2 3 4 5 6 7 8 9 10 11 12 13 0 1 2 3 4 5

您可以在这里看到，产生某些数字的机会比其他数字更多:偏差.值得庆幸的是，Int 类型的实际范围要大得多……以至于在大多数情况下，偏差几乎无法检测到.但是，如果您发现自己为严重的安全代码执行此操作，则需要注意这一点.

I need a different random number for each row in my table. The following seemingly obvious code uses the same random value for each row.

SELECT table_name, RAND() magic_number 
FROM information_schema.tables 

I'd like to get an INT or a FLOAT out of this. The rest of the story is I'm going to use this random number to create a random date offset from a known date, e.g. 1-14 days offset from a start date.

This is for Microsoft SQL Server 2000.

解决方案

Take a look at SQL Server - Set based random numbers which has a very detailed explanation.

To summarize, the following code generates a random number between 0 and 13 inclusive with a uniform distribution:

ABS(CHECKSUM(NewId())) % 14

To change your range, just change the number at the end of the expression. Be extra careful if you need a range that includes both positive and negative numbers. If you do it wrong, it's possible to double-count the number 0.

A small warning for the math nuts in the room: there is a very slight bias in this code. CHECKSUM() results in numbers that are uniform across the entire range of the sql Int datatype, or at least as near so as my (the editor) testing can show. However, there will be some bias when CHECKSUM() produces a number at the very top end of that range. Any time you get a number between the maximum possible integer and the last exact multiple of the size of your desired range (14 in this case) before that maximum integer, those results are favored over the remaining portion of your range that cannot be produced from that last multiple of 14.

As an example, imagine the entire range of the Int type is only 19. 19 is the largest possible integer you can hold. When CHECKSUM() results in 14-19, these correspond to results 0-5. Those numbers would be heavily favored over 6-13, because CHECKSUM() is twice as likely to generate them. It's easier to demonstrate this visually. Below is the entire possible set of results for our imaginary integer range:

Checksum Integer: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Range Result:     0 1 2 3 4 5 6 7 8 9 10 11 12 13  0  1  2  3  4  5

You can see here that there are more chances to produce some numbers than others: bias. Thankfully, the actual range of the Int type is much larger... so much so that in most cases the bias is nearly undetectable. However, it is something to be aware of if you ever find yourself doing this for serious security code.

这篇关于如何为 TSQL Select 中的每一行生成随机数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！