Oracle查询DURATION大于平均DURATION报ORA-00920错误解决方法

你写的SQL触发ORA-00920错误的核心问题有两个：

• 聚合函数sum()、count()不能直接写在WHERE子句中：SQL执行顺序里WHERE过滤在聚合计算之前，没有配套GROUP BY的情况下直接在WHERE中调用聚合函数，Oracle无法完成语法解析。
• 你需要的全表平均时长是一个独立的全局聚合值，不能和逐行过滤的条件放在同一查询层级直接计算。
  另外你自己写的sum(ENDDATE - STARTDATE) / count(*)完全可以用Oracle内置的AVG()函数替代，写法更简洁，也能自动处理空值问题。

写法1：子查询计算全局平均值（兼容性最好） #

直接在WHERE条件里嵌套一个子查询，先算出全表的平均入住时长，再逐行过滤时长大于平均值的记录：

SELECT
  RESNO,
  (ENDDATE - STARTDATE) AS DURATION
FROM reservation
WHERE (ENDDATE - STARTDATE) > (
  SELECT AVG(ENDDATE - STARTDATE)
  FROM reservation
);

写法2：窗口函数计算（大表场景性能更优） #

用分析函数AVG() OVER()直接在同一查询块里计算全局平均值，不需要二次扫描全表，数据量大的时候执行效率更高：

SELECT RESNO, DURATION
FROM (
  SELECT
    RESNO,
    (ENDDATE - STARTDATE) AS DURATION,
    AVG(ENDDATE - STARTDATE) OVER () AS AVG_DURATION
  FROM reservation
)
WHERE DURATION > AVG_DURATION;

基于你给出的样例数据，全表9条记录的总入住时长为39天，平均时长约4.33天，最终查询会返回时长大于平均值的3条记录：

• RESNO=2，时长5天
• RESNO=3，时长10天
• RESNO=9，时长5天

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