Mysql滿意度調(diào)查分組去除最高最低求平均分的實現(xiàn)思路

更新時間：2025年01月21日 11:44:59 作者：碼到三十五

該文章介紹了如何使用SQL查詢實現(xiàn)按部門分組,去除每個部門的最高分和最低分,并計算剩余分?jǐn)?shù)的平均分,文章詳細(xì)描述了實現(xiàn)這一需求的步驟,包括使用窗口函數(shù)、子查詢和CTE等技術(shù),感興趣的朋友跟隨小編一起看看吧

場景描述

我們有一個員工滿意度調(diào)查系統(tǒng)，數(shù)據(jù)庫中有一張表：

survey_scores 表：存儲員工對公司的滿意度打分。

表結(jié)構(gòu)如下：

-- 調(diào)查打分表
CREATE TABLE survey_scores (
    score_id INT AUTO_INCREMENT PRIMARY KEY,
    department VARCHAR(50), -- 部門
    employee_id INT,        -- 員工ID
    score DECIMAL(5, 2)     -- 滿意度打分（0-10分）
);

插入一些測試數(shù)據(jù)：

-- 插入調(diào)查打分?jǐn)?shù)據(jù)
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 查詢，實現(xiàn)以下需求：

按部門（department）分組，去除每個部門的最高分和最低分。
計算每個部門的平均分（去除最高分和最低分后）。
返回以下字段：
- department（部門）
- avg_score（去除最高分和最低分后的平均分）

預(yù)期結(jié)果

上述數(shù)據(jù)，查詢結(jié)果類似于：

department	avg_score
HR	8.17
Engineering	8.00
Sales	7.00

HR 部門：

原始分?jǐn)?shù)：[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

解題思路

標(biāo)記每個部門的最高分和最低分：

使用窗口函數(shù) ROW_NUMBER() 對每個部門的打分按升序和降序排名，標(biāo)記最高分和最低分。

過濾掉每個部門的最高分和最低分：

使用子查詢或 CTE（Common Table Expressions）過濾掉每個部門的最高分和最低分。

計算每個部門的平均分：

對過濾后的數(shù)據(jù)按部門分組，計算平均分。

SQL 實現(xiàn)

-- 第一步：標(biāo)記每個部門的最高分和最低分
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;