mysql窗口函数（保姆级文档）

文学者

已于 2023-05-29 22:57:24 修改

阅读量266

点赞数 5

分类专栏： mysql 文章标签： mysql 数学建模数据库

于 2023-05-29 22:57:12 首次发布

本文链接：https://blog.csdn.net/weixin_62404729/article/details/130938315

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

mysql窗口函数

第一步：创建数据库

CREATE DATABASE jwdg

USE `jwdg`;

第二步：创建表格

DROP TABLE employee;
CREATE TABLE employee(
employee_id INT PRIMARY KEY, -- 员工id
employee_name VARCHAR(50),-- 员工姓名
employee_sex CHAR(3), -- 员工性别
employee_age INT, -- 员工年龄
department VARCHAR(50), -- 部门名
entry_date DATE, -- 入职日期
salary DOUBLE -- 薪资
)ENGINE=INNODB DEFAULT CHARSET=utf8;

第三步：插入数据

INSERT INTO employee VALUES(75001,'张三',"男",26,'人事部','2022-01-01',4000);
INSERT INTO employee VALUES(75002,'李四',"男",35,'人事部','2022-01-02',9000);
INSERT INTO employee VALUES(75003,'王五',"男",25,'人事部','2022-01-03',7500);
INSERT INTO employee VALUES(75004,'六毛',"男",31,'人事部','2022-01-04',8000);
INSERT INTO employee VALUES(75005,'玉儿',"女",23,'人事部','2022-01-05',6800);
INSERT INTO employee VALUES(75006,'孙文',"男",33,'销售部','2022-01-06',9000);
INSERT INTO employee VALUES(75007,'黎浩',"男",21,'销售部','2022-01-07',4000);
INSERT INTO employee VALUES(75008,'项钰',"女",35,'销售部','2022-01-08',6000);
INSERT INTO employee VALUES(75009,'蜜儿',"女",23,'销售部','2022-01-09',5000);
INSERT INTO employee VALUES(75010,'儒少',"男",31,'销售部','2022-01-10',4000);
INSERT INTO employee VALUES(75011,'小松',"男",23,'技术部','2022-01-11',8500);
INSERT INTO employee VALUES(75012,'李天纲',"男",22,'技术部','2022-01-12',7000);
INSERT INTO employee VALUES(75013,'文绝',"男",25,'技术部','2022-01-13',9000);
INSERT INTO employee VALUES(75014,'小苏',"女",27,'技术部','2022-01-14',10000);
INSERT INTO employee VALUES(75015,'李天',"男",27,'技术部','2022-01-15',11000);
INSERT INTO employee VALUES(75016,'战天',"男",30,'安保部','2022-01-16',8000);
INSERT INTO employee VALUES(75017,'斗地',"男",29,'安保部','2022-01-17',7000);
INSERT INTO employee VALUES(75018,'剑圣',"男",35,'安保部','2022-01-18',5500);
INSERT INTO employee VALUES(75019,'枪手',"男",42,'安保部','2022-01-19',8000);
INSERT INTO employee VALUES(75020,'老六',"男",22,'安保部','2022-01-20',7000);

SELECT * FROM  employee;

第四步:窗口函数

1、序号函数

**序号函数- ROW_NUMBER、RANK、DENSE_RANK**
eq:row_number() over (partition by xxx order by xxx )

练习题需求：对每个部门的员工按照薪资排序，并给出排名

-- 查询出每个部门并进行排序 如薪资相同排名不相同，
-- 如: 有三个人薪资是6000 我查询后看到的排名是 1 2 3名 他们的排名不一样
SELECT *,row_number() over (PARTITION BY department ORDER BY salary DESC) AS "排名" FROM employee



-- 查询出每个部门并进行排序 如薪资相同排名相同，
-- 有一个问题
-- 如果两个排名第一的话就不会有第二名、如果有三个人 分别为值分别为  2 2 1 的话 
-- 他们的排名就是 两个第一 一个第三
SELECT *,rank() over (PARTITION BY department ORDER BY salary DESC) AS "排名" FROM employee




-- 查询除每个部门并进行排序 如薪资相同排名相同  
SELECT *,DENSE_RANK() over (PARTITION BY department ORDER BY salary DESC) AS "排名" FROM employee

2、其他函数

其他函数-NTH_VALUE(expr, n)、NTILE(n)
练习题需求：将每个部门员工按照入职日期分成3组

SELECT 
  department,
  employee_name,
  employee_sex,
  employee_age,
  department,
  salary,
ntile(3) over(PARTITION BY department ORDER BY  entry_date  ) AS "分组"
FROM employee;

– 返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
– 需求：截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资

SELECT 
  department,
    employee_name,
    employee_sex,
    employee_age,
    department,
    salary,
  nth_value(salary,2) over(PARTITION BY department ORDER BY entry_date) AS "查看第二个入职的薪资",
  nth_value(salary,3) over(PARTITION BY department ORDER BY entry_date) AS "查询第三个入职的薪资"
FROM employee

3、头尾函数

头尾函数-FIRST_VALUE和LAST_VALUE

返回第一个（FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）expr的值
需求：截止到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资

-- first_value 如果是升序就回事第一条数据的薪资如果是降序就会使当前部门最后一条数据
-- LAST_VALUE  默认统计范围是取当前行数据与当前行之前的数据的比较。
-- 如果崽 升序或者降序后面添加 ROWS BETWEEN unbounded preceding AND unbounded following 效果也就和 first_value 意思相差不大
-- 唯一的差距就是first_value函数默认升序时查看第一行数据的薪资 而LAST_VALUE函数默认升序时查看最后一行数据的薪资
SELECT
     department,
    employee_name,
    employee_sex,
    employee_age,
    department,
    salary,
  first_value(salary) over(PARTITION BY department ORDER BY entry_date ASC) AS "每个部门入职第一人薪资", -- 查看第一个薪资
  LAST_VALUE(salary) over(PARTITION BY department ORDER BY entry_date  ROWS BETWEEN unbounded preceding AND unbounded following ) AS "每个部门入职最后一个人薪资"
FROM  employee;

4、聚合函数

在窗口中每条记录动态地应用聚合函数（SUM()、AVG()、MAX()、MIN()、COUNT()）

-- 可以动态计算在指定的窗口内的各种聚合函数值。
--  重要: 5 指的是当前这行数据叠加前五条数据从
-- sum 叠加六条数据的薪资 SUM(salary) over(PARTITION BY dname ORDER BY salary   ROWS BETWEEN 5 preceding AND current ROW)
-- sum 叠加六条数据的薪资平均值 AVG(salary) over(PARTITION BY dname ORDER BY salary   ROWS BETWEEN 5 preceding AND current ROW)
-- sum 叠加六条数据的薪资最小值 MIN(salary) over(PARTITION BY dname ORDER BY salary   ROWS BETWEEN 5 preceding AND current ROW)
-- sum 叠加六条数据的薪资最大值 MAX(salary) over(PARTITION BY dname ORDER BY salary   ROWS BETWEEN 5 preceding AND current ROW)
SELECT
     department,
    employee_name,
    employee_sex,
    employee_age,
    department,
    salary,
    SUM(salary) over(PARTITION BY department ORDER BY salary   ROWS BETWEEN 2 preceding AND current ROW) AS '叠加薪资'
FROM employee GROUP BY employee_id;

SELECT
   department,
    employee_name,
    employee_sex,
    employee_age,
    department,
    salary,
     SUM(salary) over(PARTITION BY department ORDER BY salary   ROWS BETWEEN 5 preceding AND current ROW) AS '叠加薪资',
    AVG(salary) over(PARTITION BY department ORDER BY salary   ROWS BETWEEN 5 preceding AND current ROW) AS '叠加时薪资平均值',
    MIN(salary) over(PARTITION BY department ORDER BY salary   ROWS BETWEEN 5 preceding AND current ROW) AS '叠加查询最小值',
    MAX(salary) over(PARTITION BY department ORDER BY salary   ROWS BETWEEN 5 preceding AND current ROW) AS '叠加查询薪资最大值'
FROM employee GROUP BY employee_id;

5、分布函数

分布函数- CUME_DIST

-- 如果按升序排列，则统计：小于等于当前值的行数/总行数
-- 如果是降序排列，则统计：大于等于当前值的行数/总行数。
-- 需求:排序每个部门根据薪资排序并且查看
-- 当前薪资当前部门有多少人达到
SELECT
     department,
    employee_name,
    employee_sex,
    employee_age,
    department,
    salary,
    DENSE_RANK() over(PARTITION BY department ORDER BY salary DESC ) AS rn,
    CUME_DIST() over(PARTITION BY department ORDER BY salary DESC ) AS "当前薪资所有占比率"
FROM employee;

6、前后函数

前后函数-LAG和LEAD

– 返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值
– 需求：查询前5人的入职时间改为2000-01-01 后2人的入职时间改为null


-- lag和lead 主要用来计算当前行的前后N 行的这种场景，一般情况下我们会对数据进行排序，
-- 因为只有在有序的情况下，前面多少行和后面多少行才有意义
SELECT 
 department,
    employee_name,
    employee_sex,
    employee_age,
    department,
    salary,
 lag(entry_date,2,'2000-01-01') over(PARTITION BY department ORDER BY entry_date) AS "时间更改为2000-01-01", -- 每个部门第一条数据改为2000-01-01
 lead(entry_date,3) over(PARTITION BY department ORDER BY entry_date) AS "时间更改为null"  -- 每个部门后两条数据改为 null
FROM employee;