sql排名函数rank()、row_number()、dense

-- rank()排名是不连续的，出现同名的排序序号会相同(比较常规的排序)，且序号会断开

select 
employee_id
,last_name
,department_id
,salary
,rank() over(partition by department_id order by salary desc) 
from employees

-- row_number()排名是连续的,出现同名的序号仍然会连续(用来记录次数比较实用)，且不会跳跃

select 
employee_id
,last_name
,department_id
,salary
,row_number() over(partition by department_id order by salary desc) 
from employees

-- dense_rank()排名是连续的，出现同名的序号会相同，且不会跳跃（用的比较少的，单纯需要排名且存在并列也不跳跃）

select 
employee_id 
,last_name 
,department_id 
,salary 
,dense_rank() over(partition by department_id order by salary desc) 
from employees 

-- ntile()根据排序指定数量进行分区编号（分区编号排序）

select 
employee_id 
,last_name 
,department_id 
,salary 
,ntile(3) over(partition by department_id order by salary desc) 
from employees