Part One
比较简单的poker game，比较手牌大小
32T3KT55J5
按手牌count后降序组合起来，比如41、311、32等。
WITH cards(rn, hands, bid) AS (
SELECT row_number() over () as rn, split_part(line, ' ', 1) as hands, split_part(line, ' ', 2) :: integer as bid
FROM lance_input
),
score(rn, ord) AS (
SELECT rn, string_agg(cnt::text, '' order by cnt desc) as ord
FROM (
SELECT rn, card, count(*) as cnt
FROM (
SELECT rn, unnest(regexp_split_to_array(hands, '')) as card
FROM cards
) t
GROUP by rn, card
) s
GROUP BY rn
)
SELECT sum(bid * rank)
FROM (
SELECT x.rn, bid, row_number() over (order by ord, replace(replace(replace(replace(replace(hands, 'T', 'B'), 'J', 'C'), 'Q', 'D'), 'K', 'E'), 'A', 'F')) as rank
FROM cards x
JOIN score y
ON x.rn = y.rn
) t;
Part Two
第二部分变成了惯蛋游戏，J可以变成任意牌
T55J5->T5555
需要先求出每手牌最多的牌
max_card (rn, card, cnt) AS (
SELECT rn, card, cnt
FROM (
SELECT rn, card, cnt, row_number() over (partition by rn order by cnt desc) as rank
FROM (
SELECT rn, card, cnt
FROM all_card
WHERE card != 'J'
) t
)s
WHERE rank = 1
)
再使用J替换最多牌后，重新进行汇总
replace_card(rn, card, cnt) AS (
SELECT rn, card, sum(cnt)
FROM (
SELECT x.rn, case when x.card = 'J' AND y.card IS NOT NULL then y.card else x.card end as card, x.cnt
FROM all_card x
LEFT JOIN max_card y
ON x.rn = y.rn
) t
GROUP by rn, card
)
最终SQL如下：
WITH cards(rn, hands, bid) AS (
SELECT row_number() over () as rn, split_part(line, ' ', 1) as hands, split_part(line, ' ', 2) :: integer as bid
FROM lance_input
),
all_card(rn, card, cnt) AS (
SELECT rn, card, count(*) as cnt
FROM (
SELECT rn, unnest(regexp_split_to_array(hands, '')) as card
FROM cards
) t
GROUP by rn, card
),
max_card (rn, card, cnt) AS (
SELECT rn, card, cnt
FROM (
SELECT rn, card, cnt, row_number() over (partition by rn order by cnt desc) as rank
FROM (
SELECT rn, card, cnt
FROM all_card
WHERE card != 'J'
) t
)s
WHERE rank = 1
),
replace_card(rn, card, cnt) AS (
SELECT rn, card, sum(cnt)
FROM (
SELECT x.rn, case when x.card = 'J' AND y.card IS NOT NULL then y.card else x.card end as card, x.cnt
FROM all_card x
LEFT JOIN max_card y
ON x.rn = y.rn
) t
GROUP by rn, card
),
score(rn, ord) AS (
SELECT rn, string_agg(cnt::text, '' order by cnt desc) as ord
FROM replace_card s
GROUP BY rn
)
SELECT sum(bid * rank)
FROM (
SELECT x.rn, bid, row_number() over (order by ord, replace(replace(replace(replace(replace(hands, 'T', 'B'), 'J', 'C'), 'Q', 'D'), 'K', 'E'), 'A', 'F')) as rank
FROM cards x
JOIN score y
ON x.rn = y.rn
) t