【Kay】SQL聚合函数和分组查询

1.截断平均值

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

SELECT tag,difficulty,
ROUND((SUM(score) - MAX(score) - MIN(score))/(COUNT(score) - 2),1)
FROM exam_record r
LEFT JOIN examination_info i
ON r.exam_id = i.exam_id
WHERE i.tag = 'SQL'
AND i.difficulty = 'hard' 
AND score IS NOT NULL 

2.统计总数

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

SELECT 
SUM(IF(start_time IS NOT NULL,1,0)) AS total_pv,
SUM(IF(submit_time IS NOT NULL,1,0)) AS complete_pv,
COUNT(DISTINCT IF(score IS NOT NULL,exam_id,NULL)) AS complete_exam_cnt
FROM
exam_record

 3.平均月活跃天数

用户在牛客试卷作答区作答记录存储在表exam_record中，内容如下：

请计算2021年每个月里试卷作答区用户平均月活跃天数avg_active_days和月度活跃人数mau

SELECT DATE_FORMAT(submit_time, '%Y%m') AS month,
       ROUND((COUNT(DISTINCT uid, date_format(submit_time, '%y%m%d'))) / COUNT(DISTINCT uid), 2) AS avg_active_days,
       COUNT(DISTINCT uid) AS mau
FROM exam_record
WHERE submit_time is not null
AND YEAR(submit_time) = 2021
GROUP BY month

4.月总刷题数和日均刷题数

现有一张题目练习记录表practice_record

统计2021年每个月日均刷题数avg_day_q_cnt。注：DAY(LAST_DAY(submit_time))

SELECT
    date_format(submit_time,"%Y%m") AS submit_month,
    COUNT(submit_month) month_q_cnt,
    round(COUNT(submit_month)/DAY(last_day(submit_time)),3) avg_day_q_cnt
FROM
    practice_record
GROUP BY 
    submit_month

5、未完成试卷数大于1的有效用户

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

请统计2021年每个未完成试卷作答数大于1的有效用户的数据（有效用户指完成试卷数至少为1）示例数据的输出结果如下： 

SELECT uid,
SUM(IF(submit_time IS NULL,1,0)) AS incomplete_cnt,
SUM(IF(submit_time IS NOT NULL,1,0)) AS complete_cnt
FROM exam_record r
GROUP BY uid
HAVING
incomplete_cnt > 1
AND complete_cnt >= 1

注：SUM(IF)语法等价于：

 SUM(CASE WHEN submit_time IS NULL THEN 1 END) AS incomplete_cnt