1. Toy模板网首页
  2. > Toy博客

MYSQL之查询按日期分组统计

8月前 作者：嘉禾嘉宁papa 分类：Toy博客 阅读(32) 违法举报

这篇具有很好参考价值的文章主要介绍了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 基本查询、条件查询、聚合查询、分组查询、排序查询、分页查询——包含ＤＱＬ所有查询语句。吐血分享。

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

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

    2024年01月19日
    浏览(45)

  • MySQL时间分组：按小时统计数据（mysql按小时分组）

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

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

    Mysql按小时进行分组统计数据

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

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

    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日
    浏览(47)
  • MYSQL按照小时、按天、按月分组统计，无数据补0

    MYSQL按照小时、按天、按月分组统计，无数据补0

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

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

    Mysql 查询分组数据中每组某一数值最大的数据

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

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

    【MySQL聚合查询】轻松实现数据统计

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

    2024年02月06日
    浏览(40)

  • MySQL数据库——MySQL WHERE：条件查询数据

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

    2024年02月03日
    浏览(43)

  • MySQL数据库实验三 MySQL查询

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

    2024年02月11日
    浏览(43)

  • mysql 分组函数,分组查询

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

    2024年01月25日
    浏览(46)

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

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

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

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

二维码1

领取红包

二维码2

领红包