mysql - 按月统计的活跃成员数...

Question

正在寻找一个优雅的解决方案...我发现我所做的任何事情都非常丑陋...我只是想绘制每个月有多少用户活跃(在他们的开始/结束日期内)。自然地，我需要计数为 0，其中月份在该报告日期范围内没有活跃用户...

TABLE MEMBERSHIPS
Name, Start_date, End_date
Joe,  2017/02/01, 2017/04/01
Bob,  2017/03/01, 2017/05/01
Moe,  2017/03/01, 2017/05/01
Lou,  2017/04/01, 2017/05/01

所以我需要一份按月统计的活跃成员(member)报告...也就是说，在给定范围内计算给定月份的活跃成员(member)数量...所以预期结果:

Reoort for 2017/01/01 to 2017/06/01

Month, Count
 01  |  0
 02  |  1
 03  |  3
 04  |  4
 05  |  3
 06  |  0

我确定这是一个常见的用例...只是找不到任何好的...

谢谢，

Answer 1

这里的问题是缺少创建报告所需的部分数据。尽管没有该月的数据，但数据库应该如何知道您要包括该月 01？

解决此问题的一种简单方法是创建一个表格，其中包含报告应包括的所有月份。为了进一步简化事情，而不是年份和月份本身，只需将位于相应月份的任意日期写入该表。

Table ReportMonths:
ReportMonth datetime
2017-01-15
2017-02-15
2017-03-15
2017-14-15
2017-05-15
2017-06-15

使用新表创建报告:

SELECT
  ReportMonths.ReportMonth,
  COUNT(*)
FROM
  ReportMonths, MEMBERSHIPS
WHERE
  (MEMBERSHIPS.Start_date <= ReportMonths.ReportMonth) AND
  (MEMBERSHIPS.End_date > ReportMonths.ReportMonth)
GROUP BY
  ReportMonths.ReportMonth;

免责声明

1) 我现在手头没有MySQL，所以无法测试代码。如有语法错误请见谅。不过，它应该为您提供解决问题的一般方法。

2) 如果您有数百万行，该解决方案可能会很慢或者可能会使 MySQL 崩溃，因为它必须首先构建两个表的叉积。如果是这种情况，我们可以构建一个性能更好的更复杂的查询。如果您有兴趣，请告诉我们...

免责声明结束

上面的解决方案会将有问题的月份作为日期返回。如果你不想这样，那么 SELECT YEAR(ReportMonths.ReportMonth), MONTH(ReportMonths.ReportMonth), ... 而不是 SELECT ReportMonths.ReportMonth, ....

如果您不想使用第二张表，恐怕您将不得不编写一个存储过程或后端应用程序，每年和每月循环一次，并在每个循环中选择行数在当前年/月之前开始和之后结束，然后将该计数写入某处，可能是另一个表或数组，以供进一步处理。

优点是数据库不必在两个表之间构建叉积；缺点是这不是单查询解决方案。