[MSSQL]SQL Server中的RAND函数的介绍和区间随机数值函数的实现

187人阅读 评论(0) 收藏 举报
分类:
转自:http://www.cnblogs.com/dzy863/p/5063558.html
工作中会遇到SQL Server模拟数据生成以及数值列值(如整型、日期和时间数据类型)随机填充等等任务,这些任务中都要使用到随机数。鉴于此,本文将对SQL Server中随机数的使用简单做个总结 。
 
T-SQL 随机有关的三个函数
    
    RAND([seed] 此函数生成从0到1之间随机 float 值(详细说明查看https://technet.microsoft.com/zh-cn/library/ms177610(v=sql.90).aspx)。
 

    CHECKSUM ( * | expression [ ,...n ] ) 此函数生成按照表的某一行或一组表达式计算出来的int校验和值,CHECKSUM 用于生成哈希索引(详细说明查看https://technet.microsoft.com/zh-cn/library/ms189788(v=sql.90).aspx)。

    
    NEWID ( ) 此函数生成 uniqueidentifier 类型的唯一值(详细说明查看https://technet.microsoft.com/zh-cn/library/ms190348(v=sql.90).aspx)。
 
生成任意一个随机数值(如整数、日期和时间数据类型)
 
    如果获得任意一个随机整数值?函数RAND生成的结果是float数据类型的,显然很难满足要求,不过可以通过对函数RAND的结果继续加工(比如其结果乘以某一个值保留整数部分等等)得到要求。很显然,函数CHECKSUM生成的结果是int数据类型,很容易满足我们的结果,不过其参数如果固定(表的某一行值相同或一组表达式值相同),那么其结果也是相同的。函数NEWID可以保证结果的唯一,但是其结果是unigueidentifer数据类型的。
 
    从以上三个函数的结果值分析:函数RNAD和CHECKSUM的结果是能获得整数数值的。如果我们将函数NEWID的结果值作为函数CHECKSUM的参数,那么其每次生成的结果值都是不一样的int数据类型的数值。以下T-SQL代码如下:
SELECT CHECKSUM(NEWID()) AS CheckSumValue, CHECKSUM(NEWID()) AS CheckSumValue2;
GO
执行后的查询结果如下:
 
    从上面的查询结果看到组合生成的整数数值都是9位数的,平时工作中大多使用的随机整数值都是不太大的且都是自然数(0和正整数的集合),这就要求限制随机生成的整数数值。可以使用函数ABS对其结果进行处理得到任意一个自然数。这样,组合一起来的格式就是这样的:ABS(CHECKSUM(NEWID()))。为了便于使用便于使用我们通过将其封装到函数中,但是函数NEWID又不能在函数中使用,那我们就要考虑其他的方式:将函数NEWID封装在单列单行的视图中。其定义视图的T-SQL代码如下:
复制代码
 1 IF OBJECT_ID(N'dbo.vRandomGuid', 'V') IS NOT NULL
 2 BEGIN
 3     DROP VIEW dbo.vRandomGuid;
 4 END
 5 GO
 6  
 7 --==================================
 8 -- 功能: 随机Guid视图
 9 -- 说明: 具体实现阐述 
10 -- 作者: XXX
11 -- 创建: yyyy-MM-dd
12 -- 修改: yyyy-MM-dd XXX 修改内容描述
13 --==================================
14 CREATE VIEW dbo.vRandomGuid
15     --$Encode$--
16 AS
17     SELECT RandomGuid = NEWID();
18 GO
19  
20 调用该视图的T-SQL代码如下:
21 SELECT TOP 1 RandomGuid
22 FROM dbo.vRandomGuid;
23 GO
复制代码

 

执行后的查询结果如下:

        
生成整数区间内的任意一个随机整数
    
    上面的视图定义,我们继续讲解组合函数的进一步封装。我们先讲解如何限制随机生成的数值,取模运算可以实现生成在指定数值区间内的任意一个数值,例如:获取区间[3,5]内任意一个数字,我们设为区间的最小值为@intMin:3,最大值为@intMax,则该区间的间隔值为 @intMax - @intMin + 1: 5 - 3 + 1(3),那么针对任意的整数值设为为@intValue,然后对这个间隔值先进行取模运算在加上区间最小值,最后得到的值为: @intValue % (@intMax - @intMin + 1) + @intMin。如果@intValue为8,则结果值为5;如果@intValue为9,则结果值为3;如果@intValue为10,则结果值为4……
 
    针对以上的分析我们封装的T-SQL代码如下:
复制代码
 1  IF OBJECT_ID(N'dbo.ufn_RandNum', 'FN') IS NOT NULL
 2 BEGIN
 3     DROP FUNCTION dbo.ufn_RandNum;
 4 END
 5 GO
 6  
 7 --==================================
 8 -- 功能: 获取区间内的任意一个随机数值
 9 -- 说明: 具体实现阐述 
10 -- 作者: XXX
11 -- 创建: yyyy-MM-dd
12 -- 修改: yyyy-MM-dd XXX 修改内容描述
13 -- 调用: SELECT dbo.ufn_RandNum(0, 1);
14 --==================================
15 CREATE FUNCTION dbo.ufn_RandNum
16 (
17     @intMin INT,            -- 随机数值的最小值
18     @intMax INT                -- 随机数值的最大值
19 ) RETURNS INT
20     --$Encode$--
21 AS
22 BEGIN    
23     SET @intMin = ISNULL(@intMin, 0);
24     SET @intMax = ISNULL(@intMax, 0);
25  
26     DECLARE @guidValue AS UNIQUEIDENTIFIER;
27  
28     SELECT TOP 1 @guidValue = RandomGuid
29     FROM dbo.vRandomGuid;
30  
31     RETURN ABS(CHECKSUM(@guidValue)) % (@intMax - @intMin + 1) + @intMin;
32 END
33 GO
复制代码

 

调用以上函数的T-SQL代码如下:
1 SELECT dbo.ufn_RandNum(0, 1) AS RandNum, dbo.ufn_RandNum(10, 11) AS RandNum2;
2 GO

 

执行后的查询结果如下:
 
生成日期区间内的任意一个随机日期
 
    那么我们继续讲解 获取指定区间内的任意一个随机日期,不过我们要使用日期和时间的两个函数:DATEADD和DATEDIFF。使用DATEDIFF计算出日期区间的间隔值,同上的思路我们计算得到的一个随机整数值,然后通过DATEADD得到我们期望的一个随机日期。封装函数的T-SQL代码如下:
复制代码
 1 IF OBJECT_ID(N'dbo.ufn_RandDate', 'FN') IS NOT NULL
 2 BEGIN
 3     DROP FUNCTION dbo.ufn_RandDate;
 4 END
 5 GO
 6  
 7 --==================================
 8 -- 功能: 获取日期区间内的任意一个随机日期
 9 -- 说明: 具体实现阐述 
10 -- 作者: XXX
11 -- 创建: yyyy-MM-dd
12 -- 修改: yyyy-MM-dd XXX 修改内容描述
13 -- 调用: SELECT @dtmRand = dbo.ufn_RandDate('2007-02-01', '2007-03-01');
14 --==================================
15 CREATE FUNCTION dbo.ufn_RandDate 
16 (
17     @dtmMin DATETIME,    -- 随机日期的最小值
18     @dtmMax DATETIME    -- 随机日期的最大值
19 ) RETURNS DATETIME
20     --$Encode$--
21 AS
22 BEGIN
23     SET @dtmMin = ISNULL(@dtmMin, '2000-01-01');
24     SET @dtmMax = ISNULL(@dtmMax, '2000-01-01');
25     DECLARE @guidVue AS UNIQUEIDENTIFIER;
26  
27     SELECT TOP 1 @guidVue = RandomGuid 
28     FROM dbo.vRandomGuid;
29  
30     -- 可以将HOUR换为DAYS
31     RETURN DATEADD(HOUR, (ABS(CHECKSUM(@guidVue)) % (1 + DATEDIFF(HOUR, @dtmMax, @dtmMin))), @dtmMin);
32 END
33 GO
复制代码

 

调用以上函数的T-SQL代码如下:
1 SELECT dbo.ufn_RandDate('2015-12-01', '2015-12-21') AS RandDate, dbo.ufn_RandDate('2016-12-01', '2016-12-21') AS RandDate2;
2 GO

 

执行后的查询结果如下:

 
区间随机数获取的扩展思考
    
    无论是获取数值区间内的随机数值,还是日期区间内的随机日期,我们都使用了视图vRandomGuid。如果有一个连续的数值序列,我们通过查询这个数值序列,结合我们的区间数值和随机排序,也可以实现以上的效果。
    假设该数值序列为SeqDataTable(Num INT),数值区间[@intMin,@intMax],那么该数值区间任意一个随机数值的T-SQL代码如下:
1 SELECT TOP 1 Num
2 FROM dbo.SeqDataTable
3 WHERE Num BETWEEN @intMin AND @intMax
4 ORDER BY NEWID() ASC;
5 GO

 

    当然也可以利用以上的数字序列SeqDataTable(Num INT),日期区间[@dtmMin,@dtmMax],那个该数字区间任意一个随机日期的T-SQL代码如下:

 

1 SELECT TOP 1 DATEADD(DAY, Num, @dtmMin)
2 FROM dbo.SeqDataTable
3 WHERE Num BETWEEN 0 AND DATEDIFF(DAY, @dtmMin, @dtmMax)
4 ORDER BY NEWID() ASC;
5 GO

 注意:以上代码无法执行成功的,也算是伪代码的。 

    以后会针对数字序列有专门的详细讲解,这儿暂时作为一个扩展思考吧。
 
补充数字辅助表
  获取指定范围的任意一个数字的T-SQL代码如下:
复制代码
1 SELECT TOP 1 Num
2 FROM dbo.ufn_GetNums(@bintMin, @bintMax)
3 ORDER BY NEWID() ASC;
4 GO
5 
6 SELECT TOP 1 Num
7 FROM dbo.ufn_GetNums(11, 15)
8 ORDER BY NEWID() ASC;
9 GO
复制代码

获取指定范围的任意一个日期的T-SQL代码如下:

复制代码
1 SELECT TOP 1 DATEADD(DAY, Num, @dtmMin)
2 FROM dbo.ufn_GetNums(0, DATEDIFF(DAY, @dtmMax, @dtmMin))
3 ORDER BY NEWID() ASC;
4 GO
5 
6 SELECT TOP 1 DATEADD(DAY, Num, '2015-12-01')
7 FROM dbo.ufn_GetNums(0, DATEDIFF(DAY, '2015-12-01', '2015-12-05'))
8 ORDER BY NEWID() ASC;
9 GO
复制代码

以上代码中的表函数ufn_GetNums可以参看这篇博文SQL Server数字辅助表的实现

 
查看评论

SQL Server 随机数,随机区间,随机抽取数据rand(),floor(),ceiling(),round(),newid()函数等

在查询分析器中执行:select rand(),可以看到结果会是类似于这样的随机小数:0.36361513486289558,像这样的小数在实际应用中用得不多,一般要取随机数都会取随机整数。那就看下面...
  • jiangfei009003
  • jiangfei009003
  • 2016-09-30 09:09:43
  • 1292

SQL server 内置函数之随机函数newid()和rand()

从A表随机取2条记录,用SELECT TOP 10 * FROM ywle order by newid() order by 一般是根据某一字段排序,newid()的返回值 是uniqueiden...
  • qq_16769857
  • qq_16769857
  • 2016-07-07 12:35:35
  • 9031

SQL中随机数函数rand()

在SQL Server中,有个随机函数rand(),
  • u014053368
  • u014053368
  • 2014-04-18 00:50:11
  • 2450

Hive取随机数 rand()函数

取随机数函数: rand 语法: rand(),rand(int seed) 返回值: double 说明:返回一个0到1范围内的随机数。如果指定种子seed,则会等到一个稳定的随机数序列 举...
  • qiezikuaichuan
  • qiezikuaichuan
  • 2015-03-09 11:28:27
  • 13713

MySQL使用rand函数实现随机数[转]

SELECT FLOOR(7 + (RAND() * 6)); 在一个范围随机产生 一个数字------------------------------------------------------...
  • dainiao01
  • dainiao01
  • 2008-09-02 11:20:00
  • 77880

SQL函数中不能使用rand()函数又一解决办法

今天需要在函数中产生一个5~10之间的随机数,不假思索的写了如下代码: Create Function XX ( @Price decimal(18,2) ) RETURNS decimal(...
  • ETZRP
  • ETZRP
  • 2011-08-29 17:16:06
  • 7060

mysql rand()函数获取随机数

1、随机获取一条数据:ORDER BY RAND(); 2、获取随机数0-1范围:SELECT RAND(); 3、取整 1:SELECT CEIL(RAND()); 1:SE...
  • misshjh
  • misshjh
  • 2016-11-29 18:32:35
  • 5058

Sqlserver中插入100内的随机整数-rand、newId使用

Sqlserver中插入100内的随机整数---rand、newId()使用   update test set qty=cast( floor(rand()*100) as int);  插入...
  • wang122188
  • wang122188
  • 2012-12-02 20:52:49
  • 7216

随机函数rand()[c++]

  一、C++中不能使用random()函数     random函数不是ANSI C标准,不能在gcc,vc等编译器下编译通过。但在C语言中int random(num)可以这样使用,它返回的是0至...
  • woxueliuyun
  • woxueliuyun
  • 2008-02-29 11:28:00
  • 42318

SQL SERVER中对查询结果随机排序

译:SQL SERVER中对查询结果随机排序 Randomly Sorting Query Results 查询结果随机排序 Q. How can I randomly sort query resu...
  • julyclyde
  • julyclyde
  • 2002-05-04 17:43:00
  • 902
    个人资料
    专栏达人 持之以恒
    等级:
    访问量: 257万+
    积分: 3万+
    排名: 133
    博客专栏
    最新评论
    文章存档