MySQL: Count of records with consecutive months

Question

I've searched around for this, but all the similar questions and answers are just different enough not to work.

I have a table with the following fields: person, thing, purdate. A new record is entered when a person buys each new thing.

I want to count the consecutive months that a person bought any "thing" (thing01 or thing02, it doesn't mater). If there is a break in consecutive purdays, then the count should start over.

With the data enclosed, I want to end up with this:

| Person     | Consec Days |
| person_01  | 3           |
| person_02  | 3           |
| person_02  | 2           |

I know I can get a distinct list of person, extract(year_month from purdate) -- which I've done in this SQLFIDDLE -- but I'm not sure how to then count only the consecutive records and start over at the break (like in my data where person_02 breaks between March and May.)

Here is the data:

create table records (
  person varchar(32) not null,
  thing varchar(32) not null,
  purdate datetime not null
);

insert into records (person, thing, purdate) values
  ('person_01', 'thing01', '2014-01-02'),
  ('person_01', 'thing02', '2014-01-02'),
  ('person_01', 'thing02', '2014-02-27'),
  ('person_01', 'thing02', '2014-03-27'),
  ('person_02', 'thing02', '2014-01-28'),
  ('person_02', 'thing01', '2014-02-28'),
  ('person_02', 'thing02', '2014-03-28'),
  ('person_02', 'thing02', '2014-05-29'),
  ('person_02', 'thing02', '2014-06-29')
;

Fragment

Without an order by there is no order in your table,so what is previous here in rapport with what?You`d need an id. – Doublespace

@Doublespace . . . The dates provide the ordering information needed to answer the question. – Jonejonell

Answer 1

You can do this in MySQL using variables (or very complicated correlated subqueries). In other databases, you would use window/analytic functions.

The logic is:

1. Get one row per month and person with a purchase.
2. Use variables to assign each group of consecutive months a "grouping" value.
3. Aggregate by the person and the "grouping" value.

Here is a query that has been tested on your SQL Fiddle:

select person, count(*) as numMonths
from (select person, ym, @ym, @person,
             if(@person = person and @ym = ym - 1, @grp, @grp := @grp + 1) as grp,
             @person := person,
             @ym := ym
      from (select distinct person, year(purdate)*12+month(purdate) as ym
            from records r
           ) r cross join
           (select @person := '', @ym := 0, @grp := 0) const
      order by 1, 2
     ) pym
group by person, grp;

Jonejonell

@Ryx5 . . . I indent my code the way that I want my code to be indented and read. You can read about the style in my book "Data Analysis Using SQL and Excel". I appreciate edits that fix small errors in the code. In general comments are the preferred feedback. – Jonejonell

Thank you @Gordon-Linoff. This is perfect. And thank you for the explanation of the logic ... that really helps me as a learner. – Fragment

This probably needs to be quickly reviewed for the @ym usage and it being set (I added the mysql-variables tag and am adding it to questions. A few at least to start). – Othilia

Answer 2

I used this StackOverflow answer for guidance (Check for x consecutive days - given timestamps in database)

SELECT a.person, COUNT(1) AS consecutive_months
FROM
(

  SELECT a.person, IF(b.YearMonth IS NULL, @val:=@val+1, @val) AS consec_set
  FROM (
    SELECT DISTINCT person, EXTRACT(YEAR_MONTH from purdate) as YearMonth from records
    ) a
  CROSS JOIN (SELECT @val:=0) var_init
  LEFT JOIN (SELECT DISTINCT person, EXTRACT(YEAR_MONTH from purdate) as YearMonth from records) b ON
      a.person = b.person AND
      a.YearMonth = b.YearMonth + 1
   ) a
GROUP BY a.consec_set
HAVING COUNT(1) >= 2    

Here is the SQLFiddle - http://sqlfiddle.com/#!2/cc5c3/55

Countersubject

There answer misses a requirement I didn't spell out but that @gordon-linoff caught in his answer. The data spans multiple years, so I need to be able to count across the years. This gives a maximum of 12 months, as that's all that is in a year. – Fragment