冬奥会在即，比赛打分去除一个最低分，去除一个最高分，求平均分？ --- 牛客网SQL54题给我的反思

2023-10-15 20:36:28

北京2022冬奥会要来了，像比如谷爱凌参加的*式滑雪大跳台项目等打分制项目，多采用去掉一个最高分、去掉一个最低分后取平均值或总和的方式进行打分，今天做了一道牛客网SQL中等难度的题去掉最大最小工资后求平均值，有一些思考和总结，写在下面，欢迎各位小伙伴、前辈探讨指导~

牛客网原题 -- 求平均工资：

查找排除在职(to_date = '9999-01-01' )员工的最大、最小salary之后，其他的在职员工的平均工资avg_salary。

建表语句：

CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
如：
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,70698,'2000-11-27','2001-11-27');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');

查询语句：

SELECT
AVG(salary)
FROM (
SELECT
*
,ROW_NUMBER() OVER(ORDER BY salary) r1 #按照升序排，此时第一名是salary最小的
,ROW_NUMBER() OVER(ORDER BY salary DESC) r2 #按照降序排，此时第一名是salary最大的
FROM salaries
WHERE to_date = '9999-01-01'
) a
WHERE a.r1 <> 1 #排除倒数第一
AND a.r2 <> 1 #排除正数第一

拓展思考

Q1：窗口函数为啥用的是ROW_NUMBER()

A1：我们来看一下我们熟知的排名窗口函数的排序效果

在数值重复的情况下，只有ROW_NUM()能保证单个排名对应的行数为1，就是一行对应一个unique的排名。

假设一种情况，去掉一个最低分去掉一个最高分的比赛打分情形，如果是个裁判都打满分10分，要是rank()和dense_rank()，岂不是都是1，都给我去掉了。。所以应该使用ROW_NUM()

Q2:求最大值/最小值的思路？

A2：①窗口函数排名 + ASC 后的第一个 = 最小值; 窗口函数排名 + DESC 后第一个 = 最大值差最值 ② max() 最大值/ min()最大值

欢迎大家批评指正，讨论补充~