甘肃省住房和城乡建设厅网站首页,网站建设企业资质等级,网站建设企业,大气公司网站源码官网链接#xff1a;
月总刷题数和日均刷题数_牛客题霸_牛客网现有一张题目练习记录表practice_record#xff0c;示例内容如下#xff1a;。题目来自【牛客题霸】https://www.nowcoder.com/practice/f6b4770f453d4163acc419e3d19e6746?tpId240
0 问题描述 基于练习记录表… 官网链接
月总刷题数和日均刷题数_牛客题霸_牛客网现有一张题目练习记录表practice_record示例内容如下。题目来自【牛客题霸】https://www.nowcoder.com/practice/f6b4770f453d4163acc419e3d19e6746?tpId240
0 问题描述 基于练习记录表practice_record统计出2021年每个月里用户的月总刷题数month_q_cnt 和日均刷题数avg_day_q_cnt按月份升序排序以及该年的总体情况示例数据输出如下 1 数据准备
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);
2 数据分析
方式一union all 常规做法
-- 方式一
selectDATE_FORMAT(submit_time,%Y%m) as submit_month,count(1)as month_q_cnt,round(count(1)/ max(day(last_day(submit_time))) ,3) as avg_day_q_cnt
from practice_record
where year(submit_time) 2021group by DATE_FORMAT(submit_time,%Y%m)
union all
select2021汇总 as submit_month,count(1) as month_q_cnt,round(count(1) / 31 ,3) as avg_day_q_cnt
from practice_record
where score is not null and year(submit_time) 2021
order by submit_month; 上述代码用到的函数last_day()返回参数日期的最后一天day(last_day())返回当月的天数
ps这里最容易出错的点在于每月天数的计算 (1) 计算每个月的天数可以用函数day(last_day(datetime)); (2) 一年12月每个月的天数 case when month(datetime) in (1,3,5,7,8,10,12) then 31 else 30 end (3) 最容易出错的点在于 : group by DATE_FORMAT(submit_time,%Y%m) 分组后select后面只能跟group by 分组字段、常量、以及 count()/ max()/min()/avg()/sum()等聚合函数 由于 count(1) / max(day(last_day(submit_time)) 中 分子count(1)用的是聚合函数分母也必须用聚合函数而函数day() 不是聚合函数因此分母最终的逻辑为max(day(last_day(submit_time)) 或min(day(last_day(submit_time)) 方式二with rollup
select coalesce(months,2021汇总) as submit_month,count(question_id) as month_q_cnt,round(count(question_id)/max(days),3) as avg_day_cnt
from(select question_id,date_format(submit_time,%Y%m) as months,day(last_day(submit_time)) as daysfrom practice_recordwhere year(submit_time) 2021) tmp1
group by months
with rollup; 上述代码拆解
step1:利用date_format函数及day(last_day(submit_time)) 函数分别获取月份及当月的天数
select question_id,date_format(submit_time,%Y%m) as months,day(last_day(submit_time)) as days
from practice_recordwhere year(submit_time) 2021 step2: 利用 group by with rollup 实现分组加和利用ifnull/coalesce函数进行null值判断及补全coalesce(months,2021汇总) as submit_month
最终的代码如下
select coalesce(months,2021汇总) as submit_month,count(question_id) as month_q_cnt,round(count(question_id)/max(days),3) as avg_day_cnt
from(select question_id,date_format(submit_time,%Y%m) as months,day(last_day(submit_time)) as daysfrom practice_recordwhere year(submit_time) 2021) tmp1
group by months
with rollup;
group by with rollup具体使用案例见文章
MySQL ——group by子句使用with rollup-CSDN博客MySQL ——group by子句使用with rolluphttps://blog.csdn.net/SHWAITME/article/details/136078305?spm1001.2014.3001.5502
3 小结 上述案例最关键的点在于group by 分组后select后面只能跟 (1) groupby 分组的字段; (2)常量; (3) count()、 max()、 min()、avg()、sum()等聚合函数
