工作中會(huì)遇到SQL Server模擬數(shù)據(jù)生成以及數(shù)值列值（如整型、日期和時(shí)間數(shù)據(jù)類型）隨機(jī)填充等等任務(wù)，這些任務(wù)中都要使用到隨機(jī)數(shù)。鑒于此，本文將對(duì)SQL Server中隨機(jī)數(shù)的使用簡單做個(gè)總結(jié) 。

T-SQL 隨機(jī)有關(guān)的三個(gè)函數(shù)

   RAND([seed] 此函數(shù)生成從0到1之間隨機(jī) float 值（詳細(xì)說明查看https://technet.microsoft.com/zh-cn/library/ms177610(v=sql.90).aspx）。

    CHECKSUM ( * | expression [ ,...n ] ) 此函數(shù)生成按照表的某一行或一組表達(dá)式計(jì)算出來的int校驗(yàn)和值，CHECKSUM 用于生成哈希索引（詳細(xì)說明查看https://technet.microsoft.com/zh-cn/library/ms189788(v=sql.90).aspx）。

    NEWID ( ) 此函數(shù)生成 uniqueidentifier 類型的唯一值（詳細(xì)說明查看https://technet.microsoft.com/zh-cn/library/ms190348(v=sql.90).aspx）。

生成任意一個(gè)隨機(jī)數(shù)值（如整數(shù)、日期和時(shí)間數(shù)據(jù)類型）

    如果獲得任意一個(gè)隨機(jī)整數(shù)值？函數(shù)RAND生成的結(jié)果是float數(shù)據(jù)類型的，顯然很難滿足要求，不過可以通過對(duì)函數(shù)RAND的結(jié)果繼續(xù)加工（比如其結(jié)果乘以某一個(gè)值保留整數(shù)部分等等）得到要求。很顯然，函數(shù)CHECKSUM生成的結(jié)果是int數(shù)據(jù)類型，很容易滿足我們的結(jié)果，不過其參數(shù)如果固定（表的某一行值相同或一組表達(dá)式值相同），那么其結(jié)果也是相同的。函數(shù)NEWID可以保證結(jié)果的唯一，但是其結(jié)果是unigueidentifer數(shù)據(jù)類型的。

    從以上三個(gè)函數(shù)的結(jié)果值分析：函數(shù)RNAD和CHECKSUM的結(jié)果是能獲得整數(shù)數(shù)值的。如果我們將函數(shù)NEWID的結(jié)果值作為函數(shù)CHECKSUM的參數(shù)，那么其每次生成的結(jié)果值都是不一樣的int數(shù)據(jù)類型的數(shù)值。以下T-SQL代碼如下：

SELECT CHECKSUM(NEWID()) AS CheckSumValue, CHECKSUM(NEWID()) AS CheckSumValue2;
GO

執(zhí)行后的查詢結(jié)果如下：

從上面的查詢結(jié)果看到組合生成的整數(shù)數(shù)值都是9位數(shù)的，平時(shí)工作中大多使用的隨機(jī)整數(shù)值都是不太大的且都是自然數(shù)（0和正整數(shù)的集合），這就要求限制隨機(jī)生成的整數(shù)數(shù)值。可以使用函數(shù)ABS對(duì)其結(jié)果進(jìn)行處理得到任意一個(gè)自然數(shù)。這樣，組合一起來的格式就是這樣的：ABS(CHECKSUM(NEWID()))。為了便于使用便于使用我們通過將其封裝到函數(shù)中，但是函數(shù)NEWID又不能在函數(shù)中使用，那我們就要考慮其他的方式：將函數(shù)NEWID封裝在單列單行的視圖中。其定義視圖的T-SQL代碼如下：

 IF OBJECT_ID(N'dbo.vRandomGuid', 'V') IS NOT NULL
 BEGIN
   DROP VIEW dbo.vRandomGuid;
 END
 GO
 --==================================
 -- 功能: 隨機(jī)Guid視圖
 -- 說明: 具體實(shí)現(xiàn)闡述 
 -- 作者: XXX
 -- 創(chuàng)建: yyyy-MM-dd
 -- 修改: yyyy-MM-dd XXX 修改內(nèi)容描述
 --==================================
 CREATE VIEW dbo.vRandomGuid
   --$Encode$--
 AS
   SELECT RandomGuid = NEWID();
 GO
 調(diào)用該視圖的T-SQL代碼如下：
 SELECT TOP RandomGuid
 FROM dbo.vRandomGuid;
 GO 

執(zhí)行后的查詢結(jié)果如下：

生成整數(shù)區(qū)間內(nèi)的任意一個(gè)隨機(jī)整數(shù)

上面的視圖定義，我們繼續(xù)講解組合函數(shù)的進(jìn)一步封裝。我們先講解如何限制隨機(jī)生成的數(shù)值，取模運(yùn)算可以實(shí)現(xiàn)生成在指定數(shù)值區(qū)間內(nèi)的任意一個(gè)數(shù)值，例如：獲取區(qū)間[3,5]內(nèi)任意一個(gè)數(shù)字，我們設(shè)為區(qū)間的最小值為@intMin：3，最大值為@intMax，則該區(qū)間的間隔值為 @intMax - @intMin + 1： 5 - 3 + 1(3)，那么針對(duì)任意的整數(shù)值設(shè)為為@intValue，然后對(duì)這個(gè)間隔值先進(jìn)行取模運(yùn)算在加上區(qū)間最小值，最后得到的值為： @intValue % (@intMax - @intMin + 1) + @intMin。如果@intValue為8，則結(jié)果值為5；如果@intValue為9，則結(jié)果值為3；如果@intValue為10，則結(jié)果值為4……

    針對(duì)以上的分析我們封裝的T-SQL代碼如下：

 IF OBJECT_ID(N'dbo.ufn_RandNum', 'FN') IS NOT NULL
 BEGIN
   DROP FUNCTION dbo.ufn_RandNum;
 END
 GO
 --==================================
 -- 功能: 獲取區(qū)間內(nèi)的任意一個(gè)隨機(jī)數(shù)值
 -- 說明: 具體實(shí)現(xiàn)闡述 
 -- 作者: XXX
 -- 創(chuàng)建: yyyy-MM-dd
 -- 修改: yyyy-MM-dd XXX 修改內(nèi)容描述
 -- 調(diào)用: SELECT dbo.ufn_RandNum(, );
 --==================================
 CREATE FUNCTION dbo.ufn_RandNum
 (
   @intMin INT,      -- 隨機(jī)數(shù)值的最小值
   @intMax INT        -- 隨機(jī)數(shù)值的最大值
 ) 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 

調(diào)用以上函數(shù)的T-SQL代碼如下：

 SELECT dbo.ufn_RandNum(, ) AS RandNum, dbo.ufn_RandNum(, ) AS RandNum;
 GO 

執(zhí)行后的查詢結(jié)果如下：

生成日期區(qū)間內(nèi)的任意一個(gè)隨機(jī)日期

    那么我們繼續(xù)講解 獲取指定區(qū)間內(nèi)的任意一個(gè)隨機(jī)日期，不過我們要使用日期和時(shí)間的兩個(gè)函數(shù)：DATEADD和DATEDIFF。使用DATEDIFF計(jì)算出日期區(qū)間的間隔值，同上的思路我們計(jì)算得到的一個(gè)隨機(jī)整數(shù)值，然后通過DATEADD得到我們期望的一個(gè)隨機(jī)日期。封裝函數(shù)的T-SQL代碼如下：

 IF OBJECT_ID(N'dbo.ufn_RandDate', 'FN') IS NOT NULL
 BEGIN
   DROP FUNCTION dbo.ufn_RandDate;
 END
 GO
 --==================================
 -- 功能: 獲取日期區(qū)間內(nèi)的任意一個(gè)隨機(jī)日期
 -- 說明: 具體實(shí)現(xiàn)闡述 
 -- 作者: XXX
 -- 創(chuàng)建: yyyy-MM-dd
 -- 修改: yyyy-MM-dd XXX 修改內(nèi)容描述
 -- 調(diào)用: SELECT @dtmRand = dbo.ufn_RandDate('--', '--');
 --==================================
 CREATE FUNCTION dbo.ufn_RandDate 
 (
   @dtmMin DATETIME,  -- 隨機(jī)日期的最小值
   @dtmMax DATETIME  -- 隨機(jī)日期的最大值
 ) 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 

調(diào)用以上函數(shù)的T-SQL代碼如下：

 SELECT dbo.ufn_RandDate('--', '--') AS RandDate, dbo.ufn_RandDate('--', '--') AS RandDate;
 GO 

執(zhí)行后的查詢結(jié)果如下：

 區(qū)間隨機(jī)數(shù)獲取的擴(kuò)展思考

    無論是獲取數(shù)值區(qū)間內(nèi)的隨機(jī)數(shù)值，還是日期區(qū)間內(nèi)的隨機(jī)日期，我們都使用了視圖vRandomGuid。如果有一個(gè)連續(xù)的數(shù)值序列，我們通過查詢這個(gè)數(shù)值序列，結(jié)合我們的區(qū)間數(shù)值和隨機(jī)排序，也可以實(shí)現(xiàn)以上的效果。

    假設(shè)該數(shù)值序列為SeqDataTable(Num INT)，數(shù)值區(qū)間[@intMin,@intMax]，那么該數(shù)值區(qū)間任意一個(gè)隨機(jī)數(shù)值的T-SQL代碼如下：

 SELECT TOP Num
 FROM dbo.SeqDataTable
 WHERE Num BETWEEN @intMin AND @intMax
 ORDER BY NEWID() ASC; 

    當(dāng)然也可以利用以上的數(shù)字序列SeqDataTable(Num INT)，日期區(qū)間[@dtmMin,@dtmMax]，那個(gè)該數(shù)字區(qū)間任意一個(gè)隨機(jī)日期的T-SQL代碼如下：

 SELECT TOP DATEADD(DAY, Num, @dtmMin)
 FROM dbo.SeqDataTable
 WHERE Num BETWEEN AND DATEDIFF(DAY, @dtmMax, @dtmMin)
 ORDER BY NEWID() ASC; 

 注意：以上代碼無法執(zhí)行成功的，也算是偽代碼的。

最新評(píng)論