当前位置: 首页 > news >正文

牛客网SQL进阶127: 月总刷题数和日均刷题数

  官网链接:

月总刷题数和日均刷题数_牛客题霸_牛客网现有一张题目练习记录表practice_record,示例内容如下:。题目来自【牛客题霸】icon-default.png?t=N7T8https://www.nowcoder.com/practice/f6b4770f453d4163acc419e3d19e6746?tpId=240

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) = '2021'group by DATE_FORMAT(submit_time,'%Y%m')
union all
select'2021汇总' 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?spm=1001.2014.3001.5502

3 小结

   上述案例最关键的点在于:group by 分组后,select后面只能跟:

(1) groupby 分组的字段;

(2)常量;

(3) count()、 max()、 min()、avg()、sum()等聚合函数;

http://www.lryc.cn/news/297701.html

相关文章:

  • 19:Web开发模式与MVC设计模式-Java Web
  • Z字形变换
  • 飞书上传图片
  • Java微服务学习Day1
  • STM32标准库驱动W25Q64模块读写字库数据+OLED0.96显示例程
  • 【java】简单的Java语言控制台程序
  • 【服务器数据恢复】HP EVA虚拟化磁盘阵列数据恢复原理方案
  • 08-OpenFeign-结合Sentinel,实现熔断降级
  • 15.实现数组的扁平化
  • 对话模型Demo解读(使用代码解读原理)
  • Android 自定义BaseFragment
  • [C#] 如何对列表,字典等进行排序?
  • Mac 下载安装Java、maven并配置环境变量
  • 【多模态】27、Vary | 通过扩充图像词汇来提升多模态模型在细粒度感知任务(OCR等)上的效果
  • |Python新手小白低级教程|第二十章:函数(2)【包括石头剪刀布判断程序(模拟版)】
  • vue3 之 商城项目—home
  • git flow与分支管理
  • 【Linux】学习-进程信号
  • webgis后端安卓系统部署攻略
  • 【数据分享】1929-2023年全球站点的逐日平均风速数据(Shp\Excel\免费获取)
  • 【多模态大模型】视觉大模型SAM:如何使模型能够处理任意图像的分割任务?
  • Shell之sed
  • AJAX——认识URL
  • 《Docker极简教程》--Docker环境的搭建--在Linux上搭建Docker环境
  • 开源微服务平台框架的特点是什么?
  • C#系列-C#操作UDP发送接收数据(10)
  • 突破编程_C++_面试(基础知识(10))
  • 初步探索Pyglet库:打造轻量级多媒体与游戏开发利器
  • 【npm】安装全局包,使用时提示:不是内部或外部命令,也不是可运行的程序或批处理文件
  • Go 语言 for 的用法