Weighted Random Numbers - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Weighted Random Numbers

Author

Topic

Leddo
Starting Member

6 Posts

Posted - 2007-04-11 : 00:11:32

Hi there... I've got an interesting one, that I can't seem to get my head around. Maybe some legend out there might be able to give me a hand...

I'm looking for a way to produce a weighted set of random numbers. I'm doing some work for a client at the moment, and they want to issue 3 random "reward cards" to their members at certain times. These are a bit like discount vouchers etc. The problem is some cards have need to have a higher frequency than the others. I guess a similar problem to baseball cards, you buy a pack of cards, you get mostly common cards, but every now and then, you get a rare card.

Here is the table setup:

CREATE TABLE [dbo].[Cards](
	[CardID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Cards_CardID]  DEFAULT (newid()),
	[CardName] [nvarchar](50) NOT NULL,
	[InsertRatio] [float] NULL,
 CONSTRAINT [PK_Cards] PRIMARY KEY CLUSTERED 
(
	[CardID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 1', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 2', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 3', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 4', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 5', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 6', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 7', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 8', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 9', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Rare 1', 0.02) /* 1:50 ratio */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Rare 2', 0.02) /* 1:50 ratio */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Rare 3', 0.02) /* 1:50 ratio */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Very Rare 1', 0.005) /* 1:200 ratio */

So what I need to do, is have a Stored Proc that I can execute and it returns back 3 random rows. Now in that single run, a card can't be duplicated.

Notice the Insert Ratio column? This has the ratio of the probability, eg a 1:50 insert ratio is equal to 0.02. For the common cards, a NULL value indicates it is a common.

Eventually, this table would have about 1000 rows in it, and about 200 of those would have various ratios (eg 1:50, 1:200, 1:1000, 1:8000 etc)

Any ideas or comments?

Thanks in advance.

Leddo
Brisbane, Australia

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-11 : 00:59:16

You can use the F_RANDOM_SAMPLE function on this link to decide if a rare card should be selected.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69499

You would give the function a sample rate corresponding to your Insert Ratio column. An Insert Ratio of .02 (1 out of 50) would match a sample rate of 20,000 out of 1,000,000 (0.02 * 1,000,000) for function F_RANDOM_SAMPLE. If the function returns a 1, it is selected.

After you select from the rare cards, you can fill out the cards with a random selection from the common cards.

This code should work with your table structure.


select
	top 3
	[CardName]
from
	(
	select
		-- Rare cards
		Type = 1,
		[CardName]
	from
		[dbo].[Cards]
	where
		[InsertRatio] is not null and
		-- Decide if rare card should be selected
		[dbo].[F_RANDOM_SAMPLE](convert(int,[InsertRatio]*1000000.00),newid()) = 1
	union all
	select
		-- Common Cards
		Type = 2,
		[CardName]
	from
		[dbo].[Cards]
	where
		[InsertRatio] is null
	) a
order by
	-- Select rare first (if any), then common
	Type,
	-- Select in random order
	newid()


Results:

CardName 
-------------------------------------------------- 
Rare 3
Common 2
Common 9

(3 row(s) affected)

CODO ERGO SUM

Leddo
Starting Member

6 Posts

Posted - 2007-04-11 : 05:06:30

Awesome stuff... Seemed so easy after I saw your response.

Thanks again.

Leddo

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-11 : 10:51:34

That's the hard part, making it look easy.

CODO ERGO SUM

Leddo
Starting Member

6 Posts

Posted - 2007-04-11 : 15:53:28

Out of Interest, if I wanted to make it so I could pass in the number of rows to return back, it doesn't seem to allow me to replace TOP 5, with TOP @ReturnCount.

Leddo

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 16:14:32

Not SQL Server 2000.
Only in SQL Server 2005. And you have to put "(" and ")" around the @ReturnCount variable.

Peter Larsson
Helsingborg, Sweden

Leddo
Starting Member

6 Posts

Posted - 2007-04-11 : 16:32:54

Far out, how simple... Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources