mysql：用于计算在一个数据范围内发现几个月的最后一天的次数的查询

30byixjq 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(240)

我有一个数据库记录客户合同。每个合同都有一个有效期，它作为日期存储在合同开始和合同结束中。
我想创建一个视图，在每个月底显示每个客户上有多少合同处于活动状态。
下面的查询显示了执行此操作的愚蠢方式，我想知道是否可以创建一个自动执行此操作的“循环”：

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

来源：https://stackoverflow.com/questions/52766895/mysql-query-to-count-how-many-times-the-last-day-of-several-months-are-found-be

2条答案

按热度按时间

sulc1iza1#

也可以尝试按年份和月份分组：

SELECT 
    customer,
    COUNT(*) AS active_contracts,
    MONTH(contract_start) as month,
    YEAR(contract_start) as year
FROM
    my_table
GROUP BY customer, year, month

赞(0）回复(0）举报 2021-06-18

x7yiwoj42#

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

展开查看全部

赞(0）回复(0）举报 2021-06-18

我来回答

mysql：用于计算在一个数据范围内发现几个月的最后一天的次数的查询

2条答案

相关问题

热门标签

最新问答