我要评论场景描述
我们有一个员工满意度调查系统，数据库中有一张表：
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 查询，实现以下需求：
- 按部门（
department）分组，去除每个部门的最高分和最低分。 - 计算每个部门的平均分（去除最高分和最低分后）。
- 返回以下字段：
department（部门）avg_score（去除最高分和最低分后的平均分）
预期结果
上述数据，查询结果类似于：
| department | avg_score |
|---|---|
| hr | 8.17 |
| engineering | 8.00 |
| sales | 7.00 |
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
解题思路
标记每个部门的最高分和最低分：
- 使用窗口函数
row_number()对每个部门的打分按升序和降序排名，标记最高分和最低分。
过滤掉每个部门的最高分和最低分：
- 使用子查询或 cte（common table expressions）过滤掉每个部门的最高分和最低分。
计算每个部门的平均分：
- 对过滤后的数据按部门分组，计算平均分。
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;查询结果
上述数据，查询结果：
| department | avg_score |
|---|---|
| hr | 8.17 |
| engineering | 8.00 |
| sales | 7.00 |
考察点
- 窗口函数：
- 使用
row_number()对每个部门的打分进行排名。
- 使用
- 子查询和 cte（common table expressions）：
- 使用
with子句将复杂查询分解为多个步骤，提高可读性。
- 使用
考虑mysql5.7版本
不支持窗口函数和cts
实现思路
- 找到每个部门的最高分和最低分：
- 使用
group by和max()、min()聚合函数找到每个部门的最高分和最低分。
- 使用
- 过滤掉每个部门的最高分和最低分：
- 使用子查询将原始数据与最高分和最低分进行比较，排除这些分数。
- 计算每个部门的平均分：
- 对过滤后的数据按部门分组，计算平均分
到此这篇关于mysql满意度调查分组去除最高最低求平均分的文章就介绍到这了,更多相关mysql内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论