专栏首页SQL实现SQL 求平均值时去掉极值

SQL 求平均值时去掉极值

在一些比赛中,为了公平起见,算法端会在评委给出的分数里面去掉一个最高分和一个最低分,再求平均分,平均分即是选手的最后得分。

在某次比赛中,评委给选手 A 打出了一组分数:98、88、94、92、95、93、92,我们怎么用 SQL 来计算选手 A 的最后得分呢?

假设评委打出的分数存储在 t 表的 score 字段里面。

方案一

找到最高分和最低分,将最高分和最低分从分数组中剔除。

使用 max()min() 函数可以分别找到最高分和最低分。

具体的 SQL 实现如下:

SELECT
  AVG(score) AS final_score
FROM
  t
WHERE score NOT IN (
    (SELECT
      MIN(score)
    FROM
      t),
    (SELECT
      MAX(score)
    FROM
      t)
  )

上面的 SQL 还存在一点问题,当最高分或者最低分的分值存在多个时,使用这个方法计算出来的最后得分是不对的。

方案二

先对所有的分数求和,再减去最高分和最低分,最后求平均值。

SELECT 
  (SUM(score) - MIN(score) - MAX(score)) / 
  (COUNT(*) - 2) AS final_score 
FROM
  t

即使存在重复的最高分或最得分,用这个方法计算出来的最后得分也是正确的。

方案三

如果数据库支持窗口函数,可以用窗口函数对分值分别按升序和降序排序(分值相同的序号也不同),去掉序号为 1 的记录再求平均值。

SELECT
  AVG(score) AS final_score
FROM
  (SELECT
    score,
    row_number () over (
  ORDER BY score) score_asc_rn,
  row_number () over (
  ORDER BY score DESC) score_desc_rn
  FROM
    t) t
WHERE score_asc_rn > 1
  AND score_desc_rn > 1

封面图片由 Юрий Сидоренко 在 Pixabay 上发布。

本文分享自微信公众号 - SQL实现(gh_684ee9235a26),作者:zero

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-08-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SQL 累计求和

    今天看到有群友在群里提了这个问题,问题描述见下图。这种需求在做报表统计时经常会遇到,会的人觉得不难,没有接触过可能会被困住,所以我把它拿出来和大家分享。

    白日梦想家
  • 从100万条数据中找到极大值所在行

    先对表数据按照客户来源渠道分组,再对分组内的数据按照授信时间降序排序,序号排在第一位的记录就是我们要找的记录。

    白日梦想家
  • 如何编写不存在即插入的 SQL

    MySQL 已提供了 INSERT IGNORE INTO 、REPLACE INTO、INSERT … ON DUPLICATE KEY UPDATE 等表达...

    白日梦想家
  • “365算法每日学计划”:05打卡-图解冒泡排序(多解法)

    好好学java
  • MySQL 从零开始:07 数据搜索与搜索

    数据库表中包含了很多数据,一般我们不会检索表中的所有行。通常会根据特定的条件来提取出表的子集,此时我们需要指定搜索条件(search criteria),搜索条...

    王强
  • python判断成绩给等级

    py3study
  • 不用SQL,也可以实现数据集的合并和连接

    数据(集)处理是数据分析过程中的重要环节,今天特别整理数据(集)合并、增减与连接的相关内容,并逐一作出示例。

    1480
  • R语言 数据(集)合并与连接/匹配 | 专题2

    数据(集)处理是数据分析过程中的重要环节,今天特别整理数据(集)合并、增减与连接的相关内容,并逐一作出示例。

    拴小林
  • 在jupyter实现数据的可视化

    df=pd.read_csv('D:\order.csv',encoding="gbk")   #读取数据 df.head(100) 

    双面人
  • 打分排序系统漫谈1 - 时间衰减

    打分排序系统的应用非常普遍,比如电影的评分,知乎帖子的热度,和新闻文章的排序。让我们从最简单直观的平均打分开始, 聊聊各种打分方法的利弊和使用场景。

    风雨中的小七

扫码关注云+社区

领取腾讯云代金券