文章来源于网络收集而来，版权归原创者所有，如有侵权请及时联系！

聚合函数

发布于 2024-08-17 15:49:00 字数 14534 浏览 0 评论 0 收藏 0

SQL 类别高难度试卷得分的截断平均值（较难）

描述：牛客的运营同学想要查看大家在 SQL 类别中高难度试卷的得分情况。

请你帮她从 exam_record 数据表中计算所有用户完成 SQL 类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）。

示例数据： examination_info （ exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2020-01-01 10:00:00
2	9002	算法	medium	80	2020-08-02 10:00:00

示例数据： exam_record （uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-02 09:01:01	2020-01-02 09:21:01	80
2	1001	9001	2021-05-02 10:01:01	2021-05-02 10:30:01	81
3	1001	9001	2021-06-02 19:01:01	2021-06-02 19:31:01	84
4	1001	9002	2021-09-05 19:01:01	2021-09-05 19:40:01	89
5	1001	9001	2021-09-02 12:01:01	(NULL)	(NULL)
6	1001	9002	2021-09-01 12:01:01	(NULL)	(NULL)
7	1002	9002	2021-02-02 19:01:01	2021-02-02 19:30:01	87
8	1002	9001	2021-05-05 18:01:01	2021-05-05 18:59:02	90
9	1003	9001	2021-09-07 12:01:01	2021-09-07 10:31:01	50
10	1004	9001	2021-09-06 10:01:01	(NULL)	(NULL)

根据输入你的查询结果如下：

tag	difficulty	clip_avg_score
SQL	hard	81.7

从 examination_info 表可知，试卷 9001 为高难度 SQL 试卷，该试卷被作答的得分有[80,81,84,90,50]，去除最高分和最低分后为[80,81,84]，平均分为 81.6666667，保留一位小数后为 81.7

输入描述：

输入数据中至少有 3 个有效分数

思路一： 要找出高难度 sql 试卷，肯定需要联 examination_info 这张表，然后找出高难度的课程，由 examination_info 得知，高难度 sql 的 exam_id 为 9001，那么等下就以 exam_id = 9001 作为条件去查询；

先找出 9001 号考试 select * from exam_record where exam_id = 9001

然后，找出最高分 select max(score) 最高分 from exam_record where exam_id = 9001

接着，找出最低分 select min(score) 最低分 from exam_record where exam_id = 9001

在查询出来的分数结果集当中，去掉最高分和最低分，最直观能想到的就是 NOT IN 或者用 NOT EXISTS 也行，这里以 NOT IN 来做

首先将主体写出来 select tag, difficulty, round(avg(score), 1) clip_avg_score from examination_info info INNER JOIN exam_record record

小 tips : MYSQL 的 ROUND() 函数 , ROUND(X) 返回参数 X 最近似的整数 ROUND(X,D) 返回 X ,其值保留到小数点后 D 位,第 D 位的保留方式为四舍五入。

再将上面的 "碎片" 语句拼凑起来即可，注意在 NOT IN 中两个子查询用 UNION ALL 来关联，用 union 把 max 和 min 的结果集中在一行当中，这样形成一列多行的效果。

答案一：

SELECT tag, difficulty, ROUND(AVG(score), 1) clip_avg_score
    FROM examination_info info  INNER JOIN exam_record record
        WHERE info.exam_id = record.exam_id
            AND  record.exam_id = 9001
                AND record.score NOT IN(
                    SELECT MAX(score)
                        FROM exam_record
                            WHERE exam_id = 9001
                                UNION ALL
                    SELECT MIN(score)
                        FROM exam_record
                            WHERE exam_id = 9001
                )

这是最直观，也是最容易想到的解法，但是还有待改进，这算是投机取巧过关，其实严格按照题目要求应该这么写：

SELECT tag,
       difficulty,
       ROUND(AVG(score), 1) clip_avg_score
FROM examination_info info
INNER JOIN exam_record record
WHERE info.exam_id = record.exam_id
  AND record.exam_id =
    (SELECT examination_info.exam_id
     FROM examination_info
     WHERE tag = 'SQL'
       AND difficulty = 'hard' )
  AND record.score NOT IN
    (SELECT MAX(score)
     FROM exam_record
     WHERE exam_id =
         (SELECT examination_info.exam_id
          FROM examination_info
          WHERE tag = 'SQL'
            AND difficulty = 'hard' )
     UNION ALL SELECT MIN(score)
     FROM exam_record
     WHERE exam_id =
         (SELECT examination_info.exam_id
          FROM examination_info
          WHERE tag = 'SQL'
            AND difficulty = 'hard' ) )

然而你会发现，重复的语句非常多，所以可以利用 WITH 来抽取公共部分

WITH 子句介绍 ：

WITH 子句，也称为公共表表达式（Common Table Expression，CTE），是在 SQL 查询中定义临时表的方式。它可以让我们在查询中创建一个临时命名的结果集，并且可以在同一查询中引用该结果集。

基本用法：

WITH cte_name (column1, column2, ..., columnN) AS (
    -- 查询体
    SELECT ...
    FROM ...
    WHERE ...
)
-- 主查询
SELECT ...
FROM cte_name
WHERE ...

WITH 子句由以下几个部分组成：

cte_name : 给临时表起一个名称，可以在主查询中引用。
(column1, column2, ..., columnN) : 可选，指定临时表的列名。
AS : 必需，表示开始定义临时表。
CTE 查询体 : 实际的查询语句，用于定义临时表中的数据。

WITH 子句的主要用途之一是增强查询的可读性和可维护性，尤其在涉及多个嵌套子查询或需要重复使用相同的查询逻辑时。通过将这些逻辑放在一个命名的临时表中，我们可以更清晰地组织查询，并消除重复代码。

此外， WITH 子句还可以在复杂的查询中实现递归查询。递归查询允许我们在单个查询中执行对同一表的多次迭代，逐步构建结果集。这在处理层次结构数据、组织结构和树状结构等场景中非常有用。

小细节 ：MySQL 5.7 版本以及之前的版本不支持在 WITH 子句中直接使用别名。

下面是改进后的答案：

WITH t1 AS
  (SELECT record.*,
          info.tag,
          info.difficulty
   FROM exam_record record
   INNER JOIN examination_info info ON record.exam_id = info.exam_id
   WHERE info.tag = "SQL"
     AND info.difficulty = "hard" )
SELECT tag,
       difficulty,
       ROUND(AVG(score), 1)
FROM t1
WHERE score NOT IN
    (SELECT max(score)
     FROM t1
     UNION SELECT min(score)
     FROM t1)

思路二：

筛选 SQL 高难度试卷： where tag="SQL" and difficulty="hard"
计算截断平均值： (和-最大值-最小值) / (总个数-2) :
- (sum(score) - max(score) - min(score)) / (count(score) - 2)
- 有一个缺点就是，如果最大值和最小值有多个，这个方法就很难筛选出来, 但是题目中说了-----> 去掉一个最大值和一个最小值后的平均值 , 所以这里可以用这个公式。

答案二：

SELECT info.tag,
       info.difficulty,
       ROUND((SUM(record.score)- MIN(record.score)- MAX(record.score)) / (COUNT(record.score)- 2), 1) AS clip_avg_score
FROM examination_info info,
     exam_record record
WHERE info.exam_id = record.exam_id
  AND info.tag = "SQL"
  AND info.difficulty = "hard";

统计作答次数

有一个试卷作答记录表 exam_record ，请从中统计出总作答次数 total_pv 、试卷已完成作答数 complete_pv 、已完成的试卷数 complete_exam_cnt 。

示例数据 exam_record 表（ uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-02 09:01:01	2020-01-02 09:21:01	80
2	1001	9001	2021-05-02 10:01:01	2021-05-02 10:30:01	81
3	1001	9001	2021-06-02 19:01:01	2021-06-02 19:31:01	84
4	1001	9002	2021-09-05 19:01:01	2021-09-05 19:40:01	89
5	1001	9001	2021-09-02 12:01:01	(NULL)	(NULL)
6	1001	9002	2021-09-01 12:01:01	(NULL)	(NULL)
7	1002	9002	2021-02-02 19:01:01	2021-02-02 19:30:01	87
8	1002	9001	2021-05-05 18:01:01	2021-05-05 18:59:02	90
9	1003	9001	2021-09-07 12:01:01	2021-09-07 10:31:01	50
10	1004	9001	2021-09-06 10:01:01	(NULL)	(NULL)

示例输出：

total_pv	complete_pv	complete_exam_cnt
11	7	2

解释：表示截止当前，有 11 次试卷作答记录，已完成的作答次数为 7 次（中途退出的为未完成状态，其交卷时间和份数为 NULL），已完成的试卷有 9001 和 9002 两份。

思路：这题一看到统计次数，肯定第一时间就要想到用 COUNT 这个函数来解决，问题是要统计不同的记录，该怎么来写？使用子查询就能解决这个题目（这题用 case when 也能写出来，解法类似，逻辑不同而已)；首先在做这个题之前，让我们先来了解一下 COUNT 的基本用法；

COUNT() 函数的基本语法如下所示：

COUNT(expression)

其中， expression 可以是列名、表达式、常量或通配符。下面是一些常见的用法示例：

计算表中所有行的数量：

SELECT COUNT(*) FROM table_name;

计算特定列非空（不为 NULL）值的数量：

SELECT COUNT(column_name) FROM table_name;

计算满足条件的行数：

SELECT COUNT(*) FROM table_name WHERE condition;

结合 GROUP BY 使用，计算分组后每个组的行数：

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

计算不同列组合的唯一组合数：

SELECT COUNT(DISTINCT column_name1, column_name2) FROM table_name;

在使用 COUNT() 函数时，如果不指定任何参数或者使用 COUNT(*) ，将会计算所有行的数量。而如果使用列名，则只会计算该列非空值的数量。

另外， COUNT() 函数的结果是一个整数值。即使结果是零，也不会返回 NULL，这点需要谨记。

答案：

SELECT
    count(*) total_pv,
    ( SELECT count(*) FROM exam_record WHERE submit_time IS NOT NULL ) complete_pv,
    ( SELECT COUNT( DISTINCT exam_id, score IS NOT NULL OR NULL ) FROM exam_record ) complete_exam_cnt
FROM
    exam_record

这里着重说一下 COUNT( DISTINCT exam_id, score IS NOT NULL OR NULL ) 这一句，判断 score 是否为 null ，如果是即为真，如果不是返回 null；注意这里如果不加 or null 在不是 null 的情况下只会返回 false 也就是返回 0；

COUNT 本身是不可以对多列求行数的， distinct 的加入是的多列成为一个整体，可以求出现的行数了; count distinct 在计算时只返回非 null 的行, 这个也要注意；

另外通过本题 get 到了------>count 加条件常用句式 count( 列判断 or null)

得分不小于平均分的最低分

描述：请从试卷作答记录表中找到 SQL 试卷得分不小于该类试卷平均得分的用户最低得分。

示例数据 exam_record 表（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-02 09:01:01	2020-01-02 09:21:01	80
2	1002	9001	2021-09-05 19:01:01	2021-09-05 19:40:01	89
3	1002	9002	2021-09-02 12:01:01	(NULL)	(NULL)
4	1002	9003	2021-09-01 12:01:01	(NULL)	(NULL)
5	1002	9001	2021-02-02 19:01:01	2021-02-02 19:30:01	87
6	1002	9002	2021-05-05 18:01:01	2021-05-05 18:59:02	90
7	1003	9002	2021-02-06 12:01:01	(NULL)	(NULL)
8	1003	9003	2021-09-07 10:01:01	2021-09-07 10:31:01	86
9	1004	9003	2021-09-06 12:01:01	(NULL)	(NULL)

examination_info 表（ exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2020-01-01 10:00:00
2	9002	SQL	easy	60	2020-02-01 10:00:00
3	9003	算法	medium	80	2020-08-02 10:00:00

示例输出数据：

min_score_over_avg
87

解释：试卷 9001 和 9002 为 SQL 类别，作答这两份试卷的得分有[80,89,87,90]，平均分为 86.5，不小于平均分的最小分数为 87

思路：这类题目第一眼看确实很复杂，因为不知道从哪入手，但是当我们仔细读题审题后，要学会抓住题干中的关键信息。以本题为例： 请从试卷作答记录表中找到 SQL 试卷得分不小于该类试卷平均得分的用户最低得分。 你能一眼从中提取哪些有效信息来作为解题思路？

第一条：找到==SQL==试卷得分

第二条：该类试卷==平均得分==

第三条：该类试卷的==用户最低得分==

然后中间的 “桥梁” 就是==不小于==

将条件拆分后，先逐步完成

-- 找出 tag 为‘SQL’的得分   【80, 89,87,90】
-- 再算出这一组的平均得分
select  ROUND(AVG(score), 1) from  examination_info info INNER JOIN exam_record record
    where info.exam_id = record.exam_id
    and tag= 'SQL'

然后再找出该类试卷的最低得分，接着将结果集 【80, 89,87,90】 去和平均分数作比较，方可得出最终答案。

答案：

SELECT MIN(score) AS min_score_over_avg
FROM examination_info info
INNER JOIN exam_record record
WHERE info.exam_id = record.exam_id
  AND tag= 'SQL'
  AND score >=
    (SELECT ROUND(AVG(score), 1)
     FROM examination_info info
     INNER JOIN exam_record record
     WHERE info.exam_id = record.exam_id
       AND tag= 'SQL' )

其实这类题目给出的要求看似很 “绕”，但其实仔细梳理一遍，将大条件拆分成小条件，逐个拆分完以后，最后将所有条件拼凑起来。反正只要记住： 抓主干，理分支 ，问题便迎刃而解。

分享到QQ

分享到微博