Mayee

外观

Mysql按自然月统计

约 2736 字大约 9 分钟

统计Sql

2021-11-02

1. 需求

实现如下查询：

注册人数删除人数日期
512021-11-01
202021-11-02
.........
002021-11-30

要求，按照自然月统计，即当月有多少天，查询结果就需要有多少天的n录，若当天没有注册记录，则注册人数和删除人数均为 0。

2. 准备

2.1. 创建表结构及导入数据

  • 创建表结构
CREATE TABLE `register`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `uid` varchar(36) NOT NULL COMMENT '用户id',
  `deleted` tinyint(1) NOT NULL COMMENT '已删除标记(1: 已删除 | 0: 未删除)',
  `create_time` datetime NOT NULL COMMENT '注册时间',
  PRIMARY KEY (`id`)
) COMMENT = '用户注册表';
  • 插入数据
INSERT INTO register(uid,deleted,create_time) VALUES
(UUID(),0,'2021-11-01 15:12:38'),
(UUID(),0,'2021-11-01 15:14:38'),
(UUID(),0,'2021-11-01 15:16:38'),
(UUID(),0,'2021-11-02 12:16:38'),
(UUID(),1,'2021-11-02 13:16:38'),
(UUID(),0,'2021-11-02 18:16:38'),
(UUID(),0,'2021-11-27 20:16:38'),
(UUID(),1,'2021-11-27 21:16:38'),
(UUID(),1,'2021-11-27 22:16:38')

3. 实现

3.1. 方案一

3.1.1. 按天查询注册人数及删除人数

SELECT
	COUNT( NULLIF( deleted, 0 )) AS `del_cnt`,-- 删除人数
	COUNT( uid ) AS `day_cnt`,-- 当天注册人数
	DATE_FORMAT( create_time, '%Y-%m-%d' ) AS `create_day` -- 日期	
FROM
	`register` 
WHERE
	DATE_FORMAT( create_time, '%Y-%m-%d' ) >= ( SELECT DATE_ADD( CURDATE(), INTERVAL - DAY ( CURDATE() ) + 1 DAY ) ) 
	AND DATE_FORMAT( create_time, '%Y-%m-%d' ) <= ( SELECT LAST_DAY( CURDATE() ) ) 
GROUP BY
	DATE_FORMAT( create_time, '%Y-%m-%d' ) 
ORDER BY
	DATE_FORMAT( create_time, '%Y-%m-%d' ) ASC;

执行结果如下：

del_cntday_cntcreate_day
032021-11-01
132021-11-02
232021-11-27

函数说明：

  • NULLIF(deleted,0) 表示当 deleted 字段为值 0，则函数返回 null，而COUNT()函数不会统计值为 null 的字段，因此所有值为 1 的记录被统计到，得到了已删除的人数；
  • CURDATE() 得到当天的日期，如：2021-11-02 ；
  • LAST_DAY(CURDATE()) 得到当月的最后一天，如： 2021-11-30 ；
  • DATE_ADD(CURDATE(), INTERVAL - DAY ( CURDATE() ) + 1 DAY) 得到当月的第一天，如：2021-11-01；
  • DAY(date) 获取给定日期的天，如：DAY('2021-11-03') 则函数返回 3。

3.1.2. 查询当月的每一天

SELECT
	DATE_ADD( CURDATE(), INTERVAL ( CAST( help_topic_id AS signed INTEGER ) - DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 1 DAY ), '%d' )) DAY ) AS `base_day` -- 基准日期
FROM
	mysql.help_topic 
WHERE
	help_topic_id < DAY ( LAST_DAY( CURDATE() ) ) 
ORDER BY
	help_topic_id;

执行结果如下：

base_day
2021-11-01
2021-11-02
2021-11-03
...
2021-11-30

3.1.3. 将上面两个查询结果做关联查询

SELECT IFNULL(b.day_cnt,0) AS `注册人数`,IFNULL(b.del_cnt,0) AS `删除人数`,a.base_day AS `日期` FROM
(
SELECT
	DATE_ADD( CURDATE(), INTERVAL ( CAST( help_topic_id AS signed INTEGER ) - DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 1 DAY ), '%d' )) DAY ) AS `base_day` 
FROM
	mysql.help_topic 
WHERE
	help_topic_id < DAY ( LAST_DAY( CURDATE() ) ) 
ORDER BY
	help_topic_id
) a LEFT JOIN
(
SELECT
	COUNT( NULLIF(deleted, 0) ) AS `del_cnt`,-- 删除人数
	COUNT( uid ) AS `day_cnt`,-- 当天注册人数
	DATE_FORMAT( create_time, '%Y-%m-%d' ) AS `create_day` -- 日期	
FROM
	`register` 
WHERE
	DATE_FORMAT( create_time, '%Y-%m-%d' ) >= ( SELECT DATE_ADD( CURDATE(), INTERVAL - DAY ( CURDATE() )+ 1 DAY ) ) 
	AND DATE_FORMAT( create_time, '%Y-%m-%d' ) <= ( SELECT LAST_DAY( CURDATE() ) ) 
GROUP BY
	DATE_FORMAT( create_time, '%Y-%m-%d' ) 
ORDER BY
	DATE_FORMAT( create_time, '%Y-%m-%d' ) ASC
) b ON a.base_day = b.create_day;

执行结果如下：

注册人数删除人数日期
302021-11-01
312021-11-02
002021-11-03
.........
322021-11-27
.........
002021-11-30

结果符合预期。

函数说明：

  • IFNULL(b.day_cnt,0) 表示当 day_cnt 字段值为 null 时，则函数返回 0 。因为表中有些日期不存在注册记录，所以关联到就为 null 。

上述方案实现需要借助 mysql.help_topic 这张表，但这张表在 mysql 库下，属于系统库需要 root 用户才能访问，而一般应用级用户是不给权限访问 mysql 库的。事实上，我们并不是非要用 help_topic 这张表，只是因为 help_topic_id 是自增 id。所以，完全可以另外创建一张表，只要主键是自增就可以了，表记录至少要有 31 行，因为一个月最多是 31 天。 但维护这一张表仅仅是为了这个需求，就得在 dev、stg、uat、pre、prd 等环境同步创建这张表，似乎有点麻烦，得不偿失。有没有一种方式可以不用新建表呢，请看方案二。

3.2. 方案二

3.2.1. 构造月份基础表

SELECT
	CONCAT( DATE_FORMAT( CURDATE(), '%Y-%m-' ),LPAD( CAST(uu.num AS CHAR), 2, '0' ) ) AS base_day 
FROM
	(
	SELECT
		@num := @num + 1 AS num 
	FROM
		( SELECT 0 UNION ALL SELECT 1 ) AS t1,
		( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 ) AS t2,
		( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 ) AS t3,
		( SELECT @num := 0 ) tb 
	) AS uu 
WHERE
	uu.num <= DAY ( LAST_DAY( CURDATE() ) );

执行结果如下：

base_day
2021-11-01
2021-11-02
2021-11-03
...
2021-11-30

函数说明：

  • CONCAT(str1,str2,...) 此函数接收若干个字符串，拼接返回。如：CONCAT('ab','-','cd') 则函数返回 'ab-cd' ；
  • DATE_FORMAT(date,format) 将 date 按照 format 格式化。如： DATE_FORMAT('2021-11-02','%Y-%m') 则函数返回 2021-11；
  • CURDATE() 获取当天日期。如：CURDATE() 这函数返回 2021-11-04；
  • LPAD(str,len,padstr) 给定字符串 str，左填充 padstr，至长度为 len。如：LPAD('1',2,'0') 则函数返回 01。这里这样做的目的是，当天数小于 10 时，显示 2 位数字，保持格式统一；
  • CAST(expr AS type) 将 expr 转换为 type 类型。如：CAST(1 AS CHAR) 则函数返回 1 。这里单从返回结果来看，没有任何变化，实际上不这么做也可以，LPAD( CAST(uu.num AS CHAR), 2, '0' ) 也可以写成 LPAD( uu.num, 2, 0 ) ，整个 sql 返回结果也一样。但 LPAD 函数实际上是接收字符类型，这里为了严谨使用 CAST 做了类型转换。

Sql 分析：

表 t1 返回结果集行数为 2，t2 返回结果集行数为 4 ，t3 返回结果集行数为 4 ，表 tb 暂且放一边。这里 SELECT 了表 t1,t2,t3，即对三个表结果集做笛卡尔交集，则返回的结果集行数为 2 * 4 * 4 = 32 。因为一个月最多 31 天，我们需要一个行数 >= 31 的临时表。因此这里没有固定写法，SELECT 从 0 开始 UNION ALL 到 30 也可以，或者因式分解为 32 = 2 * 16 只用 t1,t2 两个表也行，不过是从写法上，2 * 4 * 4 是写的比较少的一种方式，只要能保证结果集的行数 >= 31 即可。 接着说表 tb，表 tb 结果集行数为 1，与前面的结果做笛卡尔交集，结果集行数为 1 * 32 = 32。@num := 0 表示在当前会话(即连接，session)中定义了一个临时变量 @num，并赋予初始值 0。 注意， := 与 = 的区别。在 Mysql 里，除 update 语句中的 SET 处 = 为赋值，其他地方则为比较判断，而 := 在任何地方都表示赋值。若，@num := 0 错写为 @num = 0，则赋值不成功，@num 的值为 null；若 @num := @num + 1 错写为 @num = @num + 1，则表示判断 @num 是否等于 @num + 1，显然这是不相等的，即表示逻辑假，因此始终返回 0。@num := @num + 1 表示将 @num 的值自增 1。

3.2.2. 将月份基础表与注册表关联查询

SELECT
	IFNULL( b.day_cnt, 0 ) AS `注册人数`,
	IFNULL( b.del_cnt, 0 ) AS `删除人数`,
	a.base_day AS `日期` 
FROM
	(
	SELECT
		CONCAT( DATE_FORMAT( CURDATE(), '%Y-%m-' ), LPAD( CAST( uu.num AS CHAR ), 2, '0' ) ) AS base_day 
	FROM
		(
		SELECT
			@num := @num + 1 AS num 
		FROM
			( SELECT 0 UNION ALL SELECT 1 ) AS t1,
			( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 ) AS t2,
			( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 ) AS t3,
			( SELECT @num := 0 ) tb 
		) AS uu 
	WHERE
		uu.num <= DAY ( LAST_DAY( CURDATE() ) ) 
	) a
	LEFT JOIN (
	SELECT
		COUNT(
		NULLIF( deleted, 0 )) AS `del_cnt`,-- 删除人数
		COUNT( uid ) AS `day_cnt`,-- 当天注册人数
		DATE_FORMAT( create_time, '%Y-%m-%d' ) AS `create_day` -- 日期		
	FROM
		`register` 
	WHERE
		DATE_FORMAT( create_time, '%Y-%m-%d' ) >= ( SELECT DATE_ADD( CURDATE(), INTERVAL - DAY ( CURDATE() )+ 1 DAY ) ) 
		AND DATE_FORMAT( create_time, '%Y-%m-%d' ) <= ( SELECT LAST_DAY( CURDATE() ) ) 
	GROUP BY
		DATE_FORMAT( create_time, '%Y-%m-%d' ) 
	ORDER BY
	DATE_FORMAT( create_time, '%Y-%m-%d' ) ASC 
	) b ON a.base_day = b.create_day;

执行结果如下：

注册人数删除人数日期
302021-11-01
312021-11-02
002021-11-03
.........
322021-11-27
.........
002021-11-30

结果符合预期。

4. 延伸思考

上面的两种方案实现了我们预期的效果，但从实际需求上来讲还有优化的空间。如，控制查询返回的起始和截止时间，而不是固定从月初到月末。假如系统是 2021-10-15 上线的，那么统计出 2021-10-01 到 2021-10-14 的记录就是无意义的，肯定没有注册用户；同理，上线当天统计出来 2021-10-16 到 201-10-31 的记录也是无意义的，未来的时间还没到，肯定也是没有注册用户的。 若出现起始-截止时间是跨月的，那么仅仅使用一条 sql 查询就变得困难了，而使用存储过程会容易些，但阿里编码规范是禁止使用存储过程的，会让系统移植、部署变得麻烦，一般传统行业使用存储过程可能会比较多，比如银行。 互联网应用追求快速响应，如果有复杂查询，会更倾向于放在后端代码中操作，因此跨月查询可以分解为多个月份，分别查询各月份的结果，然后在代码中取并集。而起始时间可能在月初或月中某一天，截止时间可能是月中某一天或月末，那么 Sql 中查询的 起始/截止 时间就需要用变量传入。 但这个需求既然已经需要在代码中操作了，何必需要那么麻烦，直接就对注册表分组查询，没有的月份在代码中给默认值即可，这么一说，似乎上文变得没必要了，实际上一般也不会用上述的方案来做统计，这里就是分享一种解决方式。 实际开发中，若注册表中记录数不多时，使用上述方式没有问题，倘若记录数达到了百万级别以上，查询速度就会明显变慢，而上述的查询语句无法用到索引，难以优化。因此，推荐如下解决方式：

  • 创建一张表，用定时任务，每天统计出当天的注册人数、删除人数等需要统计的信息，存入表中。这样查询的时候就只是单表查询，会极大提高查询速度；
  • 若注册表记录更多时，可以考虑将数据表同步存入到 Elasticsearch 中进行查询。如，使用 Canal ，它可以将自己伪装成 Mysql 的从节点，接收主节点的 binary log，处理后存入到 Elasticsearch 中，因此可用它来做异库同步。不过此种方案需要引入 Canal 组件，增加了系统复杂度，需要做好高可用。

以上介绍的解决方式，根据自己的情况以及系统体量做选择。

本文参考

版权归属：Mayee

许可证：署名 4.0 国际 (CC-BY-4.0)

©2021 Mayee