MySQL-任務管理器-S-Curve-總結任務

我正在使用 PHP 和 MySQL 以及其他語言構建專案管理器系統，并且我正在努力查詢我的資料以創建 S 曲線圖。在我的資料庫中，我有下表名為“任務”

要繪制圖表，我需要在任務表表的最小日期和最大日期之間每天都有一行，即使任務表中不存在此確切日期（例如：2021-12-04 - 它沒有寫在桌子上，但它發生在 2021-12-01 和 2021-12-07 之間）。而且，對于每一天，我需要計算日期之前存在的所有任務的持續時間，并且僅對已完成的任務計算相同的持續時間（我將嘗試用示例更好地解釋）：

說明：最后一天 (2020-12-07) 是出現在任務表上的最后一天。對于那一天，我要數 17 個“天數任務”（任務 1 為 6 天，因為所有 6 天的持續時間都發生在 2020 年 12 月 7 日之前 任務 2 的所有天數 持續時間為任務 3) 已完成的“天數任務”也是如此，但只計算已完成任務的天數 - 因此，不考慮任務 1，因為它是“待定”）。

老實說，我不知道如何使用 MySQL 執行該操作。我嘗試了一堆查詢，但似乎沒有任何效果如我所愿。

我能夠使用以下方法獲得包含所有日期的表格：

@i<-1;
SELECT DATE(ADDDATE((SELECT MIN(Beginning_Date) from tasks), INTERVAL @i:=@i 1 DAY)) AS date FROM `tasks`
HAVING 
@i < DATEDIFF((SELECT MAX(End_date) from tasks), (SELECT MIN(Beginning_Date) from tasks )))

但是，我不知道如何計算這些日期的任務。

有誰知道該怎么做？

uj5u.com熱心網友回復：

您的所需輸出表中似乎存在一些錯誤。如果我理解正確，以下查詢有效，但這需要 MySQL 8。第一個CTE是您的示例資料。第二個 CTE 是遞回 CTE，用于提供日期范圍。然后查詢根據日期和狀態進行一些聚合。

WITH RECURSIVE `Tasks` (`ID`, `Task`, `Beginning_Date`, `End_date`, `Status`) AS
(
    SELECT 1, 'Task-1', DATE('2021-12-01'), DATE('2021-12-06'), 'Pendent' UNION ALL
    SELECT 2, 'Task-2', DATE('2021-12-02'), DATE('2021-12-07'), 'Completed' UNION ALL
    SELECT 3, 'Task-3', DATE('2021-12-03'), DATE('2021-12-07'), 'Completed'
),
`dates` (`date`) AS
(
  SELECT MIN(`Beginning_Date`) FROM `Tasks`
  UNION ALL
  SELECT `date`   INTERVAL 1 DAY FROM `dates`
  WHERE `date`   INTERVAL 1 DAY <= (SELECT MAX(`End_date`) FROM `Tasks`)
)
SELECT
    `d`.`date` AS `Day`,
    SUM(DATEDIFF(LEAST(`d`.`date`, `t`.`End_date`), `t`.`Beginning_Date`)   1) AS `Days_Tasks`,
    SUM(IF(`t`.`Status` = 'Completed', DATEDIFF(LEAST(`d`.`date`, `t`.`End_date`), `t`.`Beginning_Date`)   1, 0)) AS `Days_Tasks_Completed`
FROM `dates` `d`
INNER JOIN `Tasks` `t` ON `t`.`Beginning_Date` <= `d`.`date`
GROUP BY `d`.`date`
ORDER BY `d`.`date`;

這是一個db<>fiddle

為了更好地理解正在發生的事情，我們可以只查看 Task-1 的 JOIN 結果

/* The CTEs are as above but left out for brevity */
SELECT
    `d`.`date` AS `Day`, t.*,
    /*
        we need to add one as MAX Days_Tasks is inclusive
        of Beginning_Date and End_date
    */
    DATEDIFF(`d`.`date`, `t`.`Beginning_Date`)   1 AS `Days_Tasks_1`,
    /*
        we need to use the LEAST of d.date and t.End_date so
        we do not continue to count days beyond the t.End_date
    */
    DATEDIFF(LEAST(`d`.`date`, `t`.`End_date`), `t`.`Beginning_Date`)   1 AS `Days_Tasks_2`
FROM `dates` `d`
INNER JOIN `Tasks` `t` ON `t`.`Beginning_Date` <= `d`.`date`
WHERE `t`.`ID` = 1
ORDER BY `d`.`date` ASC;

回傳 -

Days_Tasks_1 看起來不錯，直到我們到達最后一行，即使 Task-1 已經結束，它仍在繼續增加。要糾正這一點，我們需要使用最小二乘的Day和End_date，所以我們不要繼續超出任務的END_DATE。見 Days_Tasks_2

現在，如果我們僅查看 2021-12-07 天的 JOIN 結果

/* CTEs omitted as above */
SELECT
    `d`.`date` AS `Day`, t.*,
    DATEDIFF(LEAST(`d`.`date`, `t`.`End_date`), `t`.`Beginning_Date`)   1 AS `Days_Tasks`,
    IF(`t`.`Status` = 'Completed', DATEDIFF(LEAST(`d`.`date`, `t`.`End_date`), `t`.`Beginning_Date`)   1, 0) AS `Days_Tasks_Completed`
FROM `dates` `d`
INNER JOIN `Tasks` `t` ON `t`.`Beginning_Date` <= `d`.`date`
WHERE `d`.`date` = '2021-12-07'
ORDER BY `t`.`ID` ASC;

回傳 -

您可以在此處查看Observation:示例列中所述的計數。隨著分組Day和SUMDays_Tasks和Days_Tasks_Completed可以看到的結果期望。

標籤：mysql sql

上一篇：有沒有辦法做一些像insertinto...whereinmysqlnode.js

下一篇：顯示最高值細節的最佳方式是什么