Anyone good with SQL

[umjewman]

I'm not an expert, but that looks spot on to me. I'll defer to Steve 2.0 to be sure though.

[Ken]

OK, switched from string concatenation to bitmasks.
Also switched my CTE to be forward only (rather than omni directional).
That made a TON of difference in performance. I think I have the final solution here.

Question: Your league has the standard (2C) 14 man offense roster. Your roster has Yan Gomes, Tom Murphy, Anthony Rizzo, Nick Madrigal, Trevor Story, Bobby Bradley, and Kris Bryant.
a) What is your most basic lineup
b) What players can you bid on

Answer:

SQL Code:

declare @Positions table (id int, pos varchar(5), twouposid bigint null)
insert into @positions (id, pos) select 1, 'C1'
insert into @positions (id, pos) select 2, 'C2'
insert into @positions (id, pos) select 3, '1B'
insert into @positions (id, pos) select 4, '2B'
insert into @positions (id, pos) select 5, '3B'
insert into @positions (id, pos) select 6, 'SS'
insert into @positions (id, pos) select 7, 'CI'
insert into @positions (id, pos) select 8, 'MI'
insert into @positions (id, pos) select 9, 'OF1'
insert into @positions (id, pos) select 10, 'OF2'
insert into @positions (id, pos) select 11, 'OF3'
insert into @positions (id, pos) select 12, 'OF4'
insert into @positions (id, pos) select 13, 'OF5'
insert into @positions (id, pos) select 14, 'UT'

update @positions set twouposid = power(2, id)

declare @playerpositions table (id int identity(1,1), playerid int, player varchar(max), pos varchar(max), posid int, twouplayerid bigint null, twouposid bigint null, twouID bigint null)
insert into @playerpositions (playerid, player, pos, posid) select 1, 'Yan Gomes', 'C1', 1
insert into @playerpositions (playerid, player, pos, posid) select 1, 'Yan Gomes', 'C2', 2
insert into @playerpositions (playerid, player, pos, posid) select 2, 'Yan Gomes', 'UT', 14
insert into @playerpositions (playerid, player, pos, posid) select 2, 'Tom Murphy', 'C1', 1
insert into @playerpositions (playerid, player, pos, posid) select 2, 'Tom Murphy', 'C2', 2
insert into @playerpositions (playerid, player, pos, posid) select 2, 'Tom Murphy', 'UT', 14
insert into @playerpositions (playerid, player, pos, posid) select 3, 'Anthony Rizzo', '1B', 3
insert into @playerpositions (playerid, player, pos, posid) select 3, 'Anthony Rizzo', 'CI', 7
insert into @playerpositions (playerid, player, pos, posid) select 3, 'Anthony Rizzo', 'UT', 14
insert into @playerpositions (playerid, player, pos, posid) select 4, 'Nick Madrigal', '2B', 4
insert into @playerpositions (playerid, player, pos, posid) select 4, 'Nick Madrigal', 'MI', 8
insert into @playerpositions (playerid, player, pos, posid) select 4, 'Nick Madrigal', 'UT', 14
insert into @playerpositions (playerid, player, pos, posid) select 5, 'Trevor Story', 'SS', 6
insert into @playerpositions (playerid, player, pos, posid) select 5, 'Trevor Story', 'MI', 8
insert into @playerpositions (playerid, player, pos, posid) select 5, 'Trevor Story', 'UT', 14
insert into @playerpositions (playerid, player, pos, posid) select 6, 'Bobby Bradley', 'UT', 14
insert into @playerpositions (playerid, player, pos, posid) select 7, 'Kris Bryant', '3B', 5
insert into @playerpositions (playerid, player, pos, posid) select 7, 'Kris Bryant', 'CI', 7
insert into @playerpositions (playerid, player, pos, posid) select 7, 'Kris Bryant', 'UT', 14

update @playerpositions set twouplayerid = power(2, playerid), twouposid = power(2, posid), twouID = power(2, id)

declare @count int
select @count = count(distinct player) from @playerpositions

select 'LINEUP'

;with lineupcombos (ids, lastplayerid, positions)
as (

select twouID as ids, p.playerid as lastplayerid, twouposid as positions
from @playerpositions p
where playerid = (select min(playerid) from @playerpositions)

union all

select
pc.ids + twouID as positions,
p.playerid,
pc.positions + twouposid as positions
from lineupcombos pc
inner join @playerpositions p
on (pc.positions & twouposid) = 0
and pc.lastplayerid = p.playerid - 1
)
select pp1.player, pp1.pos
from @playerpositions pp1
inner join (select top 1 * from lineupcombos where lastplayerid = @count order by positions asc) as l
on (l.ids & pp1.twouID) = pp1.twouID
order by pp1.posid

SELECT 'CAN BID ON'

;with playercombo (lastplayerid, positions)
as (

select p.playerid as lastplayerid, twouposid as positions from @playerpositions p
where playerid = (select min(playerid) from @playerpositions)

union all

select p.playerid,
pc.positions + twouposid as positions
from playercombo pc
inner join @playerpositions p
on (pc.positions & twouposid) = 0
and pc.lastplayerid = p.playerid - 1
)
select distinct pos.id, pos.pos
from @Positions pos
inner join playercombo pc
on pc.lastplayerid = @count
and (pc.positions & pos.twouposid) = 0
order by pos.id asc

Results:

LINEUP

Tom Murphy C1
Yan Gomes C2
Anthony Rizzo 1B
Nick Madrigal 2B
Kris Bryant 3B
Trevor Story SS
Bobby Bradley UT

CAN BID ON

3 1B
4 2B
5 3B
6 SS
7 CI
8 MI
9 OF1
10 OF2
11 OF3
12 OF4
13 OF5

Execution time < 100ms

[Redbirds Fan]

OK, switched from string concatenation to bitmasks.
Also switched my CTE to be forward only (rather than omni directional).
That made a TON of difference in performance. I think I have the final solution here.

Question: Your league has the standard (2C) 14 man offense roster. Your roster has Yan Gomes, Tom Murphy, Anthony Rizzo, Nick Madrigal, Trevor Story, Bobby Bradley, and Kris Bryant.
a) What is your most basic lineup
b) What players can you bid on

Answer:

SQL Code:


Results:









Execution time < 100ms

Hooray!

[Ken]

Hooray!

Heh, yeah we can discuss the implications of this tonight on the zoom call!

[Steve 2.0]

I'm not an expert, but that looks spot on to me. I'll defer to Steve 2.0 to be sure though.

I am not the geek you are looking for.

[Ken]

This failed once we got to 13 hitters (permutations of 13 players at x positions each - for example an outfielder had 7 positions - OF1, OF2, OF3, OF4, OF5, UT1, UT2). I say failed - it still operates, but it's so slow to return that it's not useful.

We were looking at permutations across each player and each possible position (including an order for positions). It made sense but it wasn't optimal. Instead, it's better to be agnostic to the order of the positions (i.e. C1 vs C2, or OF1, OF2, OF3, OF4, OF5). That reduces our permutation count which speeds us up.

Switching to a model where we just keep track of a COUNT scales much better than adding all those permutations.

Posting this mainly for posterity so when I come back later I can find the optimized path.

declare @Positions table (id int, pos varchar(5), multiplier bigint, poscount int)

insert into @positions (id, pos, poscount) select 1, 'C', 2
insert into @positions (id, pos, poscount) select 2, '1B', 1
insert into @positions (id, pos, poscount) select 3, '2B', 1
insert into @positions (id, pos, poscount) select 4, '3B', 1
insert into @positions (id, pos, poscount) select 5, 'SS', 1
insert into @positions (id, pos, poscount) select 6, 'MI', 1
insert into @positions (id, pos, poscount) select 7, 'CI', 1
insert into @positions (id, pos, poscount) select 8, 'OF', 5
insert into @positions (id, pos, poscount) select 9, 'UT', 1

update @positions set multiplier = power(10, id-1)

declare @playerpositions table (id int identity(1,1), playerid int, player varchar(max), pos varchar(max), posmult bigint, poscount int)
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 1, 'Yan Gomes', 'C', 1, 2
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 2, 'Yan Gomes', 'UT', 100000000, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 2, 'Tom Murphy', 'C', 1, 2
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 2, 'Tom Murphy', 'UT', 100000000, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 3, 'Anthony Rizzo', '1B', 10, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 3, 'Anthony Rizzo', 'CI', 1000000, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 3, 'Anthony Rizzo', 'UT', 100000000, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 4, 'Nick Madrigal', '2B', 100, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 4, 'Nick Madrigal', 'MI', 100000, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 4, 'Nick Madrigal', 'UT', 100000000, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 5, 'Trevor Story', 'SS', 10000, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 5, 'Trevor Story', 'MI', 100000, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 5, 'Trevor Story', 'UT', 100000000, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 6, 'Bobby Bradley', 'UT', 100000000, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 7, 'Kris Bryant', '3B', 1000, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 7, 'Kris Bryant', 'CI', 1000000, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 7, 'Kris Bryant', 'UT', 100000000, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 8, 'Mike Trout', 'UT', 100000000, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 8, 'Mike Trout', 'OF', 10000000, 5
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 9, 'Mookie Betts', 'UT', 100000000, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 9, 'Mookie Betts', 'OF', 10000000, 5

insert into @playerpositions (playerid, player, pos, posmult, poscount) select 10, 'Anthony Santander', 'UT', 100000000, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 10, 'Anthony Santander', 'OF', 10000000, 5
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 11, 'David Dahl', 'UT', 100000000, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 11, 'David Dahl', 'OF', 10000000, 5
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 12, 'Justin Upton', 'UT', 100000000, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 12, 'Justin Upton', 'OF', 10000000, 5
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 13, 'Brandon Lowe', '2B', 100, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 13, 'Brandon Lowe', 'MI', 100000, 1
insert into @playerpositions (playerid, player, pos, posmult, poscount) select 13, 'Brandon Lowe', 'UT', 100000000, 1


declare @count int
select @count = count(distinct player) from @playerpositions


;with playercombo (lastplayerid, positions)
as (

select p.playerid as lastplayerid, posmult as positions from @playerpositions p
where playerid = 1

union all

select p.playerid,
pc.positions + posmult as positions
from playercombo pc
inner join @playerpositions p
on pc.lastplayerid = p.playerid - 1
and (pc.positions / p.posmult % 10) < p.poscount
)
select distinct pos.id, pos.pos
from @Positions pos
inner join playercombo pc
on pc.lastplayerid = @count
and (pc.positions / pos.multiplier % 10) < pos.poscount
order by pos.id asc