窗口函数大概分类

窗口函数不改变原始数据，只在原表上新增字段，并在新增字段实现结果，为每一行都返回一个结果。

 

一、排名窗口函数

row_number() over(partition by xxx order by xxx desc/asc) 窗口内记录的唯一序号，没有重复值

rank() over(partition by xxx order by xxx desc/asc) 排名，存在并列名次，跳跃排名

dense_rank() over(partition by xxx order by xxx desc/asc) 排名，存在并列名次，但是不跳跃

 

二、聚合窗口函数

avg() over(partition by xxx order by xxx desc/asc) 平均值

sum() over(partition by xxx order by xxx desc/asc) 求和

count() over(partition by xxx order by xxx desc/asc) 计数

max() over(partition by xxx order by xxx desc/asc) 最大

min() over(partition by xxx order by xxx desc/asc) 最小

 

三、取值窗口函数

first_value() over(partition by xxx order by xxx desc/asc) 窗口内的第一条记录的值

last_value() over(partition by xxx order by xxx desc/asc) 窗口内的最后一条记录的值

nth_value(col, n) 窗口内第n行记录的值 

lag(col, n) 向前取第n行的值（默认是前一行）

lead(col, n) 向后取第n行的值（默认是后一行）

 

例1：查看每位员工比前一位员工的工资多多少？

SELECT
employee_id,
salary,
LAG(salary) OVER (ORDER BY hire_date) AS previous_salary, --取当前行前一行的salary
salary - LAG(salary) OVER (ORDER BY hire_date) AS salary_diff --当前行和前一行的diff
FROM employees;

例2: 每个部门中，展示该部门最早入职员工的薪资。

SELECT
employee_id,
department_id,
salary,
FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS first_salary 
FROM employees;