MySQL:查询以计算在数据范围之间找到几个月的最后一天的次数

Question

我有一个跟踪客户契约(Contract)的数据库。每个契约(Contract)都有一个有效期，这作为日期存储在 contract_start 和 contract_end 中。

我想创建一个 View ，在每个月底显示每个客户的活跃契约(Contract)数量。

以下查询显示了执行此操作的愚蠢方法，我想知道是否可以创建一个自动执行此操作的“循环”:

SELECT 
    '2018-01-31' AS snapshot_day,
    customer,
    COUNT(*) AS active_contracts
FROM
    my_table
WHERE
    contract_start < '2018-02-01'
        AND contract_end >= '2018-02-01'
GROUP BY 2

UNION SELECT 
    '2018-02-28' AS snapshot_day,
    customer,
    COUNT(*) AS active_contracts
FROM
    my_table
WHERE
    contract_start < '2018-03-01'
        AND contract_end >= '2018-03-01'
GROUP BY 2

UNION SELECT 
    '2018-03-30' AS snapshot_day,
    customer,
    COUNT(*) AS active_contracts
FROM
    my_table
WHERE
    contract_start < '2018-04-01'
        AND contract_end >= '2018-04-01'
GROUP BY 2

.
.
.

and so on

示例输入:

customer | contract_start | contract_end
CustA    | 2018-01-22     | 2019-01-22
CustA    | 2018-03-15     | 2019-03-15
CustA    | 2018-07-10     | 2019-07-10
CustA    | 2018-09-08     | 2018-12-10
CustB    | 2018-02-17     | 2018-11-17
CustB    | 2018-05-13     | 2019-05-13
CustB    | 2018-10-01     | 2019-10-01
CustB    | 2018-12-25     | 2019-12-25

期望的输出:

snapshot_day | customer | active_contracts
2018-01-31   | CustA    | 1
2018-02-28   | CustA    | 1
2018-03-31   | CustA    | 2
2018-04-30   | CustA    | 2
2018-05-31   | CustA    | 2
2018-06-30   | CustA    | 2
2018-07-31   | CustA    | 3
2018-08-31   | CustA    | 3
2018-09-30   | CustA    | 4
2018-10-31   | CustA    | 4
2018-11-30   | CustA    | 4
2018-12-31   | CustA    | 3
2018-01-31   | CustB    | 0
2018-02-28   | CustB    | 1
2018-03-31   | CustB    | 1
2018-04-30   | CustB    | 1
2018-05-31   | CustB    | 2
2018-06-30   | CustB    | 2
2018-07-31   | CustB    | 2
2018-08-31   | CustB    | 2
2018-09-30   | CustB    | 2
2018-10-31   | CustB    | 3
2018-11-30   | CustB    | 2
2018-12-31   | CustB    | 2

提前致谢!

Answer 1

请考虑以下事项。这不是最优雅的解决方案，它使用了一个并非绝对必要的实用程序表(如果您使用的是 MySQL 8 或更高版本，则更不用说)。

但是，子查询的使用有望使解决方案的各个步骤变得相当容易理清。

此外，我的结果与您的不同。这可能是因为我考虑了该月最后一天有多少活跃合约，而您实际上在寻找稍微不同的东西。同样，这可能是因为您只是犯了一个错误 ;-)

最后，如果你想为自己制作一个日历表，那里有很多教程...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(customer VARCHAR(12) NOT NULL
,contract_start DATE NOT NULL
,contract_end DATE NOT NULL
,PRIMARY KEY(customer,contract_start)
);

INSERT INTO my_table VALUES
('CustA','2018-01-22','2019-01-22'),
('CustA','2018-03-15','2019-03-15'),
('CustA','2018-07-10','2019-07-10'),
('CustA','2018-09-08','2018-12-10'),
('CustB','2018-02-17','2018-11-17'),
('CustB','2018-05-13','2019-05-13'),
('CustB','2018-10-01','2019-10-01'),
('CustB','2018-12-25','2019-12-25');

SELECT a.snapshot
     , b.customer
     , COUNT(*) active_contracts
  FROM 
     ( SELECT DISTINCT LAST_DAY(x.dt) snapshot
                  FROM calendar x
                  JOIN 
                     ( SELECT LAST_DAY(MIN(contract_start)) range_start
                            , LAST_DAY(MAX(contract_end)) range_end 
                         FROM my_table
                     ) y
                    ON x.dt BETWEEN range_start AND range_end
     ) a
  JOIN my_table b
    ON a.snapshot BETWEEN b.contract_start AND b.contract_end
 GROUP
    BY customer, snapshot     
 ORDER 
    BY customer, snapshot;

+------------+----------+------------------+
| snapshot   | customer | active_contracts |
+------------+----------+------------------+
| 2018-01-31 | CustA    |                1 |
| 2018-02-28 | CustA    |                1 |
| 2018-03-31 | CustA    |                2 |
| 2018-04-30 | CustA    |                2 |
| 2018-05-31 | CustA    |                2 |
| 2018-06-30 | CustA    |                2 |
| 2018-07-31 | CustA    |                3 |
| 2018-08-31 | CustA    |                3 |
| 2018-09-30 | CustA    |                4 |
| 2018-10-31 | CustA    |                4 |
| 2018-11-30 | CustA    |                4 |
| 2018-12-31 | CustA    |                3 |
| 2019-01-31 | CustA    |                2 |
| 2019-02-28 | CustA    |                2 |
| 2019-03-31 | CustA    |                1 |
| 2019-04-30 | CustA    |                1 |
| 2019-05-31 | CustA    |                1 |
| 2019-06-30 | CustA    |                1 |
| 2018-02-28 | CustB    |                1 |
| 2018-03-31 | CustB    |                1 |
| 2018-04-30 | CustB    |                1 |
| 2018-05-31 | CustB    |                2 |
| 2018-06-30 | CustB    |                2 |
| 2018-07-31 | CustB    |                2 |
| 2018-08-31 | CustB    |                2 |
| 2018-09-30 | CustB    |                2 |
| 2018-10-31 | CustB    |                3 |
| 2018-11-30 | CustB    |                2 |
| 2018-12-31 | CustB    |                3 |
| 2019-01-31 | CustB    |                3 |
| 2019-02-28 | CustB    |                3 |
| 2019-03-31 | CustB    |                3 |
| 2019-04-30 | CustB    |                3 |
| 2019-05-31 | CustB    |                2 |
| 2019-06-30 | CustB    |                2 |
| 2019-07-31 | CustB    |                2 |
| 2019-08-31 | CustB    |                2 |
| 2019-09-30 | CustB    |                2 |
| 2019-10-31 | CustB    |                1 |
| 2019-11-30 | CustB    |                1 |
+------------+----------+------------------+