MYSQL之查询按日期分组统计

这篇具有很好参考价值的文章主要介绍了MYSQL之查询按日期分组统计。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一、按日分组

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)

结语

  本文的操作都是基于mysql8.0的版本,搞懂mysql的函数这些查询都会变得简单。文章来源地址https://www.toymoban.com/news/detail-780973.html

到了这里,关于MYSQL之查询按日期分组统计的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Mysql 数据库DQL 数据查询语言 SELECT 基本查询、条件查询、聚合查询、分组查询、排序查询、分页查询——包含DQL所有查询语句。吐血分享。

    DQL:数据查询语言; 用来对表内的数据进行查找 。Database Query Language SQL语句分为:基本查询、条件查询、聚合查询、分组查询、排序查询、分页查询。  可以发现name字段就只剩下一个张三了;   条件: 条件查询—比较运算符 比较运算符 功能 大于 = 大于等于 小于 = 小于等

    2024年01月19日
    浏览(56)
  • MySQL时间分组:按小时统计数据(mysql按小时分组)

    MySQL时间分组是一类涉及MySQL数据库的SQL语句,能够以小时对数据进行分组统计。 比如,如果你想根据一定的时间间隔,把MySQL数据库中的记录分成几组,那么MySQL时间分组技术就派上用场了。 MySQL时间分组主要使用MySQL语句YEAR(), WEEK(), HOUR()和GROUP BY来实现,按小时统计数据实

    2024年02月12日
    浏览(54)
  • Mysql按小时进行分组统计数据

    目录 前言 按1小时分组统计 按2小时分组统计 按X小时分组统计 统计数据时这种是最常见的需求场景,今天写需求时发现按2小时进行分组统计也特别简单,特此记录下。 sql: 返回值: 没啥好说的,直接使用 hour() 函数获取时间字段的小时部分进行分组统计即可 sql: 返回值:

    2024年02月13日
    浏览(45)
  • mysql统计近7天数据量,,按时间戳分组

    可以使用以下 SQL 语句来统计近7天的数据量,并按时间戳分组。如果某一天没有数据,则将其填充为0。 解释说明: DATE_FORMAT(FROM_UNIXTIME( timestamp ), \\\'%Y-%m-%d\\\') AS date 将时间戳转换为日期格式,例如 2021-03-01。 COUNT(*) AS count 统计每天数据的数量。 WHERE timestamp = UNIX_TIMESTAMP(DATE_SUB

    2024年02月13日
    浏览(58)
  • MYSQL按照小时、按天、按月分组统计,无数据补0

    在开发过程中,写统计接口时,总会遇到按时、按天、按月统计,无数据进行补零的业务需求 现在市面既有补0的做法;也有不补0的做法: 不补0对于数据统计来说简洁明了,一个单表分组查询完事。(也就是在前端展示时,某个时间没有数据则不展示该时间,对于用户来说,

    2024年02月03日
    浏览(42)
  • Mysql 查询分组数据中每组某一数值最大的数据

    该表表名为customer,  park_id表示园区id,joined_at表示用户的加入时间,created_at表示用户的创建时间。 需求:查出每个园区中,最早加入园区的第一位用户 查出id为 1和9的两条数据 解题思路:       【提示】  此处使用 limit 是为了确保,group by分组的时候会按照子查询中输出的

    2024年02月08日
    浏览(46)
  • 【MySQL聚合查询】轻松实现数据统计

    聚合查询:行和行之间的运算 常见的聚合函数: count:数量 sum:求和 avg:求平均值 max:最大值 min:最小值 1.2.1 count 函数 现在有一个 class 表: 现在我们就用 count 来统计 class 表中有多少个班级 1.2.2 sum 函数 现在有一个 score 表: 现在我们就用 sum函数来求 language 成绩的总和

    2024年02月06日
    浏览(47)
  • MySQL数据库——MySQL WHERE:条件查询数据

    在 MySQL 中,如果需要有条件的从数据表中查询数据,可以使用 WHERE 来指定查询条件。 使用 WHERE 的语法格式如下: WHERE 查询条件 查询条件可以是: 带比较运算符和逻辑运算符的查询条件 带 BETWEEN AND 的查询条件 带 IS NULL 的查询条件 带 IN

    2024年02月03日
    浏览(64)
  • MySQL数据库实验三 MySQL查询

    一、实验项目: MySQL查询。 二、实验目的 掌握MySQL的查询操作。 三、实验内容 (一): 1、查询lineitem表中商品编号(productid)和单价(unitprice),要求消除重复行。 2、计算lineitem表中每条记录的商品金额。 3、显示orders表单笔高于200元的客户号(userid)、成交金额(total

    2024年02月11日
    浏览(56)
  • mysql 分组函数,分组查询

    #1.分组函数 功能:用作统计使用,又称聚合函数,统计函数,组函数 分类: sum :求和,avg 平均值,max最大值,min最小值,count计算个数 特点: sum, avg 一般用于处理数值型 max ,min ,count 可以处理任何类型 是否忽略Null count: MYISAM 存储引擎下,COUNT( ) 的效率高 INNODB 存储引擎下,COUN

    2024年01月25日
    浏览(56)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包