Jump to content

Current winning streak query


oliver79

Recommended Posts

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

Link to comment
Share on other sites

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 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 ?

 

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 2 weeks later...
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.