〇、今日内容概述

一、聚合函数

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、得分不小于平均分的最低分

分组的可以在分组内使用join
自己的思路

点击查看代码

SELECT

    MIN(score) AS min_score_over_avg

FROM exam_record A

JOIN examination_info B

JOIN (SELECT exam_id,AVG(score) AS ex_score

      FROM exam_record

      GROUP BY exam_id) AVG_E

USING exam_id

WHERE

    score<ex_score

    AND

    tag='SQL'

* 正确答案

点击查看代码

SELECT

    MIN(score) AS min_score_over_avg

FROM exam_record er

JOIN examination_info ei

ON er.exam_id=ei.exam_id

WHERE

    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 mau

FROM exam_record

WHERE

    submit_time IS NOT NULL

    AND

    YEAR(submit_time)='2021'

GROUP BY month

* 陷阱在于九月份有个用户同一天做了两种卷子，直接count统计的话活跃天数会多一天，即用户ID和做题日期submit_time要同时去重才能得出正确的活跃天数.
* 正确答案

点击查看代码

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

## 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_cnt

FROM practice_record

WHERE

    submit_time IS NOT NULL

    AND

    YEAR(submit_time)=2021

GROUP BY submit_month

ORDER 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_record

WHERE YEAR(submit_time)=2021

GROUP BY submit_month

UNION ALL

SELECT

    "2021汇总" submit_month,

    COUNT(submit_time) month_q_cnt,

    ROUND(COUNT(submit_time)/31,3) avg_day_q_cnt

FROM practice_record

WHERE YEAR(submit_time)=2021

ORDER 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 detail

FROM exam_record er

LEFT JOIN examination_info ei

ON er.exam_id=ei.exam_id

WHERE YEAR(er.start_time)=2021

GROUP BY er.uid

HAVING

    complete_cnt>=1

    AND

    incomplete_cnt<5

    AND

    incomplete_cnt>1

ORDER BY incomplete_cnt DESC

巴特西

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

〇、今日内容概述

一、聚合函数

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

2、统计作答次数

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

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

最新文章

热门文章