mariadb 在mysql中计算两个日期之间的处理时间

我在一家小型外包公司担任jr系统管理员，我们为其他公司提供IT服务。我们使用osTicket作为门票管理系统，但其中的报告和导出非常奇怪，并不真正聪明。我设法为我的IT团队制定KPI，每月一次，我需要从osTicket网络平台导出所有门票，并将其组织在Excel工作表中，在其中添加所有需要的信息，例如每个类别，每个运营商，每个公司等的门票数量。
为了自动化这个过程，我使用Metabase来读取osTicket DB，我只是错过了最后一个信息，让它完美地工作，因为我会：在票据之间交叉的处理时间被打开和关闭。
“易”的计算相当简单：

ROUND(SUM(TIMESTAMPDIFF(SECOND, ost_ticket.created, ost_ticket.closed)/3600)) AS 'Processing Time'

问题是，这个简单的操作计算了打开和关闭之间的总时间（以小时为单位，保留2位小数）：可以想象，为了正确分析SLA，查询应该考虑到我们不是24/7工作，而是工作日的09：00 am到1：00 pm和2：00 pm到6：00 pm。
我试着阅读大量的论坛，甚至要求ChatGPT，但这只是一个月，我开始写查询，这只是不是我。你能帮助我更好地理解我应该使用的逻辑来实现这个结果吗？
我尝试了以下方法：

TIMESTAMPDIFF(
     MINUTE
    ,CASE WHEN DAYOFWEEK(ost_ticket.created) = 6
          THEN DATE_ADD(DATE(ost_ticket.created), INTERVAL 18 HOUR) -- Friday
          WHEN DAYOFWEEK(ost_ticket.created) = 7
          THEN DATE_ADD(DATE(ost_ticket.created), INTERVAL 10 HOUR) -- Saturday
          ELSE ost_ticket.created
     END
    ,CASE WHEN DAYOFWEEK(ost_ticket.closed) = 6
          THEN DATE_ADD(DATE(ost_ticket.closed), INTERVAL 10 HOUR) -- Saturday
          WHEN DAYOFWEEK(ost_ticket.closed) = 7
          THEN DATE_ADD(DATE(ost_ticket.closed), INTERVAL 18 HOUR) -- Sunday
          ELSE ost_ticket.closed
     END
) / 60 AS 'Tempo di evasione'

正如ChatGPT所建议的那样，但是它太复杂了，并且在几分钟内获得的结果似乎不正确。

我喜欢分阶段解决这些问题。解决方案可能不是最优雅或最有效的，但它有效。
首先，让我们获得一些我们可以使用的数据：

DROP TEMPORARY TABLE IF EXISTS OST_TICKET;
CREATE TEMPORARY TABLE OST_TICKET (
    TKT_ID   INT AUTO_INCREMENT PRIMARY KEY,
    CREATED  DATETIME,
    CLOSED   DATETIME
);

INSERT INTO OST_TICKET (CREATED, CLOSED)
VALUES
    ('2023-07-31 08:15:00', '2023-08-04 14:33:48'),
    ('2023-07-30 07:15:00', '2023-08-03 19:33:12'),
    ('2023-08-01 09:45:00', '2023-08-01 12:33:27'),
    ('2023-08-03 12:45:00', '2023-08-03 17:21:18'),
    ('2023-08-03 14:13:00', '2023-08-03 16:11:33');

现在，我们需要知道的是时间跨度在哪里重叠的班次。我采取的方法是找到所有与时间跨度重叠的班次，调整第一个和最后一个班次，然后将它们相加。首先要做的是找到时间跨度中的所有天数。我们可以使用以下递归CTE来实现这一点：

代码段1

WITH RECURSIVE ALL_DAYS AS (
    SELECT
            TKT_ID,
            CREATED,
            CLOSED,
            DATE(CREATED) AS WORK_DATE
      FROM
            OST_TICKET
    UNION ALL
    SELECT
            TKT_ID,
            CREATED,
            CLOSED,
            DATE_ADD(WORK_DATE, INTERVAL 1 DAY)
      FROM
            ALL_DAYS
     WHERE
            WORK_DATE < DATE(CLOSED)
),

对于样本数据集，这产生：

+--------+---------------------+---------------------+------------+
| TKT_ID |       CREATED       |       CLOSED        | WORK_DATE  |
+--------+---------------------+---------------------+------------+
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-07-31 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-01 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-02 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-03 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-04 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-07-30 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-07-31 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-08-01 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-08-02 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-08-03 |
|      3 | 2023-08-01 09:45:00 | 2023-08-01 12:33:27 | 2023-08-01 |
|      4 | 2023-08-03 12:45:00 | 2023-08-03 17:21:18 | 2023-08-03 |
|      5 | 2023-08-03 14:13:00 | 2023-08-03 16:11:33 | 2023-08-03 |
+--------+---------------------+---------------------+------------+

从那里，我们可以通过交叉连接ALL_DAYS和时间表来为每个班次创建行。这个阶段也可以消除那些周末。第二个CTE是：

代码段2

ALLSHIFTS AS (
    SELECT
            TKT_ID,
            CREATED,
            CLOSED,
            TIMESTAMPADD(HOUR, SHIFTS.S_START, WORK_DATE) AS SHIFT_START,
            TIMESTAMPADD(HOUR, SHIFTS.S_END, WORK_DATE)   AS SHIFT_END
      FROM
            ALL_DAYS
            CROSS JOIN ( SELECT 9 AS S_START
                               ,13 AS S_END
                         UNION ALL
                         SELECT 14
                               ,18) AS SHIFTS
     WHERE
            DAYOFWEEK(WORK_DATE) NOT IN (1, 7)
),

它产生：

+--------+---------------------+---------------------+---------------------+---------------------+
| TKT_ID |       CREATED       |       CLOSED        |     SHIFT_START     |      SHIFT_END      |
+--------+---------------------+---------------------+---------------------+---------------------+
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-07-31 09:00:00 | 2023-07-31 13:00:00 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-07-31 14:00:00 | 2023-07-31 18:00:00 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-01 09:00:00 | 2023-08-01 13:00:00 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-01 14:00:00 | 2023-08-01 18:00:00 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-02 09:00:00 | 2023-08-02 13:00:00 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-02 14:00:00 | 2023-08-02 18:00:00 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-03 09:00:00 | 2023-08-03 13:00:00 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-03 14:00:00 | 2023-08-03 18:00:00 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-04 09:00:00 | 2023-08-04 13:00:00 |
|      1 | 2023-07-31 08:15:00 | 2023-08-04 14:33:48 | 2023-08-04 14:00:00 | 2023-08-04 18:00:00 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-07-31 09:00:00 | 2023-07-31 13:00:00 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-07-31 14:00:00 | 2023-07-31 18:00:00 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-08-01 09:00:00 | 2023-08-01 13:00:00 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-08-01 14:00:00 | 2023-08-01 18:00:00 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-08-02 09:00:00 | 2023-08-02 13:00:00 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-08-02 14:00:00 | 2023-08-02 18:00:00 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-08-03 09:00:00 | 2023-08-03 13:00:00 |
|      2 | 2023-07-30 07:15:00 | 2023-08-03 19:33:12 | 2023-08-03 14:00:00 | 2023-08-03 18:00:00 |
|      3 | 2023-08-01 09:45:00 | 2023-08-01 12:33:27 | 2023-08-01 09:00:00 | 2023-08-01 13:00:00 |
|      3 | 2023-08-01 09:45:00 | 2023-08-01 12:33:27 | 2023-08-01 14:00:00 | 2023-08-01 18:00:00 |
|      4 | 2023-08-03 12:45:00 | 2023-08-03 17:21:18 | 2023-08-03 09:00:00 | 2023-08-03 13:00:00 |
|      4 | 2023-08-03 12:45:00 | 2023-08-03 17:21:18 | 2023-08-03 14:00:00 | 2023-08-03 18:00:00 |
|      5 | 2023-08-03 14:13:00 | 2023-08-03 16:11:33 | 2023-08-03 09:00:00 | 2023-08-03 13:00:00 |
|      5 | 2023-08-03 14:13:00 | 2023-08-03 16:11:33 | 2023-08-03 14:00:00 | 2023-08-03 18:00:00 |
+--------+---------------------+---------------------+---------------------+---------------------+

现在是时候引入开始和结束时间在班次内的情况了。请注意，我们还希望消除任何没有意义的情况。此CTE执行两个步骤：

代码段4

ACTUALS AS (
    SELECT
            TKT_ID,
            CREATED,
            SHIFT_START,
            GREATEST(CREATED, SHIFT_START) AS ACTUAL_START,
            CLOSED,
            SHIFT_END,
            LEAST(CLOSED, SHIFT_END)        AS ACTUAL_END
     FROM
            ALLSHIFTS
    WHERE
            GREATEST(CREATED, SHIFT_START) < LEAST(CLOSED, SHIFT_END)
    
)

制作：

+--------+---------------------+---------------------+
| TKT_ID |    ACTUAL_START     |     ACTUAL_END      |
+--------+---------------------+---------------------+
|      1 | 2023-07-31 09:00:00 | 2023-07-31 13:00:00 |
|      1 | 2023-07-31 14:00:00 | 2023-07-31 18:00:00 |
|      1 | 2023-08-01 09:00:00 | 2023-08-01 13:00:00 |
|      1 | 2023-08-01 14:00:00 | 2023-08-01 18:00:00 |
|      1 | 2023-08-02 09:00:00 | 2023-08-02 13:00:00 |
|      1 | 2023-08-02 14:00:00 | 2023-08-02 18:00:00 |
|      1 | 2023-08-03 09:00:00 | 2023-08-03 13:00:00 |
|      1 | 2023-08-03 14:00:00 | 2023-08-03 18:00:00 |
|      1 | 2023-08-04 09:00:00 | 2023-08-04 13:00:00 |
|      1 | 2023-08-04 14:00:00 | 2023-08-04 14:33:48 |
|      2 | 2023-07-31 09:00:00 | 2023-07-31 13:00:00 |
|      2 | 2023-07-31 14:00:00 | 2023-07-31 18:00:00 |
|      2 | 2023-08-01 09:00:00 | 2023-08-01 13:00:00 |
|      2 | 2023-08-01 14:00:00 | 2023-08-01 18:00:00 |
|      2 | 2023-08-02 09:00:00 | 2023-08-02 13:00:00 |
|      2 | 2023-08-02 14:00:00 | 2023-08-02 18:00:00 |
|      2 | 2023-08-03 09:00:00 | 2023-08-03 13:00:00 |
|      2 | 2023-08-03 14:00:00 | 2023-08-03 18:00:00 |
|      3 | 2023-08-01 09:45:00 | 2023-08-01 12:33:27 |
|      4 | 2023-08-03 12:45:00 | 2023-08-03 13:00:00 |
|      4 | 2023-08-03 14:00:00 | 2023-08-03 17:21:18 |
|      5 | 2023-08-03 14:13:00 | 2023-08-03 16:11:33 |
+--------+---------------------+---------------------+

有了这些，剩下的就是计算日期差异并计算小时数。

代码段4

SELECT
         TKT_ID,
         ROUND(
                SUM(
                     TIMESTAMPDIFF( SECOND
                                   ,ACTUAL_START
                                   ,ACTUAL_END )
                   ) / 3600.0
               ,2
              ) AS HOURS_WORKED
    FROM
         ACTUALS
GROUP BY
         TKT_ID
ORDER BY
         TKT_ID
;

对于我的样本集，它会产生以下结果：

+--------+--------------+
| TKT_ID | HOURS_WORKED |
+--------+--------------+
|      1 |        36.56 |
|      2 |        32.00 |
|      3 |         2.81 |
|      4 |         3.61 |
|      5 |         1.98 |
+--------+--------------+

要运行实际的解决方案，您必须将所有四个代码段合并组合到一个查询中。您还需要调整字段和表名以适应您的特殊情况。

mariadb 在mysql中计算两个日期之间的处理时间

1条答案

相关问题

热门标签

最新问答