在Oracle中分组vs分区

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仓库中获取记录。它是一个简单的Select Query,在几个表上有连接,我有很少的列要聚合。因此,我最终在其余列上使用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(paritition by)子句来实现相同目的。

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

我不确定哪个更适合仓库或一般。

3 个解决方案

#1

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

#2

Check this link The 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.

检查此链接聚合和分析函数之间的主要区别在于,虽然分析函数提供聚合结果,但它们不会对结果集进行分组。它们会在每条记录中多次返回组值。

#3

-1

With PARTITON BY it 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;

#1