mysql grp 添加节点,mysql：按第一次和最后一次出现分组

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?

解决方案

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.