我在嘗試使用RAND和CHOOSE函式在 T-sql 中生成隨機值時遇到了一個有趣的 SQL 服務器行為。
我的目標是嘗試使用 RAND() 作為 rng 回傳兩個給定值之一。很容易吧？
對于那些不知道的人，CHOOSE 函式接受一個索引號 (int) 以及一組值，并回傳指定索引處的值。很簡單。
第一次嘗試時，我的 SQL 如下所示：
select choose(ceiling((rand()*2)) ,'a','b')
令我驚訝的是，這個運算式回傳了三個值之一：null、'a' 或 'b'。由于我沒想到空值我開始挖掘。RAND() 函式回傳一個范圍從 0（包含）到 1（排除）的浮點數。由于我將其乘以 2，因此它應該回傳 0（包括）到 2（不包括）范圍內的任何值。因此，在使用 CEILING 函式后，最終值應為：0,1,2 之一。在意識到我通過'c'擴展了值串列以檢查是否可能回傳。我還檢查了CEILING的檔案頁面并了解到：
回傳值與 numeric_expression 具有相同的型別。
我假設 CEILING 函式回傳 int，但在這種情況下，這意味著該值在用于 CHOOSE 之前被隱式轉換為 int，這在檔案頁面上確實說明了：
如果提供的索引值具有 int 以外的數字資料型別，則該值將隱式轉換為整數。
以防萬一我添加了一個明確的演員表。我的 SQL 查詢現在看起來像這樣：
select choose(cast(ceiling((rand()*2)) as int) ,'a','b','c')
但是，結果集沒有改變。為了檢查哪些值導致了問題，我嘗試預先生成值并將其與 CHOOSE 結果一起選擇。它看起來像這樣：
declare @int int = cast(ceiling((rand()*2)) as int)
select @int,choose( @int,'a','b','c')
有趣的是，現在結果集變成了 (1,a), (2,b)，這是我最初的目標。在深入研究 CHOOSE docs 頁面并進行一些測驗后，我了解到在以下兩種情況之一中回傳“null”：
- 給定索引為空
- 給定索引超出范圍
在這種情況下，這意味著在 SELECT 陳述句中生成的索引值是 0 或大于 2/3（我假設這里不可能出現負數并且 CHOOSE 函式索引從 1 開始）。正如我之前所說的 0 應該是以下可能性之一：
ceiling((rand()*2))
，但出于某種原因，它永遠不會為 0（至少當我像這樣嘗試了 100 萬次以上時）
set nocount on
declare @test table(ceiling_rand int)
declare @counter int = 0
while @counter<1000000
begin
insert into @test
select ceiling((rand()*2))
set @counter=@counter 1
end
select distinct ceiling_rand from @test
Therefore I assume that the value generated in SELECT is greater than 2/3 or NULL. Why would it be like this only when generated in SELECT statement? Perhaps order of resolving CAST, CELING or RAND inside SELECT is different than it would seem? It's true I've only tried it a limited number of times, but at this point the chances of it being a statistical fluctuation are extremely small. Is it somehow a floating-point error? I truly am stumbled and looking forward to any explanation.
TL;DR: When generating a random number inside a SELECT statement result set of possible values is different then when it's generated before the SELECT statement.
Cheers, NFSU
EDIT: Formatting
uj5u.com熱心網友回復：
如果您查看執行計劃，您就可以看到發生了什么。
SET SHOWPLAN_TEXT ON
GO
SELECT (select choose(ceiling((rand()*2)) ,'a','b'))
退貨
|--Constant Scan(VALUES:((CASE WHEN CONVERT_IMPLICIT(int,ceiling(rand()*(2.0000000000000000e 000)),0)=(1) THEN 'a' ELSE CASE WHEN CONVERT_IMPLICIT(int,ceiling(rand()*(2.0000000000000000e 000)),0)=(2) THEN 'b' ELSE NULL END END)))
該CHOOSE擴展出
SELECT CASE
WHEN ceiling(( rand() * 2 )) = 1 THEN 'a'
ELSE
CASE
WHEN ceiling(( rand() * 2 )) = 2 THEN 'b'
ELSE NULL
END
END
并且rand()被參考了兩次。每次評估都可以回傳不同的結果。
下面的重寫也被擴展了，你也會遇到同樣的問題
SELECT CASE ceiling(( rand() * 2 ))
WHEN 1 THEN 'a'
WHEN 2 THEN 'b'
END
避免CASE為此及其任何變體。
一種方法是
SELECT JSON_VALUE ( '["a", "b"]' , CONCAT('$[', FLOOR(rand()*2) ,']') )
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/340081.html
標籤：sql-server tsql random rounding