SQL Server中的RAND函數的介紹和區間隨機數值函數的實現

標籤：

    工作中會遇到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 -- 作者: XXX11 -- 建立: yyyy-MM-dd12 -- 修改: yyyy-MM-dd XXX 修改內容描述13 --==================================14 CREATE VIEW dbo.vRandomGuid15     --$Encode$--16 AS17     SELECT RandomGuid = NEWID();18 GO19  20 調用該視圖的T-SQL代碼如下：21 SELECT TOP 1 RandomGuid22 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 -- 作者: XXX11 -- 建立: yyyy-MM-dd12 -- 修改: yyyy-MM-dd XXX 修改內容描述13 -- 調用: SELECT dbo.ufn_RandNum(0, 1);14 --==================================15 CREATE FUNCTION dbo.ufn_RandNum16 (17     @intMin INT,            -- 隨機數值的最小值18     @intMax INT                -- 隨機數值的最大值19 ) RETURNS INT20     --$Encode$--21 AS22 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 = RandomGuid29     FROM dbo.vRandomGuid;30  31     RETURN ABS(CHECKSUM(@guidValue)) % (@intMax - @intMin + 1) + @intMin;32 END33 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 -- 作者: XXX11 -- 建立: yyyy-MM-dd12 -- 修改: 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 DATETIME20     --$Encode$--21 AS22 BEGIN23     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換為DAYS31     RETURN DATEADD(HOUR, (ABS(CHECKSUM(@guidVue)) % (1 + DATEDIFF(HOUR, @dtmMax, @dtmMin))), @dtmMin);32 END33 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 Num2 FROM dbo.SeqDataTable3 WHERE Num BETWEEN @intMin AND @intMax4 ORDER BY NEWID() ASC;

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

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

    注意：以上代碼無法執行成功的，也算是虛擬碼的。

      以後會針對數字序列有專門的詳細講解，這兒暫時作為一個擴充思考吧。  參考清單列表https://technet.microsoft.com/zh-cn/library/ms177563(v=sql.90).aspxhttps://technet.microsoft.com/zh-cn/library/ms177610(v=sql.90).aspxhttps://technet.microsoft.com/zh-cn/library/ms189788(v=sql.90).aspxhttps://technet.microsoft.com/zh-cn/library/ms190348(v=sql.90).aspxhttp://www.cnblogs.com/chenxizhang/archive/2009/06/26/1511898.html      博友如有其他更好的解決方案，也請不吝賜教，萬分感謝。

 

SQL Server中的RAND函數的介紹和區間隨機數值函數的實現