SQL Server CESQL Server 2000DBASQL Server 2008ArchitectAdvancedSQL Server 2005IntermediateDevSQL ServerSQL
用 SQL 思考 - 生成随机数
4.80/5 (2投票s)
讨论了在 SQL Server 中创建随机数序列的方法。
简介
欢迎回来。我承认，我正在考虑去掉期号，因为每一篇文章都越来越少地按顺序排列，而更多地独立存在。我打算继续写 SQL 文章，并使用一个总标题来将它们分组，仍然创建这个系列的概念，以便学习者可以遵循一套信息，而不是仅仅阅读风格、质量和准确性各异的随机文章。
我还决定将这个系列重命名为“SQL 编程思维”，因为 SQL 确实需要一种不同的思维方式，而这正是我所有文章试图鼓励的。
数据库通常用于以结构化的格式存储数据，因此我们可以依靠它们每次都向我们提供相同的信息，当我们提出相同的请求时。然而，有时我们确实想在 SQL Server 中生成随机数，尤其是在我们尝试填充测试数据时。因此，今天的文章是关于在 SQL Server 中生成随机数是如何工作的。一如既往，它比你最初想的要有趣，并且有更多的要点需要考虑。
生成随机数
select rand()
这就是在 SQL 中生成随机数所需要做的全部。这会创建一个介于 0 和 1 之间的值，所以你可以通过乘以 xx 来创建一个高达 xx 的范围，如果你需要的话，你可以将其转换为 int 来使其成为一个整数。如果你只是在需要整数的地方使用它，那么转换也是隐式的。
生成随机数序列
现在运行此代码以获取一组五个随机数
select rand() union all
select rand() union all
select rand() union all
select rand() union all
select rand()
非常直接，对吧？但是，如果你想创建很多，它的扩展性并不好。这时就可以使用 tally table。下面是使用 tally table 生成 100 个随机数的 SQL 代码。
WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows
select rand() from e2
;
发生了什么？SQL Server 优化了我们的查询，并决定只调用一次 rand()。它没有意识到每次都会返回一个不同的值，因此假设它正在为我们提高效率。这通常是一件好事，但在这种情况下，这是一个问题。
我们将尝试抽象出随机数生成的第一个方法是使用视图。创建这个
CREATE VIEW vwRand
AS
SELECT RAND() as Rand
go
然后像这样运行它：
WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows
select rand() from e2, vwRand
;这是一个交叉连接，但因为 vwRand 中只有一个值，所以我们仍然得到了 100 个值。可悲的是，它们都是相同的。那么，在 SQL 中有什么方法可以知道不要被优化掉呢？答案是用户定义函数。CREATE FUNCTION RandNumber()
RETURNS float
AS
BEGIN
RETURN (SELECT rand() as RandNumber)
END
go 这是不允许的。Rand 被认为是一个“有副作用的函数”（也许它会递增一个序列，所有的“随机”序列实际上并非真正随机）。所以，我们需要分两步来完成，如下所示：-- Function to create random number by selecting from view
CREATE FUNCTION Random()
RETURNS float
AS
BEGIN
RETURN (SELECT Rand FROM vwRand)
END
go 所以，我们的视图仍在创建随机数，但一个函数导致视图为每一行被调用。就函数的一般效率而言，这是一个很好的教训，但就我们的情况而言，它是完美的。WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows
select dbo.random() from e2
;
这最终给了我们 100 个随机数。当然，它们都在 0 到 1 之间。只要我们完全包裹了它，最好能轻松指定我们的范围是多少。这里有一个函数可以做到这一点：CREATE FUNCTION RandomNumber(@max int)
RETURNS float
AS
BEGIN
RETURN (SELECT Rand * @max FROM vwRand)
END
go 现在你可以调用这个来获取指定最大值以下的随机值序列。WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows
select dbo.randomNumber(40) from e2
;
go如果我们想得到整数随机值，我们可以将其转换为 int，或者我们可以采取不同的方法。这段代码会取随机数，将小数点向右移动，然后使用 % 运算符（即模运算）来提取余数。因为这会返回一个 0 到 n-1 的范围，所以我们会在结果中加一。CREATE FUNCTION RandomInt(@max int)
RETURNS int
AS
BEGIN
RETURN (SELECT convert(int, Rand * 100000)% @max FROM vwRand) + 1
END
go 这里是调用它的代码。WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows
select dbo.randomInt(50) from e2
;
基准测试我们的结果
任何随机序列的重大问题是，它有多随机？我们将使用 SQL 来找出答案。我们的策略是创建一个小的数字范围，计算它们，看看分布是否大致均匀。如果我们要求一个介于 0 和 5 之间的值，并且它 60% 的时间都是 4，那就不能说明值的分布是合理的……WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
rand as(select dbo.randomInt(5) as r from e2)
select r, count(r) from rand group by r order by count(r) desc
;这将返回类似这样的结果：2 26 3 21 1 19 4 18 5 16这是一个合理的分布（如果都是 20，那也不随机）。
创建随机数的另一种方法
创建随机数的另一种方法是，从 GUID 开始。GUID 的定义就是随机的。以下代码将 GUID 转换为 6 字节的二进制值。然后我们使用模运算符来限制到我们想要的范围，可能的最大值超过 20 亿。这还将值转换为 int，但它可能是负数，所以我们使用 ABS 来去除负号，并再次加一以限制到我们的范围（如上）。SELECT ABS(CAST(NEWID() AS binary(6)) %10) + 1 下一个问题是，这会在内联代码中起作用吗？运行此代码，您将看到它确实起作用，NewId() 会为每一行调用。WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows
select ABS(CAST(NEWID() AS binary(6)) %10) + 1 from e2
;然而，它还是有点冗长，所以有必要将其包装在一个视图和一个函数中。我们需要一个函数才能传入我们想要的范围的最大值，否则我们只需要使用一个视图。然而，函数更有意义，因为我们只需要一个值。值得一提的是，使用函数会减慢你的 SQL 速度，所以你应该根据你的需求做出明智的选择。create VIEW vwRandFromId
AS
select abs(convert(int, CAST(NEWID() AS binary(6)))) as rand
go
CREATE FUNCTION RandomIntFromId(@max int)
RETURNS int
AS
BEGIN
RETURN (SELECT Rand% @max FROM vwRandFromId)
END
现在我们可以在需要的地方这样做。WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows
select dbo.RandomIntFromId(500) from e2
;
再次，让我们看看它到底有多随机。我们没有使用专为随机性设计的函数，所以值得研究一下。WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
rand as(select dbo.RandomIntFromId(5) as r from e2)
select r, count(r) from rand group by r order by count(r) desc
;
我的结果如下：0 28
1 22
3 17
4 17
2 16对我来说，这似乎是一个不错的分布，比内置函数好得多，如果我们愿意的话，可以用它来代替。