Mysql – how to recognize a bowling split in sql

I want to propose a solution that can be simply applied to determine whether the standing pins combination is Split.

There 1024 possible combinations of pins in bowling field.
Some of these combinations are called Split. According to the meaning of the term, it is clear that the remaining figures should be divided into 2 or more groups.However, there are some additional conditions, such as that Pin 1 must be hited. In the terminology used by the players, there are combinations that formally do not fit the condition, but are also called Split. Some of the most common, as well as the most difficult combinations to continue the game, have their own names.

Therefore, I suggest creating a table of possible combinations and determining in it whether this combination is Split. Having such a table, the solution to the problem is quite simple.

select r.*,isSplit
  ,pinsStand,vHit,vStand,SplitName,Id
from rounds r
left join Splits s on r.pinsHit=s.pinsHit
order by gameid,thrownum,frame;

For example

Such a table Splits can be created and filled like this:

1.Define pins and pins neighbourhood. This is the field where we will place the elements of the standPins set

create table pins(pin int);
insert into pins values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
create table PinLink (pin1 int,pin2 int);
insert into PinLink values
 (1,2),(1,3)
,(2,1),(2,3),(2,4),(2,5)
,(3,1),(3,2),(3,5),(3,6)
,(4,2),(4,5),(4,7),(4,8)
,(5,2),(5,3),(5,4),(5,6),(5,8),(5,9)
,(6,3),(6,5),(6,9),(6,10)
,(7,4),(7,8)
,(8,4),(8,5),(8,7),(8,9)
,(9,5),(9,6),(9,8),(9,10)
,(10,6),(10,9)
;

2.And check the complete connectivity of the set on this undirected graph.
Perhaps this is the most interesting part of the task.

create table Splits(id int,vHit int,pinsHit varchar(30),vStand int
  ,pinsStand varchar(30),standCount int,isSplit int,SplitName varchar(30));

 SET SESSION cte_max_recursion_depth = 1024;

insert into Splits (id,vHit,vStand,pinsHit,pinsStand,standCount,isSplit,SplitName) 
with recursive  r as(
  select 0 
  union all
  select v+1
  from r where v< 1023
)
select id,vHit,vStand
  ,case when standCount<10 then left(pinsHit,char_length(pinshit)-0) 
   else pinsHit
   end pinsHit
  ,case when standCount>0 then left(pinsStand,char_length(pinsStand)-0) 
   else pinsStand
   end pinsStand
  ,standCount
  ,0 isSplit
  ,'' SplitName
from(
select
  v id,v vHit, ~v&1023 vStand 
  ,concat_ws(','
   ,case when (v&1)>0 then '1' end 
  ,case when (v&2)>0 then '2' end  
  ,case when v&4>0 then '3' end  
  ,case when v&8>0 then '4' end  
  ,case when v&16>0 then '5' end  
  ,case when v&32>0 then '6' end  
  ,case when v&64>0 then '7' end  
  ,case when v&128>0 then '8' end  
  ,case when v&256>0 then '9' end  
  ,case when (v&512)>0 then '10' end  
  ) pinsHit
  ,concat_ws(','
   ,case when v&1=0 then '1' end 
   ,case when v&2=0 then '2'end  
   ,case when v&4=0 then '3'end  
   ,case when v&8=0 then '4' end  
   ,case when v&16=0 then '5' end  
   ,case when v&32=0 then '6' end  
   ,case when v&64=0 then '7' end  
   ,case when v&128=0 then '8' end  
   ,case when v&256=0 then '9' end  
   ,case when v&512=0 then '10' end  
   )  pinsStand
  ,10 - bit_count(v) standCount
from r
) tmp
;
-- some examples 
select * from Splits  
where vHit in(871,507,407,1017,671,1023,959,647,147,576)  
order by id;

3. Check completly conectivity.

We take the first (any) element of the set and traverse the tree with a recursive query. If as a result we go through all the elements of the set, then the set is completely connected on this graph and the combination is not Split. Otherwise, they are called disconnected – Split.

with recursive rG as(
select 0 lvl 
   ,left(pinsStand,locate(',',concat(pinsStand,','))-1) head
   ,cast(left(pinsStand,locate(',',concat(pinsStand,','))-1) as char) St1
  ,id,pinsHit,pinsStand,vStand
--  ,cast(left(pinsStand,locate(',',concat(pinsStand,','))-1) as signed) p1
  ,cast(left(pinsStand,locate(',',concat(pinsStand,','))-1) as signed) p2
  ,concat(left(pinsStand,locate(',',concat(pinsStand,','))-1),',') sPath
from Splits  where standCount>1
 union all
select lvl+1 lvl , head
  ,cast(pl.pin2 as char) St1
  ,id,pinsHit,pinsStand,vStand
  -- ,pl.pin1
  ,pl.pin2
  ,cast(concat(sPath,cast(pl.pin2 as char),',') as char) sPath
from rG  inner join PinLink pl on  pl.pin1= St1
  and locate(concat(pl.pin2,','),concat(pinsStand,','))>0
  and locate(concat(pl.pin2,','),spath)=0
  where lvl<11
)

update Splits 
join (
  select id,pinsHit,pinsStand
    ,group_concat(p2 order by p2 separator ',') gap
    ,case when pinsStand<>group_concat(p2 order by p2 separator ',') then 1
     else 0
     end fSplit
  from (
    select distinct head,id,pinsHit
       ,pinsStand
       ,vStand,p2 
    from rG 
    )rG
   group by id,pinsHit,pinsStand
)y on y.id=Splits.id
  set isSplit=case when standCount>1 then fsplit else 0 end
;

Table is ready. The table can be created 1 time and stored in the database.
Some examples

select * from Splits  
where vHit in(0,871,507,407,1017,671,1023,959,647,147,576)  
order by id;

Fiddle here

Remark 1. I was considering Split = disconnected

Update2. About vHit and vStand. I use numbers for convenient, concise designations of combinations.

We can denote any combination of 10 pins with number from 0 to 1023 or binary number with 10 bit.
The set of Pins {1,3,5,7} as number 0001010101 in binary notation, where 1 – standPin,0 – hitPin.
Another example

standPins      ({7,5,4,2})
   --bit number 987654321    
{2,4,5,7}     =0001011010 =hex 0x05A=0+2+0+8+16+0+64+0+0=90=vStand.  
And on the contrary, if hitPins 
{1,3,6,8,9,10)=1110100101 =hex 0x3A5=1+0+4+0+0+32+0+128+256+512=933=vHit.

All pins{1,2,3,4,5,6,7,8,9,10}=1111111111=0x3FF=1023
Empty{}=0000000000=0x000=0
``
hitPins=1023^standPins (XOR operation), hitPins+standPins=1023
Numbers from 0 to 1023 describe all possible combinations of 10 pins