Post date: Nov 19, 2014 1:15:35 AM
"Import a file containing the details of 1000 poker hands between 2 players. The cards are encoded in 2 character strings, space separated, first five for player 1, next five for player 2. If there are no ties, how many hands did player 1 win?"
I hate doing this with a big function, but I can't think of any way to do it in a simple query. There are too many varied rules. Is there a mathematical solution here? This isn't currently what I would call math, just assigning points to card combinations. Game programming.
CREATE FUNCTION dbo.Poker (
@Card1 char(2),
@Card2 char(2),
@Card3 char(2),
@Card4 char(2),
@Card5 char(2)
)
RETURNS NUMERIC(20,10)
AS
BEGIN
/* This function assigns giant point differentials to different poker card values, ranging from 0 to 10000000. Then small point bonuses
are added to cover points for the value of pairs, additional cards, etc. These are used to break ties, but will never be enough for
a weaker hand to beat a stronger hand. */
-- This table of value mappings exists because TJQKA are not integers. And TBH using the character datatype would force some ickiness.
DECLARE @Values TABLE (Card char(1) NOT NULL, Value int NOT NULL)
INSERT INTO @Values VALUES ('A',14), ('K',13), ('Q',12), ('J',11), ('T',10), ('9',9), ('8',8), ('7',7), ('6',6), ('5',5), ('4',4), ('3',3), ('2',2)
DECLARE @Hand TABLE (Card char(2) NOT NULL)
INSERT INTO @Hand VALUES (@Card1), (@Card2), (@Card3), (@Card4), (@Card5)
DECLARE @Three INT, @Pair1 numeric(20,10), @Pair2 numeric(20,10), @Other numeric(20,10), @Four numeric(20,10)
-- Royal and Straight Flushes have all one suit, five different values, with the smallest value difference possible between the maximum and minimum cards.
IF (SELECT COUNT(DISTINCT(RIGHT(Card,1))) FROM @Hand) = 1
BEGIN
SELECT @Other = MAX(Value)
FROM @Hand h
INNER JOIN @Values p
ON p.Card = LEFT(h.Card,1)
HAVING MAX(p.Value) - MIN(p.Value) = 4
AND COUNT(DISTINCT p.Value) = 5
IF @Other = 14 RETURN 10000000 -- Royal Flush
IF @Other IS NOT NULL RETURN 900000 + @Other -- Straight Flush
END -- Royal/straight flush
-- Capture matches into a nice table
-- Matches are easily found with a group by.
DECLARE @Matches TABLE (Value INT NOT NULL, Matches INT NOT NULL)
; WITH Matches AS
(
SELECT p.Value, COUNT(*) AS Matches
FROM @Hand h
INNER JOIN @Values p
ON p.Card = LEFT(h.Card,1)
GROUP BY p.Value
)
INSERT INTO @Matches
SELECT Value, Matches
FROM Matches
-- 4 of a kind
SELECT @Four = Value
FROM @Matches
WHERE Matches = 4
IF @Four IS NOT NULL
BEGIN
-- Grab the non-four of a kind card, to break ties
SELECT @Other = Value
FROM @Matches
WHERE Matches <> 4
RETURN 800000 + 1000 * @Four + @Other
END -- 4 of a kind
-- Capture data that will be used for three of a kind/pairs/full house
-- 3 of a kind
SELECT @Three = Value
FROM @Matches
WHERE Matches = 3
-- Biggest pair
SELECT TOP 1 @Pair1 = Value
FROM @Matches
WHERE Matches = 2
ORDER BY Value DESC
-- Another pair, only if the biggest pair was found
SELECT @Pair2 = Value
FROM @Matches
WHERE Matches = 2
AND Value <> @Pair1
-- Full House is three of a kind and the first pair
-- The three-of-a-kind value gets a 1000X modifier to make it count a lot, but not as much as the 100k difference between hands.
-- The value of the pair is just a minor tiebreaker.
IF @Three IS NOT NULL AND @Pair1 IS NOT NULL
BEGIN
RETURN 700000 + 1000 * @Three + @Pair1
END -- Full house
-- A flush is a hand with only one suit.
-- To get points for tie breaking flushes, the values are encoded in a string of 10 decimal places, from highest to lowest.
-- The row_number function tells us how many zeroes to pad on the left, which controls the position in the string.
IF (SELECT COUNT(DISTINCT(RIGHT(Card,1))) FROM @Hand) = 1
BEGIN
; WITH Ordered (Value) AS
(
SELECT CONVERT(numeric(20,10),'0.' + LEFT(REPLICATE('00',ROW_NUMBER() OVER (ORDER BY p.Value DESC) - 1) + RIGHT('00' + RTRIM(p.Value),2) + REPLICATE('00',4),10))
FROM @Hand h
INNER JOIN @Values p
ON p.Card = LEFT(h.Card,1)
)
SELECT @Other = SUM(Value)
FROM Ordered
RETURN 600000 + @Other
END -- Flush.
-- Straight is a hand with five distinct values and the minimal difference between highest and lowest.
SET @Other = NULL
SELECT @Other = MAX(Value)
FROM @Hand h
INNER JOIN @Values p
ON p.Card = LEFT(h.Card,1)
HAVING MAX(p.Value) - MIN(p.Value) = 4
AND COUNT(DISTINCT p.Value) = 5
IF @Other IS NOT NULL
BEGIN
RETURN 500000 + @Other
END
-- Three of a kind. We already checked for this when doing full house.
-- We must encode the extra 2 cards in another decimal string for breaking ties.
IF @Three IS NOT NULL
BEGIN
; WITH Ordered (Value) AS
(
SELECT CONVERT(numeric(20,10),'0.00' + LEFT(REPLICATE('00',ROW_NUMBER() OVER (ORDER BY p.Value DESC) - 1) + RIGHT('00' + RTRIM(p.Value),2) + REPLICATE('00',4),8))
FROM @Hand h
INNER JOIN @Values p
ON p.Card = LEFT(h.Card,1)
WHERE p.Value <> @Three
)
SELECT @Other = SUM(Value)
FROM Ordered
RETURN 400000 + @Three + @Other
END -- 3 of a kind
-- Two pair. We already checked for this when doing full house.
-- We have to give points to both pairs and the extra card.
IF @Pair1 IS NOT NULL AND @Pair2 IS NOT NULL
BEGIN
SELECT @Other = Value
FROM @Matches
WHERE Value <> @Pair1
AND Value <> @Pair2
RETURN 300000 + 5000 * @Pair1 + 100 * @Pair2 + @Other
END -- 2 pair
-- One pair
-- We have to generate a decimal string to store the other 3 cards.
IF @Pair1 IS NOT NULL
BEGIN
; WITH Ordered (Value) AS
(
SELECT CONVERT(numeric(20,10),'0.00' + LEFT(REPLICATE('00',ROW_NUMBER() OVER (ORDER BY p.Value DESC) - 1) + RIGHT('00' + RTRIM(p.Value),2) + REPLICATE('00',4),8))
FROM @Hand h
INNER JOIN @Values p
ON p.Card = LEFT(h.Card,1)
WHERE p.Value <> @Pair1
)
SELECT @Other = SUM(Value)
FROM Ordered
RETURN 200000 + @Pair1 + @Other
END -- Pair
-- High Cards. This is where I developed the decimal string formula, before copying it everywhere else.
; WITH Ordered (Value) AS
(
SELECT CONVERT(numeric(20,10),'0.' + LEFT(REPLICATE('00',ROW_NUMBER() OVER (ORDER BY p.Value DESC) - 1) + RIGHT('00' + RTRIM(p.Value),2) + REPLICATE('00',4),10))
FROM @Hand h
INNER JOIN @Values p
ON p.Card = LEFT(h.Card,1)
)
SELECT @Other = SUM(Value)
FROM Ordered
RETURN @Other
END
GO
-- Let's play some poker. This takes a lot of time, but less than an actual poker game.
CREATE TABLE #Hands (
P1_1 char(2), P1_2 char(2), P1_3 char(2), P1_4 char(2), P1_5 char(2),
P2_1 char(2), P2_2 char(2), P2_3 char(2), P2_4 char(2), P2_5 char(2)
)
BULK INSERT #Hands FROM 'D:\poker.txt' WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = ' ')
SELECT COUNT(*)
FROM #Hands
WHERE dbo.Poker(P1_1, P1_2, P1_3, P1_4, P1_5) > dbo.Poker(P2_1, P2_2, P2_3, P2_4, P2_5)
DROP TABLE #Hands
GO
DROP FUNCTION dbo.Poker
GO