[大数据]SQL进阶14-15

SQL14 SQL类别高难度试卷得分的截断平均值 2.21

描述

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

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

所有用户表：

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

exam_record表：

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

结果保留一位小数
【分析】
截断平均值可以理解为：avg(sum - min - max)
round是用于小数精度处理

SELECT ei.tag, ei.difficulty,
ROUND((SUM(er.score) - MAX(er.score) - MIN(er.score))/(COUNT(er.score)-2), 1)  clip_avg_score
FROM examination_info  ei
JOIN exam_record  er   -- join exam_record using(exam_id)
WHERE ei.exam_id = er.exam_id AND ei.tag = 'SQL' AND ei.difficulty = 'hard'

1.ROUND(X, D) -- D表示保留几位小数
2.当两张表使用相同的列名时，join……on……可以简化为join……using (字段名)，故上述join操作可以改为join exam_record using(exam_id)

SQL 15 统计作答次数2.21

描述

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

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

示例输出：

解释：表示截止当前，有11次试卷作答记录，已完成的作答次数为7次（中途退出的为未完成状态，其交卷时间和份数为NULL），已完成的试卷有9001和9002两份。
【分析】
total_pv表示有开始时间start_time的次数–作答记录
complete_pv表示有提交时间的次数–提交记录
complete_exam_cnt表示有得分的次数–得分记录

-- 方法1
SELECT COUNT(start_time) AS total_pv, 
COUNT(submit_time) as complete_pv,
COUNT(DISTINCT exam_id AND score IS NOT NULL) AS complete_exam_cnt
FROM exam_record
-- count中带表达式书写方式

-- 方法2
select 
count(exam_id) total_pv,
count(score) complete_pv,
(
    select count(DISTINCT exam_id)
    from exam_record
    WHERE score is not null
   ) complete_exam_cnt
FROM exam_record
-- 方式2第三句计算complete_exam_cnt时容易写成如下错误
-- count(select DISTINCT exam_id
--    from exam_record
--    WHERE score  is not null
--   ) complete_exam_cnt
   
-- 方法3 case whrn then else end 语句
SELECT
COUNT(start_time) AS total_pv, 
COUNT(submit_time) as complete_pv,
COUNT(DISTINCT case when score is not null then 1 else 0 end)
FROM exam_record