Mysql select rows with 7 continuous dates

Question

Imagine a simple table with these columns: item_id, date
And values:

CREATE TABLE foo (item_id int, date date);

INSERT INTO foo(item_id, date)
VALUES
    ( 1, '2017-02-10' ),
    ( 2, '2017-02-10' ),
    ( 1, '2017-02-11' ),
    ( 1, '2017-02-12' ),
    ( 1, '2017-02-13' ),
    ( 2, '2017-02-13' ),
    ( 1, '2017-02-14' );

How to select item_ids that have 7 continuous day records in table?

Start and end dates are unknown. It should just be available from any starting date up to 7 continuous days after.

What's the datatype of date? Can the time component ever be anything but 00:00:00. And, are you looking for a simple list of item_ids, without any reference to the range of dates found? And, do you realize that your tet data wouldn't return anything, since there are only 5 consecutive dates there? — RDFozz
– RDFozz, Commented Aug 8, 2017 at 17:21
It's important to know about the time component simply to know if we need to remove the time component to easily compare the dates; 2017-08-08 00:00:00 is not 1 day different from 2017-08-09 00:00:01. Being able to avoid truncating the dates to ensure everything is exactly 1 day apart saves time. — RDFozz
– RDFozz, Commented Aug 8, 2017 at 18:13
Do you want exactly 7 consecutive dates? What would be the result if you have 8 consecutive dates? — Lennart - Slava Ukraini
– Lennart - Slava Ukraini, Commented Aug 9, 2017 at 5:54
You can't get 8 consecutive without 7 consecutive. So I think that satisfies the criteria in my parsing. — Evan Carroll
– Evan Carroll, Commented Aug 9, 2017 at 6:14

Evan Carroll · Accepted Answer · 2017-08-09 06:13:19Z

MySQL 8

MySQL 8 provides window functions...

SELECT item_id
FROM (
  SELECT
    item_id,
    date,
    count(coalesce(diff, 1)=1 OR null) OVER (PARTITION BY item_id ORDER BY date) seq
  FROM (
    SELECT
      item_id,
      date,
      date - lag(date) OVER (PARTITION BY item_id ORDER BY date) AS diff
    FROM foo
  ) AS t
) AS t2
GROUP BY item_id
HAVING max(seq) > 7;

Explanation

This is what we're doing on the inner.

SELECT
  item_id,
  date,
  date - lag(date) OVER (PARTITION BY item_id ORDER BY date) AS diff
FROM foo

 item_id |    date    | diff 
---------+------------+------
       1 | 2017-02-10 |     
       1 | 2017-02-11 |    1
       1 | 2017-02-12 |    1
       1 | 2017-02-13 |    1
       1 | 2017-02-14 |    1
       2 | 2017-02-10 |     
       2 | 2017-02-13 |    3
(7 rows)

Here we return the differences. What we need to now do is isolate the ones where the date difference is 1. We assume here if the result of the difference is null it is because there was no previous date to subtract, so we set it to 1. Then we if we don't have a 1, we set the value to null so count() skips it.

SELECT
  item_id,
  date,
  count(coalesce(diff, 1)=1 OR null) OVER (PARTITION BY item_id ORDER BY date) seq
FROM (
  SELECT
    item_id,
    date,
    date - lag(date) OVER (PARTITION BY item_id ORDER BY date) AS diff
  FROM foo
) AS t; 

 item_id |    date    | seq 
---------+------------+-----
       1 | 2017-02-10 |   1
       1 | 2017-02-11 |   2
       1 | 2017-02-12 |   3
       1 | 2017-02-13 |   4
       1 | 2017-02-14 |   5
       2 | 2017-02-10 |   1
       2 | 2017-02-13 |   1
(7 rows)

From this point, it's just a GROUP BY and HAVING.

This was tested in PostgreSQL because MySQL 8 wasn't out yet. If you haven't used PostgreSQL, download it for free and check it out. It's like MySQL but better in every single way.

Lennart - Slava Ukraini · Accepted Answer · 2017-08-09 06:36:41Z

A naive approach is to do 7 self joins:

select f1.item_id, f1.dt, f7.dt
from foo f1 
join foo f2 
    on date_add(f1.dt, interval 1 day) = f2.dt
    and f1.item_id = f2.item_id
join foo f3
    on date_add(f2.dt, interval 1 day) = f3.dt
    and f2.item_id = f3.item_id
join foo f4 
    on date_add(f3.dt, interval 1 day) = f4.dt
    and f3.item_id = f4.item_id
join foo f5 
    on date_add(f4.dt, interval 1 day) = f5.dt
    and f4.item_id = f5.item_id
join foo f6 
    on date_add(f5.dt, interval 1 day) = f6.dt
    and f5.item_id = f6.item_id
join foo f7 
    on date_add(f6.dt, interval 1 day) = f7.dt
    and f6.item_id = f7.item_id;

A little less naive approach would be to find the consecutive intervals and check which intervals is >= 7 days:

select item_id from (
    select lower.item_id, lower.min_dt, min(upper.max_dt) as max_dt  
    from (      
        select item_id, dt as min_dt      
        from foo f1      
        where not exists (         
            select 1 from foo f2
            where f1.item_id = f2.item_id
              and f1.dt = date_add(f2.dt, interval 1 day)
        )  
    ) as lower  
    join (
        select item_id, dt as max_dt      
        from foo f3      
        where not exists (
            select 1 from foo f4          
            where f3.item_id = f4.item_id 
              and f3.dt = date_sub(f4.dt, interval 1 day)     
        )  
    ) as upper
         on lower.item_id = upper.item_id
        and max_dt >= min_dt  
    group by lower.item_id, lower.min_dt
) as x 
where datediff(max_dt, min_dt) >= 7;

As someone mentioned in the comments, this kind of problem is a breeze with window functions.

LoztInSpace · Accepted Answer · 2017-08-09 13:32:02Z

2

    select 
    item_id
    from foo 
    where
    (
      select count(distinct date) from foo d2 
       where d2.date>=foo.date and d2.date<=date_add(foo.date, interval 6 day)
       and foo.item_id=d2.item_id -- if IDs need to be the same
    ) =7
    order by item_id

So take each date for each row, add 6 days to make a 7 day period. If there are 7 distinct dates in that period then they must be continuous.

edited Aug 9, 2017 at 13:32

answered Aug 9, 2017 at 7:43

LoztInSpace

9234 silver badges8 bronze badges

You are missing the correlation between the outer and inner item_id in your query

Lennart - Slava Ukraini
– Lennart - Slava Ukraini

2017-08-09 08:01:56 +00:00
Commented Aug 9, 2017 at 8:01
1

@Lennart I certainly messed up the translation between SQL Server and MySQL so I've tried to fix that. I think it works though.

LoztInSpace
– LoztInSpace

2017-08-09 08:08:33 +00:00
Commented Aug 9, 2017 at 8:08
1

No, you will count any item_id in that date range. I believe you must add the predicate and foo.item_id=d2.item_id to the sub select in your where clause. Clever solution btw.

Lennart - Slava Ukraini
– Lennart - Slava Ukraini

2017-08-09 08:28:22 +00:00
Commented Aug 9, 2017 at 8:28
1

You can try: delete from foo; INSERT INTO foo(item_id, dt) VALUES ( 1, '2017-02-04' ), ( 2, '2017-02-05' ), ( 3, '2017-02-06' ), ( 3, '2017-02-07' ), ( 3, '2017-02-08' ), ( 1, '2017-002-09' ), ( 1, '2017-02-10' ); There are 7 consecutive dates, but for different item_id:s.

Lennart - Slava Ukraini
– Lennart - Slava Ukraini

2017-08-09 08:36:31 +00:00
Commented Aug 9, 2017 at 8:36
Ah ok. The question did not actually state that the the consecutive dates should be of the same ID. Easy enough to add the condition you suggest if that's the case.

LoztInSpace
– LoztInSpace

2017-08-09 10:16:54 +00:00
Commented Aug 9, 2017 at 10:16

Add a comment |

Stack Exchange Network

Mysql select rows with 7 continuous dates

3 Answers 3

MySQL 8

Explanation

Your Answer

Hot Network Questions

Mysql select rows with 7 continuous dates

3 Answers 3

MySQL 8

Explanation

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions