一个关于薪资分析的SQL实战挑战

SQL - 这篇文章属于一个选集。

§ 1: 本文

最近我遇到了一个有趣的 SQL 挑战。这个场景要求使用 窗口函数 来分析各部门员工的薪资，这是一个非常强大但有时也有些棘手的功能。

下面是问题的描述以及我如何解决它。

问题描述
#

问题：任务要求编写一个查询来分析各个部门员工的薪资排名，具体要求如下：

按薪资排名：对每个部门的员工，按薪资降序排列并给出排名。
薪资差异计算：计算每个员工和同一部门中薪资低于他们的下一位员工之间的薪资差异。对于薪资最低的员工，SalaryDifference 应显示为 NULL。
突出高薪员工：添加一个列，命名为 IsTopEarner，如果员工是部门内薪资最高的，显示 “Yes”，否则显示 “No”。

ID	Name	DivisionID	ManagerID	Salary
356	Daniel Smith	100	133	40000
122	Arnold Sully	101	null	60000
467	Lisa Roberts	100	null	80000
112	Mary Dial	105	467	65000
775	Dennis Front	103	null	90000
111	Larry Weis	104	35534	75000
222	Mark Red	102	133	86000
577	Robert Night	105	12353	76000
133	Susan Wall	105	577	110000

Notice:

Table name: Employees
MySQL version: 5.5.56-log

查询结果应该包括以下列，并按 SalaryRank 升序排列：

DivisionID（部门 ID）
Name（员工名字）
Salary（员工薪资）
SalaryRank（员工在部门内的薪资排名，1 为最高薪资）
SalaryDifference（该员工和薪资低于他/她的下一位员工之间的薪资差异）
IsTopEarner（该员工是否是部门内薪资最高的员工）

-- Create the table
CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(100),
    DivisionID INT,
    ManagerID INT,
    Salary DECIMAL(10, 2)
);

-- Insert data into the Employees table
INSERT INTO Employees (ID, Name, DivisionID, ManagerID, Salary)
VALUES
(356, 'Daniel Smith', 100, 133, 40000),
(122, 'Arnold Sully', 101, NULL, 60000),
(467, 'Lisa Roberts', 100, NULL, 80000),
(112, 'Mary Dial', 105, 467, 65000),
(775, 'Dennis Front', 103, NULL, 90000),
(111, 'Larry Weis', 104, 35534, 75000),
(222, 'Mark Red', 102, 133, 86000),
(577, 'Robert Night', 105, 12353, 76000),
(133, 'Susan Wall', 105, 577, 110000);

解答与分析
#

1. 分析问题
#

这是一个典型的 SQL 窗口函数 应用场景，涉及：

需要对数据进行按部门分组（PARTITION BY DivisionID）。
按照某一条件对行进行排名（RANK() 或 ROW_NUMBER()）。
比较当前行与相邻行的值（LEAD()）。
确定每个分组中的第一行（FIRST_VALUE() 或使用条件逻辑）。

2. 编写查询
#

下面是满足要求的完整查询：

SELECT
    DivisionID,
    Name,
    Salary,
    ROW_NUMBER() OVER (PARTITION BY DivisionID ORDER BY Salary DESC) AS SalaryRank,
    Salary - LEAD(Salary) OVER (PARTITION BY DivisionID ORDER BY Salary DESC) AS SalaryDifference,
    CASE
        WHEN RANK() OVER (PARTITION BY DivisionID ORDER BY Salary DESC) = 1 THEN 'Yes'
        ELSE 'No'
    END AS IsTopEarner
FROM Employees
ORDER BY DivisionID, SalaryRank;

3. 查询解析
#

按薪资排名使用 RANK()或者ROW_NUMBER() 函数根据部门（PARTITION BY DivisionID）对员工按薪资降序排列（ORDER BY Salary DESC），生成薪资排名：

RANK() OVER (PARTITION BY DivisionID ORDER BY Salary DESC) AS SalaryRank
# or
ROW_NUMBER() OVER (PARTITION BY DivisionID ORDER BY Salary DESC) AS SalaryRank

计算薪资差异使用 LEAD() 函数获取同一部门内下一行（薪资较低的员工）的薪资（PARTITION BY DivisionID），并计算两者之间的差异：

Salary - LEAD(Salary) OVER (PARTITION BY DivisionID ORDER BY Salary DESC) AS SalaryDifference,

对于薪资最低的员工，由于 LEAD() 无法获取下一行的值，差异将为 NULL。

突出高薪员工使用 CASE 语句判断是否为部门内薪资最高的员工（SalaryRank = 1），并标记为 “Yes”，其他员工为 “No”：

CASE
    WHEN RANK() OVER (PARTITION BY DivisionID ORDER BY Salary DESC) = 1 THEN 'Yes'
    ELSE 'No'
END AS IsTopEarner
# or
CASE
    WHEN ROW_NUMBER() OVER (PARTITION BY DivisionID ORDER BY Salary DESC) = 1 THEN 'Yes'
    ELSE 'No'
END AS IsTopEarner

运行结果
#

查询结果将会是：

DivisionID	Name	Salary	SalaryRank	SalaryDifference	IsTopEarner
100	Lisa Roberts	80000.00	1	40000.00	Yes
100	Daniel Smith	40000.00	2		No
101	Arnold Sully	60000.00	1		Yes
102	Mark Red	86000.00	1		Yes
103	Dennis Front	90000.00	1		Yes
104	Larry Weis	75000.00	1		Yes
105	Susan Wall	110000.00	1	34000.00	Yes
105	Robert Night	76000.00	2	11000.00	No
105	Mary Dial	65000.00	3		No

关键点
#

窗口函数的强大功能像 RANK(), ROW_NUMBER() 和 LEAD() 这样的窗口函数在需要在分组内按行执行计算时非常有效。
模块化查询编写将任务分解为较小的部分（如排名、差异计算和条件逻辑）有助于简化问题解决过程，确保查询的清晰性和可维护性。
高效调试独立测试查询的各个部分（如先检查 RANK() 的输出，再添加 LEAD() 计算）可以加速调试过程。

总结
#

这个挑战是一个很好的实践，帮助我应用 SQL 窗口函数 解决实际问题。从薪资排名到比较相邻行值，再到标识高薪员工，这个查询展示了窗口函数如何将复杂的任务转化为简洁的解决方案。如果你正在面对类似的挑战，掌握这些窗口函数将大大提升你的 SQL 技能和数据分析能力。关于窗口函数的详细内容，我写了一篇文章介绍，欢迎阅读交流。

SQL - 这篇文章属于一个选集。

§ 1: 本文

§ 2: 掌握 SQL 窗口函数：综合指南

问题解决——本地提交至GitHub仓库，贡献不显示

2 分钟

Yuzhen 问题解决 Git

问题描述 #

解答与分析 #

1. 分析问题 #

2. 编写查询 #

3. 查询解析 #

运行结果 #

关键点 #

总结 #

相关文章