广西城乡建设网站seo算法培训
学习链接
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，…等 |
| %% | 将百分比(%)字符添加到输出 |