Mastermind solution
07/08/23 09:59
| Everybody knows the Mastermind game, here is a solution in SQL supporting 4 or 5 pawns with 4, 5, 6, 7, 8, … colors. You supply the problem definition as a JSON with the guesses you have attempted and their results: size is the number of pawns (4,5), ncolors the number of colors (4 to 8 usually), the guesses is an array of your attempts, the ids of the pawns and "p" (for placed correctly) and "n" for present but not placed correctly pawns. |
with jsont as (
select
gsize, ncolors, blacks, whites, power(2,idx-1) as id, g1, g2, g3, g4, g5
from
json_table(
-- (4,6), (4,7), (4,8), (5,8)
q'~{
"size" : 4,
"ncolors" : 8,
"guesses" : [
{
"values" : [ 1,2,3,4 ],
"p" : 0, "n" : 2
},
{
"values" : [ 5,6,7,8 ],
"p" : 1, "n" : 1
},
{
"values" : [ 2,1,7,5 ],
"p" : 0, "n" : 2
},
{
"values" : [ 3,6,1,7 ],
"p" : 0, "n" : 0
}
]
}~',
'$'
columns(
gsize number(2,0) path '$.size',
ncolors number(2,0) path '$.ncolors',
nested path '$.guesses[*]'
columns (
idx for ordinality,
blacks number(2,0) path '$.p',
whites number(2,0) path '$.n',
g1 number(2,0) path '$.values[0]',
g2 number(2,0) path '$.values[1]',
g3 number(2,0) path '$.values[2]',
g4 number(2,0) path '$.values[3]',
g5 number(2,0) path '$.values[4]'
)
)
)
),
vals(v,id) as (
select level, power(2,level-1) from dual
connect by level <= (select distinct ncolors from jsont)
)
, permuts4(t1, t2, t3, t4) as (
select v1.v, v2.v, v3.v, v4.v
from vals v1, vals v2, vals v3, vals v4
where v1.id + v2.id + v3.id + v4.id =
bitor(bitor(bitor(v1.id, v2.id), v3.id), v4.id)
)
, permuts5(t1, t2, t3, t4, t5) as (
select v1.v, v2.v, v3.v, v4.v, v5.v
from vals v1, vals v2, vals v3, vals v4, vals v5
where v1.id + v2.id + v3.id + v4.id + v5.id =
bitor(bitor(bitor(bitor(v1.id, v2.id), v3.id), v4.id), v5.id)
)
, guesses4(id, g1,g2,g3,g4, blacks, whites) as (
select id, g1,g2,g3,g4, blacks, whites from jsont
where gsize = 4
)
, guesses5(id, g1,g2,g3,g4,g5, blacks, whites) as (
select id, g1,g2,g3,g4,g5, blacks, whites from jsont
where gsize = 5
)
, sol4(t1, t2, t3, t4, guesses) as (
select t1, t2, t3, t4, sum(id) as guesses from (
select * from (
select p.*, g.*,
case t1 when g1 then 1 else 0 end
+ case t2 when g2 then 1 else 0 end
+ case t3 when g3 then 1 else 0 end
+ case t4 when g4 then 1 else 0 end as calc_blacks,
case when t1 in (g2, g3, g4) then 1 else 0 end
+ case when t2 in (g1, g3, g4) then 1 else 0 end
+ case when t3 in (g1, g2, g4) then 1 else 0 end
+ case when t4 in (g1, g2, g3) then 1 else 0 end as calc_whites
from permuts4 p,
guesses4 g
)
where calc_blacks = blacks and calc_whites = whites
) d
group by t1, t2, t3, t4 having(sum(id) = (select sum(id) from guesses4))
)
, sol5(t1, t2, t3, t4, t5, guesses) as (
select t1, t2, t3, t4, t5, sum(id) as guesses from (
select * from (
select p.*, g.*,
case t1 when g1 then 1 else 0 end
+ case t2 when g2 then 1 else 0 end
+ case t3 when g3 then 1 else 0 end
+ case t4 when g4 then 1 else 0 end
+ case t5 when g5 then 1 else 0 end as calc_blacks,
case when t1 in (g2, g3, g4, g5) then 1 else 0 end
+ case when t2 in (g1, g3, g4, g5) then 1 else 0 end
+ case when t3 in (g1, g2, g4, g5) then 1 else 0 end
+ case when t4 in (g1, g2, g3, g5) then 1 else 0 end
+ case when t5 in (g1, g2, g3, g4) then 1 else 0 end
as calc_whites
from permuts5 p,
guesses5 g )
where calc_blacks = blacks and calc_whites = whites
) d
group by t1, t2, t3, t4, t5 having(sum(id) = (select sum(id) from guesses5))
)
select t1, t2, t3, t4, null as t5 from sol4 d
union all
select t1, t2, t3, t4, t5 from sol5 d
order by t1, t2, t3, t4, t5
;