avg时间oracle sql

Question

我试图找到日期范围的avg启动时间，但是avg函数似乎不能在to_char(cast(Start_Dt as date),'HH24:MI') (Start_Dt示例：12/12/18 02:30:13)上工作。对我怎样才能得到我想要的结果有什么想法吗？

样本：

SELECT
job_nm
avg(to_char(cast(Start_Dt as date),'HH24:MI'))
FROM batch_table
Where Start_Dt >= trunc(sysdate-10)
group by job_nm

错误: ORA-01722:无效编号01722.00000 -“无效号码”*原因:指定号码无效。*行动:指定有效号码。

理想的结果将是查询只查看在过去10天中运行的作业和该期间的平均开始时间。

预期结果：

 JOB_NM | AVG Time
    open   |  2:30
    close  |  3:30

Answer 1

要计算平均时间，您可以使用：

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;

而且，如果您希望计算一个平均日期，则可以将日期转换为Julian date格式，计算平均值，然后将其转换为date，例如：

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;

Answer 2

Oracle在将日期作为浮点数处理时非常灵活，因此可能不需要在接受的答案中进行大规模复杂的数据类型转换：

SELECT
  job_nm,
  to_char(trunc(sysdate) + avg(start_dt - trunc(start_dt)),'HH24:MI')) avg_time
FROM batch_table
  Where Start_Dt >= trunc(sysdate-10)
  group by job_nm

因为start_dt - trunc(start_dt)是一个在0到1之间的浮点数，代表一天中的时间(0.5 =中午12，0.75 = 6pm)，这些都可以被平均地给出另一个浮点数，也就是平均时间(早上6点和下午6点是平均的，中午)。0.25和0.75为平均0.5)。

将它添加回任何日期(比如trunc(sysdate))会给出日期和时间，而to_char只是打印它的时间部分，抛出日期。Oracle应该足够聪明，不要坚持按trunc(sysdate)分组。

11.2&fiddle=2563874ab5566f9ff6030601497b718b