月总刷题数和日均刷题数（sql练习）

加油箱加油

于 2024-07-25 14:20:23 发布

阅读量261

点赞数 8

分类专栏： sql练习文章标签： sql 数据库

本文链接：https://blog.csdn.net/JYXCCC/article/details/140688656

版权

sql练习专栏收录该内容

123 篇文章 0 订阅

订阅专栏

现有一张题目练习记录表practice_record，示例内容如下：

id	uid	question_id	submit_time	score
1	1001	8001	2021-08-02 11:41:01	60
2	1002	8001	2021-09-02 19:30:01	50
3	1002	8001	2021-09-02 19:20:01	70
4	1002	8002	2021-09-02 19:38:01	70
5	1003	8002	2021-08-01 19:38:01	80

请从中统计出2021年每个月里用户的月总刷题数month_q_cnt 和日均刷题数avg_day_q_cnt（按月份升序排序）以及该年的总体情况，示例数据输出如下：

submit_month	month_q_cnt	avg_day_q_cnt
202108	2	0.065
202109	3	0.100
2021汇总	5	0.161

解释：2021年8月共有2次刷题记录，日均刷题数为2/31=0.065（保留3位小数）；2021年9月共有3次刷题记录，日均刷题数为3/30=0.100；2021年共有5次刷题记录（年度汇总平均无实际意义，这里我们按照31天来算5/31=0.161）

牛客已经采用最新的Mysql版本，如果您运行结果出现错误：ONLY_FULL_GROUP_BY，意思是：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，也就是说查出来的列必须在group by后面出现否则就会报错，或者这个字段出现在聚合函数里面。

示例1

输入：

drop table if exists practice_record;
CREATE TABLE  practice_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    question_id int NOT NULL COMMENT '题目ID',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO practice_record(uid,question_id,submit_time,score) VALUES
(1001, 8001, '2021-08-02 11:41:01', 60),
(1002, 8001, '2021-09-02 19:30:01', 50),
(1002, 8001, '2021-09-02 19:20:01', 70),
(1002, 8002, '2021-09-02 19:38:01', 70),
(1003, 8002, '2021-08-01 19:38:01', 80);

输出：

202108|2|0.065
202109|3|0.100
2021汇总|5|0.161

本题考察sql的综合应用。

首先将日期转换为对应格式，这里用到concat字符拼接和if判断。

其次是根据月份获得对应月的总天数，以便后续进行运算，这里用到case when语句，其语法是：

case when 表达式 then ... when 表达式 then ... end as new_name。注意这里的end不能忘记。

另外还用到了union拼接两个结构相同的表。这里注意order by不能在待拼接的表中单独使用，会报错，必须在union后的整张表使用。本题中对整张表使用order by恰好能达成最终的效果。

select
    submit_month,
    count(*) month_q_cnt,
    round(count(*)/all_day,3) avg_day_q_cnt
from
    (select
        concat(year(submit_time),if(length(month(submit_time))=1,concat('0',month(submit_time)),month(submit_time))) submit_month,
        case
            when month(submit_time) in (1,3,5,7,8,10,12) then 31
            when month(submit_time) in (4,6,8,9,11) then 30
            when month(submit_time)=2 then 28
        end
        as all_day
    from
        practice_record
    where
        year(submit_time)='2021') a
group by
    submit_month,all_day

union

select
    (select '2021汇总') submit_month,
    count(*) month_q_cnt,
    round(count(*)/31,3) avg_day_q_cnt
from
    (select
        concat(year(submit_time),if(length(month(submit_time))=1,concat('0',month(submit_time)),month(submit_time))) submit_month,
        case
            when month(submit_time) in (1,3,5,7,8,10,12) then 31
            when month(submit_time) in (4,6,8,9,11) then 30
            when month(submit_time)=2 then 28
        end
        as all_day
    from
        practice_record
    where
        year(submit_time)='2021') a

order by
    submit_month