MySQL:连续几个月的记录数 [英] MySQL: Count of records with consecutive months

问题描述

我已经搜索了这个问题，但是所有类似的问题和答案都完全不同，无法正常工作.

我有一个包含以下字段的表格:人，事物，purdate.一个人购买每件新东西时，都会输入一条新记录.

我想计算一个人连续购买东西"(thing01或thing02，没有关系)的月份.如果连续的休息日有休息，那么计数应该重新开始.

随数据附上，我想以此结尾:

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

我知道我可以得到一个人的清单，摘录(来自purdate的year_month)-我已经在 SQLFIDDLE -但我不确定如何只对连续记录进行计数并在中断处重新开始(例如在我的数据中，person_02在3月至5月之间中断).

以下是数据:

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

解决方案

您可以在MySQL中使用变量(或非常复杂的相关子查询)进行此操作.在其他数据库中，您将使用窗口/分析功能.

逻辑是:

1. 每月获得一排，每人有一次购买.
2. 使用变量为连续几个月的每个组分配一个分组"值.
3. 按人员和分组"值进行汇总.

以下是已在您的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;

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:

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;

这篇关于MySQL:连续几个月的记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！