MySQL如何按周统计表中数据_mysql怎么对一张表里按周统计-优快云博客

微信搜索“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;