September 24, 2013 at 1:58 pm
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)
TicketIDsRacesPartyiAgeiRandom
181954WR57282
124937WR495866
96760BD397212
133906WD359853
140457WD3615684
59788BD3619464
108750WR5720181
101073WR6820440
161663BD2520612
123544AR6921100
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
TicketIDsRacesPartyiAgeiRandom
181954WR57282
124937WR495866
96760BD397212
108750WR5720181
101073WR6820440
September 24, 2013 at 2:10 pm
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
September 24, 2013 at 8:24 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply