Mysql – How to collapse contiguous single day start/end date ranges in MySQL

gaps-and-islandsMySQLquery

(edited – I oversimplified the original problem)

I'm trying to figure out a MySQL query that will collapse one-day long contiguous start/end date ranges. Here is my sample input data:

INSERT INTO my_table(tag, start_date, end_date) VALUES ('AA', '2020-07-01', '2020-07-02');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('XB', '2020-07-02', '2020-07-03');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('YC', '2020-07-03', '2020-07-04');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('ZD', '2020-07-04', '2020-07-05');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('0E', '2020-07-05', '2020-07-06');

INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-06', '2020-07-15');

INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-20', '2020-07-21');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-21', '2020-07-22');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-22', '2020-07-23');

INSERT INTO my_table(tag, start_date, end_date) VALUES ('EA', '2020-07-23', '2020-07-31');

Here is the output I want:

AA,2020-07-01,2020-07-06
CA,2020-07-06,2020-07-15
CA,2020-07-20,2020-07-23
EA,2020-07-23,2020-07-31

It seems like it should be simple, but I can't figure out how to do it in pure MySQL. I've tried joining the table to itself and using various combinations of MIN() and MAX(), but the solution eludes me. I can do it in PHP code, but would prefer to let MySQL do the work.

I can't rely on the tag field for grouping (it can be anything). The date collapse should be done only on the date ranges that are 1-day long and contiguous. Date ranges longer than one day should not be collapsed. The selected tag should be the value associated with the first date in the range

Best Answer

After a little more research, I figured out the solution to this "gaps and islands" problem:

SELECT t.tag, begin AS start_date, MAX(end) AS end_date
FROM (
    SELECT
    a.*
    CASE WHEN (DATEDIFF(start_date, @prev_date) = 1 AND DATEDIFF(end_date, start_date) = 1)
        THEN @begin := @begin
        ELSE @begin := start_date
    END AS begin,
    end_date AS end,
    @prev_date := start_date
    FROM (SELECT @prev_date := NULL, @begin := NULL) AS vars
    JOIN my_table a
    ORDER BY start_date
) AS t
GROUP BY begin;

Related Solutions

Postgresql – Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

How about using with recursive

test view:

create view v as 
select *
from ( values ('foo', 2),
              ('foo', 3),
              ('foo', 4),
              ('foo', 10),
              ('foo', 11),
              ('foo', 13),
              ('bar', 1),
              ('bar', 2),
              ('bar', 3)
     ) as baz ("name", "int");

query:

with recursive t("name", "int") as ( select "name", "int", 1 as span from v
                                     union all
                                     select "name", v."int", t.span+1 as span
                                     from v join t using ("name")
                                     where v."int"=t."int"+1 )
select "name", "start", "start"+span-1 as "end", span
from( select "name", ("int"-span+1) as "start", max(span) as span
      from ( select "name", "int", max(span) as span 
             from t
             group by "name", "int" ) z
      group by "name", ("int"-span+1) ) z;

result:

 name | start | end | span
------+-------+-----+------
 foo  |     2 |   4 |    3
 foo  |    13 |  13 |    1
 bar  |     1 |   3 |    3
 foo  |    10 |  11 |    2
(4 rows)

I'd be interested to know how that performs on your billion row table.

Mysql – Optimize query execution based on two datetime columns

Thanks to everyone who took the time to respond. I got it fixed in a different way. Since the values can't overlap and mysql is efficient using one single index for ranges, what i did was find the first col1 and last col1 and use a select between. This turned out really fast because mysql now analyses only the lines in the range.

As for the aggregates which were causing problems I still had to go round them anyways because I had to execute it separately or otherwise MySQL optimizer was really inefficient. The way I did it was by selecting the results into a temporary memory table and then loop those results (cursor wasn't a choice because I already had a loop) and doing the 3 aggregate functions at once. This is was the final result (just the loop part):

myLoop: LOOP

IF (SELECT COUNT(*) FROM (SELECT
    @CarrId := carrier_id,
    @FistVTime := firstValidTIME,
    @LastVTime := lastValidTIME
FROM startStopReportTable
WHERE maxSpeed = -1) AS dummyTbl) < 1 THEN
    LEAVE myLoop;
END IF;

UPDATE startStopReportTable AS t0,
    (SELECT
        SUM(l0.distance) AS distance,
        AVG(l0.speed) AS avgspeed,
        MAX(l0.speed) AS maxspeed
        FROM
            pcoarg AS l0
        WHERE
            l0.carrier_id = @CarrId
        AND
            l0.lastValidTIME BETWEEN @FistVTime AND @LastVTime
    ) AS t1
SET
    t0.distance = t1.distance,
    t0.averageSpeed = t1.avgspeed,
    t0.maxSpeed = t1.maxspeed
WHERE t0.carrier_id = @CarrId AND t0.firstValidTIME = @FistVTime AND t0.lastValidTIME = @LastVTime;
END LOOP;

LastValidTIME is col1 startStopReportTable is temporary table

Profiling results:

96  0.00058100  UPDATE startStopReportTable AS t0,
      (SELECT
       SUM(l0.distance) AS distance,
       AVG(l0.speed) AS avgspeed,
       MAX(l0.speed) AS maxspeed
       FROM
        pcoarg AS l0
       WHERE
        l0.carrier_id = @CarrId
       AND
        l0.lastValidTI...
97  0.00045100  UPDATE startStopReportTable AS t0,
      (SELECT
       SUM(l0.distance) AS distance,
       AVG(l0.speed) AS avgspeed,
       MAX(l0.speed) AS maxspeed
       FROM
        pcoarg AS l0
       WHERE
        l0.carrier_id = @CarrId
       AND
        l0.lastValidTI...
98  0.00094900  UPDATE startStopReportTable AS t0,
      (SELECT
       SUM(l0.distance) AS distance,
       AVG(l0.speed) AS avgspeed,
       MAX(l0.speed) AS maxspeed
       FROM
        pcoarg AS l0
       WHERE
        l0.carrier_id = @CarrId
       AND
        l0.lastValidTI...

It's really a shame that the optimizer can't combine sub query aggregates in one query while it can combine them in one query.

Best Answer

Related Solutions

Postgresql – Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

Mysql – Optimize query execution based on two datetime columns

Related Question