学习链接
mysql按照日期分组统计数据
博主-山茶花开时的 【Mysql专栏学习】
mysql按照日期分组统计数据
Mysql的date_format函数想必大家都使用过吧,一般用于日期时间转化,如下所示
# 可以得出 2023-01-01 08:30:50
select DATE_FORMAT('2023-01-01 08:30:50','%Y-%m-%d %H:%i:%s')# 或者是如下,可以得出 2023-01-01
select DATE_FORMAT('2023-01-01 08:30:50','%Y-%m-%d')# 或者是如下,可以得到,将字符串转变为日期/时间值
#(STR_TO_DATE函数与DATE_FORMAT函数的作用是相反的,
# 如果不能按照format解析str,STR_TO_DATE函数将返回NULL
# 如果其中任何一个参数为NULL,STR_TO_DATE函数将返回NULL
# )
select str_to_date('2023-10-02','%Y-%m-%d') -- 格式与date_format一致
不过很多时间不仅仅仅限于这些最基础的使用,当我们需要对于时间的数据有更高的要求时,就需要对这个函数有更多的了解了,例如按一定的时间段分组统计这段时间的数据,本期我们主要讲这个分组统计的操作,下面直接进入正题
按天统计
按天统计是一个比较基础的统计方式,大部分人也应该能直接写出来
SELECTdate_format( o.bill_date, '%Y-%m-%d' ) evertday,COUNT(*) orderNumFROM TABLE o
GROUP BYevertday
ORDER BYevertday DESC
按周统计
第一种
SELECTsubdate( date_format( o.bill_date, '%Y-%m-%d' ), date_format( o.bill_date, '%w' ) ) weekend,COUNT(*) orderNum
FROMtest o
GROUP BYweekend
ORDER BYweekend DESC LIMIT 9
这边可以看出日期已经按照周进行排序分组了,7月30到8月5号的数据都已经统计到了7月30这个weekend中 我们简单剖析一下: 首先date_format(o.bill_date,‘%Y-%m-%d’)大家都很清楚会把日期变成yyyy-MM-dd形式
而date_format(o.bill_date,‘%w’)会统计这天是这周的礼拜几,这个参数我在最后整理了一张表格可供大家查看使用 那么8月2号正好是礼拜三,再通过subdate函数去减一下这个日期就可以统计出来一个对应的时间了,或者也有朋友喜欢用DATE_SUB函数都是一样的效果,这样就统计出来了上周末的时间,也就是上周末到这周末是一个轮回
第二种
SELECTweekday( bill_date ) AS weekday,date_format( date_add( bill_date, INTERVAL - ( weekday( bill_date ) + 1 ) DAY ), '%Y-%m-%d' ) AS monday,count( 1 )
FROMt
GROUP BYmondayorder BY monday DESC
总体的思路都差不多,计算出了上周日的时间点进行group by
按月统计
按月统计的话就是另外一种思路了,因为按周的统计的话还存在日的概念,而按月统计的话就可以省略掉日期了
SELECTDATE_FORMAT ( bill_date, '%Y-%m' ) months,count( 1 ) count
FROMt
GROUP BYmonthsorder BY months DESC
按年统计
这个跟按月统计一个道理,只统计年份就可以了
select DATE_FORMAT( bill_date, '%Y' ) months,count(1 ) count from t group by monthsorder by months desc
格式 | 描述 |
---|---|
%a | 三个字符缩写的工作日名称,例如: Mon,Tue,Wed等 |
%b | 三个字符缩写的月份名称,例如: Jan,Feb,Mar等 |
%c | 以数字表示的月份值,例如: 1, 2, 3…12 |
%D | 以数字表示月份中的某天(可理解为一个月之中的第几天),后跟英文后缀,例如: 1st,2nd,3rd等 |
%d | 以数值表示月份中的某天,如果是小于10的数字,该数字需要加前导0, 例如: 00,01,02, …31 |
%e | 以数值表示月份中的某天,小于10的数字不需要加前导0,例如: 1,2,… 31 |
%f | 微秒,范围在(000000到999999) |
%H | 24小时格式的小时,前导加0,例如: 00,01…23 |
%h | 12小时格式的小时,前导加0,例如: 00,01 … 12 |
%I | 与%h相同12小时格式的小时,前导加0,例如: 00,01 … 12 |
%i | 分钟,范围在00到59 |
%j | 一年中的的第几天,前导加0,例如: 001,002,… 366 |
%k | 24小时格式的小时,无前导0,例如: 0,1,2 … 23 |
%l | 12小时格式的小时,无前导0,例如: 1,2 … 12 |
%M | 月份全名称,例如: January, February…December |
%m | 以数值形式显示的月份名称,前导加0,例如: 01,02,… 12 |
%p | AM或PM,取决于其他时间说明符 |
%r | 表示时间,12小时格式hh:mm:ss AM/PM |
%S | 秒,前导加0,例如: 00,01…59 |
%s | 与%S相同 |
%T | 表示时间,24小时格式hh:mm:ss |
%U | 表示周数,星期日为一周的第一天,例如:00,01,02 … 53时,前导0的周数 |
%u | 表示周数,星期一为一周的第一天,例如:00,01,02 … 53时,前导0的周数 |
%V | 与%U相同,它与%X一起使用 |
%v | 与%u相同,它与%x一起使用 |
%W | 工作日的全称,例如: Sunday, Monday,…, Saturday |
%w | 工作日,以数字来表示(0 = 星期日,6 = 星期六) |
%X | 周的四位数表示年份,第一天是星期日,经常与%V一起使用 |
%x | 周的四位数表示年份,第一天是星期一,经常与%v一起使用 |
%Y | 表示年份,四位数,例如: 2000,2001,…等 |
%y | 表示年份,两位数,例如: 00,01,…等 |
%% | 将百分比(%)字符添加到输出 |