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

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

article 2024/12/28 8:43:58

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,
       CASE
           WHEN COUNT(*) = 1
               THEN max(role)
           ELSE 'B' END  com_role
FROM Roles
WHERE role IN ('D', 'O')
GROUP BY person;

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

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

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

SELECT person,
       SUBSTR('DOB', cast(SUM(CASE
                                  WHEN role = 'D' THEN 1
                                  WHEN role = 'O' THEN 2
                                  ELSE 0
           END) 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。

数据转换的创意：