How do I group repeated values to find the start and end of series?

Question

I have a table with the following data.

serial   date     flag
1        1 aug    1
2        2 aug    1
3        3 aug    0   
4        4 aug    0
5        5 aug    1
6        6 aug    1
7        7 aug    1

What I need is this result set:

from     to       flag
1-aug    2-aug    1
3-Aug    4-Aug    0
5-Aug    7-Aug    1

When I group on flag all 1 values are combined. Any suggestions?

Answer 1

This detects the interval start and end dates, unoptimized. It finds the dates where the flag values flip, then for each of those it finds the date after of the flip back.

Setup:

CREATE TABLE MyTable (
     serial INT
    ,`date` DATE
    ,`flag` INT
)
;

INSERT INTO MyTable
VALUES
( 1,'2010-08-01', 1 ),
( 2,'2010-08-02', 1 ),
( 3,'2010-08-03', 0 ),
( 4,'2010-08-04', 0 ),
( 5,'2010-08-05', 1 ),
( 6,'2010-08-06', 1 ),
( 7,'2010-08-07', 1 )
;

Query:

SELECT
    x.StartDate AS `from`
    , MIN( y.EndDate ) AS `to`
    , x.flag
FROM
    ( SELECT
        tab1.`date` AS StartDate
        , tab1.flag
    FROM
        MyTable tab1
    LEFT JOIN
        MyTable tab2
    ON tab1.`date` = DATE_ADD( tab2.`date`, INTERVAL 1 DAY )
    WHERE
        tab1.flag != IFNULL( tab2.flag, tab1.Flag - 1 )
    ) x,
    ( SELECT
        tab1.`date` AS EndDate
    FROM
        MyTable tab1
    LEFT JOIN
        MyTable tab2
    ON tab1.`date` = DATE_SUB( tab2.`date`, INTERVAL 1 DAY )
    WHERE
        tab1.flag != IFNULL( tab2.flag, tab1.Flag - 1 )
    ) y
WHERE
    y.EndDate >= x.StartDate
GROUP BY
    x.StartDate
HAVING
    x.StartDate = MIN( x.StartDate ) 
ORDER BY
    1

Result:

from        to           flag
2010-08-01  2010-08-02      1
2010-08-03  2010-08-04      0
2010-08-05  2010-08-07      1