MySQL Winning Streak for one team

Question

I have an SQL query that returns the following table about a single team:

date         gameid     pointsfor     pointsagainst

2011-03-20   15         1             10
2011-03-27   17         7             3
2011-04-03   23         6             5
2011-04-10   30         5             4
2011-04-17   35         4             8
2011-05-01   38         8             1
2011-05-08   43         3             7
2011-05-15   48         6             2
2011-05-22   56         10            2
2011-05-29   59         4             5
2011-06-05   65         2             3
2011-06-19   71         5             6
2011-06-19   74         12            2
2011-06-19   77         5             2
2011-06-19   80         5             4

From this table, could anybody please help me calculate what the longest winning and losing streaks are?

I've had a look at a few other examples on here but have struggled to follow them as they are not quite the same as mine. Any help would be greatly appreciated. Thanks!

Answer 1

You'll have to implement some MySQL variables to help handle this more efficiently than multiple query/join/group by. This has a single pass through all the records, then once more getting the max per type of win/loss (or tie). I'm assuming the data you've provided is for a single time, and the date is the obvious order of the games... Points For is the team you are interested in, and points against is whoever the opponent is. That said, my "alias" name will be "YourResultSingleTeam".

The inner query will pre-determine the status of the game as "W"in or "L"oss. Then, see if that value is the same as the previous instance for the team. If so, add 1 to the existing win/loss counter. If not, set the counter back to 1. Then, preserve the status of the current game back into the "LastStatus" value for comparison to the next game.

After that is done, its a simple game result, max() grouped by the game result status

select
      StreakSet.GameResult,
      MAX( StreakSet.WinLossStreak ) as MaxStreak
   from
      ( select YR.Date,
               @CurStatus := if( YR.PointsFor > YR.PointsAgainst, 'W', 'L' ) as GameResult,
               @WinLossSeq := if( @CurStatus = @LastStatus, @WinLossSeq +1, 1 ) as WinLossStreak,
               @LastStatus := @CurStatus as carryOverForNextRecord
            from 
               YourResultSingleTeam YR,
               ( select @CurStatus := '',
                        @LastStatus := '',
                        @WinLossSeq := 0 ) sqlvars
            order by
               YR.Date ) StreakSet
   group by
      StreakSet.GameResult

As offered by Nikola, if you want to consider "tie" games, we can adjust by just changing the @CurStatus to the case/when condition to

@CurStatus := case when YR.PointsFor > YR.PointsAgainst then 'W'
                   when YR.PointsFor < YR.PointsAgainst then 'L'
                   else 'T' end as GameResult,

Answer 2

There is a solution but I don't think you are going to like it because it requires a self-join and your table is not a table but query.

Inner query will transform dates into ranges - that it, for each date in a table it will find first date having different outcame, or, in case of last game, the date of this game. This data will be aggregated by first date of different streak to flatten and count streaks; outer query then finds extremes by outcome.

select case Outcome 
            when -1 then 'Losses'
            when 1 then 'Wins'
            else 'Undecided'
        end Title
      , max(Streak) Streak
from
(
  select min(date) date, date_to, Outcome, count(*) Streak
  from
  (
    select t1.date, 
           sign (t1.pointsfor - t1.pointsagainst) Outcome, 
           ifnull (min(t2.date), t1.date) date_to
     from table1 t1
     left join table1 t2
       on t1.date < t2.date
      and sign (t1.pointsfor - t1.pointsagainst) 
       <> sign (t2.pointsfor - t2.pointsagainst)
    group by t1.date, sign (t1.pointsfor - t1.pointsagainst)
  ) a
  group by date_to, Outcome
) a
group by Outcome

To circumvent the need to replace table1 with - probably - cumbersome query you might use temporary table, or have the data already in appropriate format in auxiliary table. There is live test at Sql fiddle, along with another, subquery-driven version that might perform better - you should try them both.

Answer 3

MySQL don't have CTE nor windowing function (e.g. SUM OVER, ROW_NUMBER OVER, etc). But it has one redeeming factor. Variables!

Use this:

select 
   min(date) as start_date,
   max(date) as end_date,
   count(date) as streak,
   group_concat(gameid) as gameid_list
from
( 
  select *,      
    IF(
        pointsfor > pointsagainst 
        and 
        @pointsfor > @pointsagainst, 
           @gn, @gn := @gn + 1)                
    as group_number,

    @date as old_date, @gameid as old_gameid, 
    @pointsfor as old_pointsfor,
    @pointsagainst as old_pointsagainst,

    @date := date, @gameid := gameid, 
    @pointsfor := pointsfor, @pointsagainst := pointsagainst      
  from tbl
  cross join 
  (
    select 
      @date := CAST(null as date) as xa,
      @gameid := null + 0 as xb, -- why CAST(NULL AS INT) doesn't work?
      @pointsfor := null + 0 as xc, @pointsagainst := null + 0 as xd, @gn := 0
  ) x
  order by date
) as y
group by group_number
order by streak desc;

Output:

START_DATE                    END_DATE                      STREAK  GAMEID_LIST
March, 27 2011 08:00:00-0700  April, 10 2011 08:00:00-0700  3       17,23,30
June, 19 2011 08:00:00-0700   June, 19 2011 08:00:00-0700   3       74,77,80
May, 15 2011 08:00:00-0700    May, 22 2011 08:00:00-0700    2       48,56
March, 20 2011 08:00:00-0700  March, 20 2011 08:00:00-0700  1       15
April, 17 2011 08:00:00-0700  April, 17 2011 08:00:00-0700  1       35
May, 01 2011 08:00:00-0700    May, 01 2011 08:00:00-0700    1       38
May, 08 2011 08:00:00-0700    May, 08 2011 08:00:00-0700    1       43
May, 29 2011 08:00:00-0700    May, 29 2011 08:00:00-0700    1       59
June, 05 2011 08:00:00-0700   June, 05 2011 08:00:00-0700   1       65
June, 19 2011 08:00:00-0700   June, 19 2011 08:00:00-0700   1       71

Live test: http://www.sqlfiddle.com/#!2/bbe78/8

Note on my solution on sqlfiddle, it has two queries. 1. Simulation on top. 2. Final query below

Answer 4

Latest version of MySQL has CTE and is windowing-capable.

Here's a solution.

First step, group winning and losing by assigning them their own streak_group number:

with t as 
(
    select
        *,      
        pointsfor - pointsagainst > 0 is_winner,
        case when pointsfor - pointsagainst > 0 
            and lag(pointsfor) over(order by date, pointsfor - pointsagainst desc) 
                - lag(pointsagainst) over(order by date, pointsfor - pointsagainst desc) > 0 
        then
            0
        else
            1
        end as is_new_group
    from tbl
)
select *, sum(is_new_group) over(order by date, pointsfor - pointsagainst desc) as streak_group
from t

Output:

date                |gameid |pointsfor |pointsagainst |is_winner |is_new_group |streak_group |
--------------------|-------|----------|--------------|----------|-------------|-------------|
2011-03-20 15:00:00 |15     |1         |10            |0         |1            |1            |
2011-03-27 15:00:00 |17     |7         |3             |1         |1            |2            |
2011-04-03 15:00:00 |23     |6         |5             |1         |0            |2            |
2011-04-10 15:00:00 |30     |5         |4             |1         |0            |2            |
2011-04-17 15:00:00 |35     |4         |8             |0         |1            |3            |
2011-05-01 15:00:00 |38     |8         |1             |1         |1            |4            |
2011-05-08 15:00:00 |43     |3         |7             |0         |1            |5            |
2011-05-15 15:00:00 |48     |6         |2             |1         |1            |6            |
2011-05-22 15:00:00 |56     |10        |2             |1         |0            |6            |
2011-05-29 15:00:00 |59     |4         |5             |0         |1            |7            |
2011-06-05 15:00:00 |65     |2         |3             |0         |1            |8            |
2011-06-19 15:00:00 |74     |12        |2             |1         |1            |9            |
2011-06-19 15:00:00 |77     |5         |2             |1         |0            |9            |
2011-06-19 15:00:00 |80     |5         |4             |1         |0            |9            |
2011-06-19 15:00:00 |71     |5         |6             |0         |1            |10           |

Final query. Count the streak of winning:

with t as 
(
    select
        *,      
        pointsfor - pointsagainst > 0 is_winner,
        case when pointsfor - pointsagainst > 0 
            and lag(pointsfor) over(order by date, pointsfor - pointsagainst desc) 
                - lag(pointsagainst) over(order by date, pointsfor - pointsagainst desc) > 0 
        then
            0
        else
            1
        end as is_new_group
    from tbl
)
, streak_grouping as
(
    select
        *, sum(is_new_group) over(order by date, pointsfor - pointsagainst desc) as streak_group
    from t
)
select 
    min(date) as start_date,
    max(date) as end_date,
    count(*) as streak,
    group_concat(gameid order by gameid) as gameid_list
from streak_grouping
group by streak_group
order by streak desc, start_date

Output:

start_date          |end_date            |streak |gameid_list |
--------------------|--------------------|-------|------------|
2011-03-27 15:00:00 |2011-04-10 15:00:00 |3      |17,23,30    |
2011-06-19 15:00:00 |2011-06-19 15:00:00 |3      |74,77,80    |
2011-05-15 15:00:00 |2011-05-22 15:00:00 |2      |48,56       |
2011-03-20 15:00:00 |2011-03-20 15:00:00 |1      |15          |
2011-04-17 15:00:00 |2011-04-17 15:00:00 |1      |35          |
2011-05-01 15:00:00 |2011-05-01 15:00:00 |1      |38          |
2011-05-08 15:00:00 |2011-05-08 15:00:00 |1      |43          |
2011-05-29 15:00:00 |2011-05-29 15:00:00 |1      |59          |
2011-06-05 15:00:00 |2011-06-05 15:00:00 |1      |65          |
2011-06-19 15:00:00 |2011-06-19 15:00:00 |1      |71          |

Answer 5

You are dealing here is to track trends of wins and loses, which needs to be computed with a loop of some sort with a running counter, not in SQL. SQL queries deal with individual rows, grouping, sorting etc; You are trying to use a language which is not meant to be solving such problems.

Answer 6

You will have to create a cursor, read all rows, compute the data ... every time you want to get the longest streaks...

I suggest a workaround that will make things easier. You add a column in your table "streakFor". every time you insert a row :

//pseudo code
if pointsFor > pointsAgainst
    if last_streakFor > 0 
        then streakFor++ 
        else streakFor = 1

else
    if last_streakFor > 0 
        then streakFor = -1 
        else streakFor--

last_streakFor is streakFor in the last inserted row
then you insert the row with column streakFor

Now you can

• select max(streakFor) from yourTable where yourConditions that will give you both the longest winning streak for "pointsFor" and the longest losing streak for "pointsAgainst"
• select min(streakFor) from yourTable where yourConditions will give you longest winning streak for "pointsAgainst" and longest losing streak for "pointsFor"

Answer 7

thanks for all the helps guys. i ended up using php to loop through as suggested. in case anyone is wondering, this is my code:

$streakSQL = "SELECT date, gameid, pointsfor, pointsagainst FROM result WHERE teamid = ".$_GET['teamid']." AND bye = 0 AND COMPLETED = 1 AND seasonid > 7 AND roundwd = 0 AND (pointsfor != 0 OR pointsagainst != 0)";
            $streak = mysql_query($streakSQL);

            $winstreak = 0;
            $maxwinstreak = 0;
            $losestreak = 0;
            $maxlosestreak = 0;
            while($streakRow = mysql_fetch_array($streak))
            {
                //calculate winning streak
                if($streakRow['pointsfor'] > $streakRow['pointsagainst'])
                { 
                    $winstreak++; 
                    if($winstreak > $maxwinstreak)
                    {
                        $maxwinstreak = $winstreak;
                    }
                }
                else{ $winstreak = 0; }
                //calculate losing streak
                if($streakRow['pointsfor'] < $streakRow['pointsagainst'])
                { 
                    $losestreak++; 
                    if($losestreak > $maxlosestreak)
                    {
                        $maxlosestreak = $losestreak;
                    }
                }
                else{ $losestreak = 0; }
            }
            echo "Biggest Winning Streak: ".$maxwinstreak;
            echo "<br />Biggest Losing Streak: ".$maxlosestreak;