计算花费时间的总和 [英] Calculate sum of spent time

问题描述

我有一个 sql 查询，用于计算并发作业所花费的时间.这是查询:

I have a sql query that calculates spent time for concurrent jobs. Here is the query:

SELECT
             user_concurrent_program_name,
             DECODE (phase_code, 'C', 'Completed', phase_code) phase_code,
             DECODE (status_code,
                     'D', 'Cancelled',
                     'E', 'Error',
                     'G', 'Warning',
                     'H', 'On Hold',
                     'T', 'Terminating',
                     'M', 'No Manager',
                     'X', 'Terminated',
                     'C', 'Normal',
                     status_code)
                AS status_code,
          TO_CHAR (
              to_date('2001,091,00:00:00' , 'YYYY,DDD,HH24:MI:SS')
               + (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE),
               'HH24:MI:SS')
            AS GECEN_SURE
        FROM apps.fnd_conc_req_summary_v
       WHERE phase_code = 'C' AND status_code = 'C';

这是我运行时的几行:

Initial Mass Copy                       Completed   Normal  22:12:35
Gather Schema Statistics                Completed   Normal  06:13:35
Request Set FAH-KPK Fislerini Olustur   Completed   Normal  04:36:36
Request Set FAH-KPK Fislerini Olustur   Completed   Normal  04:15:18
Request Set FAH-KPK Fislerini Olustur   Completed   Normal  04:13:45
Request Set FAH-KPK Fislerini Olustur   Completed   Normal  04:10:51

我想要的是获得花费时间的总和.所以我的查询结果会是这样的:

What i want is get the sum of spent time. So my query result will be like:

    Initial Mass Copy                           Completed   Normal  22:12:35
    Gather Schema Statistics                Completed   Normal  06:13:35
    Request Set FAH-KPK Fislerini Olustur   Completed   Normal  17:02:36

推荐答案

你可以在这个 小提琴

CREATE TABLE test
(
   A   CHAR (1),
   b   VARCHAR2 (10)
);

INSERT INTO TEST
     VALUES ('A', '04:00:01');

INSERT INTO TEST
     VALUES ('A', '04:00:01');

INSERT INTO TEST
     VALUES ('A', '13:02:01');

INSERT INTO TEST
     VALUES ('A', '11:00:01');

INSERT INTO TEST
     VALUES ('A', '02:59:59');

COMMIT;

SELECT    TO_CHAR (TRUNC (total / 3600))
       || ':'
       || TO_CHAR (TRUNC (ABS (MOD (total, 3600)) / 60), 'fm00')
       || ':'
       || TO_CHAR (MOD (total, 60), 'fm00')
  FROM (SELECT SUM (
                    TO_NUMBER (SUBSTR (b, 1, 2)) * 60 * 60
                  + TO_NUMBER (SUBSTR (b, 4, 2)) * 60
                  + TO_NUMBER (SUBSTR (b, 7, 2)))
                  TOTAL
          FROM TEST);
35:02:03

PS:这可以包含在您的聚合部分中.

PS: This can be included in your aggregation part.

所以应该是这样的

WITH TEST
     AS (SELECT user_concurrent_program_name,
                DECODE (phase_code, 'C', 'Completed', phase_code) phase_code,
                DECODE (status_code,
                        'D', 'Cancelled',
                        'E', 'Error',
                        'G', 'Warning',
                        'H', 'On Hold',
                        'T', 'Terminating',
                        'M', 'No Manager',
                        'X', 'Terminated',
                        'C', 'Normal',
                        status_code)
                   AS status_code,
                TO_CHAR (
                     TO_DATE ('2001,091,00:00:00', 'YYYY,DDD,HH24:MI:SS')
                   + (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE),
                   'HH24:MI:SS')
                   AS GECEN_SURE
           FROM apps.fnd_conc_req_summary_v
          WHERE phase_code = 'C' AND status_code = 'C')
SELECT user_concurrent_program_name,
       phase_code,
       status_code,
          TO_CHAR (TRUNC (GECEN_SURE/ 3600))
       || ':'
       || TO_CHAR (TRUNC (ABS (MOD (GECEN_SURE, 3600)) / 60), 'fm00')
       || ':'
       || TO_CHAR (MOD (GECEN_SURE, 60), 'fm00') TOTAL
  FROM (  SELECT user_concurrent_program_name,
                 phase_code,
                 status_code,
                 SUM (
                      TO_NUMBER (SUBSTR (GECEN_SURE, 1, 2)) * 60 * 60
                    + TO_NUMBER (SUBSTR (GECEN_SURE, 4, 2)) * 60
                    + TO_NUMBER (SUBSTR (GECEN_SURE, 7, 2)))
                    GECEN_SURE
            FROM TEST
        GROUP BY user_concurrent_program_name, phase_code, status_code);

这篇关于计算花费时间的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！