前言
快下班,女朋友发给我一张截图,问我会不会写个 sql 查询结果如图。
真男人怎么能说不行?!
看了眼她的需求,很快写好发给她。
没想到,她又说,能不能这样…那样…
我一听有点不对劲,要哪样?她一个产品经理,需要写复杂的 Sql 语句?
于是问她:怎么了?是不是那帮开发又不听话了?
“呜呜呜…”
果然,产品经理提出需求,需要按日统计每个月的数据,如果当天没有数据,也要显示,数据为0即可。
嗯……看起来并不太复杂,不过对于她公司那帮刺儿头来说,大概就是嫌麻烦吧。于是…
一、需求
实现如下查询:
注册人数 | 删除人数 | 日期 |
---|---|---|
5 | 1 | 2021-11-01 |
2 | 0 | 2021-11-02 |
… | … | … |
0 | 0 | 2021-11-30 |
要求,按照自然月统计,即当月有多少天,查询结果就需要有多少天的记录,若当天没有注册记录,则注册人数和删除人数均为 0 。
二、准备
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.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_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 查询当月的每一天
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;
执行结果如下:
注册人数 | 删除人数 | 日期 |
---|---|---|
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 构造月份基础表
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;
执行结果如下:
注册人数 | 删除人数 | 日期 |
---|---|---|
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 |
结果符合预期。
四、延伸思考
上面的两种方案实现了我们预期的效果,但从实际需求上来讲还有优化的空间。如,控制查询返回的起始和截止时间,而不是固定从月初到月末。假如系统是 2021-10-15 上线的,那么统计出 2021-10-01 到 2021-10-14 的记录就是无意义的,肯定没有注册用户;同理,上线当天统计出来 2021-10-16 到 201-10-31 的记录也是无意义的,未来的时间还没到,肯定也是没有注册用户的。
若出现起始-截止时间是跨月的,那么仅仅使用一条 sql 查询就变得困难了,而使用存储过程会容易些,但阿里编码规范是禁止使用存储过程的,会让系统移植、部署变得麻烦,一般传统行业使用存储过程可能会比较多,比如银行。
互联网应用追求快速响应,如果有复杂查询,会更倾向于放在后端代码中操作,因此跨月查询可以分解为多个月份,分别查询各月份的结果,然后在代码中取并集。而起始时间可能在月初或月中某一天,截止时间可能是月中某一天或月末,那么 Sql 中查询的 起始/截止 时间就需要用变量传入。
但这个需求既然已经需要在代码中操作了,何必需要那么麻烦,直接就对注册表分组查询,没有的月份在代码中给默认值即可,这么一说,似乎上文变得没必要了,实际上一般也不会用上述的方案来做统计,这里就是分享一种解决方式。
实际开发中,若注册表中记录数不多时,使用上述方式没有问题,倘若记录数达到了百万级别以上,查询速度就会明显变慢,而上述的查询语句无法用到索引,难以优化。因此,推荐如下解决方式:
- 创建一张表,用定时任务,每天统计出当天的注册人数、删除人数等需要统计的信息,存入表中。这样查询的时候就只是单表查询,会极大提高查询速度;
- 若注册表记录更多时,可以考虑将数据表同步存入到 Elasticsearch 中进行查询。如,使用 Canal ,它可以将自己伪装成 Mysql 的从节点,接收主节点的 binary log,处理后存入到 Elasticsearch 中,因此可用它来做异库同步。不过此种方案需要引入 Canal 组件,增加了系统复杂度,需要做好高可用。
以上介绍的解决方式,根据自己的情况以及系统体量做选择。
结尾
我把 Sql 语句发给女朋友之后…
“哼!明天看我打脸那帮程序员!”
看着这句话,我都能想象出她在屏幕那边开心的样子。
不过呢,为了她日后的工作开展,我还是发了句:打人不打脸,私法给他就好,要是在众人面前让他下不来台,以后岂不是更麻烦。
参考文章:
MySQL日期获取:本月第一天、本月最后一天、上月第一天、上月最后一天、下月第一天、下月最后一天…
mysql动态查询当月的每一天-绝对正确