How to get a Random (Ranged) Number based on a Per

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to get a Random (Ranged) Number based on a Per

Author

Topic

Maj0rrush
Starting Member

1 Post

Posted - 2012-12-05 : 12:15:14

Hello,

I have a table with 1 million records. For each of these records I want to generate a random number. This random number can be between 0 and 9 Each of these numbers, however, should get a chance to drop. For example, the 0 to have a 15% chance. The 1 is a 20% chance ...

How I would do it in T-SQL? I think of a scalar function. My approach:


DECLARE @RandomNumber float
DECLARE @RandomInteger int
DECLARE @MaxValue int
DECLARE @MinValue int
 
SET @MaxValue = 9
SET @MinValue = 0
 
SELECT @RandomNumber = RAND()
 
SELECT @RandomInteger = ((@MaxValue + 1) - @MinValue) * @RandomNumber + @MinValue
SELECT @RandomInteger

Now has the chance to be accommodated ...
But how .. ? :-(

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-05 : 13:08:51

One way (perhaps crude) is to have a table of discrete probabilities and then pick the value based on that as well. If I want to generate something between 0 and 5:

CREATE TABLE #tmp(Value INT, CumulativeProbability FLOAT);
INSERT INTO #tmp VALUES
(0,15.0),(1,20.0),(3,25),(4,26),(5,100);

SELECT MIN(VALUE) FROM #tmp WHERE CumulativeProbability >= 100*RAND()

DROP TABLE #tmp;

I am giving a 74% probability to 100 in this case, so most of the results would be (should be) 5.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources