100日でSQLの達人になる@LeetCode! Day54 <MS SQL Serverで乱数>

乱数を発生させる

前回に引き続きLeetCodeの問題はお休み。
LeetCodeの問題を休んでいたのは、この乱数のテーマを取り扱いたかったから。
LeetCodeでは乱数の問題はデータベースの問題では無かったからです。
その準備に色々とかかってしまいました。

前回同様にdb<>fiddleを使って、テーブルにデータを格納するクエリを書いていきます。

SQLの乱数発生関数は貧弱で基本はRAND(SEED値)を使うのですが、
色々調べてみるとNEWID()も使えそうです。
RAND(SEED値)のSEED値は省略可能です。NEWID()にはSEED値を設定できません。

NEWID()はもともとユニークIDをランダムに作成するための関数です。

これが基本形。

SQL

SELECT RAND()

これで0以上1未満の値(float型)が入手できる。
しかし、下記実行結果を参考に色々と考えてみたい。

下記が実行例です。

CREATE TABLE tbl1 (id INT)

DECLARE @num INT =1

WHILE @num <= 10000
BEGIN
INSERT INTO tbl1 (id) VALUES(@num)
SET @num=@num+1
END
-- ここまでで合計10000行の行番号のみ入ったテーブルが出来ています。

SELECT id, 
RAND()                       AS rnd1,
RAND(id)                     AS rnd2,
FLOOR(RAND(id) * 10)         AS rnd3,
CHECKSUM(RAND(id))           AS rnd4,
ABS(CHECKSUM(RAND(id)) % 10) AS rnd5,
newid()                      AS rnd6,
ABS(CHECKSUM(newid()) % 10)  AS rnd7
INTO tbl2
FROM tbl1

SELECT TOP 10 * FROM tbl2

SELECT rnd3, count(rnd3) AS counts
FROM tbl2
GROUP BY rnd3
ORDER BY rnd3

SELECT rnd5, count(rnd5) AS counts
FROM tbl2
GROUP BY rnd5
ORDER BY rnd5

SELECT rnd7, count(rnd7) AS counts
FROM tbl2
GROUP BY rnd7
ORDER BY rnd7
GO

id |               rnd1 |              rnd2 | rnd3 |        rnd4 | rnd5 | rnd6                                 | rnd7
-: | -----------------: | ----------------: | ---: | ----------: | ---: | :----------------------------------- | ---:
 1 | 0.0991731233917515 | 0.713591993212924 |    7 |  -539493514 |    4 | 6ff1105c-b642-4ef4-8528-d0ea85ab4e58 |    7
 2 | 0.0991731233917515 | 0.713610626184182 |    7 |  -866675389 |    9 | 760726f5-8ccc-418c-b5ca-fbbc5d814c55 |    8
 3 | 0.0991731233917515 |  0.71362925915544 |    7 |   953648264 |    4 | 49a94b3a-221c-4110-a708-fe8bf421f93c |    7
 4 | 0.0991731233917515 | 0.713647892126698 |    7 |   626504279 |    9 | 4fd136fb-3e62-4bc0-9f8f-231b2acdc7c2 |    3
 5 | 0.0991731233917515 | 0.713666525097956 |    7 |   299343897 |    7 | c0776af5-179e-4671-8624-3f3fe6da1f0e |    1
 6 | 0.0991731233917515 | 0.713685158069215 |    7 |  2119655074 |    4 | 71af704f-8bab-418f-a79d-c508dfe700aa |    3
 7 | 0.0991731233917515 | 0.713703791040473 |    7 |  1792495446 |    6 | 5f21ae72-5077-4794-aeff-d568bbd12d0d |    6
 8 | 0.0991731233917515 | 0.713722424011731 |    7 |  1465351437 |    7 | db7fb2ab-f899-4f6a-ae0e-d77cdaf1f5e5 |    0
 9 | 0.0991731233917515 | 0.713741056982989 |    7 |  1138191179 |    9 | eda717cc-f59e-40c0-981d-d8c078034ccd |    1
10 | 0.0991731233917515 | 0.713759689954247 |    7 | -1336469372 |    2 | 77eb28e1-99d8-466b-b5c9-8c29cf235985 |    3

rnd3 | counts
---: | -----:
   7 |   4638
   8 |   5362

rnd5 | counts
---: | -----:
   0 |    987
   1 |   1005
   2 |   1059
   3 |   1027
   4 |    977
   5 |   1032
   6 |   1038
   7 |    964
   8 |    939
   9 |    972

rnd7 | counts
---: | -----:
   0 |    940
   1 |   1052
   2 |   1002
   3 |   1011
   4 |   1018
   5 |    987
   6 |   1007
   7 |    982
   8 |   1008
   9 |    993

db<>fiddle here
乱数を7種類作成しましたが、まずrnd1は駄目です。すべての行に同じ値が入ってしまっています。
それを回避したのがrnd2です。RAND(id)として行番号をSEEDとして各行ランダムな値を算出しています。
しかし、ほとんど0.713周囲で次のrnd3はrnd2を10倍した整数値部分を取ってきていますが、1万行算出しても7と8しか出てきておらず乱数の役目を果たしていないことが分かります。
そこでCHECKSUM関数を使ったのがrnd4です。チェックサムはINT型で返ってきます。今回のように1-9までの乱数を発生させたい場合がrnd5です。
集計を見ると0-9までほぼ均一に出ていることが分かります。
rnd6, rnd7はNEWID()を使った方法。こちらもバランス良く乱数として利用できそうです。
ただ、rnd6, rnd7はSEEDの指定ができないために実行するたびに結果が変わります。
プログラミングで毎回同じ結果を担保したい場合にはrnd4, rnd5を使う方が良さそうです。

今日のポイントは0-9の乱数を発生させたい場合には、ABS(CHECKSUM(RAND(id)) % 10)か、ABS(CHECKSUM(newid()) % 10)を使う、しかし乱数を毎回固定したい場合にはRAND(id)の方を利用するとした。