如果我有一个如下所示的 SQL 表,我如何计算当前的连胜或连败(以及按赛季分组/重置连胜或连败)。我想更新表格并填充每条记录的条纹。
因此,对于#1,连胜将是“-1”,#2 将是“1”,#3 将是“2”,但一旦我们下降到#7,它将再次重置为“1”。 (+1 表示“赢 1 场比赛”,-1 表示“输 1 场比赛”等)
ID team date Result season streak
1 76ers 2000-01-01 Loss 2000 Null
2 76ers 2000-01-05 Win 2000 Null
3 76ers 2000-01-08 Win 2000 Null
4 Lakers 2000-01-03 Loss 2000 Null
5 Lakers 2000-01-07 Loss 2000 Null
6 Lakers 2000-01-01 Win 2000 Null
7 76ers 2002-03-01 Win 2001 Null
8 76ers 2002-03-05 Win 2001 Null
9 76ers 2002-03-08 Loss 2001 Null
10 Lakers 2002-03-03 Loss 2001 Null
11 Lakers 2002-03-07 Loss 2001 Null
12 Lakers 2002-03-01 Win 2001 Null
对于每场比赛,计算之前结果相同的比赛,以便中间不存在结果相反的比赛。将结果存储在临时表中:
CREATE TEMPORARY TABLE STREAK_TABLE
SELECT
ID,
(
SELECT 1 + COUNT(*) -- Earlier games with the same result, team and season.
FROM YOUR_TABLE T2
WHERE
T1.Result = T2.Result
AND T1.team = T2.team
AND T1.season = T2.season
AND T1.date > T2.date
AND NOT EXISTS (
SELECT * -- The games in between, with the same team and season but opposite result.
FROM YOUR_TABLE T3
WHERE
T2.Result <> T3.Result
AND T1.team = T3.team
AND T1.season = T3.season
AND T3.date BETWEEN T2.date AND T1.date
)
) S
FROM YOUR_TABLE T1
然后,更新原始表(并消除过程中的连败):
UPDATE YOUR_TABLE
SET streak = (
SELECT CASE Result WHEN 'Win' THEN S ELSE -S END
FROM STREAK_TABLE
WHERE STREAK_TABLE.ID = YOUR_TABLE.ID
)
最后,清理临时表:
DROP TABLE STREAK_TABLE
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)