详解Mysql日期格式并聚合统计示例

介绍

在实际的开发中，我们通常需要对数据进行分组统计，而时间日期是一个常见的分组条件。在 MySQL 中，我们可以使用日期格式化的函数将日期转换成指定格式的字符串，然后按照需要的时间粒度进行分组统计。本文将介绍如何使用 MySQL 日期格式化并按天、周、月、年分组统计数据。

准备工作

在开始之前，我们需要先准备一张测试数据表，并插入一些数据，以便于我们后续的实验和测试。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

CREATE TABLE `test` (    `id` int (11) NOT NULL AUTO_INCREMENT,    ` name ` varchar (50) DEFAULT NULL ,    `created_at` datetime NOT NULL ,    PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `test` (` name `, `created_at`) VALUES ( 'test1' , '2022-01-01 00:00:00' ), ( 'test2' , '2022-01-02 00:00:00' ), ( 'test3' , '2022-01-02 12:00:00' ), ( 'test4' , '2022-01-03 00:00:00' ), ( 'test5' , '2022-01-07 00:00:00' ), ( 'test6' , '2022-01-08 00:00:00' ), ( 'test7' , '2022-01-09 00:00:00' ), ( 'test8' , '2022-01-10 00:00:00' ), ( 'test9' , '2022-01-14 00:00:00' ), ( 'test10' , '2022-01-15 00:00:00' ), ( 'test11' , '2022-01-16 00:00:00' ), ( 'test12' , '2022-01-17 00:00:00' ), ( 'test13' , '2022-02-01 00:00:00' ), ( 'test14' , '2022-02-02 00:00:00' ), ( 'test15' , '2022-02-03 00:00:00' ), ( 'test16' , '2022-03-01 00:00:00' ), ( 'test17' , '2022-03-02 00:00:00' ), ( 'test18' , '2022-03-03 00:00:00' ), ( 'test19' , '2022-04-01 00:00:00' ), ( 'test20' , '2022-04-02 00:00:00' ), ( 'test21' , '2022-04-03 00:00:00' ), ( 'test22' , '2022-05-01 00:00:00' ), ( 'test23' , '2022-05-02 00:00:00' ), ( 'test24' , '2022-05-03 00:00:00' );

这里我们创建了一张名为 test 的测试表，包含三个字段 id、name 和 created_at。其中 created_at 表示记录创建时间的日期时间类型字段。我们插入了一些测试数据，包括从 2022 年1月份到5月份的数据.

实现原理

当我们需要根据日期进行分组统计时，MySQL提供了很多内置的日期函数，如YEAR()、MONTH()、WEEK()、DAY()、HOUR()等等。这些函数能够根据日期对数据进行分组并统计相应的数量。

对于本需求，我们需要根据日期进行分组，并统计7天、4周和3个月的数据。因此，我们需要结合使用日期格式化和日期函数。

首先，我们需要将日期格式化成相应的格式。可以使用DATE_FORMAT()函数来实现，该函数接收两个参数：日期和格式化字符串。例如，我们可以将日期格式化成"yyyy-MM-dd"的格式，如下所示：

?

1	SELECT DATE_FORMAT(date_column, '%Y-%m-%d' ) AS formatted_date FROM table_name;

实际操作

按天统计

接下来，我们需要根据日期分组并统计数量。可以使用GROUP BY子句和相应的日期函数来实现。例如，我们可以根据日期分组统计每天的数量，如下所示：

?

1	SELECT DATE_FORMAT(date_column, '%Y-%m-%d' ) AS formatted_date, COUNT (*) AS count FROM table_name GROUP BY formatted_date;

按周统计

?

1	SELECT DATE_FORMAT(date_column, '%x-%v' ) AS formatted_week, COUNT (*) AS count FROM table_name GROUP BY formatted_week;

或者

?

1	SELECT CONCAT( YEAR (date_column), '-' , WEEK(date_column)) AS formatted_week, COUNT (*) AS count FROM table_name GROUP BY formatted_week;`

按月统计

?

1	SELECT DATE_FORMAT(date_column, '%Y-%m-%d' ) AS formatted_month, COUNT (*) AS count FROM table_name GROUP BY formatted_month;

或者

?

1	SELECT CONCAT( YEAR (date_column), '-' , MONTH (date_column)) AS formatted_month, COUNT (*) AS count FROM table_name GROUP BY formatted_month;

以上就是详解Mysql日期格式并聚合统计示例的详细内容，更多关于Mysql日期格式聚合统计的资料请关注服务器之家其它相关文章！

原文链接：https://juejin.cn/post/7223658167493296189