每月活跃成员的计数

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

所以我需要一个月的活动成员的报告...也就是说，在给定月份中有多少成员在给定范围内有多少成员...因此，预期结果：

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, ...。

如果您不想使用第二个表，那么我恐怕您将必须编写一个存储过程或后端应用程序，该应用程序会循环每年和月，在每个循环中都会选择行计数在当前年/月之后开始之前和结束，然后写入某个地方（可能是另一个表或数组）以进行进一步处理。

优势是数据库不必在两个表之间构建跨产品。缺点是这不是一个疑问。