sql - 计算花费时间的总和

Question

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

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

我想要的是得到花费时间的总和。所以我的查询结果将是这样的:

    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

Answer 1

似乎您想对时间间隔求和。如果您有开始和结束时间的日期，您可以将它们转换为时间戳，然后减去它们，从而为您提供间隔。棘手的部分是聚合间隔，您需要一个自定义聚合函数。

要总结间隔，请参阅我之前的帖子 here

编译后，您可以简单地执行以下操作:

with d as (
    -- 1 day duration
    select 'X' as typ, sysdate - 4 as start_dte, sysdate - 3 as end_dte from dual
    union all
    -- 1.5 days duration
    select 'X' as typ, sysdate - 3 as start_dte, sysdate - 1.5 as end_dte from dual
    union all
    -- 0.5 days duration
    select 'Y' as typ, sysdate - 1.5 as start_dte, sysdate - 1 as end_dte from dual
    union all
    -- 1 day duration
    select 'Z' as typ, sysdate - 1 as start_dte, sysdate as end_dte from dual
)
-- subtract timestamp gives intervals
select typ, sum_interval(cast(end_dte as timestamp) - cast(start_dte as timestamp)) as duration
from d
group by typ;

输出:

TYP DURATION
X   +02 12:00:00.000000
Y   +00 12:00:00.000000
Z   +01 00:00:00.000000