Запросить игроков, которые все еще могут выиграть или сыграть вничью в пуле Classic NFL Football Pick'em ⇐ MySql

[Anonymous]

У меня есть веб-сайт футбольного пула НФЛ. Каждую неделю мои друзья выбирают победителей каждой игры. Я хочу отметить игроков, которые не смогли выиграть неделю по следующим причинам:

• Слишком много неправильных выборов
• Слишком много оставшихся пиков такие же, как у игроков выше.

Первый довольно легко найти, просто подсчитайте общее количество оставшихся возможных очков и прибавьте к текущим очкам. , а затем сравните с нынешним лидером.
Второй действительно тяжелый. Я долго возился с этим и недавно обратился за помощью к Близнецам (ИИ) и Гроку (ИИ). Оба получили схожие результаты с большим количеством вызовов CTE и сравнений.
Произошло две вещи. Во-первых, результаты будут очень медленными, если в пуле осталось много игр и/или много игроков. Я могу ограничить расчет так, чтобы он возвращал результаты только тогда, когда на неделе осталось, скажем, еще 3 игры.
Во-вторых, при попытке точно настроить запрос, чтобы он обрабатывал В условиях, когда 2 или более игроков в настоящее время являются лидерами и имеют разные варианты выбора, ИИ ввел концепцию «окна», а моей платформе SQL (MariaDB) это не нравится, и я рассматриваю возможность обновления.
Интересно, если Этот запрос становится слишком сложным из-за того, что ИИ пытается его залатать, или, если это действительно так, насколько сложна проблема. Примеры таблиц ниже (некоторые опущены для простоты). И текущий код от Grok(AI) ниже этого.
Сложная часть — это SUM(CASE) в CTE PotentialPoints. Остальное тоже выглядит сложно, но, по крайней мере, работает. Я постарался упростить этот вопрос, поэтому, если я что-то пропустил или есть опечатка, дайте мне знать.
Profiles (pool_profiles)
+-----------+-------------+
| profileID | profilename |
+-----------+-------------+
| 51 | Player A |
| 52 | Player B |
| 53 | Player C |
+-----------+-------------+

Teams (pool_teams)
+--------+----------+
| teamID | teamname |
+--------+----------+
| 1 | Bears |
| 2 | 49ers |
| 3 | Rams |
| 4 | Chargers |
| 5 | Lions |
| 6 | Colts |
| 7 | Rednecks |
| 8 | Falcons |
+--------+----------+

Games (pool_games)
+--------+--------+---------+------+--------+
| gameID | weekID | visitor | home | winner |
+--------+--------+---------+------+--------+
| 1 | 1 | 1 | 2 | 2 |
| 2 | 1 | 3 | 4 | 3 |
| 3 | 1 | 5 | 6 | |
| 4 | 1 | 7 | 8 | |
| 5 | 2 | 1 | 4 | |
| 6 | 2 | 3 | 6 | |
| 7 | 2 | 5 | 8 | |
| 8 | 2 | 7 | 2 | |
+--------+--------+---------+------+--------+
'visitor', 'home', and 'winner' are FK of 'pool_teams.teamID'

Picks (pool_picks)
+-----------+--------+--------+
| profileID | gameID | teamID |
+-----------+--------+--------+
| 51 | 1 | 1 |
| 51 | 2 | 3 |
| 51 | 3 | 6 |
| 51 | 4 | 8 |
| 52 | 1 | 1 |
| 52 | 2 | 3 |
| 52 | 3 | 5 |
| 52 | 4 | 8 |
| 53 | 1 | 2 |
| 53 | 2 | 3 |
| 53 | 3 | 6 |
| 53 | 4 | 7 |
+-----------+--------+--------+

WITH
CurrentScores AS (
SELECT
spp.profileID,
spp.profilename,
COALESCE(SUM(
CASE
WHEN pp.pick = pg.winner THEN 2
ELSE 0
END
), 0) AS current_points
FROM
pool_profiles spp
LEFT JOIN
pool_picks pp ON spp.profileID = pp.profileID
LEFT JOIN
$gametbl pg ON pp.gameID = pg.gameID
WHERE
pg.weekID = 1 -- $current_week
GROUP BY
spp.profileID, spp.profilename
),
RemainingGames AS (
SELECT
pg.gameID,
pg.weekID
FROM
pool_games pg
WHERE
pg.weekID = 1 -- $current_week
AND pg.winner = NULL
),
PotentialPoints AS (
SELECT
pp.profileID,
SUM(CASE
WHEN pp.pick = rg.actual_winner THEN 2
WHEN pp.pick NOT IN (SELECT pick FROM pool_picks WHERE gameID = pp.gameID AND profileID = (SELECT profileID FROM CurrentScores ORDER BY current_points DESC LIMIT 1)) THEN 2
ELSE 0
END) AS max_potential_points
FROM
pool_picks pp
JOIN
RemainingGames rg ON pp.gameID = rg.gameID
GROUP BY
pp.profileID
),
LeaderAnalysis AS (
SELECT
cs.profileID,
cs.profilename,
cs.current_points,
COALESCE(pp.max_potential_points, 0) AS potential_points,
cs.current_points + COALESCE(pp.max_potential_points, 0) AS total_possible_points,
MAX(cs.current_points) OVER () AS current_leader,
MAX(cs.current_points + COALESCE(pp.max_potential_points, 0)) OVER () AS potential_leader
FROM
CurrentScores cs
LEFT JOIN
PotentialPoints pp ON cs.profileID = pp.profileID
)
SELECT
la.profileID,
la.current_points,
la.potential_points,
la.total_possible_points,
CASE
WHEN la.current_leader = la.current_points THEN 'Current Leader'
WHEN la.total_possible_points > la.current_leader AND la.total_possible_points < la.potential_leader THEN 'Can Win'
WHEN la.total_possible_points = la.current_leader AND la.total_possible_points < la.potential_leader THEN 'Can Tie'
ELSE 'Cannot Win'
END AS status
FROM
LeaderAnalysis la
ORDER BY
la.total_possible_points DESC,
la.current_points DESC;


Подробнее здесь: https://stackoverflow.com/questions/793 ... ck-em-pool