查找员工在入职以来的薪水涨幅情况
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;