Oracle 中带和不带 KEEP 的 PARTITION BY

2021-12-06 00:00:00 sql 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 个一致的获取 vs 34 个获取替代).

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

相关文章