Geek Answers Handbook

MySQL: number of records with consecutive months

I searched for this, but all of the similar questions and answers are just different so as not to work.

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

I want to count the consecutive months that a person bought any "thing" (thing01 or thing02, she is not a mater). If there is a gap in consecutive days, then the count should begin.

With the attached data, I want to end with this:

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

I know that I can get an excellent list of people, extract (year_month from purdate) - which I made in this SQLFIDDLE - but I'm not sure how then to count only sequential records and start from the break (as in my data, where person_02 breaks between March and mayem.)

Here are 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') ;
+6
source share
2 answers

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

The logic is this:

  • Get one line per month and the person with the purchase.
  • Use variables to assign a grouping value to each group of consecutive months.
  • The totality for a person and the importance of grouping.

Here is the query that was tested on your SQL script:

 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;
+5
source

I used this StackOverflow answer for guidance ( Check x days in a row - 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 SQLFiddle - http://sqlfiddle.com/#!2/cc5c3/55

+3
source

More articles:


All Articles