工作中会遇到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代码如下:
1
2
|
SELECT CHECKSUM(NEWID()) AS CheckSumValue, CHECKSUM(NEWID()) AS CheckSumValue2;
GO
|
执行后的查询结果如下:
从上面的查询结果看到组合生成的整数数值都是9位数的,平时工作中大多使用的随机整数值都是不太大的且都是自然数(0和正整数的集合),这就要求限制随机生成的整数数值。可以使用函数ABS对其结果进行处理得到任意一个自然数。这样,组合一起来的格式就是这样的:ABS(CHECKSUM(NEWID()))。为了便于使用便于使用我们通过将其封装到函数中,但是函数NEWID又不能在函数中使用,那我们就要考虑其他的方式:将函数NEWID封装在单列单行的视图中。其定义视图的T-SQL代码如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
IF OBJECT_ID(N 'dbo.vRandomGuid' , 'V' ) IS NOT NULL
BEGIN
DROP VIEW dbo.vRandomGuid;
END
GO
--==================================
-- 功能: 随机Guid视图
-- 说明: 具体实现阐述
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
--==================================
CREATE VIEW dbo.vRandomGuid
--$Encode$--
AS
SELECT RandomGuid = NEWID();
GO
调用该视图的T-SQL代码如下:
SELECT TOP RandomGuid
FROM dbo.vRandomGuid;
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
IF OBJECT_ID(N 'dbo.ufn_RandNum' , 'FN' ) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_RandNum;
END
GO
--==================================
-- 功能: 获取区间内的任意一个随机数值
-- 说明: 具体实现阐述
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SELECT dbo.ufn_RandNum(, );
--==================================
CREATE FUNCTION dbo.ufn_RandNum
(
@intMin INT , -- 随机数值的最小值
@intMax INT -- 随机数值的最大值
) RETURNS INT
--$Encode$--
AS
BEGIN
SET @intMin = ISNULL (@intMin, );
SET @intMax = ISNULL (@intMax, );
DECLARE @guidValue AS UNIQUEIDENTIFIER;
SELECT TOP @guidValue = RandomGuid
FROM dbo.vRandomGuid;
RETURN ABS (CHECKSUM(@guidValue)) % (@intMax - @intMin + ) + @intMin;
END
GO
|
调用以上函数的T-SQL代码如下:
1
2
|
SELECT dbo.ufn_RandNum(, ) AS RandNum, dbo.ufn_RandNum(, ) AS RandNum;
GO
|
执行后的查询结果如下:
生成日期区间内的任意一个随机日期
那么我们继续讲解 获取指定区间内的任意一个随机日期,不过我们要使用日期和时间的两个函数:DATEADD和DATEDIFF。使用DATEDIFF计算出日期区间的间隔值,同上的思路我们计算得到的一个随机整数值,然后通过DATEADD得到我们期望的一个随机日期。封装函数的T-SQL代码如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
IF OBJECT_ID(N 'dbo.ufn_RandDate' , 'FN' ) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_RandDate;
END
GO
--==================================
-- 功能: 获取日期区间内的任意一个随机日期
-- 说明: 具体实现阐述
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SELECT @dtmRand = dbo.ufn_RandDate('--', '--');
--==================================
CREATE FUNCTION dbo.ufn_RandDate
(
@dtmMin DATETIME, -- 随机日期的最小值
@dtmMax DATETIME -- 随机日期的最大值
) RETURNS DATETIME
--$Encode$--
AS
BEGIN
SET @dtmMin = ISNULL (@dtmMin, '--' );
SET @dtmMax = ISNULL (@dtmMax, '--' );
DECLARE @guidVue AS UNIQUEIDENTIFIER;
SELECT TOP @guidVue = RandomGuid
FROM dbo.vRandomGuid;
-- 可以将HOUR换为DAYS
RETURN DATEADD( HOUR , ( ABS (CHECKSUM(@guidVue)) % ( + DATEDIFF( HOUR , @dtmMax, @dtmMin))), @dtmMin);
END
GO
|
调用以上函数的T-SQL代码如下:
1
2
|
SELECT dbo.ufn_RandDate( '--' , '--' ) AS RandDate, dbo.ufn_RandDate( '--' , '--' ) AS RandDate;
GO
|
执行后的查询结果如下:
区间随机数获取的扩展思考
无论是获取数值区间内的随机数值,还是日期区间内的随机日期,我们都使用了视图vRandomGuid。如果有一个连续的数值序列,我们通过查询这个数值序列,结合我们的区间数值和随机排序,也可以实现以上的效果。
假设该数值序列为SeqDataTable(Num INT),数值区间[@intMin,@intMax],那么该数值区间任意一个随机数值的T-SQL代码如下:
1
2
3
4
|
SELECT TOP Num
FROM dbo.SeqDataTable
WHERE Num BETWEEN @intMin AND @intMax
ORDER BY NEWID() ASC ;
|
当然也可以利用以上的数字序列SeqDataTable(Num INT),日期区间[@dtmMin,@dtmMax],那个该数字区间任意一个随机日期的T-SQL代码如下:
1
2
3
4
|
SELECT TOP DATEADD( DAY , Num, @dtmMin)
FROM dbo.SeqDataTable
WHERE Num BETWEEN AND DATEDIFF( DAY , @dtmMax, @dtmMin)
ORDER BY NEWID() ASC ;
|
注意:以上代码无法执行成功的,也算是伪代码的。