SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to get a Random (Ranged) Number based on a Percentage Index


How to get a Random (Ranged) Number based on a Percentage Index

Author
Message
ghostsgrafix
ghostsgrafix
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 28
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 .. ? :-(
kl25
kl25
Say Hey Kid
Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)

Group: General Forum Members
Points: 688 Visits: 1875
I'm sure there's a more elegant way to do this but here's one possibility. It involves randomly selecting a number between 0 and 9 using 100 of those 10 numbers distributed according to your chance criteria. For this example, I used the chance percent values as follows:

No. - %
0 - 15
1 - 20
2 - 5
3 - 5
4 - 25
5 - 5
6 - 10
7 - 5
8 - 5
9 - 5

Then in the code, I used the chance percentages to determine how many of those numbers to include in the Substring that selects the random number. The query below will give you the distribution of each of those numbers out of a million records. If you calculate the percentages for each out of the total, you'll see they very closely match the percentages above.


--Build cteTally with 1M rows
With E1(N)
As
(
Select 1 Union All Select 1 Union All Select 1 Union All
Select 1 Union All Select 1 Union All Select 1 Union All
Select 1 Union All Select 1 Union All Select 1 Union All Select 1
),
E2(N)
As
(
Select 1
From
E1 a, E1 b
),
E4(N)
As
(
Select 1
From E2 a, E2 b
),
E6(N)
As
(
Select 1
From E4 a, E2 b
),
cteTally(N)
As
(
Select Row_Number() Over (Order By (Select Null)) From E6
),
--Do the select for the numbers from 0-9 based on the requested distribution
chance
As
(
Select
Substring('0000000000000001111111111111111111122222333334444444444444444444444444555556666666666777778888899999', Cast(Floor((Rand(Checksum(Newid()))*100) + 1) as tinyint),1) as test_group
From
cteTally
)

--Check the results
Select
test_group,
Count(test_group) as total
From
chance
Group by test_group
Order by test_group
;



mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1590 Visits: 3317
Hi

I've tried a slightly different approach. This will allow set of values with a chance value (weight) against to be used.

;WITH chances(RES, CHANCE) AS (
-- Range of values to generate and chance of being picked.
-- This is not a percentage but odds. eg 'You' has a 20 in 81 chance
SELECT RES, CHANCE
FROM (
VALUES
('Me', 2 ),
('You', 20),
('Them', 18),
('Others', 1 ),
('Everyone', 30),
('Nobody', 10)
) AS a(RES, CHANCE)
),
-- Create a table of ranges for the results
chancerange(RES,rLow,rHigh) AS (
SELECT a.RES, CAST(SUM(ISNULL(b.chance,0)) AS FLOAT) rLow, CAST(SUM(ISNULL(b.chance,0)) + a.Chance AS FLOAT) rHigh
FROM chances a
LEFT OUTER JOIN Chances b ON b.RES < a.RES
GROUP BY a.RES, a.Chance
),
-- Generate a random number for each record in the target table
randomNum AS (
SELECT RAND(Checksum(Newid())) * c.T SeedResult, a.*
FROM Tally a -- REPLACE WITH TABLE TO GENERATE NUMBERS FOR
CROSS APPLY (SELECT SUM(CHANCE) T FROM Chances) c
),
-- Build the results
randomResults AS (
SELECT (SELECT RES FROM chancerange WHERE SeedResult BETWEEN rLow AND rHigh) RandomResult, *
FROM randomNum
)
SELECT RandomResult, count(*) num, cast(count(*) / 10000.00 as decimal(4,2)) pct
FROM randomResults
GROUP BY RandomResult



I've used a Tally table in here to test it, but this can be replaced with any other table.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search