MYSQL數(shù)據(jù)庫(kù)查詢(xún)按日期分組統(tǒng)計(jì)詳細(xì)代碼
一、按日分組
1.1、dayofyear、dayofmonth、dayofweek
- dayofyear(date) 函數(shù)返回日期位于所在年份的第幾天，范圍是1 ~ 366
- dayofmonth(date) 函數(shù)返回日期位于所在月份的第幾天，范圍是1 ~ 31
- dayofweek(date) 函數(shù)返回日期位于所在周的第幾天，范圍是1 ~ 7
查詢(xún)語(yǔ)句
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;
查詢(xún)結(jié)果
+----------+----------+
| 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 時(shí)間跨月或者周，多月或者多周數(shù)據(jù)會(huì)合并到一起，如果希望分開(kāi)，則可以采用下面的格式化方法。
1.2、格式化函數(shù)
- DATE_FORMAT(date, ‘%Y-%m-%d’) 函數(shù)按指定表達(dá)返回格式化后的日期，包含年月日
查詢(xún)語(yǔ)句
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;
查詢(xún)結(jié)果
+------------+----------+
| 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函數(shù)
- week(date) 函數(shù)返回日期是本年的第幾周，每周是從周日開(kāi)始，取值范圍是0 ~ 53
查詢(xún)語(yǔ)句
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;
查詢(xún)結(jié)果
+-----------+----------+
| 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函數(shù)
- weekofyear(date) 函數(shù)返回日期是本年的第幾周，每周是從周一開(kāi)始，取值范圍是1 ~ 53
查詢(xún)語(yǔ)句
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;
查詢(xún)結(jié)果
+-----------+----------+
| 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函數(shù)
- weekday(date) 返回周幾，注意，周一是0，周二是1，。。。周日是6
查詢(xún)語(yǔ)句
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;
查詢(xún)結(jié)果
+-----------+----------+
| transWeek | transNum |
+-----------+----------+
| 0 | 3262 |
| 1 | 2434 |
| 2 | 3155 |
| 3 | 3893 |
| 4 | 7598 |
| 5 | 14266 |
| 6 | 9572 |
+-----------+----------+
7 rows in set (0.05 sec)
同時(shí)如果覺(jué)得不直觀(guān)，你想獲取名稱(chēng)，可以使用 dayname(date) 替代 weekday(date) ，結(jié)果如下
2.4、dayname函數(shù)
- dayname(date) 返回星期幾：MONDAY，TUESDAY…SUNDAY
查詢(xún)語(yǔ)句
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;
查詢(xún)結(jié)果
+-----------+----------+
| 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函數(shù)
- month(date) 函數(shù)返回日期對(duì)應(yīng)的月份，范圍1～12
查詢(xún)語(yǔ)句
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;
查詢(xún)結(jié)果
+------------+----------+
| 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)
同時(shí)如果你想獲取名字，可以使用 MONTHNAME(date) 替代 month(date) ，結(jié)果如下：
+------------+----------+
| 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)
需要注意的是，如果是跨年了，多年的數(shù)據(jù)會(huì)合并到一起，就不知道每一年的某月是多少了，如果希望分開(kāi)，則可以采用下面的格式化方法。
3.2、DATE_FORMAT函數(shù)
- DATE_FORMAT(date, ‘%Y-%m’) 函數(shù)按指定表達(dá)返回格式化后的日期，包含年月
查詢(xún)語(yǔ)句
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;
查詢(xún)結(jié)果
+------------+----------+
| 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函數(shù)
- quarter(date) 函數(shù)返回日期對(duì)應(yīng)的季度，范圍為1～4
查詢(xún)語(yǔ)句
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;
查詢(xún)結(jié)果
+-----------+----------+
| quarterNo | transNum |
+-----------+----------+
| 1 | 135 |
| 2 | 4953 |
| 3 | 11556 |
| 4 | 98324 |
+-----------+----------+
4 rows in set (0.12 sec)
五、按年分組
5.1、year函數(shù)
- year(date) 函數(shù)返回日期對(duì)應(yīng)的年份
查詢(xún)語(yǔ)句
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;
查詢(xún)結(jié)果
+-----------+----------+
| 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函數(shù)
- DATE_FORMAT(date, ‘%Y’) 函數(shù)按指定表達(dá)返回格式化后的日期，包含年
查詢(xún)語(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;
查詢(xún)結(jié)果
+-----------+----------+
| transYear | transNum |
+-----------+----------+
| 2015 | 6 |
| 2016 | 114968 |
| 2017 | 66703 |
| 2018 | 2738 |
| 2019 | 1853 |
| 2020 | 651 |
| 2021 | 40 |
+-----------+----------+
7 rows in set (0.19 sec)
其他
格式轉(zhuǎn)換
select from_unixtime(create_time / 1000, '%Y-%m-%d %H:%i:%S') create_time from t_content
結(jié)語(yǔ)
本文的操作都是基于mysql8.0的版本，搞懂mysql的函數(shù)這些查詢(xún)都會(huì)變得簡(jiǎn)單。
到此這篇關(guān)于MYSQL數(shù)據(jù)庫(kù)查詢(xún)按日期分組統(tǒng)計(jì)的文章就介紹到這了,更多相關(guān)MYSQL查詢(xún)按日期分組統(tǒng)計(jì)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
- MySQL分組查詢(xún)獲取每組最新的一條數(shù)據(jù)詳解(group?by)
- Mysql分組查詢(xún)每組最新的一條數(shù)據(jù)的五種實(shí)現(xiàn)方法
- MySQL數(shù)據(jù)庫(kù)聚合函數(shù)與分組查詢(xún)舉例詳解
- Mysql分組查詢(xún)每組最新一條數(shù)據(jù)的三種實(shí)現(xiàn)方法
- MySql數(shù)據(jù)庫(kù)基礎(chǔ)之分組查詢(xún)?cè)斀?/a>
- MySQL數(shù)據(jù)庫(kù)分組查詢(xún)group by語(yǔ)句詳解
- Mysql分組查詢(xún)每組最新的一條數(shù)據(jù)的五種實(shí)現(xiàn)過(guò)程
相關(guān)文章
windows 64位下mysql 8.0.13 安裝配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了windows 64位下mysql 8.0.13 安裝配置方法圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-11-11
MySQL創(chuàng)建和刪除數(shù)據(jù)庫(kù)的命令及相關(guān)PHP腳本的操作方法
這篇文章主要介紹了MySQL創(chuàng)建和刪除數(shù)據(jù)庫(kù)的命令及相關(guān)PHP腳本的操作方法,這里主要講述Linux中在mysqladmin下的命令操作,需要的朋友可以參考下2015-11-11
記錄一個(gè)比較簡(jiǎn)便的mysql的主從同步設(shè)置步驟，方便日后使用。2012-06-06
這篇文章主要介紹了MySQL 慢查詢(xún)的相關(guān)資料，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫(kù)，感興趣的朋友可以了解下2020-12-12
SQL實(shí)現(xiàn)LeetCode(196.刪除重復(fù)郵箱)
這篇文章主要介紹了SQL實(shí)現(xiàn)LeetCode(196.刪除重復(fù)郵箱),本篇文章通過(guò)簡(jiǎn)要的案例,講解了該項(xiàng)技術(shù)的了解與使用,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下2021-08-08
MySQL系統(tǒng)變量(system variables)實(shí)際上是一些系統(tǒng)參數(shù)，用于初始化或設(shè)定數(shù)據(jù)庫(kù)對(duì)系統(tǒng)資源的占用，文件存放位置等等，本篇文章我們來(lái)探究一下variables2022-06-06
安裝MySQL時(shí),輸入mysqld --install后,顯式該文件已存在問(wèn)題
這篇文章主要介紹了安裝MySQL時(shí),輸入mysqld --install后,顯式該文件已存在問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-12-12
這篇文章主要為大家詳細(xì)介紹了mysql?8.0.28安裝配置方法圖文教程，文中安裝步驟介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-06-06
MySQL教程數(shù)據(jù)定義語(yǔ)言DDL示例詳解
這篇文章主要為大家介紹了MySQL教程中什么是數(shù)據(jù)定義語(yǔ)言DDL的示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步2021-10-10
MySQL5.7并行復(fù)制原理及實(shí)現(xiàn)
MySQL 5.7并行復(fù)制的思想簡(jiǎn)單易懂，本文就詳細(xì)的介紹了MySQL5.7并行復(fù)制原理及實(shí)現(xiàn)，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-06-06