查找员工在入职以来的薪水涨幅情况

查找员工在入职以来的薪水涨幅情况

drop table if exists  `employees` ; 
drop table if exists  `salaries` ;

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));


INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','2001-06-22');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1999-08-03');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');

SELECT * from employees;
SELECT * from salaries;

请你查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-b8Ctifca-1646911194785)(C:\Users\86187\AppData\Roaming\Typora\typora-user-images\image-20220310190344029.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JA6J03Sk-1646911194787)(C:\Users\86187\AppData\Roaming\Typora\typora-user-images\image-20220310190359758.png)]

整体sql，下面拆解

WITH ranking as(
	SELECT 
		emp_no,
		salary,
		from_date,
		to_date
	FROM `salaries`
	 GROUP BY emp_no,salary,from_date,to_date
	 order by emp_no,from_date)
SELECT 
	DISTINCT	e.emp_no,
	birth_date,
	first_name,
	last_name,
	gender,
	hire_date,
	(LAST_VALUE(salary)OVER(PARTITION by emp_no))-(FIRST_VALUE(salary) OVER(PARTITION by emp_no))as incress
FROM ranking r
join employees e
 on r.emp_no = e.emp_no

第一步：

	# 先将这个薪资表处理好（grouop by 需要的字段，order by 需要的字段），虽说这个题只有几行，但是我们要把他当作几百上千行去处理
	SELECT 
		emp_no,
		salary,
		from_date,
		to_date
	FROM `salaries`
	 GROUP BY emp_no,salary,from_date,to_date
	 order by emp_no,from_date

第二步

	(LAST_VALUE(salary)OVER(PARTITION by emp_no))-(FIRST_VALUE(salary) OVER(PARTITION by emp_no))as incress
	
	# 这是比较重要的一步，分组，提取出组内第一行和最后一行
	
	
	

组合一下：

WITH ranking as(
	SELECT 
		emp_no,
		salary,
		from_date,
		to_date
	FROM `salaries`
	 GROUP BY emp_no,salary,from_date,to_date
	 order by emp_no,from_date)
SELECT 
	DISTINCT	e.emp_no,
	birth_date,
	first_name,
	last_name,
	gender,
	hire_date,
	(LAST_VALUE(salary)OVER(PARTITION by emp_no))-(FIRST_VALUE(salary) OVER(PARTITION by emp_no))as incress
FROM ranking r
join employees e
 on r.emp_no = e.emp_no

## FIRST_VALUE(x)与LAST_VALUE(x)

# FISRT_VALUE函数，从名字中能看出，返回指定列的第一个值
SELECT
  name,
  opened,
  budget,
  FIRST_VALUE(budget) OVER(ORDER BY opened)
FROM website;

# 需求：统计id为2的网站每天用户访问情况，以及最少用户访问人数。
SELECT
  day,
  users,
  FIRST_VALUE(users) OVER(ORDER BY users) as `first_value`
FROM statistics
WHERE website_id = 2;

# FIRST_VALUE(x)返回第一个值，LAST_VALUE(x)返回最后一个值

# LAST_VALUE 与 window frame

在上面的例子中，我们没有得到想要的结果，回顾一下之前我们所介绍的 window frame
当OVER子句中包含ORDER BY时，如果我们不显式定义window frame，SQL会自动带上默认的window frame语句：
      RANGE UNBOUNDED PRECEDING, 意味着我们的查询范围被限定在第一行到当前行（current row)

如果想通过LAST_VALUE 与ORDER BY配合得到所有数据排序后的最后一个值，需要吧window frame语句RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
      或者
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
      
SELECT
  name,
  opened,
  LAST_VALUE(opened) OVER(
    ORDER BY opened
    RANGE BETWEEN UNBOUNDED PRECEDINGAND AND UNBOUNDED FOLLOWING
  ) AS `last_value`
FROM website;


ned,
  LAST_VALUE(opened) OVER(
    ORDER BY opened
    RANGE BETWEEN UNBOUNDED PRECEDINGAND AND UNBOUNDED FOLLOWING
  ) AS `last_value`
FROM website;