如何在Oracle SQL中计算结果集的平均日期差值

1. 仅获取平均天数差 #

如果只需要所有符合条件工单的平均处理天数，直接用AVG()聚合函数包裹DTR的计算表达式即可，SQL代码如下：

SELECT
    AVG(TRUNC(SUPPORTCALL.RESOLVEDDATETIME) - TRUNC(SUPPORTCALL.CALLDATETIME)) AS "平均处理天数"
FROM
    SUPPORTCALL,
    PROBLEMTYPE
WHERE
    SUPPORTCALL.PROBLEM_ID = PROBLEMTYPE.PROBLEM_ID
    AND PROBLEMTYPE.PROBLEMCATEGORY = 'VMware'
    AND RESOLVEDDATETIME IS NOT NULL

2. 保留工单明细同时显示平均值 #

如果需要保留原有所有工单明细，同时额外显示整体的平均处理天数，可以使用窗口函数AVG() OVER()：

SELECT
    SUPPORTCALL.CALL_ID "Call ID",
    PROBLEMTYPE.PROBLEMCATEGORY "Problem Category",
    SUPPORTCALL.CALLDATETIME "Opened Date/Time",
    SUPPORTCALL.RESOLVEDDATETIME "Resolved Date/Time",
    TRUNC(SUPPORTCALL.RESOLVEDDATETIME) - TRUNC(SUPPORTCALL.CALLDATETIME) DTR,
    AVG(TRUNC(SUPPORTCALL.RESOLVEDDATETIME) - TRUNC(SUPPORTCALL.CALLDATETIME)) OVER() AS "平均处理天数"
FROM
    SUPPORTCALL,
    PROBLEMTYPE
WHERE
    SUPPORTCALL.PROBLEM_ID = PROBLEMTYPE.PROBLEM_ID
    AND PROBLEMTYPE.PROBLEMCATEGORY = 'VMware'
    AND RESOLVEDDATETIME IS NOT NULL

针对你提供的示例数据，第一种查询会返回(32+1+2+1)/4 = 9的结果；第二种查询会在每一行末尾都显示这个平均值9。

内容的提问来源于stack exchange，提问作者Simon Todd