DWG | MySQL Consecutive Row Streaks

I was trying to help a coworker out with a side home project. He's just learning PHP and SQL and coming up with things that are useful for his home life to develop these skills. This evening he posed a puzzle to me, to add a feature to one of these projects (a table to keep track of games of gin between him and his wife):

<Greg> in php, how can I pull out consecutive wins?
<Greg> like a winning streak
<Greg> or a losing streak for that matter
<Greg> like, "greg has won the last x games"
<Mike> can probably be done almost entirely in SQL
<Mike> sec
<Mike> lemme put that into my own DB to test

So, I got his db schema, and a couple of rows of actual data, and set about seeing if I could solve this puzzle. I have, and I spent enough time on it that I want to share it with everyone, along with an explanation of how it works. Also, I can come back and reference this later if I forget!

For following-along purposes, here is the db schema, and 12 rows of data (the first 3 are legitimate scores from his table, the other 9 I made up for testing my query):

CREATE TABLE greg_gin_db (
  game_id int(11) NOT NULL AUTO_INCREMENT,
  greg_win int(11) NOT NULL,
  april_win int(11) NOT NULL,
  greg_total int(11) NOT NULL,
  april_total int(11) NOT NULL,
  PRIMARY KEY (game_id),
  KEY greg_win (greg_win,april_win)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

INSERT INTO greg_gin_db (game_id, greg_win, april_win, greg_total, april_total) VALUES
(1, 1, 0, 102, 64),
(2, 1, 0, 118, 88),
(3, 0, 1, 79, 101),
(4, 0, 1, 1, 2),
(5, 0, 1, 2, 3),
(6, 0, 1, 3, 4),
(7, 1, 0, 2, 1),
(8, 1, 0, 3, 2),
(9, 1, 0, 4, 3),
(10, 1, 0, 6, 4),
(11, 1, 0, 5, 1),
(12, 0, 1, 1, 5);

Now, the basic aim is to find streaks, roll them up into groups with a count of each streak's length, and then just grab the most recent one.

I'll do this in steps. First, we want to find the streaks. We do that like so:

SELECT greg_gin_db.*, 
       @r := @r + (COALESCE(@greg_win, greg_win) <> greg_win) AS series,
       @greg_win := greg_win
FROM(
  SELECT @r := 0, @greg_win := NULL
) vars, greg_gin_db
ORDER BY game_id;

Note that you could use april_win instead of greg_win in this case. Either works just as well as the other. Anyway, this query returns the following results:

+---------+----------+-----------+------------+-------------+--------+-----------------------+
| game_id | greg_win | april_win | greg_total | april_total | series | @greg_win := greg_win |
+---------+----------+-----------+------------+-------------+--------+-----------------------+
|       1 |        1 |         0 |        102 |          64 |      0 |                     1 |
|       2 |        1 |         0 |        118 |          88 |      0 |                     1 |
|       3 |        0 |         1 |         79 |         101 |      1 |                     0 |
|       4 |        0 |         1 |          1 |           2 |      1 |                     0 |
|       5 |        0 |         1 |          2 |           3 |      1 |                     0 |
|       6 |        0 |         1 |          3 |           4 |      1 |                     0 |
|       7 |        1 |         0 |          2 |           1 |      2 |                     1 |
|       8 |        1 |         0 |          3 |           2 |      2 |                     1 |
|       9 |        1 |         0 |          4 |           3 |      2 |                     1 |
|      10 |        1 |         0 |          6 |           4 |      2 |                     1 |
|      11 |        1 |         0 |          5 |           1 |      2 |                     1 |
|      12 |        0 |         1 |          1 |           5 |      3 |                     0 |
+---------+----------+-----------+------------+-------------+--------+-----------------------+
12 rows in set (0.00 sec)

As you can see, essentially what is happening is, the query increments the new, generated column "series" by one every time greg_win is not the same as the previous row's greg_win. So from here, all we have to do is narrow the query with another outer query to get the streaks:

SELECT greg_win, april_win, COUNT(*) AS streaklen
FROM(
  SELECT greg_gin_db.*, 
         @r := @r + (COALESCE(@greg_win, greg_win) <> greg_win) AS series,
         @greg_win := greg_win
  FROM(
    SELECT @r := 0, @greg_win := NULL
  ) vars, greg_gin_db
  ORDER BY game_id) q
GROUP BY series;

For the given set of data, this returns:

+----------+-----------+-----------+
| greg_win | april_win | streaklen |
+----------+-----------+-----------+
|        1 |         0 |         2 |
|        0 |         1 |         4 |
|        1 |         0 |         5 |
|        0 |         1 |         1 |
+----------+-----------+-----------+
4 rows in set (0.00 sec)

Now from here we can go two directions. To fulfill Greg's request and get the latest streak only:

SELECT greg_win, april_win, COUNT(*) AS streaklen
FROM(
  SELECT greg_gin_db.*, 
         @r := @r + (COALESCE(@greg_win, greg_win) <> greg_win) AS series,
         @greg_win := greg_win
  FROM(
    SELECT @r := 0, @greg_win := NULL
  ) vars, greg_gin_db
  ORDER BY game_id DESC) q
GROUP BY series LIMIT 1;

Or, we can add yet a third layer to the puzzle and find the largest streak:

SELECT greg_win, april_win, MAX(streaklen) AS strk
FROM(
  SELECT greg_win, april_win, COUNT(*) AS streaklen
  FROM(
     SELECT greg_gin_db.*, 
           @r := @r + (COALESCE(@greg_win, greg_win) <> greg_win) AS series,
           @greg_win := greg_win
    FROM(
      SELECT @r := 0, @greg_win := NULL
    ) vars, greg_gin_db
    ORDER BY game_id) q
  GROUP BY series) q2;

Which gives us:

+----------+-----------+------+
| greg_win | april_win | strk |
+----------+-----------+------+
|        1 |         0 |    5 |
+----------+-----------+------+
1 row in set (0.00 sec)

So there you have it. Counting consecutive rows in MySQL.

Write a comment

• Required fields are marked with *.

Name:
Email:
Subject:
Comment: *

Help prevent spam - enter security code above:
 

mortgage loans
Posts: 5

Re:
Reply #5 on : Tue December 20, 2011, 21:52:27

---

I received my first credit loans when I was a teenager and it aided my business very much. But, I require the small business loan once more time.

Paul @ Cincy
Posts: 5

quite useful!
Reply #4 on : Sat December 17, 2011, 08:03:39

---

Thank, helped me out of a particularly difficult issue.

Caell
Posts: 5

beware
Reply #3 on : Wed July 27, 2011, 05:50:12

---

There is ONE caveat:

This - like all those nice @var tricks - works only, if the intended ordering of the result is congruent with the physical ordering of the table-data. In the example, it works, since the data is inserted in the order of the ids.

It would most certainly not work if you mixed up the sequence of the inserted values...

Its a great solution, I just thought I'd post this here in case someone wonders why it's not working for them.

boubbin
Posts: 5

Erm.
Reply #2 on : Wed June 15, 2011, 04:20:26

---

Am i mistaken here or does this also count loss streaks as winstreaks?

COUNT() also calculates the zeros?

boubbin
Posts: 5

Nice
Reply #1 on : Wed June 15, 2011, 03:24:32

---

Nice tutorial, thank you!