Alberto Alberto - 5 months ago 19
MySQL Question

mysql: group by first and last occurrence

I have a table like the following one:

+-------------+--------------+
| current_day | browser |
+-------------+--------------+
| 2016-05-02 | Safari |
| 2016-05-03 | Safari |
| 2016-05-04 | Safari |
| 2016-05-05 | Safari |
| 2016-05-06 | Safari |
| 2016-05-07 | Safari |
| 2016-05-08 | Safari |
| 2016-05-09 | Opera |
| 2016-05-10 | Opera |
| 2016-05-11 | Opera |
| 2016-05-12 | Opera |
| 2016-05-13 | Opera |
| 2016-05-14 | Opera |
| 2016-05-15 | Chrome |
| 2016-05-16 | Firefox |
| 2016-05-17 | Firefox |
| 2016-05-18 | Firefox |
| 2016-05-19 | Firefox |
| 2016-05-20 | Firefox |
| 2016-05-21 | Firefox |
| 2016-05-22 | Firefox |
| 2016-05-23 | Safari |
| 2016-05-24 | Safari |
| 2016-05-25 | Safari |
| 2016-05-26 | Safari |
| 2016-05-27 | Safari |
| 2016-05-28 | Safari |
| 2016-05-29 | Safari |
| 2016-05-30 | Opera |
| 2016-05-31 | Opera |
| 2016-06-01 | Opera |
| 2016-06-02 | Firefox |
| 2016-06-03 | Firefox |
| 2016-06-04 | Firefox |
| 2016-06-05 | Firefox |
| 2016-06-06 | Firefox |
| 2016-06-07 | Firefox |
| 2016-06-08 | Firefox |
| 2016-06-09 | Chrome |
| 2016-06-10 | Chrome |
| 2016-06-11 | Chrome |
| 2016-06-12 | Chrome |
| 2016-06-13 | Chrome |
| 2016-06-14 | Chrome |
| 2016-06-15 | Chrome |
| 2016-06-16 | Chrome |
+-------------+--------------+


I would like to extract the first and last occurrence of each sequence in the 'browser' column, in other words the following result:

+-------------+--------------+
| current_day | browser |
+-------------+--------------+
| 2016-05-02 | Safari |
| 2016-05-08 | Safari |
| 2016-05-09 | Opera |
| 2016-05-14 | Opera |
| 2016-05-15 | Chrome |
| 2016-05-16 | Firefox |
| 2016-05-22 | Firefox |
| 2016-05-23 | Safari |
| 2016-05-29 | Safari |
| 2016-05-30 | Opera |
| 2016-06-01 | Opera |
| 2016-06-02 | Firefox |
| 2016-06-08 | Firefox |
| 2016-06-09 | Chrome |
| 2016-06-16 | Chrome |
+-------------+--------------+


Is it possible/convenient to create a MYSQL query? Or is it better to extract all results and perform some post-processing with php?

Answer

You can use the following query:

SELECT MIN(current_day) AS start_day, 
       MAX(current_day) AS stop_day, 
       browser
FROM (
   SELECT current_day, browser,
          @grp := IF(@br = browser, @grp,
                     IF(@br := browser, @grp+1, @grp+1)) AS grp
   FROM mytable 
   CROSS JOIN (SELECT @grp := 0, @br := '') AS vars
   ORDER BY current_day) AS t
GROUP BY browser, grp

The above query uses variables, in order to identify islands of consecutive records having the same browser value. It returns a single row per browser. You have to repeat the same subquery twice and use UNION if you want to get two separate lines for each of the min/max dates.

Demo here

Comments