按第一次和最后一次出现分组 [英] 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       |
    +-------------+--------------+

是否可以/方便地创建一个 MYSQL 查询?还是提取所有结果并用php进行一些后处理更好?

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

推荐答案

您可以使用以下查询:

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

上述查询使用变量，以识别具有相同browser 值的连续记录的孤岛.它为每个浏览器返回一行.如果您想为每个最小/最大日期获得 两 行，您必须重复相同的子查询两次并使用 UNION.

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.

此处演示

这篇关于按第一次和最后一次出现分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！