[SQL] 순위 함수 ROW_NUMBER(), RANK(), DENSE_RANK()

순위를 매기는 함수 종류

1. RANK

2. DENSE_RANK

3. ROW_NUMBER

4. NTILE

5. PARTITION BY

 

- RANK, DENSE_RANK는 공동순위를 인정

- ROW_NUMBER은 공동순위 인정x 

 

1. RANK

RANK 함수는 중복 값들에 대해서 동일 순위로 표시하고, 중복 순위 다음 값에 대해서는 중복 개수만큼 떨어진 순위로 출력하도록 하는 함수 

ex) 1 2 2 4

RANK() OVER (ORDER BY  salary DESC) 

 

2. DENSE_RANK

DENSE_RANK 함수는 중복 값들에 대해서 동일 순위로 표시하고, 중복 순위 다음 값에 대해서는 중복 값 개수와 상관없이 순차적인 순위 값을 출력하도록 하는 함수 

ex) 1 2 2 3

DENSE_RANK() OVER (ORDER BY salary DESC) 

 

3. ROW_NUMBER

DENSE_RANK 함수는 중복 값들에 대해서 동일 순위로 표시하고, 중복 순위 다음 값에 대해서는 중복 값 개수와 상관없이 순차적인 순위 값을 출력하도록 하는 함수 

ex) 1 2 3 4 

ROW_NUMBER() OVER (ORDER BY  salary DESC) 

 

4. NTILE

NTILE함수는 뒤에 함께 적어주는 숫자 만큼으로 등분을 하는 함수

만약 직원들 데이터에 대해서 salary 순서를 기준으로 4등분을 하고자 한다면?

NTILE(4) OVER (ORDER BY salary DESC)  # salary 순서를 기준으로 4등분 

 

5. PARTITION BY

단순히 모든 사람의 salary를 순위 매기고 싶은 것이 아니라, 직급별 순위를 매기고 싶다면?

PARTITION BY절을 사용하여 직급 별로 구분을 해서 순위를 매기면 됨

RANK() OVER (PARTITION BY job ORDER BY salary DESC) 

---

리트코드 문제 예시 

 

586. Customer Placing the Largest Number of Orders

Table: Orders

+-----------------+----------+
| Column Name     | Type     |
+-----------------+----------+
| order_number    | int      |
| customer_number | int      |
+-----------------+----------+
order_number is the primary key for this table.
This table contains information about the order ID and the customer ID.

 

Write an SQL query to find the customer_number for the customer who has placed the largest number of orders.

The test cases are generated so that exactly one customer will have placed more orders than any other customer.

The query result format is in the following example.

 

Example 1:

Input: 
Orders table:
+--------------+-----------------+
| order_number | customer_number |
+--------------+-----------------+
| 1            | 1               |
| 2            | 2               |
| 3            | 3               |
| 4            | 3               |
+--------------+-----------------+
Output: 
+-----------------+
| customer_number |
+-----------------+
| 3               |
+-----------------+
Explanation: 
The customer with number 3 has two orders, which is greater than either customer 1 or 2 because each of them only has one order. 
So the result is customer_number 3.

 

풀이1 : WITH절 서브쿼리 이용

1. customer_number별 count를 가진 cnt with절 서브쿼리 생성

2. customer_number이 최대인 customer_number select

WITH cnt AS (
  SELECT customer_number, COUNT(customer_number) c
  FROM orders
  GROUP BY customer_number
)

SELECT customer_number
FROM cnt
WHERE c = (SELECT MAX(c) FROM cnt)

 

풀이2 : FROM절 서브쿼리 - DENSE_RANK 함수 이용 

1. FROM절 서브쿼리에서 customer_number별 customer_number과 order_number의 순위 select

2.  order_rank가 1인 customer_number select

SELECT customer_number
FROM (SELECT customer_number
    , DENSE_RANK() OVER(ORDER BY COUNT(order_number) DESC) order_rank
      FROM orders
      GROUP BY customer_number) r
WHERE order_rank = 1