目录
- 前言
- 按天统计
- 按周统计
- 按月统计
- 按年统计
- date_format参数
前言
mysql的date_format函数想必大家都使用过吧,一般用于日期时间转化
# 例如
select DATE_FORMAT('2023-01-01 08:30:50','%Y-%m-%d %H:%i:%s')
# 可以得出 2023-01-01 08:30:50# 或者是
select DATE_FORMAT('2023-01-01 08:30:50','%Y-%m-%d')
# 可以得出
不过很多时间不仅仅仅限于这些最基础的使用,当我们需要对于时间的数据有更高的要求时,就需要对这个函数有更多的了解了,例如按一定的时间段分组统计这段时间的数据,本期我们主要讲这个分组统计的操作,下面直接进入正题
按天统计
按天统计是一个比较基础的统计方式,大部分人也应该能直接写出来
select date_format(o.bill_date,'%Y-%m-%d') evertday,COUNT(*) orderNumFROM table oGROUP BY evertdayORDER BY evertday DESC
按周统计
按周统计就比较麻烦一点了,我这边提供了两种思路:
第一种:
SELECTsubdate(date_format(o.bill_date,'%Y-%m-%d'),date_format(o.bill_date,'%w')) weekend,COUNT(*) orderNumFROM test oGROUP BY weekendORDER BY weekend DESCLIMIT 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
函数都是一样的效果,这样就统计出来了上周末的时间,也就是上周末到这周末是一个轮回
第二种:
-- 2、
select -- weekday(bill_date) as weekday,date_format(date_add(bill_date, interval - (weekday(bill_date) +1) day),'%Y-%m-%d') as monday,count(1)
from t
group by monday
order by monday desc
总体的思路都差不多,计算出了上周日的时间点进行group by
按月统计
按月统计的话就是另外一种思路了,因为按周的统计的话还存在日的概念,而按月统计的话就可以省略掉日期了
selectDATE_FORMAT( bill_date, '%Y-%m' ) months,count(1) count
fromt
group by months
order by months desc
按年统计
这个跟按月统计一个道理,只统计年份就可以了
selectDATE_FORMAT( bill_date, '%Y' ) months,count(1 ) count
fromt
group by months
order by months desc
date_format参数
格式 | 描述 |
---|---|
%M | 月名字(January……December) |
%W | 星期名字(Sunday……Saturday) |
%D | 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。) |
%Y | 年, 数字, 4 位 |
%y | 年, 数字, 2 位 |
%a | 缩写的星期名字(Sun……Sat) |
%d | 月份中的天数, 数字(00……31) |
%e | 月份中的天数, 数字(0……31) |
%m | 月, 数字(01……12) |
%c | 月, 数字(1……12) |
%b | 缩写的月份名字(Jan……Dec) |
%j | 一年中的天数(001……366) |
%H | 小时(00……23) |
%k | 小时(0……23) |
%h | 小时(01……12) |
%I | 小时(01……12) |
%l | 小时(1……12) |
%i | 分钟, 数字(00……59) |
%r | 时间,12 小时(hh:mm:ss [AP]M) |
%T | 时间,24 小时(hh:mm:ss) |
%S | 秒(00……59) |
%s | 秒(00……59) |
%p | AM或PM |
%w | 一个星期中的天数(0=Sunday ……6=Saturday ) |
%U | 星期(0……52), 这里星期天是星期的第一天 |
%u | 星期(0……52), 这里星期一是星期的第一天 |
%% | 一个文字“%” |