MySQL中如何按周统计表中的数据

MySQL按周统计

最新推荐文章于 2025-06-02 07:00:00 发布

原创最新推荐文章于 2025-06-02 07:00:00 发布 · 2.5k 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql

MySQL 专栏收录该内容

36 篇文章

订阅专栏


微信搜索『coder-home』，或者扫一扫右侧的二维码，关注『程序猿集锦』。了解更多干货分享，还有各类视频教程。扫描它，带走我。

文章目录

背景

今天算法的同事过来问我MySQL中是否可以根据周的维度去统计某个指标？比如按周统计订单的数量。因为数据量比较大，如果直接全部查询到内存中去统计可能比较慢。所以他系统能够在SQL层面先做一次聚合统计，维度是按照周去统计。

刚听到这个需求，我有点蒙。按照周的方式去统计？MySQL中估计没有这样的by week的函数。有按照天统计简单，按照月统计也简单，我们只要把日期截取一下转为YYYY-MM的字符串然后再分组聚合就可以了。但是他要按照周来统计，确实有点难度。

但是MySQL这么成熟的数据库软件了，也不能说实现不了吧。说干就干，开整。

环境准备

准备建表语句

使用中使用到的表结构如和建表语句如下：

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(32) DEFAULT NULL,
  `create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

初始化数据语句

测试使用到的初始化数据使用如下的SQL进行初始化：

/*如下SQL执行多次即可产生多条测试数据*/
insert into test(order_no, create_timestamp) value(substr(rand(),3,18) ,date_add(date_add(date_add(date_add(now(), interval floor(1+rand()*23) hour), interval floor(1+rand()*60) minute), interval floor(1+rand()*60) second), interval -floor(1+rand()*40) day));

select * from test;

最后的实验环境

我们的实验环境如下所示，要求按周统计出订单的数目。
在这里插入图片描述

思路分析

MySQL中没有自带的把一个日期给转换为周的函数。不过，我们可以采用曲线救国的方式来实现。虽然它没有直接的函数，但是它自带的日期类型的函数还是很多的。

思路：我们的目的是把每一个订单创建时间这个字段的值，设置为一个可以使用的group by的key。这个key要求是根据同一个周的订单使用同一个时间。所以，我们需要想办法把属于同一个周的订单的创建时间转换为一个相同的日期，比如把所有属于同一个周的所有订单的创建时间，都转换为每周的周一这个时间。这样就可以根据周去聚合统计订单的数目了。

在把所有订单的创建时间转换为这个订单所属的周的周一时，就要知道这个订单的创建时间，距离这个周的一查几天，这样在这个订单的创建时间这个值上减去这个天数，就可以得到周一这个时间。

MySQL中有一个函数叫做weekday(x)，其中x就是传入的一个日期类型的数据，传入后，返回这个日期是属于一周当中的第几天。是一个整形的数据，值的范围是[0,6]，两个边界值分别代表每周的周一和周日。

一周有七天，当我们传入一个周一的日期给weekday(x)函数的时候，它会返回一个整形的数字0；当我们传入一个周日的日期的时候，它会返回一个整形的数字6。示例如下：
在这里插入图片描述

基于上面的函数weekday(x)，我们可以得到每一个订单创建的时间属于一周当中的第几天。SQL语句如下所示：

select 
	*,
	weekday(create_timestamp) as weekday
from test limit 10;

实验结果如下所示：
在这里插入图片描述

然后我们在基于这个得到的整形的周几，用日期相加函数data_add()把每一个订单创建时间改为每一周的周一。使用如下的SQL语句来实现：

select 
	*,
	weekday(create_timestamp) as weekday,
	date_add(create_timestamp, interval - (weekday(create_timestamp)) day) as monday
from test limit 10;

结果截图如下：
在这里插入图片描述

此时我们已经得到每个订单创建时间所对应的周一这个日期，但是在monday这一列中还有时分秒，这将会影响我们进行聚合的时候作为key来使用这个字段，所以，我们需要把时分秒去掉只保留年月日即可，采用date_format(x,'%Y-%m-%d')函数就可以对这个日期进行格式化成我们希望要的格式。SQL语句如下：

select 
	*,
	weekday(create_timestamp) as weekday,
	date_format(date_add(create_timestamp, interval - (weekday(create_timestamp) + 1) day),'%Y-%m-%d') as monday
from test limit 10;

结果如下图所示：
在这里插入图片描述

最后，我们就可以使用聚合函数进行统计每周的订单数了，SQL语句如下：

select monday, count(1) from (
	select 
		*,
		weekday(create_timestamp) as weekday,
		date_format(date_add(create_timestamp, interval - (weekday(create_timestamp) + 1) day),'%Y-%m-%d') as monday
	from test
) as x 
group by monday 
order by monday;

最后的统计结果如下图所示，这就是我们希望要的结果了。
在这里插入图片描述

总结

最后梳理一下思路：根据每一行的创建时间计算出这个时间属于对应周第几天，然后用这个创建时间再减去这个的得到的第几天，就可以把每一个创建时间转换为每周的周一。这样就可以把所有属于同一个周的订单的创建时间，转换为了每一个周的周一。最后基于这个每周的周一就可以统计得到最后的每周的订单数目。

在MySQL中，按周的统计虽然不能直接实现，但是我们可以根据它现有个各种日期类型的函数，稍微转换变通一下，就可以实现我们平时的需求。所以，在遇到类似的这样的需求的时候，不要着急，按部就班分析一下，就可以基于现有的函数慢慢的一步步实现最后希望达到的效果。

这个解题的思路不仅仅可以适用于MySQL数据库，向Oracle数据库、SQLserver数据库、postgresql数据库都可以根据这个思路来实现按周去统计数据。


微信搜索『coder-home』，或者扫一扫右侧的二维码，关注『程序猿集锦』。了解更多干货分享，还有各类视频教程。扫描它，带走我。