SQL Oracle 中带和不带 KEEP 的 PARTITION BY

PARTITION BY with and without KEEP in Oracle

提问by aurelije

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.

第一个版本更合乎逻辑，但第二个版本可能是某种特殊情况，可能是一些性能优化。

采纳答案by Matthew McPeak

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 differentcolumns. For example (borrowing the "test" table from the other answer)...

在您的示例中，没有区别，因为您的聚合位于您正在排序的同一列上。“KEEP”的真正意义/力量是当您对不同的列进行聚合和排序时。例如（从另一个答案借用“测试”表）...

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

;

;

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

此查询获取每个部门中薪水最高的人的姓名。考虑没有“KEEP”子句的替代方案：

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 ) ) )

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

KEEP 子句更简单、更有效（在这个简单的例子中，只有 3 个一致的获取 vs 34 个获取替代）。

回答by MT0

MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno)

The statement can be considered in (roughly) right-to-left order:

可以按（大致）从右到左的顺序考虑该语句：

• OVER (PARTITION BY deptno)means partition the rows into distinct groups of deptno; then
• ORDER BY salmeans, for each partition, order the rows by sal(implicitly using ASCending order); then
• KEEP (DENSE_RANK FIRSTmeans give a (consecutive) ranking to the ordered rows for each partition (rows with identical values for the ordering columns will be given the same rank) and discard all rows which are not ranked first; and finally
• MIN(sal)for the remaining rows of each partition, return the minimum salary.

• OVER (PARTITION BY deptno)表示将行划分为不同的组deptno；然后
• ORDER BY sal意味着，对于每个分区，按sal（隐式使用ASC结束顺序）对行进行排序；然后
• KEEP (DENSE_RANK FIRST意味着对每个分区的有序行进行（连续）排名（排序列具有相同值的行将被赋予相同的排名）并丢弃所有未排名第一的行；最后
• MIN(sal)对于每个分区的剩余行，返回最低工资。

In this case the MINand DENSE_RANK FIRSTare both operating on the salcolumn so will do the same thing and the KEEP (DENSE_RANK FIRST ORDER BY sal)is redundant.

在这种情况下，MIN和DENSE_RANK FIRST都在sal列上运行，因此会做同样的事情并且KEEP (DENSE_RANK FIRST ORDER BY sal)是多余的。

However if you use a different column for the minimum then you can see the effects:

但是，如果您使用不同的列作为最小值，那么您可以看到效果：

SQL Fiddle

SQL小提琴

Oracle 11g R2 Schema Setup:

Oracle 11g R2 架构设置：

CREATE TABLE test (name, sal, deptno) AS
SELECT 'a', 1, 1 FROM DUAL
UNION ALL SELECT 'b', 1, 1 FROM DUAL
UNION ALL SELECT 'c', 1, 1 FROM DUAL
UNION ALL SELECT 'd', 2, 1 FROM DUAL
UNION ALL SELECT 'e', 3, 1 FROM DUAL
UNION ALL SELECT 'f', 3, 1 FROM DUAL
UNION ALL SELECT 'g', 4, 2 FROM DUAL
UNION ALL SELECT 'h', 4, 2 FROM DUAL
UNION ALL SELECT 'i', 5, 2 FROM DUAL
UNION ALL SELECT 'j', 5, 2 FROM DUAL;

Query 1:

查询 1：

SELECT DISTINCT
  MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS min_sal_first_sal,
  MAX(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS max_sal_first_sal,
  MIN(name) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS min_name_first_sal,
  MAX(name) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS max_name_first_sal,
  MIN(name) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS min_name_last_sal,
  MAX(name) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS max_name_last_sal,
  deptno
FROM test

Results:

结果：

| MIN_SAL_FIRST_SAL | MAX_SAL_FIRST_SAL | MIN_NAME_FIRST_SAL | MAX_NAME_FIRST_SAL | MIN_NAME_LAST_SAL | MAX_NAME_LAST_SAL | DEPTNO |
|-------------------|-------------------|--------------------|--------------------|-------------------|-------------------|--------|
|                 1 |                 1 |                  a |                  c |                 e |                 f |      1 |
|                 4 |                 4 |                  g |                  h |                 i |                 j |      2 |

回答by Roger Smith

To elaborate on one difference mentioned in the @MT0's answer: In your first query, the aggregate functions MIN and MAX are doing the job, while in the second, the actual rows are being picked by FIRST, LAST and KEEP.

详细说明@MT0 的答案中提到的一个差异：在您的第一个查询中，聚合函数 MIN 和 MAX 正在执行这项工作，而在第二个查询中，实际行由 FIRST、LAST 和 KEEP 选择。

You can even substitute MAX with MIN in the second example and it will still give the right answer (highest salary).

你甚至可以在第二个例子中用 MIN 代替 MAX，它仍然会给出正确的答案（最高工资）。

For more info refer to the following article.

有关更多信息，请参阅以下文章。

回答by Manish Jindal

It can also be helpful if you are ordering on basis of two columns and fetching either one or both those columns.

如果您根据两列进行排序并获取其中一列或两列，这也很有帮助。

CREATE TABLE test (name, sal, deptno) AS
SELECT 'adam', 100, 1 FROM DUAL
UNION ALL SELECT 'bravo', 500, 1 FROM DUAL
UNION ALL SELECT 'coy', 456, 1 FROM DUAL
UNION ALL SELECT 'david', 50, 1 FROM DUAL
UNION ALL SELECT 'ethan', 50, 1 FROM DUAL
UNION ALL SELECT 'feral', 300, 1 FROM DUAL;

Now you want to select employee with lowest salary as well as the salary of person.Condition is if two employee have same lowest salary fetch one with whose name comes first alphabetically.

现在您要选择工资最低的员工以及人员的工资。条件是如果两名员工的最低工资相同，则取姓名按字母顺序排在第一位的员工。

  select o.deptno
,min(o.sal) keep 
  (dense_rank first order by o.sal, o.name) least_salary
,min(o.name) keep 
  (dense_rank first order by o.sal, o.name) least_salary_person
 from test o
  group by 
 o.deptno;

OUTPUT:

输出：

DEPTNO  LEAST_SALARY    LEAST_SALARY_PERSON
1        50             david

回答by Ranaj Parida

This query gets the name of person with the highest salary in each department.

此查询获取每个部门中薪水最高的人的姓名。

select MIN(ename),sal,deptno
from emp where sal in
   (
    select max(sal) from emp group by deptno
   )
GROUP BY sal,deptno;