MySQL sequence create

Question

I need some help pulling records that happen in a sequence in the MySQL environment.

My dataset consists of cross-country games and the winning and losing country. I need to identify countries which have won atleast 3 games in a row. Below is a reproducible example. I created a matches dataset.

    CREATE TABLE matches (date DATE, winner CHAR(10), loser CHAR(10));
    INSERT INTO matches (date,winner,loser) VALUES (STR_TO_DATE('3-03-2013', '%m-%d-%Y') ,'USA','CHINA');
    INSERT INTO matches (date,winner,loser) VALUES (STR_TO_DATE('3-05-2013', '%m-%d-%Y') ,'USA','RUSSIA');
    INSERT INTO matches (date,winner,loser) VALUES (STR_TO_DATE('3-06-2013', '%m-%d-%Y') ,'FRANCE','GERMANY');
    INSERT INTO matches (date,winner,loser) VALUES (STR_TO_DATE('3-09-2013', '%m-%d-%Y') ,'USA','RUSSIA');
    INSERT INTO matches (date,winner,loser) VALUES (STR_TO_DATE('3-11-2013', '%m-%d-%Y') ,'USA','INDIA');
    INSERT INTO matches (date,winner,loser) VALUES (STR_TO_DATE('3-15-2013', '%m-%d-%Y') ,'USA','AUSTRALIA');
    INSERT INTO matches (date,winner,loser) VALUES (STR_TO_DATE('3-15-2013', '%m-%d-%Y') ,'USA','NEW ZEALAND');

I created another dataset which has a row number for each country ordered by date.

    CREATE TABLE matches2 
    (
         date DATE, 
         winner CHAR(10),
         loser CHAR(10),
         row INT
     );
    INSERT INTO matches2 
    (
        row,
        winner,
        date,
        loser
    )
    SELECT row,
           winner,
           date , 
           loser
    FROM 
    (
       SELECT winner, 
              (@winner:=@winner+1) AS row, 
               date ,
               loser
       FROM matches ,
       (SELECT @winner := 0) r
    ) x
    ORDER BY date;

The table matches2 looks like below

   date         winning   losing        row
   2013-03-03   USA       CHINA          1
   2013-03-05   USA       RUSSIA         2
   2013-03-06   FRANCE    GERMANY        3
   2013-03-09   USA       RUSSIA         4
   2013-03-11   USA       INDIA          5
   2013-03-15   USA       AUSTRALIA      6
   2013-03-15   USA       NEW ZEALAN     7

As the data shows, USA has won >3 games in a row. how I write a code to capture this sequence ?

I tried creating a delta which calculates difference in row_number values. basically any country where the difference is 1 . But it is not working. SET Delta = row - (SELECT row FROM matches2 t1 WHERE matches2.row > matches2.row ORDER BY matches2.row ASC LIMIT 1) — userNid
– userNid, Commented Jun 4, 2015 at 1:47

Gordon Linoff · Accepted Answer · 2015-06-04 01:47:59Z

1

You can do this with a sequence of joins:

select m1.*, m2.date, m3.date
from matches2 m1 join
     matches2 m2
     on m2.row = m1.row + 1 and m2.winner = m1.winner join
     matches2 m3
     on m3.row = m2.row + 1 and m3.winner = m2.winner join
     matches2 m4
     on m4.row = m3.row + 1 and m4.winner = m3.winner;

answered Jun 4, 2015 at 1:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

spencer7593 Over a year ago

This seems like a really odd result, for "3 in a row". What if there was a match between say, Sri Lanka and China on 3-13-2013. If we added that row, then this query wouldn't return a row. Nothing wrong with that result, but it just seems odd, given that USA has won a series of four of matches in a row, of the matches that they participated in. Seems kind of odd that a match between two other countries would break the USA winning streak.

Gordon Linoff Over a year ago

@spencer7593 . . . That is an ambiguity for the OP. As I understood the question, it meant three matches in a row, not three matches played by the team in a row.

spencer7593 Over a year ago

I concur. I'm saying that if someone requested a result "Which teams have won three (or more) matches in a row", my natural inclination for a "winning streak" result would be to include/consider only matches where that team was a participant, and actually had a chance of winning.

spencer7593 · Accepted Answer · 2015-06-04 03:03:01Z

Here's another approach to returning the "winner" of at least three in a row, if we consider only the matches that the country participated in as a series. That is, an intervening match between two different countries isn't considered to break another teams winning streak.

SELECT z.winner
  FROM (SELECT @cnt := IF(v.team=@prev_team AND v.winner=@prev_winner,@cnt+1,1) AS cnt
             , @prev_team   := v.team                                           AS team
             , @prev_winner := v.winner                                         AS winner
          FROM (SELECT t.team
                     , m.winner
                     , m.loser
                     , m.date
                  FROM (SELECT @prev_team := NULL, @prev_winnner := NULL, @cnt := 0) i
                         CROSS
                          JOIN ( SELECT w.winner AS team
                                   FROM matches w
                                  GROUP BY w.winner
                               ) t
                          JOIN matches m
                            ON m.winner = t.team
                     ORDER BY t.team, m.date
               ) v
       ) z
 WHERE z.cnt = 3
 GROUP BY z.winner

Here's an example test case:

CREATE TABLE matches (`date` DATE, `winner` VARCHAR(12), `loser` VARCHAR(12), `row` INT);
INSERT INTO matches (`date`,`winner`,`loser`,`row`) VALUES
 (STR_TO_DATE('3-03-2013', '%m-%d-%Y') ,'USA'      ,'CHINA'      ,1)
,(STR_TO_DATE('3-05-2013', '%m-%d-%Y') ,'USA'      ,'RUSSIA'     ,2)
,(STR_TO_DATE('3-06-2013', '%m-%d-%Y') ,'FRANCE'   ,'GERMANY'    ,3)
,(STR_TO_DATE('3-08-2013', '%m-%d-%Y') ,'USA'      ,'RUSSIA'     ,4)
,(STR_TO_DATE('3-10-2013', '%m-%d-%Y') ,'FRANCE'   ,'RUSSIA'     ,5)
,(STR_TO_DATE('3-12-2013', '%m-%d-%Y') ,'SRI LANKA','MALAYSIA'   ,6)
,(STR_TO_DATE('3-14-2013', '%m-%d-%Y') ,'USA'      ,'AUSTRALIA'  ,7)
,(STR_TO_DATE('3-16-2013', '%m-%d-%Y') ,'FRANCE'   ,'RUSSIA'     ,8) 
,(STR_TO_DATE('3-18-2013', '%m-%d-%Y') ,'USA'      ,'NEW ZEALAND',9);

In the matches that 'USA' participated in, they won every time. They played 5 matches, and they won 5 matches.

France also won three matches that they participated in, with no "loss" between those wins.

The query in this answer reports both 'USA' and 'FRANCE' as winning "three in a row".

Collectives™ on Stack Overflow

MySQL sequence create

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related