欢迎投稿

今日深度：

> > 正文

MYSQL数据库查询按日期分组统计详细代码，

来源:未知 阅读:15766 评论 500 热度3

标签：mysql 查询 分组

MYSQL数据库查询按日期分组统计详细代码，


目录
  • 一、按日分组
    • 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查询按日期分组统计内容请搜索PHP之友以前的文章或继续浏览下面的相关文章希望大家以后多多支持PHP之友！

      您可能感兴趣的文章:
      • mysql按照时间分组查询的语句

      www.htsjk.Com true http://www.htsjk.com/Mysql/47518.html NewsArticle MYSQL数据库查询按日期分组统计详细代码， 目录 一、按日分组 1.1、dayofyear、dayofmonth、dayofweek 1.2、格式化函数 二、按周分组 2.1、week函数 2.2、weekofyear函数 2.3、weekday函数 2.4、dayname函数...
      本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
      同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。
      返回首页
      相关文章
      评论暂时关闭