学习链接

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)%H24小时格式的小时，前导加0，例如: 00,01…23%h12小时格式的小时，前导加0，例如: 00,01 … 12%I与%h相同12小时格式的小时，前导加0，例如: 00,01 … 12%i分钟，范围在00到59%j一年中的的第几天，前导加0，例如: 001,002，… 366%k24小时格式的小时，无前导0，例如: 0,1,2 … 23%l12小时格式的小时，无前导0，例如: 1,2 … 12%M月份全名称，例如: January, February…December%m以数值形式显示的月份名称，前导加0，例如: 01,02，… 12%pAM或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，…等%%将百分比(%)字符添加到输出