3

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.

5
  • 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
    Aug 8 '17 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
    Aug 8 '17 at 18:13
  • This would be so simple if you had window functions Aug 9 '17 at 5:37
  • Do you want exactly 7 consecutive dates? What would be the result if you have 8 consecutive dates?
    – Lennart
    Aug 9 '17 at 5:54
  • You can't get 8 consecutive without 7 consecutive. So I think that satisfies the criteria in my parsing. Aug 9 '17 at 6:14
5

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.

2

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.

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.

5
  • You are missing the correlation between the outer and inner item_id in your query
    – Lennart
    Aug 9 '17 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. Aug 9 '17 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
    Aug 9 '17 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
    Aug 9 '17 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. Aug 9 '17 at 10:16

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.