嵌套聚合函数

2021-10-26 00:00:00 sql database oracle aggregate-functions 
SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;

这个查询有什么问题?

Avg 返回单个值 否Max 需要一个小组来处理，所以它不会执行并给出错误?请解释工作这是一个不会执行的测验问题 我想知道它不执行的原因 我不知道允许嵌套聚合函数吗?

Avg returns single value no Max requires a group to work on so it dosent execute and give error? Please explain working It's a quiz question according to which it won't execute I want to know the reason why it dosent execute I can't figure it out nested aggregate functions are allowed right?

推荐答案

Oracle 允许嵌套聚合函数(参见 文档).

Oracle allows nested aggregation functions (see the documentation).

然而，它需要一个GROUP BY.所以这是允许的:

However, it requires a GROUP BY. So this is allowed:

SELECT MAX(AVG(SYSDATE - inv_date))
FROM invoice
GROUP BY Cust_ID;

基本上，这是一个捷径:

Basically, this is a short-cut for:

SELECT MAX(x)
FROM (SELECT AVG(SYSDATE - inv_date) as x
      FROM invoice
       GROUP BY Cust_Id
     ) i;

不过，就您而言，没有 GROUP BY.Oracle 不允许在没有 GROUP BY 的情况下嵌套 GROUP BY.

In your case, though, there is no GROUP BY. Oracle doesn't allow nested GROUP BY without the GROUP BY.

如果您好奇，我不喜欢这种扩展功能.我不认为它实际上解决了问题.

And if you are curious, I'm not a fan of this extended functionality. I don't see that it actually solves a problem.

相关文章