时间戳

首页 > 解决方案 > MySQL 查询以在项目期间将不同的项目 ID 计数为总和

mysql - MySQL 查询以在项目期间将不同的项目 ID 计数为总和

问题描述

我有一个如下所示的数据集：

+------------+----------+-------------+------------+-----------+
| Project_ID | Location |   status    | Start Date | End Date  |
+------------+----------+-------------+------------+-----------+
|     122234 | aaaa     | ON HOLD     | 1/1/2019   | 1/10/2019 |
|     122235 | aaab     | IN PROGRESS | 1/1/2019   | 1/15/2019 |
|     122236 | aaac     | ON HOLD     | 1/5/2019   | 1/10/2019 |
|     122237 | aaad     | IN PROGRESS | 1/8/2019   | 2/1/2019  |
|     122238 | aaae     | IN PROGRESS | 1/12/2019  | 2/5/2019  |
|     122239 | aaaf     | ON HOLD     | 1/5/2019   | 1/15/2019 |
|     122240 | aaag     | IN PROGRESS | 1/1/2019   | 1/10/2019 |
+------------+----------+-------------+------------+-----------+

这是一个非常小的数据样本，我有 2020 年的数据。

我需要在开始和结束之间的日期范围内计算每个正在进行的 ProjectID，并进行列调用 IN PROGRESS COUNT。我无法弄清楚如何仅在开始日期和结束日期之间进行计数。

这是经过思考后的查询，看看我是否可以使用它：

SELECT 
    Project_ID,
    Location,
    Status, 
    'Start Date' AS Date_Type,
    `Start Date` AS Date,
    1 AS Count
FROM 
    Table
WHERE
    Status NOT IN ('COMPLETED')
AND
    `Snapshot` = (SELECT MAX(`Snapshot`) FROM Table)
GROUP BY 
    Project_ID
UNION ALL
SELECT 
    Project_ID,
    Location,
    Status, 
    'End Date' AS Date_Type,
    `End Date` AS Date,
    -1 AS Count
FROM 
    Table
WHERE
    Status NOT IN ('COMPLETED')
AND
    `Snapshot` = (SELECT MAX(`Snapshot`) FROM Table)
GROUP BY 
    Project_ID

这应该允许我使用 running_sum 来获取活动项目。

标签： mysql

解决方案

如果某个状态在日期范围内从进行中变为暂停，则不清楚您想要什么。但这的核心是，如果开始和结束日期在范围内，或者开始日期在范围内，或者结束日期在范围内，或者范围在开始和结束日期之间，那么你就会感兴趣。

所以给定

MariaDB [sandbox]> SELECT * FROM T;
+------------+----------+-------------+------------+------------+
| Project_ID | Location | status      | Start_Date | End_Date   |
+------------+----------+-------------+------------+------------+
|     122234 | aaaa     | ON HOLD     | 2019-01-01 | 2019-01-10 |
|     122235 | aaab     | IN PROGRESS | 2019-01-01 | 2019-01-15 |
|     122236 | aaac     | ON HOLD     | 2019-01-05 | 2019-01-10 |
|     122237 | aaad     | IN PROGRESS | 2019-01-08 | 2019-02-01 |
|     122238 | aaae     | IN PROGRESS | 2019-01-12 | 2019-02-05 |
|     122239 | aaaf     | ON HOLD     | 2019-01-05 | 2019-01-15 |
|     122240 | aaag     | IN PROGRESS | 2019-01-01 | 2019-01-10 |
|     122240 | aaag     | on hold     | 2019-01-11 | 2019-01-12 |
+------------+----------+-------------+------------+------------+
8 rows in set (0.00 sec)

这可能会

SELECT *
FROM T
WHERE STATUS = 'IN PROGRESS' AND
        (
        (START_DATE BETWEEN '2019-01-01' AND '2019-01-02') OR
        (END_DATE BETWEEN '2019-01-01' AND '2019-01-02') OR
        (START_DATE <= '2019-01-01' AND END_DATE >= '2019-01-02')
        );

+------------+----------+-------------+------------+------------+
| Project_ID | Location | status      | Start_Date | End_Date   |
+------------+----------+-------------+------------+------------+
|     122235 | aaab     | IN PROGRESS | 2019-01-01 | 2019-01-15 |
|     122240 | aaag     | IN PROGRESS | 2019-01-01 | 2019-01-10 |
+------------+----------+-------------+------------+------------+
2 rows in set (0.00 sec)

推荐阅读