SQL Oracle 中的分组依据与分区依据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6847814/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Group by vs Partition by in Oracle
提问by Tony Andrews
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.
我正在编写一个查询以从 Oracle 仓库中获取记录。它是一个简单的选择查询，在几个表上连接，我有几列要聚合。因此，我最终在其余的列上使用了 Groupby。
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.
假设我选择了 10 列，其中 5 列是聚合列。所以我需要对其他 5 列进行分组。我什至可以通过不执行 Groupby 并在我想要派生的每个聚合列上使用 over (partition by) 子句来实现相同的目的。
I am not sure which is better against a warehouse or in general.
我不确定哪个更适合仓库或一般情况。
回答by Tony Andrews
They are not the same.
她们不一样。
This will return 3 rows:
这将返回 3 行：
select deptno, count(*) c from emp group by deptno;
DEPTNO C
------ -
10 3
20 5
30 6
This will return 14:
这将返回 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
回答by Anurag Peshne
Check this linkThe main difference between aggregate and analytic functions is that though analytic functions give aggregate result they do not group the result set. They return the group value multiple times with each record.
检查此链接聚合函数和分析函数之间的主要区别在于，尽管分析函数给出聚合结果，但它们并不对结果集进行分组。他们对每条记录多次返回组值。
回答by SLAVICA
With PARTITON BYit is posible to do this in one query to get different calculations or group by.
有了PARTITON BY它更多钞票来做到这一点在一个查询通过获得不同的计算或组。
select
DISTINCT deptno, count(*) over (partition by deptno) c,
COUNT(*) OVER (PARTITION BY NULL) AS TOTAL
from emp;
