MySQL：连续月份记录数

Question

我搜寻了相关资源，但所有类似的问题和答案都不太适用。 我有一个表格，其字段如下：person，thing，purdate。每当一个人购买一件新物品时，就会输入一条新记录。我想计算一个人购买任何“物品”（thing01或thing02，无论哪个）的连续月数。如果在连续购买日期中间有间断，则计数应该重新开始。使用以上数据，我希望得到以下结果：

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

我知道我可以在这个SQLFIDDLE中，提取purdate的年月份来获得一个人的不同列表，但我不确定如何仅计算连续记录并从断点重新开始（就像在我的数据中person_02在三月和五月之间中断一样）。

以下是数据:

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

- Crit

---

没有order by语句，你的表中就没有顺序，那么这里的previous与什么相关呢？你需要一个ID。 - Mihai

@Mihai...这些日期提供了回答问题所需的排序信息。 - Gordon Linoff

Answer 1

您可以使用变量（或非常复杂的相关子查询）在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;

- Gordon Linoff

---

@Ryx5 . . . 我会按照我想要代码缩进和阅读的方式进行缩进。你可以在我的书《使用SQL和Excel进行数据分析》中了解这种风格。我欣赏修复代码中小错误的编辑。总的来说，评论是首选的反馈方式。 - Gordon Linoff

谢谢@Gordon-Linoff。这太完美了。还有感谢您对逻辑的解释...这对我作为学习者真的很有帮助。 - Crit

这可能需要快速审查 @ym 的使用情况以及它是否被设置（我添加了 mysql-variables 标签并将其添加到问题中。至少先从几个开始）。 - Drew

Answer 2

我使用了这个StackOverflow答案作为指导（检查在数据库中给定的时间戳所涵盖的x个连续天数）。

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    

这是 SQLFiddle 的链接 - http://sqlfiddle.com/#!2/cc5c3/55。

- Jeffrey Linwood

---

他们的答案忽略了一个我没有明确说明但@gordon-linoff在他的答案中捕捉到的要求。数据跨越多年，因此我需要能够跨年计数。这给出了最大的12个月，因为一年只有12个月。 - Crit