MySql:选择按月分组的事件订阅

Question

我有以下带有订阅的 MySQL 数据库表。每个订阅作为开始日期和结束日期。

id  | start_date | end_date
  1 | 2017-01-01 | 2017-07-01
  2 | 2017-01-15 | 2017-07-12
  3 | 2017-02-01 | 2017-08-01
  4 | 2017-03-01 | 2017-08-01
  5 | 2017-03-12 | 2017-08-12
  6 | 2017-03-30 | 2017-08-30
  7 | 2017-05-01 | 2017-11-01
  8 | 2017-06-01 | 2017-12-01
  9 | 2017-07-01 | 2018-01-01
 10 | 2017-08-01 | 2018-02-01
 11 | 2018-01-01 | 2018-07-01
 12 | 2018-02-01 | 2018-08-01
 13 | 2018-03-01 | 2018-09-01
... |        ... |        ...

我想选择每个月内的所有有效订阅。这可以用一个 SQL 查询实现吗？我想知道:一月、二月、三月等有多少活跃订阅？例如，仅针对 2017 年 6 月的查询将是:

SELECT COUNT(*) FROM table 
WHERE start_date <= '2017-06-30' AND 
      end_date >= '2017-06-01'

我希望我的文字是可以理解的。

Answer 1

此方法使用日历表，其中每个月都由该月的第一天表示。然后，我们只需使用重叠范围将此日历表左联接到您当前的表，即可找到每个月的订阅数。

SELECT
    c.month,
    COUNT(t.start_date) AS num_subscriptions
FROM
(
    SELECT '2017-01-01' AS month UNION ALL
    SELECT '2017-02-01' UNION ALL
    ...
    SELECT '2018-12-01'
) c
LEFT JOIN yourTable t
    ON c.month <= t.end_date AND LAST_DAY(c.month) >= t.start_date
GROUP BY
    c.month;

Demo