ORACLE DENSE_RANK()

오라클 DENSE_RANK()

   - 순위를 정하기 좋은 함수 같다. 그룹내에 정렬된 데이타의 순위를 매기기 좋은 함수 정도 ??

 

Aggregate Syntax 

생략

 

Analytic Syntax

DENSE_RANK() OVER(PARTION BY ~~~ ORDER BY ~~~)

 

DENSE_RANK computes the rank of row in an ordered group of rows and returns the rank as a NUMBER. Then ranks are consecutive integers beginning with 1. The larget rank value is the number of unique values returned by the queyr. Rank values are not skipped in the event of ties.  Rows with equal values for the ranking criteria receive the same rank.  This function is useful for top-N and bottom-N reporting.

 

This function accepts as arguments any numeric data type and returns NUMBER.

 

• As an aggregate function, DENSE_RANK calculates the dense rank of a hypothetical row identified by the arguments of the function with respect to a given sort specification. The arguments of the function must all evaluage to constant expressions within each aggregate group, because they identify a single row within each grou. The constant argument expressions and the expression in the order_by_clause of the aggregate match by position. Therefore, the number of arguments must be the same and types must be compatible.
• As an analytic function DENSE_RANK computes the rank of each row returned form a query with respect to the other rows, based on the values of the value_exprs in the order_by_caluse.

 

Aggregate Example

 

SQL > SELECT DENSE_RANK(15500, 0.5) WITHIN GROUP (ORDER BY salary desc, commission_pct) from employees ;

   --> 3

 

Analytic Example

 

SQL> SELECT employee_id, department_id, last_name, salary, DENSE_RANK() OVER(PARTITION BY department_id ORDER BY SALARY) AS DENSE_RANK
   FROM employees
WHERE department_id=20 ;

 

--> 202   20  Fay         6000     1
     201   20  Hartstein  13000    2

 

 

 

 

RANK() 와 조금 다르다.

 

https://web-obj.tistory.com/433

[Oracle] RANK()