当前位置：首页 > news >正文

SQL进阶技巧：如何分析双重职务问题？

news 来源：原创 2025/5/16 11:46:51

0 背景描述

1 数据准备

2 问题分析

方法2：利用substr函数，充分利用数据特点【优秀解法】

3 小结

0 背景描述

在 CompuServe 刚成立时，Nigel Blumenthal 遇到一个应用程序中的困难。
他需要获取公司人员所担任角色的源表，其中‘D’表示主管（Director），‘O’表示高级职员（Officer）。
需要生成一个包含代码‘B’的报表，表示这个人同时（Both）担任主管和高级职员。

给定数据

原始数据（Roles 表）：

person    role
'Smith'   'O'
'Smith'   'D'
'Jones'   'O'
'White'   'D'
'Brown'   'X'

期望结果（结果表）：

person    combined_role
'Smith'   'B'
'Jones'   'O'
'White'   'D'

Roy Harvey 未经考虑的第一反应是使用分组查询。但除了双重职务（同时有‘D’和‘O’角色）的人，也需要显示只有‘D’或‘O’角色的人。这种思路扩展后的 SQL 查询语句如下

SELECT R1.person, R1.role
FROM Roles AS R1
WHERE R1.role IN ('D', 'O')
GROUP BY R1.person
HAVING COUNT(DISTINCT R1.role) = 1
UNION
SELECT R2.person, 'B'
FROM Roles AS R2
WHERE R2.role IN ('D', 'O')
GROUP BY R2.person
HAVING COUNT(DISTINCT R2.role) = 2

但是这样做有两个分组查询的开销。现状聪明的你，如何帮他优化，写出更高效的SQL呢？

1 数据准备

-- 创建Roles表
CREATE TABLE Roles (person STRING,role STRING
);
-- 插入数据到Roles表
INSERT INTO Roles VALUES ('Smith', 'O');
INSERT INTO Roles VALUES ('Smith', 'D');
INSERT INTO Roles VALUES ('Jones', 'O');
INSERT INTO Roles VALUES ('White', 'D');
INSERT INTO Roles VALUES ('Brown', 'X');

2 问题分析

方法1：采用case when优化

SELECT person,CASEWHEN COUNT(*) = 1THEN max(role)ELSE 'B' END  com_role
FROM Roles
WHERE role IN ('D', 'O')
GROUP BY person;

或利用最大最小值判断，当最大值和最小值相等说明只有一个职位

SELECT person,CASEWHEN MIN(role) <> MAX(role)THEN 'B'ELSE MIN(role) ENDAS combined_role
FROM Roles
WHERE role IN ('D', 'O')
GROUP BY person;

方法2：利用substr函数，充分利用数据特点【优秀解法】

SELECT person,SUBSTR('DOB', cast(SUM(CASEWHEN role = 'D' THEN 1WHEN role = 'O' THEN 2ELSE 0END) as int), 1) AS combined_role
FROM Roles
WHERE role IN ('D', 'O')
GROUP BY person;

这个解答使用了嵌套函数调用，substr()中使用聚合函数。对于人名组成的每个组，case when 语句将在角色列中返回1代表·D'，2代表'O'。然后sum聚合函数将使用这些结果求和，将1转换回'D'，2转换回'O'，3转换回'B'。这是共轭性一个相当有趣的用法，这种用于来回转换的数学方式使问题变得容易。

3 小结

本文方法2更简洁高效，这个 SQL 语句的巧妙之处在于将数据的转换、分组聚合和字符串提取函数结合起来，以简洁的方式实现了根据人员角色组合判断最终角色结果的功能。它充分利用了 SQL 的聚合和函数特性，避免了繁琐的中间步骤和额外的表操作，体现了 SQL 的强大和灵活性。

具体巧妙之处体现在：

简洁性与功能性的结合：

这个 SQL 语句在一行代码中实现了较为复杂的数据转换和分组汇总功能。
它通过使用 CASE 语句对 role 进行映射，将 'D' 映射为 1，'O' 映射为 2，其他情况映射为 0，利用 SUM 函数对映射后的值进行求和，再使用 SUBSTRING 函数根据求和结果从字符串 'DOB' 中提取相应的字符作为最终的 result_role。

数据转换的创意：

利用 CASE 语句实现了数据的条件转换，巧妙地将 role 的不同值映射为数字，为后续的计算和处理提供了便利。

分组与聚合的有效利用：

使用 GROUP BY person 对数据进行分组，确保对每个人员的角色信息进行独立处理。
结合 SUM 函数，将不同的角色映射值聚合在一起，为后续的 SUBSTRING 函数操作提供了基础。

如果您觉得本文还不错，对你有帮助，那么不妨可以关注一下我的数字化建设实践之路专栏，这里的内容会更精彩。

专栏原价99，现在活动价59.9，按照阶梯式增长，还差5个人上升到69.9，最终恢复到原价。

专栏优势：
（1）一次收费持续更新。

（2）实战中总结的SQL技巧，帮助SQLBOY 在SQL语言上有质的飞越，无论你应对业务难题及面试都会游刃有余【全网唯一讲SQL实战技巧，方法独特】

（3）实战中数仓建模技巧总结，让你认识不一样的数仓。【数据建模+业务建模，不一样的认知体系】（如果只懂数据建模而不懂业务建模，数仓体系认知是不全面的）

（4）数字化建设当中遇到难题解决思路及问题思考。

我的专栏具体链接如下

数字化建设通关指南_莫叫石榴姐的博客-CSDN博客

https://blog.csdn.net/godlovedaniel/category_12706766.html

0 背景描述

1 数据准备

2 问题分析

方法1：采用case when优化

方法2：利用substr函数，充分利用数据特点【优秀解法】

3 小结

相关文章：