CODEWITHSUNDEEP
mysqlsqldate
dean2020
dean2020

Reputation: 665

MySQL count / track streaks or consecutive dates - follow up

This is a follow up question to MySQL count / track streaks or consecutive dates

The solution provided by Matt to my earlier question works great, but I'm running into an issue now that I'm dealing with 1 extra column (prod_cond). A product can be used or new, and will be listed under the same prod_id. In these cases the streaks are not calculated correctly anymore.

I created an example here: http://sqlfiddle.com/#!9/3f04c3/17

I haven't been able to get them to display correctly with this additional column.

+-----+------------+------------+-----------------------------------+
| id  | seller_id  | prod_id    | prod_cond    | date               |
+-----+------------+------------------------------------------------+
|   1 | 283        | 4243       | 1           | 2016-10-10 23:55:01 |
|   2 | 283        | 4243       | 2           | 2016-10-10 02:01:06 |
|   3 | 283        | 4243       | 1           | 2016-10-11 23:55:06 |
|   4 | 283        | 4243       | 2           | 2016-10-11 23:55:07 |
|   5 | 283        | 4243       | 1           | 2016-10-12 23:55:07 |
|   6 | 283        | 4243       | 2           | 2016-10-13 23:55:07 |
|   7 | 283        | 4243       | 1           | 2016-10-14 23:55:07 |
|   8 | 283        | 4243       | 2           | 2016-10-14 23:57:06 |
|   9 | 283        | 4243       | 1           | 2016-10-15 23:57:06 |
|  10 | 283        | 4243       | 2           | 2016-10-15 23:57:06 |
+-----+------------+------------+-------------+---------------------+

So basically I need to identify each block of consecutive dates for each seller (seller_id) selling products (prod_id) with product condition (prod_cond) new (1) or (2) used.

This is what the result should look like:

+------------+---------+---------+----------------+---------------+
| seller_id  | prod_id | cond_id | streak in days | begin streak  | 
+------------+---------+---------+----------------+---------------+
| 283        | 4243    | 1       | 3              | 2016-10-10    |
| 283        | 4243    | 1       | 2              | 2016-10-14    |
| 283        | 4243    | 2       | 2              | 2016-10-10    |
| 283        | 4243    | 2       | 3              | 2016-10-13    |
+------------+---------+---------+----------------+---------------|

But as you can see here: http://sqlfiddle.com/#!9/3f04c3/17 It is not working correctly.

Upvotes: 0

Views: 138

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

In MySQL, you would do this using variables:

select seller_id, prod_id, cond_id, count(*) as numdays,
       min(date), max(date)
from (select t.*,
             (@rn := if(@grp = concat_ws(':', seller_id, prod_id, cond_id), @rn + 1,
                         if(@grp := concat_ws(':', seller_id, prod_id, cond_id), @rn + 1, @rn + 1)
                        )
             ) rn
      from transact t cross join
           (select @grp := 0, @rn := '') params
      order by seller_id, prod_id, cond_id, date
     ) t
group by seller_id, prod_id, cond_id, date_sub(date(date), interval rn day)

The idea is that for each group -- based on seller, product, and condition -- the query enumerates the dates. Then, the date minus the enumerated value is constant for consecutive dates.

Here is a SQL Fiddle showing it working.

Upvotes: 1

Related Questions