程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL Server中的RAND函數的介紹和區間隨機數值函數的實現

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

編輯:關於SqlServer

工作中會遇到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代碼如下:

 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代碼如下:

 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代碼如下:

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

執行後的查詢結果如下:

 

生成日期區間內的任意一個隨機日期

    那麼我們繼續講解 獲取指定區間內的任意一個隨機日期,不過我們要使用日期和時間的兩個函數:DATEADD和DATEDIFF。使用DATEDIFF計算出日期區間的間隔值,同上的思路我們計算得到的一個隨機整數值,然後通過DATEADD得到我們期望的一個隨機日期。封裝函數的T-SQL代碼如下:

 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代碼如下:

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

執行後的查詢結果如下:

 

 區間隨機數獲取的擴展思考

    無論是獲取數值區間內的隨機數值,還是日期區間內的隨機日期,我們都使用了視圖vRandomGuid。如果有一個連續的數值序列,我們通過查詢這個數值序列,結合我們的區間數值和隨機排序,也可以實現以上的效果。

    假設該數值序列為SeqDataTable(Num INT),數值區間[@intMin,@intMax],那麼該數值區間任意一個隨機數值的T-SQL代碼如下:

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

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

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

 注意:以上代碼無法執行成功的,也算是偽代碼的。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved