时间戳

首页 > 解决方案 > MySQL：查询以计算在数据范围之间找到几个月的最后一天的次数

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

问题描述

我有一个跟踪客户合同的数据库。每个合同都有一个有效期限，它作为日期存储在contract_start 和contract_end 中。

我想创建一个视图，在每个月底显示每个客户有多少合同处于活动状态。

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

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

提前致谢！

标签： mysql

解决方案

考虑以下。这不是最优雅的解决方案，它使用了一个并非绝对必要的实用程序表（如果您使用的是 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 |
+------------+----------+------------------+

推荐阅读