Hi,
How can we model how sports teams draft new players, such that teams pick players one at a time, and once a team has picked a player, that player is unavailable to other teams?
Say we have these teams:
CREATE TABLE team (team_name) AS -- team name is unique
SELECT 'Atletico' FROM dual UNION ALL
SELECT 'Burnley' FROM dual UNION ALL
SELECT 'Crystal' FROM dual UNION ALL
SELECT 'Dortmund' FROM dual UNION ALL
SELECT 'Everton' FROM dual;
and they can choose from these players:
CREATE TABLE player (player_name) AS -- player_name is unique
SELECT 'Mane' FROM dual UNION ALL
SELECT 'Neymar' FROM dual UNION ALL
SELECT 'Ronaldo' FROM dual UNION ALL
SELECT 'Silva' FROM dual UNION ALL
SELECT 'Weah' FROM dual UNION ALL
SELECT 'Xavi' FROM dual UNION ALL
SELECT 'Yorke' FROM dual UNION ALL
SELECT 'Zidane' FROM dual;
The teams have rated various players as shown in the choice table; a higher rating means more desirable.
CREATE TABLE choice (team_name, player_name, rating) AS -- combination of
SELECT 'Atletico', 'Mane', 90 FROM dual UNION ALL -- (team_name, player_name)
SELECT 'Atletico', 'Neymar', 80 FROM dual UNION ALL -- is unique; so is
SELECT 'Atletico', 'Ronaldo', 60 FROM dual UNION ALL -- (team_name, rating)
SELECT 'Burnley', 'Silva', 91 FROM dual UNION ALL
SELECT 'Burnley', 'Weah', 71 FROM dual UNION ALL
SELECT 'Burnley', 'Zidane', 51 FROM dual UNION ALL
SELECT 'Crystal', 'Silva', 82 FROM dual UNION ALL
SELECT 'Crystal', 'Weah', 72 FROM dual UNION ALL
SELECT 'Crystal', 'Mane', 62 FROM dual UNION ALL
SELECT 'Dortmund', 'Silva', 93 FROM dual UNION ALL
SELECT 'Dortmund', 'Weah', 83 FROM dual UNION ALL
SELECT 'Dortmund', 'Mane', 73 FROM dual UNION ALL
SELECT 'Everton', 'Weah', 94 FROM dual UNION ALL
SELECT 'Everton', 'Silva', 84 FROM dual UNION ALL
SELECT 'Everton', 'Yorke', 74 FROM dual;
Let's say the teams pick in alphabetic order.
Atletico is first in alphabetic order, so Atleco picks the player they rated highest, i.e. Mane.
Burnley picks next. It's top pick, Silva, hasn't been picked yet, so Burnley picks Silva.
Crystal picks next. It's top pick, Mane, has already been picked, but their next choice, Weah, is still available, so Crystal picks Weah.
Dortmund picks next. All the players Dortmund rated have already been picked, so Dortmund doesn't pick anyone. (I know that's not how sports teams really operate.)
Everton picks next. It's two highest-rated players have already been picked, so they have to settle for their third choice, Yorke.
So the results I want from this sample data are:
TEAM_NAME PLAYER_NAME
--------- ------------
Atletico Mane
Burnley Silva
Crystal Weah
Dortmund
Everton Yorke
What's a good way to get those results in Oracle SQL, without using PL/SQL? I'm using Oracle 12.2, but I'd be interested in solutions from any version.
I found one way:
WITH player_plus (player_name, player_letter) AS
(
SELECT player_name
, CHR (32 + ROW_NUMBER () OVER (ORDER BY player_name))
FROM player
)
, team_plus (team_name, team_num, player_list) AS
(
SELECT t.team_name
, ROW_NUMBER () OVER (ORDER BY t.team_name)
, LISTAGG (p.player_letter) WITHIN GROUP (ORDER BY c.rating DESC)
FROM team t
JOIN choice c ON c.team_name = t.team_name
JOIN player_plus p ON p.player_name = c.player_name
GROUP BY t.team_name
)
, recruit (team_name, team_num, player_letter, already_taken) AS
(
SELECT team_name, team_num
, SUBSTR (player_list, 1, 1)
, SUBSTR (player_list, 1, 1)
FROM team_plus
WHERE team_num = 1
UNION ALL
SELECT t2.team_name, t2.team_num
, SUBSTR ( LTRIM (t2.player_list, r2.already_taken)
, 1
, 1
)
, SUBSTR ( LTRIM (t2.player_list, r2.already_taken)
, 1
, 1
) || r2.already_taken
FROM recruit r2
JOIN team_plus t2 ON t2.team_num = r2.team_num + 1
)
SELECT rc.team_name
, pp.player_name
FROM recruit rc
LEFT OUTER JOIN player_plus pp ON pp.player_letter = rc.player_letter
ORDER BY rc.team_name
;
which works for a small number of players, but I'm wondering if there's a better way that does not depend on the number of characters in my character set, or the maximum length of strings.