嵌套聚合函数 [英] Nested 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.

这篇关于嵌套聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！