要计算平均时间,您将使用:
SELECT
job_nm,
TO_CHAR(
TO_DATE(
AVG(
TO_NUMBER(
TO_CHAR(
TO_DATE(
TO_CHAR(Start_Dt,'HH24:MI:SS'),
'HH24:MI:SS'),
'sssss')
)
),
'sssss'),
'hh24:mi:ss')
FROM batch_table
WHERE Start_Dt >= trunc(sysdate-10)
GROUP BY job_nm;
这里还有另一个选择,也许不那么复杂:
SELECT
job_nm,
FLOOR(24 * AVG(Start_Dt- TRUNC(Start_Dt)))
|| ':'
|| FLOOR(MOD(24 * AVG(Start_Dt - TRUNC(Start_Dt)),1) * 60)
|| ':'
|| FLOOR(MOD(MOD(24 * AVG(Start_Dt- TRUNC(Start_Dt)),1) * 60,1) * 60)
FROM batch_table
WHERE Start_Dt >= trunc(sysdate-10)
GROUP BY job_nm;
为了以防万一,如果你想计算一个平均日期,你可以把日期转换成儒略日期格式,计算平均值,然后转换回日期,比如:
SELECT
job_nm,
TO_DATE(
ROUND(
AVG(
TO_NUMBER(TO_CHAR(Start_Dt, 'J'))
)
),
'J')
FROM batch_table
WHERE Start_Dt >= trunc(sysdate-10)
GROUP BY job_nm;