在Oracle中使用和不使用KEEP进行分区 [英] PARTITION BY with and without KEEP in Oracle

问题描述

我遇到了两个查询，它们似乎具有相同的结果:在分区上应用聚合函数.

I came across two queries which seems to have the same result: applying aggregate function on partition.

我想知道这两个查询之间是否有任何区别:

I am wondering if there is any difference between these two queries:

SELECT empno,
   deptno,
   sal,
   MIN(sal) OVER (PARTITION BY deptno) "Lowest",
   MAX(sal) OVER (PARTITION BY deptno) "Highest"
FROM empl

SELECT empno,
   deptno,
   sal,
   MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) "Lowest",
   MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) "Highest"
FROM empl

第一个版本更具逻辑性，但是第二个版本可能是某种特殊情况，也许是一些性能优化.

The first version is more logical but second one may be some kind special case, maybe some performance optimization.

推荐答案

在您的示例中，没有什么区别，因为您的汇总位于要排序的同一列上. "KEEP"的真正点/功效是当您对不同列进行汇总和排序时.例如(从其他答案中借用测试"表)...

In your example, there's no difference, because your aggregate is on the same column that you are sorting on. The real point/power of "KEEP" is when you aggregate and sort on different columns. For example (borrowing the "test" table from the other answer)...

SELECT deptno,  min(name) keep ( dense_rank first order by sal desc, name  ) ,
max(sal)
FROM test
group by deptno

;

此查询获取每个部门中薪水最高的人员的姓名.考虑不带"KEEP"子句的替代方法:

This query gets the name of person with the highest salary in each department. Consider the alternative without a "KEEP" clause:

SELECT deptno, name, sal
FROM test t
WHERE not exists ( SELECT 'person with higher salary in same department'
                                            FROM test t2  
                                            WHERE t2.deptno = t.deptno
                                            and ((  t2.sal > t.sal )
                                            OR ( t2.sal = t.sal AND t2.name < t.name ) ) )

KEEP子句更简单，更有效(在这个简单的示例中，只有3个一致的获取与34个一致的获取).

The KEEP clause is easier and more efficient (only 3 consistent gets vs 34 gets for the alternative, in this simple example).

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