mysql篇-窗口函数

1.窗口函数（mysql 8.0版本之后才能使用）：

定义：SELECT <window functions> OVER(PARTITION BY ___ ORDER BY___) FROM Table
注：window functions包括： sum()、max()、min()、count()、avg()

2.排序函数和窗口函数配合使用

 定义：<ranking function> OVER (ORDER BY <order by columns>)

3.示例：

1. 数据准备

create database ai charset=utf8;
use ai;

create table employee(
    id int unsigned primary key not null,
    first_name varchar(20) not null,
    last_name varchar(30) not null,
    department_id  tinyint not null,
    salary int not null,
    years_worked  tinyint not null
);
insert into employee values
(1, 'Diane', 'Turner', 1, 5330, 4),
(2, 'Clarence', 'Robinson', 1, 3617, 2),
(3, 'Eugene', 'Phillips', 1, 4877, 2),
(4, 'Philip', 'Mitchell', 1, 5259, 3),
(5, 'Ann', 'Wright', 2, 2094, 5),
(6, 'Charles', 'Wilson', 2, 5167, 5),
(7, 'Russell', 'Johnson', 2, 3762, 4),
(8, 'Jacqueline', 'Cook', 2, 6923, 3),
(9, 'Larry', 'Lee', 3, 2796, 4),
(10, 'Willie', 'Patterson', 3, 4771, 5),
(11, 'Janet', 'Ramirez', 3, 3782, 2),
(12, 'Doris', 'Bryant', 3, 6419, 1),
(13, 'Amy', 'Williams', 3, 6261, 1),
(14, 'Keith', 'Scott', 3, 4928, 8),
(15, 'Karen', 'Morris', 4, 6347, 6),
(16, 'Kathy', 'Sanders', 4, 6286, 1),
(17, 'Joe', 'Thompson', 5, 5639, 3),
(18, 'Barbara', 'Clark', 5, 3232, 1),
(19, 'Todd', 'Bell', 5, 4653, 1),
(20, 'Ronald', 'Butler', 5, 2076, 5)
;

2. 数据表结构为图示：
   

select id,salary, rank() over (order by salary) as rank_ from employee;
select id,salary, dense_rank() over (order by salary) as rank_ from employee;
select id,salary, row_number() over (order by salary) as rank_ from employee;

select id,first_name,salary,avg(salary) over(partition by department_id) as avg_bydepart,salary-avg(salary) over(partition by department_id) as cha_  from employee;