Jednym z intuicyjnych sposobów rozwiązania tego problemu jest:
- Znajdź najnowszy wynik dla każdej drużyny
- Sprawdź poprzednie dopasowanie i dodaj jeden do liczby serii, jeśli typ wyniku jest zgodny
- Powtórz krok 2, ale zatrzymaj się, gdy tylko pojawi się pierwszy inny wynik
Strategia ta może wygrać z rozwiązaniem funkcji okna (które wykonuje pełne skanowanie danych) w miarę powiększania się tabeli, przy założeniu, że strategia rekurencyjna jest skutecznie wdrażana. Kluczem do sukcesu jest zapewnienie wydajnych indeksów w celu szybkiego zlokalizowania wierszy (za pomocą wyszukiwań) i uniknięcia sortowania. Potrzebne indeksy to:
-- New index #1
CREATE UNIQUE INDEX uq1 ON dbo.FantasyMatches
(home_fantasy_team_id, match_id)
INCLUDE (winning_team_id);
-- New index #2
CREATE UNIQUE INDEX uq2 ON dbo.FantasyMatches
(away_fantasy_team_id, match_id)
INCLUDE (winning_team_id);
Aby pomóc w optymalizacji zapytań, użyję tabeli tymczasowej do przechowywania wierszy określonych jako część bieżącej serii. Jeśli smugi są zazwyczaj krótkie (jak to jest w przypadku zespołów, które śledzę, niestety), ta tabela powinna być dość mała:
-- Table to hold just the rows that form streaks
CREATE TABLE #StreakData
(
team_id bigint NOT NULL,
match_id bigint NOT NULL,
streak_type char(1) NOT NULL,
streak_length integer NOT NULL,
);
-- Temporary table unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq ON #StreakData (team_id, match_id);
Moje rozwiązanie do zapytań rekurencyjnych jest następujące ( tutaj Fiddle SQL ):
-- Solution query
WITH Streaks AS
(
-- Anchor: most recent match for each team
SELECT
FT.team_id,
CA.match_id,
CA.streak_type,
streak_length = 1
FROM dbo.FantasyTeams AS FT
CROSS APPLY
(
-- Most recent match
SELECT
T.match_id,
T.streak_type
FROM
(
SELECT
FM.match_id,
streak_type =
CASE
WHEN FM.winning_team_id = FM.home_fantasy_team_id
THEN CONVERT(char(1), 'W')
WHEN FM.winning_team_id IS NULL
THEN CONVERT(char(1), 'T')
ELSE CONVERT(char(1), 'L')
END
FROM dbo.FantasyMatches AS FM
WHERE
FT.team_id = FM.home_fantasy_team_id
UNION ALL
SELECT
FM.match_id,
streak_type =
CASE
WHEN FM.winning_team_id = FM.away_fantasy_team_id
THEN CONVERT(char(1), 'W')
WHEN FM.winning_team_id IS NULL
THEN CONVERT(char(1), 'T')
ELSE CONVERT(char(1), 'L')
END
FROM dbo.FantasyMatches AS FM
WHERE
FT.team_id = FM.away_fantasy_team_id
) AS T
ORDER BY
T.match_id DESC
OFFSET 0 ROWS
FETCH FIRST 1 ROW ONLY
) AS CA
UNION ALL
-- Recursive part: prior match with the same streak type
SELECT
Streaks.team_id,
LastMatch.match_id,
Streaks.streak_type,
Streaks.streak_length + 1
FROM Streaks
CROSS APPLY
(
-- Most recent prior match
SELECT
Numbered.match_id,
Numbered.winning_team_id,
Numbered.team_id
FROM
(
-- Assign a row number
SELECT
PreviousMatches.match_id,
PreviousMatches.winning_team_id,
PreviousMatches.team_id,
rn = ROW_NUMBER() OVER (
ORDER BY PreviousMatches.match_id DESC)
FROM
(
-- Prior match as home or away team
SELECT
FM.match_id,
FM.winning_team_id,
team_id = FM.home_fantasy_team_id
FROM dbo.FantasyMatches AS FM
WHERE
FM.home_fantasy_team_id = Streaks.team_id
AND FM.match_id < Streaks.match_id
UNION ALL
SELECT
FM.match_id,
FM.winning_team_id,
team_id = FM.away_fantasy_team_id
FROM dbo.FantasyMatches AS FM
WHERE
FM.away_fantasy_team_id = Streaks.team_id
AND FM.match_id < Streaks.match_id
) AS PreviousMatches
) AS Numbered
-- Most recent
WHERE
Numbered.rn = 1
) AS LastMatch
-- Check the streak type matches
WHERE EXISTS
(
SELECT
Streaks.streak_type
INTERSECT
SELECT
CASE
WHEN LastMatch.winning_team_id IS NULL THEN 'T'
WHEN LastMatch.winning_team_id = LastMatch.team_id THEN 'W'
ELSE 'L'
END
)
)
INSERT #StreakData
(team_id, match_id, streak_type, streak_length)
SELECT
team_id,
match_id,
streak_type,
streak_length
FROM Streaks
OPTION (MAXRECURSION 0);
Tekst T-SQL jest dość długi, ale każda sekcja zapytania ściśle odpowiada ogólnemu zarysowi procesu podanemu na początku tej odpowiedzi. Kwerenda jest dłuższa z powodu potrzeby użycia pewnych sztuczek, aby uniknąć sortowania i wygenerowania TOPrekurencyjnej części zapytania (co zwykle nie jest dozwolone).
Plan wykonania jest stosunkowo niewielki i prosty w porównaniu z zapytaniem. Zacieniowałem obszar zakotwiczenia na żółto, a część rekurencyjną na zielonym zrzucie ekranu:
Dzięki zarejestrowaniu wierszy pasm w tabeli tymczasowej łatwo jest uzyskać wymagane podsumowanie wyników. (Użycie tabeli tymczasowej pozwala również uniknąć wycieku sortowania, który mógłby wystąpić, gdyby poniższe zapytanie zostało połączone z głównym zapytaniem rekurencyjnym)
-- Basic results
SELECT
SD.team_id,
StreakType = MAX(SD.streak_type),
StreakLength = MAX(SD.streak_length)
FROM #StreakData AS SD
GROUP BY
SD.team_id
ORDER BY
SD.team_id;
To samo zapytanie może być wykorzystane jako podstawa do aktualizacji FantasyTeamstabeli:
-- Update team summary
WITH StreakData AS
(
SELECT
SD.team_id,
StreakType = MAX(SD.streak_type),
StreakLength = MAX(SD.streak_length)
FROM #StreakData AS SD
GROUP BY
SD.team_id
)
UPDATE FT
SET streak_type = SD.StreakType,
streak_count = SD.StreakLength
FROM StreakData AS SD
JOIN dbo.FantasyTeams AS FT
ON FT.team_id = SD.team_id;
Lub, jeśli wolisz MERGE:
MERGE dbo.FantasyTeams AS FT
USING
(
SELECT
SD.team_id,
StreakType = MAX(SD.streak_type),
StreakLength = MAX(SD.streak_length)
FROM #StreakData AS SD
GROUP BY
SD.team_id
) AS StreakData
ON StreakData.team_id = FT.team_id
WHEN MATCHED THEN UPDATE SET
FT.streak_type = StreakData.StreakType,
FT.streak_count = StreakData.StreakLength;
Każde z tych podejść tworzy efektywny plan wykonania (na podstawie znanej liczby wierszy w tabeli tymczasowej):
Wreszcie, ponieważ metoda rekurencyjna naturalnie obejmuje match_idprzetwarzanie, łatwo jest dodać listę wyników match_idtworzących każdą serię do wyniku:
SELECT
S.team_id,
streak_type = MAX(S.streak_type),
match_id_list =
STUFF(
(
SELECT ',' + CONVERT(varchar(11), S2.match_id)
FROM #StreakData AS S2
WHERE S2.team_id = S.team_id
ORDER BY S2.match_id DESC
FOR XML PATH ('')
), 1, 1, ''),
streak_length = MAX(S.streak_length)
FROM #StreakData AS S
GROUP BY
S.team_id
ORDER BY
S.team_id;
Wynik:
Plan wykonania:
