【SQL进阶】【CASE/IF、COUNT/SUM、多条记录拼接为一个内容】Day03：聚合分组查询

【SQL进阶】【CASE/IF、COUNT/SUM、多条记录拼接为一个内容】Day03：聚合分组查询

〇、今日内容概述

一、聚合函数

1、SQL类别高难度试卷得分的截断平均值【去最高最低分求平均】

自己的想法​​SELECT tag, difficulty, ROUND((SUM(score)-MIN(score)-MAX(score))/(COUNT(score)-2),1) AS clip_avg_score FROM examination_info,exam_record WHERE examination_info.exam_id=exam_record.exam_id AND tag='hard' AND tag='SQL'​​报错：​​Execution Error SQL_ERROR_INFO: "In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'examination_info.difficulty'; this is incompatible with sql_mode=only_full_group_by"​​正确做法​​SELECT tag, difficulty, ROUND((SUM(score)-MIN(score)-MAX(score))/(COUNT(score)-2),1) AS clip_avg_score FROM examination_info JOIN exam_record USING(exam_id) WHERE tag='SQL' AND difficulty='hard'​​ 或​​SELECT tag, difficulty, ROUND((SUM(score)-MIN(score)-MAX(score))/(COUNT(score)-2),1) AS clip_avg_score FROM examination_info,exam_record WHERE examination_info.exam_id=exam_record.exam_id AND difficulty='hard' AND tag='SQL'​​

2、统计作答次数

自己的想法​​​SELECT COUNT(*) AS total_pv, SUM((CASE WHEN score IS NULL AND submit_time IS NULL THEN 0 ELSE 1 )) AS complete_pv, COUNT(DISTINCT exam_id) AS complete_exam_cnt FROM exam_record​​报错​​​Execution Error SQL_ERROR_INFO: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) AS complete_pv,\n COUNT(DISTINCT exam_id) AS complete_exam_cnt\nFROM exam_re' at line 7"​​正确做法​​​SELECT COUNT(*) AS total_pv, SUM((CASE WHEN score IS NULL AND submit_time IS NULL THEN 0 ELSE 1 END )) AS complete_pv, COUNT(DISTINCT exam_id AND score IS NOT NULL AND submit_time IS NOT NULL) AS complete_exam_cnt FROM exam_record​​原因：CASE ... WHEN ... THEN ... END方法2：使用IF​​​SELECT COUNT(*) AS total_pv, SUM((CASE WHEN score IS NULL AND submit_time IS NULL THEN 0 ELSE 1 END )) AS complete_pv, COUNT(DISTINCT IF(score IS NOT NULL, exam_id, NULL)) AS complete_exam_cnt FROM exam_record​​

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

SELECT MIN(score) AS min_score_over_avgFROM exam_record AJOIN examination_info BJOIN (SELECT exam_id,AVG(score) AS ex_score FROM exam_record GROUP BY exam_id) AVG_EUSING exam_id WHERE score

SELECT MIN(score) AS min_score_over_avgFROM exam_record erJOIN examination_info eiON er.exam_id=ei.exam_idWHERE tag='SQL' AND score>= (SELECT AVG(score) FROM exam_record er WHERE tag='SQL' AND er.exam_id=ei.exam_id GROUP BY er.exam_id)

* 方法2：使用over函数- # 二、分组函数 ## 1、平均**活跃天数**和月活人数

自己写的

SELECT DATE_FORMAT(submit_time,"%Y%m") AS month, ROUND(SUM(IF(submit_time IS NOT NULL,1,0))/COUNT(DISTINCT uid),2) AS avg_active_days, COUNT(DISTINCT uid) AS mauFROM exam_recordWHERE submit_time IS NOT NULL AND YEAR(submit_time)='2021'GROUP BY month

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 mauFROM exam_recordWHERE submit_time IS NOT NULL AND YEAR(submit_time)='2021'GROUP BY month

## 2、月总刷题数和日均刷题数【拼接未知数据使用UNION】

自己写的【错误】：

SELECT DATE_FORMAT(submit_time,"%Y%m") AS submit_month, COUNT(submit_time) AS month_q_cnt, ROUND(COUNT(submit_time)/( CASE WHEN MONTH(submit_time)=1 THEN 31 WHEN MONTH(submit_time)=2 THEN 28 WHEN MONTH(submit_time)=3 THEN 31 WHEN MONTH(submit_time)=4 THEN 30 WHEN MONTH(submit_time)=5 THEN 31 WHEN MONTH(submit_time)=6 THEN 30 WHEN MONTH(submit_time)=7 THEN 31 WHEN MONTH(submit_time)=8 THEN 31 WHEN MONTH(submit_time)=9 THEN 30 WHEN MONTH(submit_time)=10 THEN 31 WHEN MONTH(submit_time)=11 THEN 30 WHEN MONTH(submit_time)=12 THEN 31 END ),3) AS avg_day_q_cntFROM practice_recordWHERE submit_time IS NOT NULL AND YEAR(submit_time)=2021GROUP BY submit_monthORDER BY submit_month ASC

正确答案

SELECT DATE_FORMAT(submit_time,"%Y%m") submit_month, COUNT(submit_time) month_q_cnt, ROUND(COUNT(submit_time)/MAX(DAY(LAST_DAY(submit_time))),3) avg_day_q_cnt -- 使用max实现去重FROM practice_recordWHERE YEAR(submit_time)=2021GROUP BY submit_monthUNION ALL SELECT "2021汇总" submit_month, COUNT(submit_time) month_q_cnt, ROUND(COUNT(submit_time)/31,3) avg_day_q_cntFROM practice_recordWHERE YEAR(submit_time)=2021ORDER BY submit_month ASC

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

SELECT uid, SUM(IF(er.submit_time IS NULL,1,0)) AS incomplete_cnt, -- COUNT(CASE WHEN er.submit_time IS NULL THEN er.start_time ELSE NULL END) AS incomplete_cnt, SUM(IF(er.submit_time IS NOT NULL,1,0)) AS complete_cnt, **GROUP_CONCAT(DISTINCT CONCAT_WS(':',DATE_FORMAT(er.start_time,"%Y-%m-%d"),ei.tag) SEPARATOR ';') **AS detailFROM exam_record erLEFT JOIN examination_info eiON er.exam_id=ei.exam_idWHERE YEAR(er.start_time)=2021GROUP BY er.uidHAVING complete_cnt>=1 AND incomplete_cnt<5 AND incomplete_cnt>1ORDER BY incomplete_cnt DESC

作者：​哥们要飞​​​

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。