Current winning streak query

[oliver79]

This is some sample data of a table named "engp":

ID DATE                 HOME      AWAY       FTH FTA  SEASON

1 22.04.2012 00:00:00  Man Utd   Everton    4   4    Premier League 11/12
2 05.11.2011 00:00:00  Arsenal   W.B.A.     3   0    Premier League 11/12
3 01.10.2011 00:00:00  Wolves    Newcastle  1   2    Premier League 11/12
4 22.04.2012 00:00:00  Liverpool W.B.A.     0   1    Premier League 11/12
5 02.10.2011 00:00:00  Swansea   Stoke      2   0    Premier League 11/12
6 07.04.2012 00:00:00  Chelsea   Wigan      2   1    Premier League 11/12
7 26.02.2012 00:00:00  Arsenal   Tottenham  5   2    Premier League 11/12
8 11.09.2011 00:00:00  Norwich   W.B.A.     0   1    Premier League 11/12
9 07.04.2012 00:00:00  W.B.A.    Blackburn  3   0    Premier League 11/12
10 14.08.2011 00:00:00  Stoke     Chelsea    0   0    Premier League 11/12

 

What I need to do is find the teams in current season with the longest sequence of homewins (FTH>FTA) during the last games played until today. (Current sequences). One suggested the following query but the problem is that this query targets the longest winning streak of each team during the whole season so far and not the latest winning streak for each team. 2nd problem is that the query does not even work for that and provides wrong results.

SET @count=0;

SELECT a.home
     , MAX(@count:=IF(b.FTH > b.FTA, @count + 1, 1)) best_streak
  FROM
     ( SELECT x.*
            , COUNT(*) rank
         FROM engp x
         JOIN engp y
           ON y.home = x.home
          AND y.league = x.league
          AND y.date <= x.date
        WHERE x.league = 'Premier League 11/12'
        GROUP
           BY id
     ) a
  LEFT
  JOIN
     ( SELECT x.*
            , COUNT(*) rank
         FROM engp x
         JOIN engp y
           ON y.home = x.home
          AND y.league = x.league
          AND y.date <= x.date
        WHERE x.league = 'Premier League 11/12'
        GROUP
           BY id
     ) b
    ON a.rank = b.rank  + 1
   AND b.home = a.home
WHERE a.FTH > a.FTA 
GROUP
    BY a.home;

 

Any help would be greatly appreciated

[Barand]

the first thing to do is change your date format to yyyy-mm-dd.

 

it is impossible to do date comparisons on your dates in that format

[Illusion]

If I understand correctly... you want teams order by number of home wins they have in the current season

 

Select home, sum( case when fth > fta  then 1 else 0 end) as homewinmatchcount from engp  where league = 'Premier League 11/12'  group by home order by homewinmatchcount desc 

[Illusion]

I think I got it wrong.... Can anybody explain me how a winning streak is calculated?  I am not into these sports 

[Barand]

@Illusion,

 

He's looking for the highest number of consecutive home wins

[oliver79]

Thank you all for your response.

Well, it seems to be a problem with the ID(primary key) field. The query itself is correct. Now I am doing this and it works just fine. I`d rather use a real temporary table but mysql docs say "You cannot refer to a TEMPORARY table more than once in the same query"

CREATE table mytemp (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (id)) select date, home, away, FTH, FTA, league from engp
order by home, date asc;

SET @count=0;

SELECT a.home
     , MAX(@count:=IF(b.FTH > b.FTA, @count + 1, 1)) best_streak
  FROM
     ( SELECT x.*
            , COUNT(*) rank
         FROM mytemp x
         JOIN mytemp y
           ON y.home = x.home
          AND y.league = x.league
          AND y.date <= x.date
        WHERE x.league = 'Premier League 11/12'
        GROUP
           BY id 
     ) a
  LEFT
  JOIN
     ( SELECT x.*
            , COUNT(*) rank
         FROM mytemp x
         JOIN mytemp y
           ON y.home = x.home
          AND y.league = x.league
          AND y.date <= x.date
        WHERE x.league = 'Premier League 11/12'
        GROUP
           BY id 
     ) b
    ON a.rank = b.rank  + 1
   AND b.home = a.home
WHERE a.FTH > a.FTA 
GROUP
    BY a.home;

drop table mytemp;

 

This outputs :

Arsenal		5
Aston Villa	1
Blackburn	1
Bolton		2
Chelsea		4
Everton		4
Fulham		2
Liverpool	2
Man City	15
Man Utd		7
Newcastle	4
Norwich		2
QPR		5
Stoke		2
Sunderland	3
Swansea		3
Tottenham	6
W.B.A.		2
Wigan		4
Wolves		2

 

These are the max winning streaks for each team season 11/12 during the whole season.

But what I really need is their current winning streak. For instance :

20.8.2011 00:00:00	Arsenal	Liverpool	0	2	
10.9.2011 00:00:00	Arsenal	Swansea		1	0	
24.9.2011 00:00:00	Arsenal	Bolton		3	0	
16.10.2011 00:00:00	Arsenal	Sunderland	2	1	
23.10.2011 00:00:00	Arsenal	Stoke		3	1	
5.11.2011 00:00:00	Arsenal	W.B.A.		3	0	
26.11.2011 00:00:00	Arsenal	Fulham		1	1	
10.12.2011 00:00:00	Arsenal	Everton		1	0	
27.12.2011 00:00:00	Arsenal	Wolves		1	1	
31.12.2011 00:00:00	Arsenal	QPR		1	0	
22.1.2012 00:00:00	Arsenal	Man Utd		1	2	
4.2.2012 00:00:00	Arsenal	Blackburn	7	1	
26.2.2012 00:00:00	Arsenal	Tottenham	5	2	
12.3.2012 00:00:00	Arsenal	Newcastle	2	1	
24.3.2012 00:00:00	Arsenal	Aston Villa	3	0	
8.4.2012 00:00:00	Arsenal	Man City	1	0	
16.4.2012 00:00:00	Arsenal	Wigan		1	2	
21.4.2012 00:00:00	Arsenal	Chelsea		0	0	
5.5.2012 00:00:00	Arsenal	Norwich		3	3

 

So Arsenals max winning streak is 5 in a row, but because they tied their last homegame the current streak would be 0. Any ideas how to update the query in order to getthis done ? Thanks.

[Illusion]

Hmm, this is a my solution...

 

set @count:=0,@home:='';


select b.home, MAX(b.flag2) as longestsequence from (Select home,if( @home!=home,@count:=0,@count) as flag1,@home:=home, ( case when fth > fta  then @count:=@count+1 else @count:=0 end) as flag2  from (select home,away,fth,fta from engp where season = 'Premier League 11/12' order by home,`date`) a)b  group by home order by MAX(b.flag2) desc  ; 

regarding your other question

 

Arsenal last home game is

 

8.4.2012 00:00:00 Arsenal Man City 1 0

 

and its not tied.

 

how the current winning streak is calculated actually ?

 

 

 

[oliver79]

Cool, your code works fine without annoying extra tables, thanks a lot !

 

And Arsenals last homegame was tied (5.5.2012 00:00:00 Arsenal Norwich 3 3)

You didn´t scroll down ;o)

 

Current winning streak is defined by how many games (in this case playing at home) a team won in a row counting from last game down.

[Illusion]

I would suggest you to have separate query to check whether last home game is a tie or not ... if it is a tie consider that current winning streak is 0 else take the value from the first query output

 

select a.home, if(fth=fta,1,0 ) as tie from engp a inner join (select home,MAX(`date`)  as lastdate from engp group by home) b on a.`date`=b.lastdate  and a.home=b.home

or

 

select home, if(fth=fta,1,0 ) as tie from engp where `date`=(select `date` from engp order by date desc limit 1)

 

if you want to combine , you could combine both queries as well.

 

 

[oliver79]

I tried to somehow figure it out but it seems as a newbie regarding mySQL I just can`t.

 

I read this article on detecting runs or streaks in data (SQL) but still I found no way.

http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data

And honestly I also dont understand what you ment with combining the 2 queries which you suggested.

 

The solution is right before my eyes...I know,....but I hope you can give me another little hint on how to

do that.

 

Thanks.

[fenway]

Why can't you just find the date of their last non-win (tie/loss), and then simply count all the records after that?

[oliver79]

If I knew how to do so I would NOT post in a forum to get some help with this query...

[fenway]

By "can't", I mean conceptually.