快捷導(dǎo)航

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

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

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

場(chǎng)景描述

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

survey_scores 表：存儲(chǔ)員工對(duì)公司的滿意度打分。

表結(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分）
);

插入一些測(cè)試數(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);

面試題

編寫(xiě)一個(gè) SQL 查詢，實(shí)現(xiàn)以下需求：

按部門（department）分組，去除每個(gè)部門的最高分和最低分。
計(jì)算每個(gè)部門的平均分（去除最高分和最低分后）。
返回以下字段：
- 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)記每個(gè)部門的最高分和最低分：

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

過(guò)濾掉每個(gè)部門的最高分和最低分：

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

計(jì)算每個(gè)部門的平均分：

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

SQL 實(shí)現(xiàn)

-- 第一步：標(biāo)記每個(gè)部門的最高分和最低分
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
)
-- 第二步：過(guò)濾掉每個(gè)部門的最高分和最低分，計(jì)算平均分
SELECT
    department,
    AVG(score) AS avg_score
FROM
    ranked_scores
WHERE
    asc_rank > 1 AND desc_rank > 1 -- 去除最高分和最低分
GROUP BY
    department;

查詢結(jié)果

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

department	avg_score
HR	8.17
Engineering	8.00
Sales	7.00

考察點(diǎn)

窗口函數(shù)：
- 使用 ROW_NUMBER() 對(duì)每個(gè)部門的打分進(jìn)行排名。
子查詢和 CTE（Common Table Expressions）：
- 使用 WITH 子句將復(fù)雜查詢分解為多個(gè)步驟，提高可讀性。

考慮mysql5.7版本

不支持窗口函數(shù)和CTS

實(shí)現(xiàn)思路

找到每個(gè)部門的最高分和最低分：
- 使用 GROUP BY 和 MAX()、MIN() 聚合函數(shù)找到每個(gè)部門的最高分和最低分。
過(guò)濾掉每個(gè)部門的最高分和最低分：
- 使用子查詢將原始數(shù)據(jù)與最高分和最低分進(jìn)行比較，排除這些分?jǐn)?shù)。
計(jì)算每個(gè)部門的平均分：
- 對(duì)過(guò)濾后的數(shù)據(jù)按部門分組，計(jì)算平均分

到此這篇關(guān)于Mysql滿意度調(diào)查分組去除最高最低求平均分的文章就介紹到這了,更多相關(guān)mysql內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: