如何在不使用GROUP BY的情况下进行聚合？ [英] How to aggregrate without using `GROUP BY`?

问题描述

我有类似以下的查询：

$ p $ SELECT a。*，b。*

（ SELECT ATTR1，ATTR2，sum（QUANTITY）AS TOTAL_QTY，
ATTR3 FROM TABLE_A
WHERE ATTR4> 0
GROUP BY ATTR1，ATTR2，ATTR3）a，

TABLE_B b

WHERE a.ATTR1 = b.ATTR1
AND a.ATTR2 = b.ATTR2


我需要 GROUP BY 只有 ATTR1 来计算正确的 TOTAL_QTY ，但是我分组其他属性的唯一原因是因为Oracle要求如果存在 GROUP BY 子句，则所有 SELECT 属性也应该在 GROUP BY 子句中。

这意味着每个我需要一个来自Table_A的查询属性，然后我需要把它放在 GROUP BY 中。这不仅看起来很丑陋，而且可能会对性能产生影响，也可能会带来不可预见的副作用。

如何重写上述查询来计算 TOTAL_QTY 在每个 ATTR1 群组中，没有 GROUP BY 子句？

解决方案

使用Oracle分析函数。将 table_a 的内联视图更改为如下形式：



  select attr1， 
 attr2，
 sum（quantity）over（attr1 order by attr1分区）as total_qty，
 attr3 
 from table_a 
 where attr4> 0 
  

这可能需要调整一下，但这是基本的想法。 b

I have query like the following

SELECT a.*, b.*

(SELECT ATTR1, ATTR2, sum(QUANTITY) AS TOTAL_QTY,
ATTR3 FROM TABLE_A
WHERE ATTR4 > 0
GROUP BY ATTR1, ATTR2, ATTR3) a,

TABLE_B b

WHERE a.ATTR1 = b.ATTR1
AND a.ATTR2 = b.ATTR2

I need to GROUP BY only ATTR1 to calculate the correct TOTAL_QTY, but the only reason I am grouping other attributes because Oracle requires that if GROUP BY clause is present then all SELECT attributes should be in the GROUP BY clause too.

This means every time I need an attribute in this query from Table_A then I need to put it in GROUP BY too. That not only looks ugly, but can have performance impact and maybe unforseen side-effect.

How do I rewrite the above query to calculate the TOTAL_QTY in each ATTR1 groups, without the GROUP BY clause?

解决方案

Use Oracle analytic functions. Change the inline view for table_a to something like:

select attr1,
       attr2,
       sum(quantity) over (partition by attr1 order by attr1) as total_qty,
       attr3
from   table_a
where  attr4 > 0

This may need tweaking a bit, but that's the basic idea.

这篇关于如何在不使用GROUP BY的情况下进行聚合？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！