目录
- 一、按日分组
- 1.1、dayofyear、dayofmonth、dayofweek
- 1.2、格式化函数
- 二、按周分组
- 2.1、week函数
- 2.2、weekofyear函数
- 2.3、weekday函数
- 2.4、dayname函数
- 三、按月分组
- 3.1、month函数
- 3.2、DATE_FORMAT函数
- 四、按季分组
- 4.1、quarter函数
- 五、按年分组
- 5.1、year函数
- 5.2、DATE_FORMAT函数
- 其他
- 结语
一、按日分组
1.1、dayofyear、dayofmonth、dayofweek
- dayofyear(date)函数返回日期位于所在年份的第几天，范围是1 ~ 366
- dayofmonth(date)函数返回日期位于所在月份的第几天，范围是1 ~ 31
- dayofweek(date)函数返回日期位于所在周的第几天，范围是1 ~ 7
查询语句
select
dayofmonth(transtime) as transDay,
count(*) as transNum
from tb_inf_otherbiz
where transtime between \’2016-10-01 00:00:00\’ and \’2016-10-31 23:59:59\’
group by transDay
order by transDay asc;
查询结果
+———-+———-+| transDay | transNum |+———-+———-+| 1 | 1704 || 2 | 985 || 3 | 723 || 4 | 606 || 5 | 581 || 6 | 1051 || 7 | 1257 || 8 | 637 || 9 | 1049 || 10 | 559 || 11 | 724 || 12 | 964 || 13 | 1139 || 14 | 2542 || 15 | 5957 || 16 | 3185 || 17 | 543 || 18 | 507 || 19 | 854 || 20 | 849 || 21 | 2216 || 22 | 3788 || 23 | 2498 || 24 | 693 || 25 | 597 || 26 | 756 || 27 | 854 || 28 | 1583 || 29 | 2180 || 30 | 1855 || 31 | 744 |+———-+———-+31 rows in set (0.05 sec)
需要注意的是，如果是dayofmonth或者dayofweek时间跨月或者周，多月或者多周数据会合并到一起，如果希望分开，则可以采用下面的格式化方法。
1.2、格式化函数
- DATE_FORMAT(date, ‘%Y-%m-%d’)函数按指定表达返回格式化后的日期，包含年月日
查询语句
select
DATE_FORMAT(transtime, \’%Y-%m-%d\’) as transDay,
count(*) as transNum
from tb_inf_otherbiz
where transtime between \’2016-10-01 00:00:00\’ and \’2016-10-31 23:59:59\’
group by transDay
order by transDay asc;
查询结果
+————+———-+| transDay | transNum |+————+———-+| 2016-10-01 | 1704 || 2016-10-02 | 985 || 2016-10-03 | 723 || 2016-10-04 | 606 || 2016-10-05 | 581 || 2016-10-06 | 1051 || 2016-10-07 | 1257 || 2016-10-08 | 637 || 2016-10-09 | 1049 || 2016-10-10 | 559 || 2016-10-11 | 724 || 2016-10-12 | 964 || 2016-10-13 | 1139 || 2016-10-14 | 2542 || 2016-10-15 | 5957 || 2016-10-16 | 3185 || 2016-10-17 | 543 || 2016-10-18 | 507 || 2016-10-19 | 854 || 2016-10-20 | 849 || 2016-10-21 | 2216 || 2016-10-22 | 3788 || 2016-10-23 | 2498 || 2016-10-24 | 693 || 2016-10-25 | 597 || 2016-10-26 | 756 || 2016-10-27 | 854 || 2016-10-28 | 1583 || 2016-10-29 | 2180 || 2016-10-30 | 1855 || 2016-10-31 | 744 |+————+———-+31 rows in set (0.08 sec)
二、按周分组
2.1、week函数
- week(date)函数返回日期是本年的第几周，每周是从周日开始，取值范围是0 ~ 53
查询语句
select
week(transtime) as transWeek,
count(*) as transNum
from tb_inf_otherbiz
where transtime between \’2016-01-01 00:00:00\’ and \’2016-12-31 23:59:59\’
group by transWeek
order by transWeek asc;
查询结果
+———–+———-+| transWeek | transNum |+———–+———-+| 0 | 2 || 1 | 8 || 2 | 9 || 3 | 1 || 4 | 4 || 5 | 7 || 6 | 4 || 7 | 9 || 8 | 7 || 9 | 2 || 10 | 21 || 11 | 18 || 12 | 19 || 13 | 34 || 14 | 31 || 15 | 17 || 16 | 130 || 17 | 261 || 18 | 230 || 19 | 494 || 20 | 452 || 21 | 485 || 22 | 590 || 23 | 684 || 24 | 580 || 25 | 620 || 26 | 370 || 27 | 155 || 28 | 721 || 29 | 747 || 30 | 659 || 31 | 775 || 32 | 843 || 33 | 897 || 34 | 926 || 35 | 975 || 36 | 975 || 37 | 1048 || 38 | 393 || 39 | 4145 || 40 | 5840 || 41 | 12934 || 42 | 11942 || 43 | 9161 || 44 | 9102 || 45 | 8284 || 46 | 6150 || 47 | 5825 || 48 | 6374 || 49 | 6929 || 50 | 4366 || 51 | 3858 || 52 | 5855 |+———–+———-+53 rows in set (0.12 sec)
2.2、weekofyear函数
- weekofyear(date)函数返回日期是本年的第几周，每周是从周一开始，取值范围是1 ~ 53
查询语句
select
weekofyear(transtime) as transWeek,
count(*) as transNum
from tb_inf_otherbiz
where transtime between \’2016-01-01 00:00:00\’ and \’2016-12-31 23:59:59\’
group by transWeek
order by transWeek asc;
查询结果
+———–+———-+| transWeek | transNum |+———–+———-+| 1 | 11 || 2 | 6 || 4 | 5 || 5 | 6 || 6 | 5 || 7 | 9 || 8 | 7 || 9 | 1 || 10 | 22 || 11 | 17 || 12 | 23 || 13 | 34 || 14 | 28 || 15 | 16 || 16 | 199 || 17 | 194 || 18 | 299 || 19 | 510 || 20 | 458 || 21 | 492 || 22 | 571 || 23 | 709 || 24 | 546 || 25 | 640 || 26 | 278 || 27 | 257 || 28 | 723 || 29 | 720 || 30 | 710 || 31 | 786 || 32 | 813 || 33 | 921 || 34 | 957 || 35 | 1002 || 36 | 932 || 37 | 1032 || 38 | 327 || 39 | 5064 || 40 | 5904 || 41 | 15070 || 42 | 11255 || 43 | 8518 || 44 | 9203 || 45 | 7836 || 46 | 5448 || 47 | 6608 || 48 | 5934 || 49 | 6639 || 50 | 4160 || 51 | 3887 || 52 | 5173 || 53 | 3 |+———–+———-+52 rows in set (0.12 sec)
2.3、weekday函数
- weekday(date)返回周几，注意，周一是0，周二是1，。。。周日是6
查询语句
select
weekday(transtime) as transWeek,
count(*) as transNum
from tb_inf_otherbiz
where transtime between \’2016-10-01 00:00:00\’ and \’2016-10-31 23:59:59\’
group by transWeek
order by transWeek asc;
查询结果
+———–+———-+| transWeek | transNum |+———–+———-+| 0 | 3262 || 1 | 2434 || 2 | 3155 || 3 | 3893 || 4 | 7598 || 5 | 14266 || 6 | 9572 |+———–+———-+7 rows in set (0.05 sec)
同时如果觉得不直观，你想获取名称，可以使用dayname(date)替代weekday(date)，结果如下
2.4、dayname函数
- dayname(date)返回星期几：MONDAY，TUESDAY…SUNDAY
查询语句
select
dayname(transtime) as transWeek,
count(*) as transNum
from tb_inf_otherbiz
where transtime between \’2016-10-01 00:00:00\’ and \’2016-10-31 23:59:59\’
group by transWeek
order by transWeek asc;
查询结果
+———–+———-+| transWeek | transNum |+———–+———-+| Friday | 7598 || Monday | 3262 || Saturday | 14266 || Sunday | 9572 || Thursday | 3893 || Tuesday | 2434 || Wednesday | 3155 |+———–+———-+7 rows in set (0.08 sec)
三、按月分组
3.1、month函数
- month(date)函数返回日期对应的月份，范围1～12
查询语句
select
month(transtime) as transMonth,
count(*) as transNum
from tb_inf_otherbiz
where transtime between \’2016-01-01 00:00:00\’ and \’2016-12-31 23:59:59\’
group by transMonth
order by transMonth asc;
查询结果
+————+———-+| transMonth | transNum |+————+———-+| 1 | 25 || 2 | 27 || 3 | 83 || 4 | 449 || 5 | 1893 || 6 | 2611 || 7 | 2411 || 8 | 3811 || 9 | 5334 || 10 | 44180 || 11 | 30140 || 12 | 24004 |+————+———-+12 rows in set (0.12 sec)
同时如果你想获取名字，可以使用MONTHNAME(date)替代month(date)，结果如下：
+————+———-+| transMonth | transNum |+————+———-+| April | 449 || August | 3811 || December | 24004 || February | 27 || January | 25 || July | 2411 || June | 2611 || March | 83 || May | 1893 || November | 30140 || October | 44180 || September | 5334 |+————+———-+12 rows in set (0.20 sec)
需要注意的是，如果是跨年了，多年的数据会合并到一起，就不知道每一年的某月是多少了，如果希望分开，则可以采用下面的格式化方法。
3.2、DATE_FORMAT函数
- DATE_FORMAT(date, ‘%Y-%m’)函数按指定表达返回格式化后的日期，包含年月
查询语句
select
DATE_FORMAT(transtime, \’%Y-%m\’) as transMonth,
count(*) as transNum
from tb_inf_otherbiz
where transtime between \’2016-01-01 00:00:00\’ and \’2016-12-31 23:59:59\’
group by transMonth
order by transMonth asc;
查询结果
+————+———-+| transMonth | transNum |+————+———-+| 2016-01 | 25 || 2016-02 | 27 || 2016-03 | 83 || 2016-04 | 449 || 2016-05 | 1893 || 2016-06 | 2611 || 2016-07 | 2411 || 2016-08 | 3811 || 2016-09 | 5334 || 2016-10 | 44180 || 2016-11 | 30140 || 2016-12 | 24004 |+————+———-+12 rows in set (0.20 sec)
四、按季分组
4.1、quarter函数
- quarter(date)函数返回日期对应的季度，范围为1～4
查询语句
select
quarter(transtime) as quarterNo,
count(*) as transNum
from tb_inf_otherbiz
where transtime between \’2016-01-01 00:00:00\’ and \’2016-12-31 23:59:59\’
group by quarterNo
order by quarterNo asc;
查询结果
+———–+———-+| quarterNo | transNum |+———–+———-+| 1 | 135 || 2 | 4953 || 3 | 11556 || 4 | 98324 |+———–+———-+4 rows in set (0.12 sec)
五、按年分组
5.1、year函数
- year(date)函数返回日期对应的年份
查询语句
select
year(transtime) as transYear,
count(*) as transNum
from tb_inf_otherbiz
where transtime between \’2015-01-01 00:00:00\’ and \’2022-12-31 23:59:59\’
group by transYear
order by transYear asc;
查询结果
+———–+———-+| transYear | transNum |+———–+———-+| 2015 | 6 || 2016 | 114968 || 2017 | 66703 || 2018 | 2738 || 2019 | 1853 || 2020 | 651 || 2021 | 40 |+———–+———-+7 rows in set (0.19 sec)
5.2、DATE_FORMAT函数
- DATE_FORMAT(date, ‘%Y’)函数按指定表达返回格式化后的日期，包含年
查询语句
select
DATE_FORMAT(transtime, \’%Y\’) as transYear,
count(*) as transNum
from tb_inf_otherbiz
where transtime between \’2015-01-01 00:00:00\’ and \’2022-12-31 23:59:59\’
group by transYear
order by transYear asc;
查询结果
+———–+———-+| transYear | transNum |+———–+———-+| 2015 | 6 || 2016 | 114968 || 2017 | 66703 || 2018 | 2738 || 2019 | 1853 || 2020 | 651 || 2021 | 40 |+———–+———-+7 rows in set (0.19 sec)
其他
格式转换
select from_unixtime(create_time / 1000, \’%Y-%m-%d %H:%i:%S\’) create_time
from t_content
结语
本文的操作都是基于mysql8.0的版本，搞懂mysql的函数这些查询都会变得简单。
到此这篇关于MYSQL数据库查询按日期分组统计的文章就介绍到这了,更多相关MYSQL查询按日期分组统计内容请搜索悠久资源网以前的文章或继续浏览下面的相关文章希望大家以后多多支持悠久资源网！
