2

I have table where I am storing some sequential data that indicate occurrences of some event given day.

id     from_id        to_id   date
1      2              1       2014-08-01
2      2              1       2014-08-02

3      2              1       2014-08-04
4      2              1       2014-08-05
6      2              1       2014-08-06

Sometimes event hasn't got place. Then I would like to get min and max from every uninterrupted sequences of records by data. According to example data it should return

from_id        to_id   min            max
2              1       2014-08-01     2014-08-02
2              1       2014-08-04     2014-08-06

If I would do simple group by with min and max

Select from_id, to_id, min(date), max(date) from events group by from_id, to_id 

it would return

from_id        to_id   min            max
2              1       2014-08-01     2014-08-06

How can I do that in MySQL?

1
  • What version of Mysql?
    – Lennart
    Apr 24 '19 at 6:28
4
SELECT from_id,to_id,
  CONCAT_WS(' - ',
    MIN(dt),
    CASE WHEN MAX(dt)>MIN(dt) THEN MAX(dt) END
  ) As intervals
FROM (
  SELECT from_id,to_id,
    CASE WHEN dt=@last_ci+INTERVAL 1 DAY THEN @n ELSE @n:=@n+1 END AS g,
    @last_ci := dt As dt
  FROM
    t, (SELECT @n:=0) r
  ORDER BY
    dt
) s
GROUP BY
  g

FIDDLE

3
  • it fails when used with ORM tool like ebean, didn't find escape char for :=
    – Rajat
    Apr 24 '19 at 5:14
  • You should add from_id,to_id to GROUP BY. If you add more from_id, to_id pairs you will get non-deterministic answers
    – Lennart
    Apr 24 '19 at 7:10
0

Rough sketch for a standard SQL92 solution, if you only intend to use MySQL @Mihai solution using variables will probably be a lot more efficient:

SELECT lb.from_id, lb.to_id, MAX(lb.dt) as from_date, ub.dt as to_date 
FROM (
    -- find start
    SELECT from_id, to_id, dt 
    FROM t t1
    WHERE NOT EXISTS (
        SELECT 1 
        FROM t t2 WHERE T2.dt = DATE_SUB(t1.dt,INTERVAL 1 DAY)
    )
) as lb
JOIN (
    -- find end
    SELECT FROM_ID, to_ID, dt FROM t t3
    WHERE NOT EXISTS (
        SELECT 1 
        FROM t t4 
        WHERE t4.dt = DATE_add(t3.dt,INTERVAL 1 DAY)
    )
) as Ub
    -- pair start with end
    ON lb.dt < ub.DT
   AND lb.from_id = ub.from_id
   AND lb.to_id = ub.to_id
GROUP BY lb.from_id, lb.to_id, ub.dt 

;

If you are using a newer version of MySQL, it is much more efficient to use window functions. Search for island and gaps for examples

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.