Альтернатива (огромному) вложенному циклу (внутреннему соединению)

Question

Пожалуйста, посмотрите на это утверждение SELECT:

SELECT
    b.player_id,
    COUNT(CASE WHEN a.team = m.team_win THEN 1 END),
    COUNT(CASE WHEN a.team <> m.team_win THEN 1 END)
FROM
    players a,
    players b,
JOIN
    matches m
    ON m.match_id = b.match_id
WHERE
    a.player_id <> b.player_id
    and a.team <> b.team
    and a.player_id = 100
GROUP BY
    b.player_id

По завершении оператор должен представить набор записей, столбцы которого:

• b.player_id который a.player_id играл против.
• count матчей где a.player_id побежденный b.player_id,
• count матчей где a.player_id был побежден b.player_id,

К сожалению, эти таблицы довольно большие. matches составляет около 1,6 миллиона строк. players составляет около 17 миллионов строк, поэтому их объединение сопряжено с рядом трудностей:

Индекс плана выполнения ищет обе таблицы players а также matches, а затем испускает nested loop (inner join) шаг с 1 176 730 000 000 предполагаемых строк.

Прочее разное Информация:

player_id является tinyint, team является bit, team_win является bit,

match_id является bigint primary key с ограничением внешнего ключа на players

Таблица матчей

CREATE TABLE [dbo].[Matches](
    [match_id] [bigint] NOT NULL,
    [match_seq_id] [bigint] NOT NULL,
    [team_win] [bit] NOT NULL,
    CONSTRAINT [PK_Matches] PRIMARY KEY CLUSTERED (
        [match_id] ASC
    ) ON [PRIMARY]
) ON [PRIMARY]

Таблица игроков

CREATE TABLE [dbo].[Players] (
    [id]         [int] PRIMARY KEY IDENTITY NOT NULL,
    [match_id]   [bigint] NOT NULL,
    [account_id] [bigint] NOT NULL,
    [team]       [bit] NOT NULL,
    [player_id]  [tinyint] NOT NULL,
    /* column list has been truncated for brevity. */
    CONSTRAINT [PK_Players] PRIMARY KEY CLUSTERED (
        [id] ASC
    ) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Players] ADD CONSTRAINT [FK_Players_Matches] FOREIGN KEY([match_id])
REFERENCES [dbo].[Matches] ([match_id])

Answer 1

Вы пропустили соединение с players стол, так что вы получаете декартово произведение. Попробуйте это вместо этого:

SELECT
    b.player_id,
    COUNT(CASE WHEN a.team = m.team_win THEN 0 END),
    COUNT(CASE WHEN a.team <> m.team_win THEN 1 END)
FROM
    matches m
JOIN players a ON m.match_id = a.match_id
JOIN players b ON m.match_id = b.match_id
WHERE
    a.player_id <> b.player_id
    and a.team <> b.team
    and a.player_id = 100
GROUP BY
    b.player_id

Answer 2

Кажется, вы хотите знать: для каждого игрока, сколько раз эта команда игроков выиграла и сколько она проиграла, когда игрок сыграл против 100. Давайте сначала подведем итоги команды:

SELECT p.team,
       SUM(CASE WHEN p.team = m.team_win THEN 1 ELSE 0 END) as teamwins,
       SUM(CASE WHEN p.team <> m.team_win THEN 1 ELSE 0 END) as teamlosts
FROM players p JOIN
     matches m
     ON m.match_id = p.match_id
GROUP BY p.team
HAVING sum(case when p.player_id = 100 then 1 else 0 end) -- be sure player 100 played

Далее вы можете присоединиться к игрокам, чтобы получить информацию о команде.

Сейчас я останавливаюсь здесь, потому что мне нужно быть уверенным, что команды стабильны во времени.