mysql连续几个月的数据,MySQL：连续几个月的记录数

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')

;

解决方案

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:

Get one row per month and person with a purchase.

Use variables to assign each group of consecutive months a "grouping" value.

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;