SQL计算成功和失败的条纹 [英] SQL Calculate winning and losing streaks

问题描述

如果我有一个如下所示的SQL表，该如何计算当前的获胜或失败连胜(以及按季节对获胜或失败连胜进行分组/重置).我想更新表并为每条记录填写条纹.

If I have a SQL table that looks like this below how can I calculate the current winning or losing streak (and having the winning or losing streak grouped/reset by season). I want to update the table and fill in the streak for every record.

因此，对于#1，条纹为"-1"，#2为"1"，#3为"2"，但一旦降至#7，它将再次重置为"1". (+1表示赢得1场比赛"，-1表示失去1场比赛"，依此类推.)

So for #1 the streak would be "-1", #2 would be "1", #3 would be "2" but once we got down to #7 it would be reset to "1" again. (+1 means "Won 1 game" and -1 means "Lost 1 game", etc.)

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

推荐答案

对于每个游戏，计算具有之前相同结果的游戏，这样就不会有中间结果相反的游戏.将结果存储在临时表中:

For each game, count games with the same result that came before it, such that there is no game with the opposite result in between. Store the results in a temporary table:

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

然后，更新原始表(并消除该过程中的丢失条纹):

Then, update the original table (and negate the losing streaks in the process):

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

这篇关于SQL计算成功和失败的条纹的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！