Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Top Randomized Results with Multiple Qualifying Conditions Expand / Collapse
Author
Message
Posted Tuesday, September 24, 2013 1:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 26, 2014 1:33 PM
Points: 28, Visits: 101
I found this to be an interesting exercise for the day. I've been assigned the task of picking the "winners" in a lottery, which must be random, but the aggregate results need to meet multiple criteria.

It would be easy if there was just a "single" criteria .. you could just pick the top "n" lottery results until the criteria was met. But I need to consider multiple factors.

And yes I can easily do this with what is described as "RBAR" ...

The table:
CREATE TABLE #Ticket 
(TicketID NUMERIC(18,0) PRIMARY KEY,
sRace CHAR(1),
iAge NUMERIC(18,0),
sParty CHAR(1))

Some data (I have thousands of records for each lottery)
INSERT INTO #Ticket
SELECT '18986', 'B', '40', 'D' UNION ALL
SELECT '39189', 'W', '63', 'R' UNION ALL
SELECT '42223', 'B', '46', 'D' UNION ALL
SELECT '44106', 'W', '82', 'R' UNION ALL
SELECT '44365', 'B', '52', 'D' UNION ALL
SELECT '45726', 'W', '42', 'D' UNION ALL
SELECT '53323', 'W', '49', 'D' UNION ALL
SELECT '58770', 'W', '70', 'R' UNION ALL
SELECT '59624', 'B', '33', 'D' UNION ALL
SELECT '59788', 'B', '36', 'D' UNION ALL
SELECT '77297', 'W', '72', 'R' UNION ALL
SELECT '81772', 'W', '66', 'R' UNION ALL
SELECT '90966', 'W', '76', 'R' UNION ALL
SELECT '95401', 'W', '56', 'R' UNION ALL
SELECT '96760', 'B', '39', 'D' UNION ALL
SELECT '101073', 'W', '68', 'R' UNION ALL
SELECT '107167', 'B', '44', 'R' UNION ALL
SELECT '108750', 'W', '57', 'R' UNION ALL
SELECT '123544', 'A', '69', 'R' UNION ALL
SELECT '124403', 'A', '44', 'R' UNION ALL
SELECT '124937', 'W', '49', 'R' UNION ALL
SELECT '126040', 'A', '56', 'R' UNION ALL
SELECT '127882', 'A', '75', 'R' UNION ALL
SELECT '128238', 'W', '26', 'R' UNION ALL
SELECT '132748', 'W', '77', 'R' UNION ALL
SELECT '133906', 'W', '35', 'D' UNION ALL
SELECT '134248', 'B', '37', 'R' UNION ALL
SELECT '136046', 'H', '43', 'D' UNION ALL
SELECT '136253', 'W', '55', 'R' UNION ALL
SELECT '138220', 'W', '52', 'D' UNION ALL
SELECT '140297', 'B', '76', 'R' UNION ALL
SELECT '140457', 'W', '36', 'D' UNION ALL
SELECT '148863', 'B', '62', 'R' UNION ALL
SELECT '148943', 'W', '69', 'R' UNION ALL
SELECT '148959', 'W', '57', 'R' UNION ALL
SELECT '151948', 'B', '64', 'R' UNION ALL
SELECT '152141', 'B', '46', 'D' UNION ALL
SELECT '153106', 'W', '53', 'R' UNION ALL
SELECT '156206', 'W', '52', 'R' UNION ALL
SELECT '160553', 'W', '60', 'R' UNION ALL
SELECT '161406', 'B', '27', 'D' UNION ALL
SELECT '161663', 'B', '25', 'D' UNION ALL
SELECT '161987', 'W', '36', 'R' UNION ALL
SELECT '162127', 'W', '61', 'R' UNION ALL
SELECT '181421', 'B', '27', 'D' UNION ALL
SELECT '181818', 'B', '42', 'D' UNION ALL
SELECT '181954', 'W', '57', 'R' UNION ALL
SELECT '182168', 'W', '66', 'R' UNION ALL
SELECT '182292', 'I', '47', 'R' UNION ALL
SELECT '182379', 'W', '44', 'R'

So I can simply "lotterize" the winners with a CTE like this:
WITH lottery AS
(SELECT
TicketID,
sRace,
sParty,
iAge,
ABS(CHECKSUM(NEWID()))%100000+1 AS iRandom
FROM
#ticket
)

SELECT TOP 5 * FROM lottery ORDER BY iRandom

**BUT**, I need to be assured that at least 80% of the "winners" have sRace = "W" **AND** 80% of the winners have sParty = "R"

So considering this sample result set (it will of course vary each time you run the code)

TicketID sRace sParty iAge iRandom
181954 W R 57 282
124937 W R 49 5866
96760 B D 39 7212
133906 W D 35 9853
140457 W D 36 15684
59788 B D 36 19464
108750 W R 57 20181
101073 W R 68 20440
161663 B D 25 20612
123544 A R 69 21100

80%+ (or 4+) must be sRace = "W" and 80%+ (or 4+) must be sParty = "R"
Or logicially (and more importantly from a coding perspective) no more than 1 of each can be other than that.

So the winners would have to be
TicketID sRace sParty iAge iRandom
181954 W R 57 282
124937 W R 49 5866
96760 B D 39 7212
108750 W R 57 20181
101073 W R 68 20440
Post #1498031
Posted Tuesday, September 24, 2013 2:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 26, 2014 1:33 PM
Points: 28, Visits: 101
I get a different random number every time I reference the CTE ?!?!

SELECT l1.ticketID, l1.iRandom, l2.iRandom FROM lottery l1 INNER JOIN lottery l2 ON l1.TicketID = l2.TicketID 

Well, I guess I will need to use a temp table
Post #1498034
Posted Tuesday, September 24, 2013 8:24 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 3,420, Visits: 5,348
I'd probably resort to a set-based loop.

DECLARE @ROWS INT = 0;
WHILE @ROWS = 0
BEGIN
WITH RandomWinners AS
(
SELECT TOP 5 *
FROM Ticket
ORDER BY NEWID()
)
SELECT *
INTO #RandomWinners
FROM RandomWinners;

IF EXISTS
(
SELECT sRace, sParty
FROM #RandomWinners
GROUP BY sRace, sParty
HAVING COUNT(CASE sRace WHEN 'W' THEN 1 END) >= 4 AND
COUNT(CASE sParty WHEN 'R' THEN 1 END) >= 4
)
SELECT *
FROM #RandomWinners;

SELECT @ROWS = @@ROWCOUNT;
DROP TABLE #RandomWinners;
END


Note that I have completely ignored political correctness w.r.t. supporting a lottery that seems to favor white republicans, on the basis that help on this forum is color blind.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1498106
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse