---
url: /article/gkwk0wpr/index.md
---
## 1. 需求

实现如下查询：

|注册人数|删除人数|日期|
|:--:|:--:|:--:|
|5|1|2021-11-01|
|2|0|2021-11-02|
|...|...|...|...|
|0|0|2021-11-30|

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

## 2. 准备

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

* **创建表结构**

```sql
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 = '用户注册表';
```

* **插入数据**

```sql
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. 按天查询注册人数及删除人数

```sql
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\_cnt|day\_cnt|create\_day|
|:--:|:--:|:--:|
|0|3|2021-11-01|
|1|3|2021-11-02|
|2|3|2021-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. 查询当月的每一天

```sql
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. 将上面两个查询结果做关联查询

```sql
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;
```

执行结果如下：

|注册人数|删除人数|日期|
|:--:|:--:|:--:|
|3|0|2021-11-01|
|3|1|2021-11-02|
|0|0|2021-11-03|
|...|...|...|...|
|3|2|2021-11-27|
|...|...|...|...|
|0|0|2021-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. 构造月份基础表

```sql
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. 将月份基础表与注册表关联查询

```sql
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;
```

执行结果如下：

|注册人数|删除人数|日期|
|:--:|:--:|:--:|
|3|0|2021-11-01|
|3|1|2021-11-02|
|0|0|2021-11-03|
|...|...|...|...|
|3|2|2021-11-27|
|...|...|...|...|
|0|0|2021-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 组件，增加了系统复杂度，需要做好高可用。

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

***

::: note 本文参考

* [MySQL日期获取：本月第一天、本月最后一天、上月第一天、上月最后一天、下月第一天、下月最后一天.....](https://blog.csdn.net/min996358312/article/details/61420462)
* [mysql动态查询当月的每一天-绝对正确](https://blog.csdn.net/yyr3201128263/article/details/102586179)

:::