blocks|key|537480|text|RANK为您提供有序分区中的排名。平局被分配相同的排名，跳过下一个排名。因此，如果你有3个项目在排名2，那么下一个列出的排名将是排名5。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|537481|DENSE_RANK再次为您提供有序分区中的排名，但排名是连续的。如果存在具有多个项目的排名，则不跳过排名。|537482|至于nulls，则取决于ORDER+BY子句。下面是一个简单的测试脚本，您可以使用它来查看发生了什么：|537483|with+q+as+(
select+10+deptno,+'rrr'+empname,+10000.00+sal+from+dual+union+all
select+11,+'nnn',+20000.00+from+dual+union+all
select+11,+'mmm',+5000.00+from+dual+union+all
select+12,+'kkk',+30000+from+dual+union+all
select+10,+'fff',+40000+from+dual+union+all
select+10,+'ddd',+40000+from+dual+union+all
select+10,+'bbb',+50000+from+dual+union+all
select+10,+'xxx',+null+from+dual+union+all
select+10,+'ccc',+50000+from+dual)
select+empname,+deptno,+sal
+++++,+rank()+over+(partition+by+deptno+order+by+sal+nulls+first)+r
+++++,+dense_rank()+over+(partition+by+deptno+order+by+sal+nulls+first)+dr1
+++++,+dense_rank()+over+(partition+by+deptno+order+by+sal+nulls+last)+dr2
+from+q;+

EMP+++++DEPTNO++++++++SAL++++++++++R++++++++DR1++++++++DR2
---+----------+----------+----------+----------+----------
xxx+++++++++10+++++++++++++++++++++1++++++++++1++++++++++4
rrr+++++++++10++++++10000++++++++++2++++++++++2++++++++++1
fff+++++++++10++++++40000++++++++++3++++++++++3++++++++++2
ddd+++++++++10++++++40000++++++++++3++++++++++3++++++++++2
ccc+++++++++10++++++50000++++++++++5++++++++++4++++++++++3
bbb+++++++++10++++++50000++++++++++5++++++++++4++++++++++3
mmm+++++++++11+++++++5000++++++++++1++++++++++1++++++++++1
nnn+++++++++11++++++20000++++++++++2++++++++++2++++++++++2
kkk+++++++++12++++++30000++++++++++1++++++++++1++++++++++1

9+rows+selected.|code-block|syntax|javascript|537484|Here's+a+link给出了一个很好的解释和一些例子。|offset|length|537485|entityMap|0|LINK|mutability|MUTABLE|url|http://www.oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions.php^0|0|0|0|0|0|D|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|Z|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|10|8|@]|9|@$M|11|N|12|1|13]]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

RANK gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5.

DENSE_RANK again gives you the ranking within your ordered partition, but the ranks are consecutive. No ranks are skipped if there are ranks with multiple items.

As for nulls, it depends on the ORDER BY clause. Here is a simple test script you can play with to see what happens:

<pre><code>with q as (
select 10 deptno, 'rrr' empname, 10000.00 sal from dual union all
select 11, 'nnn', 20000.00 from dual union all
select 11, 'mmm', 5000.00 from dual union all
select 12, 'kkk', 30000 from dual union all
select 10, 'fff', 40000 from dual union all
select 10, 'ddd', 40000 from dual union all
select 10, 'bbb', 50000 from dual union all
select 10, 'xxx', null from dual union all
select 10, 'ccc', 50000 from dual)
select empname, deptno, sal
 , rank() over (partition by deptno order by sal nulls first) r
 , dense_rank() over (partition by deptno order by sal nulls first) dr1
 , dense_rank() over (partition by deptno order by sal nulls last) dr2
 from q; 

EMP DEPTNO SAL R DR1 DR2
--- ---------- ---------- ---------- ---------- ----------
xxx 10 1 1 4
rrr 10 10000 2 2 1
fff 10 40000 3 3 2
ddd 10 40000 3 3 2
ccc 10 50000 5 4 3
bbb 10 50000 5 4 3
mmm 11 5000 1 1 1
nnn 11 20000 2 2 2
kkk 12 30000 1 1 1

9 rows selected.
</code></pre>

<a href="http://www.oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions.php" rel="noreferrer">Here's a link</a> to a good explanation and some examples.

blocks|key|537545|text|select+empno
+++++++,salary
+++++++,row_number()+over(order+by+salary+desc)+as+Serial
+++++++,Rank()+over(order+by+salary+desc)+as+rank
+++++++,dense_rank()+over(order+by+salary+desc)+as+denseRank
from+emp+;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|537546|用于生成序列号的Row_number()+->|unstyled|offset|length|style|CODE|537547|Dense_rank()将提供连续排名，但Rank()将在排名冲突的情况下跳过排名。|537548|entityMap^0|0|8|C|0|0|C|L|6|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|P|8|@$G|Q|H|R|I|J]]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@$G|T|H|U|I|J]|$G|V|H|W|I|J]]|9|@]|A|$]]|$1|M|3|-4|5|F|7|X|8|@]|9|@]|A|$]]]|N|$]]

<pre><code>select empno
 ,salary
 ,row_number() over(order by salary desc) as Serial
 ,Rank() over(order by salary desc) as rank
 ,dense_rank() over(order by salary desc) as denseRank
from emp ;
</code></pre>
<code>Row_number()</code> -&gt; Used for generating serial number
<code>Dense_rank()</code> will give continuous rank but <code>Rank()</code> will skip rank in case of clash of rank.

blocks|key|537571|text|SELECT+empno,
+++++++deptno,
+++++++sal,
+++++++RANK()+OVER+(PARTITION+BY+deptno+ORDER+BY+sal)+"rank"
FROM+++emp;

+++++EMPNO+++++DEPTNO++++++++SAL+++++++rank
----------+----------+----------+----------
++++++7934+++++++++10+++++++1300++++++++++1
++++++7782+++++++++10+++++++2450++++++++++2
++++++7839+++++++++10+++++++5000++++++++++3
++++++7369+++++++++20++++++++800++++++++++1
++++++7876+++++++++20+++++++1100++++++++++2
++++++7566+++++++++20+++++++2975++++++++++3
++++++7788+++++++++20+++++++3000++++++++++4
++++++7902+++++++++20+++++++3000++++++++++4
++++++7900+++++++++30++++++++950++++++++++1
++++++7654+++++++++30+++++++1250++++++++++2
++++++7521+++++++++30+++++++1250++++++++++2
++++++7844+++++++++30+++++++1500++++++++++4
++++++7499+++++++++30+++++++1600++++++++++5
++++++7698+++++++++30+++++++2850++++++++++6


SELECT+empno,
+++++++deptno,
+++++++sal,
+++++++DENSE_RANK()+OVER+(PARTITION+BY+deptno+ORDER+BY+sal)+"rank"
FROM+++emp;

+++++EMPNO+++++DEPTNO++++++++SAL+++++++rank
----------+----------+----------+----------
++++++7934+++++++++10+++++++1300++++++++++1
++++++7782+++++++++10+++++++2450++++++++++2
++++++7839+++++++++10+++++++5000++++++++++3
++++++7369+++++++++20++++++++800++++++++++1
++++++7876+++++++++20+++++++1100++++++++++2
++++++7566+++++++++20+++++++2975++++++++++3
++++++7788+++++++++20+++++++3000++++++++++4
++++++7902+++++++++20+++++++3000++++++++++4
++++++7900+++++++++30++++++++950++++++++++1
++++++7654+++++++++30+++++++1250++++++++++2
++++++7521+++++++++30+++++++1250++++++++++2
++++++7844+++++++++30+++++++1500++++++++++3
++++++7499+++++++++30+++++++1600++++++++++4
++++++7698+++++++++30+++++++2850++++++++++5|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|537572|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT empno,
 deptno,
 sal,
 RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM emp;

 EMPNO DEPTNO SAL rank
---------- ---------- ---------- ----------
 7934 10 1300 1
 7782 10 2450 2
 7839 10 5000 3
 7369 20 800 1
 7876 20 1100 2
 7566 20 2975 3
 7788 20 3000 4
 7902 20 3000 4
 7900 30 950 1
 7654 30 1250 2
 7521 30 1250 2
 7844 30 1500 4
 7499 30 1600 5
 7698 30 2850 6


SELECT empno,
 deptno,
 sal,
 DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM emp;

 EMPNO DEPTNO SAL rank
---------- ---------- ---------- ----------
 7934 10 1300 1
 7782 10 2450 2
 7839 10 5000 3
 7369 20 800 1
 7876 20 1100 2
 7566 20 2975 3
 7788 20 3000 4
 7902 20 3000 4
 7900 30 950 1
 7654 30 1250 2
 7521 30 1250 2
 7844 30 1500 3
 7499 30 1600 4
 7698 30 2850 5
</code></pre>

blocks|key|320754|text|RANK()和DENSE_RANK()函数之间的唯一区别是在存在“平局”的情况下；即在一个集合中的多个值具有相同的排名的情况下。在这种情况下，RANK()将为集合中的值分配非连续的“排名”(当存在平局时，会导致整数排名值之间的差距)，而DENSE_RANK()将为集合中的值分配连续的排名(因此，在平局的情况下，整数排名值之间将没有差距)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|320755|例如，考虑集合{25，25，50，75，75,100}。对于这样的集合，RANK()将返回{1，1，3，4，4，4，6}+(请注意，值2和5已被跳过)，而DENSE_RANK()将返回{1,1,2,3,3,4}。|320756|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a “tie”; i.e., in cases where multiple values in a set have the same ranking. In such cases, RANK() will assign non-consecutive “ranks” to the values in the set (resulting in gaps between the integer ranking values when there is a tie), whereas DENSE_RANK() will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie).

For example, consider the set {25, 25, 50, 75, 75, 100}. For such a set, RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped), whereas DENSE_RANK() will return {1,1,2,3,3,4}.

blocks|key|537687|text|row_number+()+SQL函数在有序的值集内生成数据的等级，但前一个等级之后的下一个等级是该特定行的rank。另一方面，Dense_Rank()+SQL函数生成下一个数字，而不是生成row_number。下面是SQL示例，它将阐明概念：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|537688|Select+ROW_NUMBER()+over+(order+by+Salary)+as+RowNum,+Salary,+
RANK()+over+(order+by+Salary)+as+Rnk,+
DENSE_RANK()+over+(order+by+Salary)+as+DenseRnk+from+(
Select+1000+as+Salary+union+all
Select+1000+as+Salary+union+all
Select+1000+as+Salary+union+all
Select+2000+as+Salary+union+all
Select+3000+as+Salary+union+all
Select+3000+as+Salary+union+all
Select+8000+as+Salary+union+all
Select+9000+as+Salary)+A|code-block|syntax|javascript|537689|它将生成以下输出：|537690|----------------------------
RowNum++Salary++Rnk+DenseRnk
----------------------------
1+++++++1000++++1+++1
2+++++++1000++++1+++1
3+++++++1000++++1+++1
4+++++++2000++++4+++2
5+++++++3000++++5+++3
6+++++++3000++++5+++3
7+++++++8000++++7+++4
8+++++++9000++++8+++5|537691|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Rank() SQL function generates rank of the data within ordered set of values but next rank after previous rank is row_number of that particular row. On the other hand, Dense_Rank() SQL function generates next number instead of generating row_number. Below is the SQL example which will clarify the concept:

<pre><code>Select ROW_NUMBER() over (order by Salary) as RowNum, Salary, 
RANK() over (order by Salary) as Rnk, 
DENSE_RANK() over (order by Salary) as DenseRnk from (
Select 1000 as Salary union all
Select 1000 as Salary union all
Select 1000 as Salary union all
Select 2000 as Salary union all
Select 3000 as Salary union all
Select 3000 as Salary union all
Select 8000 as Salary union all
Select 9000 as Salary) A
</code></pre>

It will generate following output:

<pre><code>----------------------------
RowNum Salary Rnk DenseRnk
----------------------------
1 1000 1 1
2 1000 1 1
3 1000 1 1
4 2000 4 2
5 3000 5 3
6 3000 5 3
7 8000 7 4
8 9000 8 5
</code></pre>

blocks|key|537834|text|等级和密集等级给出了分区数据集中的等级。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|537835|Rank()：它不会给出连续的整数。|537836|Dense_rank()：它提供连续的整数。|537837|​|537838|📷|atomic|offset|length|537839|537840|在上图中，当考虑row_number时，dense_rank()函数的10008+zip的排名是2，rank()函数的排名是24。|537841|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/0f42c268a0e0bfc0567b1886a66419cc.png|imageAlt^0|0|0|0|0|0|1|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|10|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|11|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|12|8|@]|9|@$K|13|L|14|1|15]]|A|$]]|$1|M|3|G|5|6|7|16|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|17|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|Q|$R|$5|S|T|U|A|$V|W|X|-4]]]]

Rank and Dense rank gives the rank in the partitioned dataset.

Rank() : It doesn't give you consecutive integer numbers.

Dense_rank() : It gives you consecutive integer numbers.

<a href="https://i.stack.imgur.com/fbv8Z.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fbv8Z.png" alt="enter image description here"></a>

In above picture , the rank of 10008 zip is 2 by dense_rank() function and 24 by rank() function as it considers the row_number.

blocks|key|537997|text|Rank(),+Dense_rank(),+row_number()这些都是窗口函数，这意味着它们首先在一些有序的输入集合上充当窗口。根据需要，这些窗口具有不同的附加功能。下面是上面的3条：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|537998|row_number()|537999|从row_number()开始，因为这构成了这些相关窗口函数的基础。顾名思义，row_number()为应用它的行集提供了一个惟一的数字。类似于给每一行一个序列号。|538000|Rank()|538001|可以说row_number()的一个subversion就是rank()。row_number()+()用于向那些重复的有序集合行提供相同的序列号，但它仍然保持计数与重复Rank()之后的所有行的计数类似，这意味着从下面开始。对于数据2，row_number()+=rank()，意思是两者只是以重复的形式不同。|538002|Data+row_number()+rank()+dense_rank()+
++++1+++++++++1++++++++++++++++++++1+++++++1
++++1+++++++++2++++++++++++++++++++1+++++++1
++++1+++++++++3++++++++++++++++++++1+++++++1
++++2+++++++++4++++++++++++++++++++4+++++++2|code-block|syntax|javascript|538003|最后，|538004|Dense_rank()是rank()的扩展版本，顾名思义，它是密集的，因为您可以从上面的示例中看到，对于所有数据1，它都是rank()+=+dense_rank()，但对于数据2，它的不同之处在于它的顺序与前一个rank()的顺序相同，而不是实际数据的顺序|538005|entityMap^0|0|Y|0|0|C|0|1|C|13|C|0|0|6|0|3|C|U|6|11|C|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|11|8|@$9|12|A|13|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|14|8|@$9|15|A|16|B|C]|$9|17|A|18|B|C]]|D|@]|E|$]]|$1|J|3|K|5|6|7|19|8|@$9|1A|A|1B|B|C]]|D|@]|E|$]]|$1|L|3|M|5|6|7|1C|8|@$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]|$9|1H|A|1I|B|C]]|D|@]|E|$]]|$1|N|3|O|5|P|7|1J|8|@]|D|@]|E|$Q|R]]|$1|S|3|T|5|6|7|1K|8|@]|D|@]|E|$]]|$1|U|3|V|5|6|7|1L|8|@]|D|@]|E|$]]|$1|W|3|-4|5|6|7|1M|8|@]|D|@]|E|$]]]|X|$]]

<code>Rank(), Dense_rank(), row_number()</code>
These all are window functions that means they act as window over some ordered input set at first. These windows have different functionality attached to it based on the requirement. Heres the above 3 :

<code>row_number()</code>

Starting by <code>row_number()</code> as this forms the basis of these related window functions. <code>row_number()</code> as the name suggests gives a unique number to the set of rows over which its been applied. Similar to giving a serial number to each row. 

<code>Rank()</code>

A subversion of <code>row_number()</code> can be said as <code>rank()</code>. Rank() is used to give same serial number to those ordered set rows which are duplicates but it still keeps the count mantained as similar to a <code>row_number()</code> for all those after duplicates rank() meaning as from below eg. For data 2 row_number() =rank() meaning both just differs in the form of duplicates. 

<pre><code>Data row_number() rank() dense_rank() 
 1 1 1 1
 1 2 1 1
 1 3 1 1
 2 4 4 2
</code></pre>

Finally, 

Dense_rank() is an extended version of rank() as the name suggests its dense because as you can see from the above example rank() = dense_rank() for all data 1 but just that for data 2 it differs in the form that it mantains the order of rank() from previous rank() not the actual data

blocks|key|538126|text|RANK()和DENSE_RANK()函数之间的唯一区别是在存在“平局”的情况下；即在一个集合中的多个值具有相同的排名的情况下。在这种情况下，RANK()将为集合中的值分配非连续的“排名”(当存在平局时，会导致整数排名值之间的差距)，而DENSE_RANK()将为集合中的值分配连续的排名(因此，在平局的情况下，整数排名值之间将没有差距)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|538127|例如，考虑集合{30，30，50，75，75,100}。对于这样的集合，RANK()将返回{1，1，3，4，4，4，6}+(请注意，值2和5已被跳过)，而DENSE_RANK()将返回{1,1,2,3,3,4}。|538128|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a “tie”; i.e., in cases where multiple values in a set have the same ranking. In such cases, RANK() will assign non-consecutive “ranks” to the values in the set (resulting in gaps between the integer ranking values when there is a tie), whereas DENSE_RANK() will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie).
For example, consider the set {30, 30, 50, 75, 75, 100}. For such a set, RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped), whereas DENSE_RANK() will return {1,1,2,3,3,4}.

What's the difference between <code>RANK()</code> and <code>DENSE_RANK()</code> functions? How to find out nth salary in the following <code>emptbl</code> table?

<pre><code>DEPTNO EMPNAME SAL
------------------------------
10 rrr 10000.00
11 nnn 20000.00
11 mmm 5000.00
12 kkk 30000.00
10 fff 40000.00
10 ddd 40000.00
10 bbb 50000.00
10 ccc 50000.00
</code></pre>

If in the table data having <code>nulls</code>, what will happen if I want to find out <code>nth</code> salary?

What's the difference between RANK() and DENSE_RANK() functions in oracle?

RANK()函数和DENSE_RANK()函数有什么不同？如何在下面的emptbl表中找到第n个薪水？DEPTNO  EMPNAME    SAL------------------------------10       rrr    10000.0011       nnn    20000.0011       ...

问oracle中的RANK()函数和DENSE_RANK()函数有什么不同？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问oracle中的RANK()函数和DENSE_RANK()函数有什么不同？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问oracle中的RANK()函数和DENSE_RANK()函数有什么不同？
EN