Oracle Analytic函数可实现分组中的最小值 [英] Oracle Analytic function for min value in grouping

问题描述

我刚开始使用解析函数.

I'm new to working with analytic functions.

DEPT EMP   SALARY
---- ----- ------
  10 MARY  100000
  10 JOHN  200000
  10 SCOTT 300000
  20 BOB   100000
  20 BETTY 200000
  30 ALAN  100000
  30 TOM   200000
  30 JEFF  300000

我希望部门和雇员的最低工资.

I want the department and employee with minimum salary.

结果应如下所示:

DEPT EMP   SALARY
---- ----- ------
  10 MARY  100000
  20 BOB   100000
  30 ALAN  100000

这是我所拥有的SQL(但是，它当然不起作用，因为它也希望group by子句中的人员):

Here's the SQL I have (but of course, it doesn't work as it wants staff in the group by clause as well):

SELECT dept, 
  emp,
  MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY salary)
FROM mytable
GROUP BY dept

推荐答案

我认为Rank()函数不是实现此目的的方法，有两个原因.

I think that the Rank() function is not the way to go with this, for two reasons.

首先，它可能比基于Min()的方法效率低.

Firstly, it is probably less efficient than a Min()-based method.

这样做的原因是查询在扫描数据时必须维护每个部门所有薪水的有序列表，然后稍后将通过重新读取此列表来分配等级.显然，在没有可用于此目的的索引的情况下，在读取完最后一个数据项之前，您无法分配等级，并且维护列表的成本很高.

The reason for this is that the query has to maintain an ordered list of all salaries per department as it scans the data, and the rank will then be assigned later by re-reading this list. Obviously in the absence of indexes that can be leveraged for this, you cannot assign a rank until the last data item has been read, and maintenance of the list is expensive.

因此，Rank()函数的性能取决于要扫描的元素总数，如果数量足以使排序溢出到磁盘上，则性能将会下降.

So the performance of the Rank() function is dependent on the total number of elements to be scanned, and if the number is sufficient that the sort spills to disk then performance will collapse.

这可能更有效:

select dept,
       emp,
       salary
from
       (
       SELECT dept, 
              emp,
              salary,
              Min(salary) Over (Partition By dept) min_salary
       FROM   mytable
       )
where salary = min_salary
/

此方法仅要求查询在每个部门中维护到目前为止所遇到的最小值的单个值.如果遇到新的最小值，则将修改现有值，否则将丢弃新值.必须保留在内存中的元素总数与部门数有关，而不是与扫描的行数有关.

This method only requires that the query maintain a single value per department of the minimum value encountered so far. If a new minimum is encountered then the existing value is modified, otherwise the new value is discarded. The total number of elements that have to be held in memory is related to the number of departments, not the number of rows scanned.

Oracle可能有一个代码路径可以识别出在这种情况下排名并不需要真正计算出来，但是我不会打赌.

It could be that Oracle has a code path to recognise that the Rank does not really need to be computed in this case, but I wouldn't bet on it.

不喜欢Rank()的第二个原因是它只是回答了错误的问题.问题不是哪个记录的薪水是每个部门的薪水递增时的第一位"，而是哪个记录的薪水是每个部门的最低薪水".至少对我来说这有很大的不同.

The second reason for disliking Rank() is that it just answers the wrong question. The question is not "Which records have the salary that is the first ranking when the salaries per department are ascending ordered", it is "Which records have the salary that is the minimum per department". That makes a big difference to me, at least.

这篇关于Oracle Analytic函数可实现分组中的最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！