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 ?

Answer 1

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".

Answer 2

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;