RANK (Lakehouse v1)

RANK() 함수는 정렬된 값 그룹 내의 각 값에 순위를 할당합니다.

순위 값은 1부터 시작하여 순차적으로 증가합니다. 두 값이 같으면 같은 순위를 가집니다.

Analyze 구문

func.rank().over(partition_by=[<columns>], order_by=[<columns>])

Analyze 예제

table.employee_id, table.first_name, table.last_name, table.department, table.salary, func.rank().over(order_by=table.salary).alias('rank')

| employee_id | first_name | last_name | department | salary | rank |
|-------------|------------|-----------|------------|--------|------|
| 1           | John       | Doe       | IT         | 90000  | 1    |
| 2           | Jane       | Smith     | HR         | 85000  | 2    |
| 3           | Mike       | Johnson   | IT         | 82000  | 3    |
| 4           | Sara       | Williams  | Sales      | 77000  | 4    |
| 5           | Tom        | Brown     | HR         | 75000  | 5    |

SQL 구문

RANK() OVER (
  [ PARTITION BY <expr1> ]
  ORDER BY <expr2> [ { ASC | DESC } ]
  [ <window_frame> ]
)

SQL 예제

테이블 생성

CREATE TABLE employees (
  employee_id INT,
  first_name VARCHAR,
  last_name VARCHAR,
  department VARCHAR,
  salary INT
);

데이터 삽입

INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
  (1, 'John', 'Doe', 'IT', 90000),
  (2, 'Jane', 'Smith', 'HR', 85000),
  (3, 'Mike', 'Johnson', 'IT', 82000),
  (4, 'Sara', 'Williams', 'Sales', 77000),
  (5, 'Tom', 'Brown', 'HR', 75000);

급여 기준으로 직원 순위 매기기

SELECT
  employee_id,
  first_name,
  last_name,
  department,
  salary,
  RANK() OVER (ORDER BY salary DESC) AS rank
FROM
  employees;

결과:

employee_id	first_name	last_name	department	salary	rank
1	John	Doe	IT	90000	1
2	Jane	Smith	HR	85000	2
3	Mike	Johnson	IT	82000	3
4	Sara	Williams	Sales	77000	4
5	Tom	Brown	HR	75000	5