在Oracle中按分组与分区 [英] Group by vs Partition by in Oracle

问题描述

我正在编写一个查询以从Oracle仓库中获取记录.它是一个简单的Select Query，在几个表上有联接，我几乎没有要聚合的列.因此，我最终在其余的列上使用了Groupby.

I am writing a query to fetch records from a Oracle warehouse. Its a simple Select Query with joins on few tables and i have few columns to be aggregated. Hence i end up using Groupby on rest of the columns.

说我选择了10列，其中5列是聚合列.所以我需要按其他5列分组.我什至可以通过不执行Groupby并在我要派生的每个聚合列上使用over(paritition by)子句来实现相同的目的.

Say I am picking some 10 columns and out of which 5 is aggregate columns. so i need to group by on the other 5 columns. I can even achieve the same by not doing a Groupby and using over (paritition by) clause on the each each aggregate column i want to derive.

我不确定哪种方法更适合于仓库或一般而言.

I am not sure which is better against a warehouse or in general.

推荐答案

它们不相同.

这将返回3行:

select deptno, count(*) c from emp group by deptno;

DEPTNO C
------ -
10     3
20     5
30     6

这将返回14:

select deptno, count(*) over (partition by deptno) c from emp;


DEPTNO C
------ -
10     3
10     3
10     3
20     5
20     5
20     5
20     5
20     5
30     6
30     6
30     6
30     6
30     6
30     6

这篇关于在Oracle中按分组与分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！