在 Oracle 中对 SUM() 聚合函数使用 Max()

Question

我正在尝试在 Oracle 中编写一个查询，该查询将从列出 pub_id、销售额、价格的标题表中返回 pub_id 和最大总收入。 我可以得到带有 pub_id 的列表和每个 pub_id 的总收入

 SELECT PUB_ID, SUM(SALES*PRICE) as TotalRevenue FROM TITLES GROUP BY PUB_ID;

或者我可以得到 MAX(销售额*价格) 有

 SELECT MAX(SUM(sales*price)) FROM titles GROUP BY pub_id;

任何想法如何才能得到 pub_id 与总收入的最大值？

Answer 1

您可以像这样使用排名功能

select * from
(
select a.*,rank() over (order by sum_sales desc) r from
(
select pub_id,sum(sales*price) sum_sales from titles group by pub_id
) a
)
where r = 1;    

Answer 2

借助强大的 ORACLE 分析函数，这一切变得简单。

下面将给出每个 pub_id 的最大收入。

  select pub_id,REV from 
   (
   select pub_id, (sales*price) as REV,
   max(sales*price) over (partition by pub_id order by 1) as MAX 
   from titles
   )
   where REV=MAX

如果您想确定收入最大的 pub_id：

   select * from
   (
   select pub_id,REV from 
   (
   select pub_id, (sales*price) as REV,
   max(sales*price) over (partition by pub_id order by 1) as MAX 
   from titles
   )
   where REV=MAX order by MAX desc
   )
   where rownum<2

Answer 3

SELECT PUB_ID, SUM(SALES*PRICE) as TotalRevenue 
FROM TITLES 
GROUP BY PUB_ID
HAVING SUM(SALES*PRICE) = ( SELECT MAX(SUM(SALES*PRICE)) 
                            FROM TITLES 
                            GROUP BY PUB_ID );

Answer 4

在这种情况下，实际上并不需要分析函数。最好的选择是将 sum() 和 max() 的以下时间与 dense_rank 选项。

select max(pub_id) keep (dense_rank last order by TotalRevenue)
from (
        SELECT PUB_ID, SUM(SALES*PRICE) as TotalRevenue 
        FROM TITLES 
        GROUP BY PUB_ID
    )