MySql场景面试题：满意度调查分组去除最高最低求平均分

场景描述

我们有一个员工满意度调查系统，数据库中有一张表：

1. survey_scores 表：存储员工对公司的满意度打分。

表结构如下：

-- 调查打分表
CREATE TABLE survey_scores (
    score_id INT AUTO_INCREMENT PRIMARY KEY,
    department VARCHAR(50), -- 部门
    employee_id INT,        -- 员工ID
    score DECIMAL(5, 2)     -- 满意度打分（0-10分）
);

插入一些测试数据：

-- 插入调查打分数据
INSERT INTO survey_scores (department, employee_id, score) VALUES
('HR', 1, 8.5),
('HR', 2, 9.0),
('HR', 3, 7.0),
('HR', 4, 8.0),
('HR', 5, 9.5),
('Engineering', 6, 7.5),
('Engineering', 7, 8.0),
('Engineering', 8, 6.5),
('Engineering', 9, 9.0),
('Engineering', 10, 8.5),
('Sales', 11, 6.0),
('Sales', 12, 7.0),
('Sales', 13, 6.5),
('Sales', 14, 8.0),
('Sales', 15, 7.5);

面试题

编写一个 SQL 查询，实现以下需求：

1. 按部门（department）分组，去除每个部门的最高分和最低分。
2. 计算每个部门的平均分（去除最高分和最低分后）。
3. 返回以下字段：
   • department（部门）
   • avg_score（去除最高分和最低分后的平均分）

预期结果

上述数据，查询结果类似于：

HR 部门：

• 原始分数：[7.0, 8.0, 8.5, 9.0, 9.5]
• 去除最高分（9.5）和最低分（7.0）后：[8.0, 8.5, 9.0]
• 平均分：(8.0 + 8.5 + 9.0) / 3 = 8.17

解题思路

1. 标记每个部门的最高分和最低分：

   • 使用窗口函数 ROW_NUMBER() 对每个部门的打分按升序和降序排名，标记最高分和最低分。

2. 过滤掉每个部门的最高分和最低分：

   • 使用子查询或 CTE（Common Table Expressions）过滤掉每个部门的最高分和最低分。

3. 计算每个部门的平均分：

   • 对过滤后的数据按部门分组，计算平均分。

SQL 实现

-- 第一步：标记每个部门的最高分和最低分
WITH ranked_scores AS (
    SELECT
        department,
        score,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY score ASC) AS asc_rank,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY score DESC) AS desc_rank
    FROM
        survey_scores
)

-- 第二步：过滤掉每个部门的最高分和最低分，计算平均分
SELECT
    department,
    AVG(score) AS avg_score
FROM
    ranked_scores
WHERE
    asc_rank > 1 AND desc_rank > 1 -- 去除最高分和最低分
GROUP BY
    department;

查询结果

上述数据，查询结果：

考察点

1. 窗口函数：

   • 使用 ROW_NUMBER() 对每个部门的打分进行排名。

2. 子查询和 CTE（Common Table Expressions）：

   • 使用 WITH 子句将复杂查询分解为多个步骤，提高可读性。

考虑mysql5.7版本

不支持窗口函数和CTS

实现思路

1. 找到每个部门的最高分和最低分：

   • 使用 GROUP BY 和 MAX()、MIN() 聚合函数找到每个部门的最高分和最低分。

2. 过滤掉每个部门的最高分和最低分：

   • 使用子查询将原始数据与最高分和最低分进行比较，排除这些分数。

3. 计算每个部门的平均分：

   • 对过滤后的数据按部门分组，计算平均分。

-- 第一步：找到每个部门的最高分和最低分
SELECT
    s.department,
    AVG(s.score) AS avg_score
FROM
    survey_scores s
JOIN (
    SELECT
        department,
        MAX(score) AS max_score, -- 最高分
        MIN(score) AS min_score  -- 最低分
    FROM
        survey_scores
    GROUP BY
        department
) min_max_scores ON s.department = min_max_scores.department
WHERE
    s.score != min_max_scores.max_score AND s.score != min_max_scores.min_score -- 去除最高分和最低分
GROUP BY
    s.department;