mysql求差集实战案例之用户统计累计用户数=旧表和新表的用户差集+新表用户

本文是针对特殊生产场景的简化分析，如果有建议或者改进意见，欢迎提出！

需求背景

我有一张 mysql 表 user_info 存储着当前的用户信息，但是有一些老的用户数据存放在 user_info_history 这张表里。

CREATE TABLE `user_info`(  
  `id` INT NOT NULL AUTO_INCREMENT,
  `cust_num` INT NOT NULL,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uk_cust_num` (`cust_num`)
);

CREATE TABLE `user_info_history`(  
  `id` INT NOT NULL AUTO_INCREMENT,
  `cust_num` INT NOT NULL,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uk_cust_num` (`cust_num`)
);

在实际问题中，user_info 和 user_info_history 共有的字段只有 cust_num 和 create_time，且两张表各自包含一些不共有的业务列（或者说业务字段），但是在本例中被我简化了。
因此，不要问我“本例中 user_info 和 user_info_history 表结构相同，为什么不直接合并两张表？”这样的问题。

初始化实验数据

INSERT INTO user_info(cust_num, create_time) VALUES(10000, "2022-07-10 08:00:00");
INSERT INTO user_info(cust_num, create_time) VALUES(10001, "2022-07-10 08:10:00");
INSERT INTO user_info(cust_num, create_time) VALUES(10002, "2022-07-10 08:20:00");
INSERT INTO user_info(cust_num, create_time) VALUES(10003, "2022-07-11 14:00:00");
INSERT INTO user_info(cust_num, create_time) VALUES(10004, "2022-07-11 15:00:00");
INSERT INTO user_info(cust_num, create_time) VALUES(10005, "2022-07-11 16:00:00");
INSERT INTO user_info(cust_num, create_time) VALUES(9000, "2022-07-11 00:01:00");

INSERT INTO user_info_history(cust_num, create_time) VALUES(9000, "2022-07-01 08:00:00");
INSERT INTO user_info_history(cust_num, create_time) VALUES(9001, "2022-07-02 08:10:00");
INSERT INTO user_info_history(cust_num, create_time) VALUES(9002, "2022-07-03 08:20:00");
INSERT INTO user_info_history(cust_num, create_time) VALUES(9003, "2022-07-04 14:00:00");
INSERT INTO user_info_history(cust_num, create_time) VALUES(9004, "2022-07-05 15:00:00");
INSERT INTO user_info_history(cust_num, create_time) VALUES(9005, "2022-07-06 16:00:00");
INSERT INTO user_info_history(cust_num, create_time) VALUES(9006, "2022-07-07 17:00:00");
INSERT INTO user_info_history(cust_num, create_time) VALUES(9007, "2022-07-08 18:00:00");
INSERT INTO user_info_history(cust_num, create_time) VALUES(9008, "2022-07-09 19:00:00");
INSERT INTO user_info_history(cust_num, create_time) VALUES(9009, "2022-07-10 20:00:00");
INSERT INTO user_info_history(cust_num, create_time) VALUES(10000, "2022-07-10 08:00:00");
INSERT INTO user_info_history(cust_num, create_time) VALUES(10001, "2022-07-10 08:10:00");
INSERT INTO user_info_history(cust_num, create_time) VALUES(10002, "2022-07-10 08:20:00");

不考虑时间字段计算差集

查找 user_info 包含，但是 user_info_history 不包含的所有用户：

SELECT a.cust_num,a.create_time,b.`cust_num` FROM user_info a LEFT JOIN user_info_history b ON a.`cust_num`=b.`cust_num` WHERE b.`cust_num` IS NULL 

查找 user_info_history 包含，但是 user_info 不包含的所有用户：

SELECT b.cust_num,b.create_time,a.`cust_num` FROM user_info a RIGHT JOIN user_info_history b ON a.`cust_num`=b.`cust_num` WHERE a.`cust_num` IS NULL 

参考文档：https://wenku.baidu.com/view/9465693c2179168884868762caaedd3383c4b5b3.html

把create_time也考虑进来

比如查询 2022-07-11 00:00:00 之前的累计用户：

SELECT COUNT(*) FROM user_info WHERE create_time < '2022-07-11 00:00:00'

首先查询出 user_info 表中，创建时间在 2022-07-11 00:00:00 之前的用户，共计 3 人

接着，还需要加上 user_info_history 中包含而 user_info 中不包含的用户数，此时如果继续使用刚才的差集查询语句：

SELECT b.cust_num,b.create_time FROM user_info a RIGHT JOIN user_info_history b ON a.`cust_num`=b.`cust_num` WHERE a.`cust_num` IS NULL 

就会出现一个问题：

（cust_num=9000) 的这个用户，在 user_info_history 中有数据创建于 2022-07-01 08:00:00，但是 用户最近一次登入则被记录在了 user_info 表，创建时间是 2022-07-11 00:01:00。
现在，要统计创建时间在 2022-07-11 00:00:00 之前的用户，（cust_num=9000) 这个用户肯定是要被计算在内的，但是查询 user_info 表时，没有被算入进来，那么，我们就必须修改差集的sql，让它显现出来。

SELECT b.cust_num,b.create_time FROM user_info a RIGHT JOIN user_info_history b ON a.`cust_num`=b.`cust_num` AND a.`create_time` < '2022-07-11 00:00:00' WHERE a.`cust_num` IS NULL

查询得知，user_info_history 中包含而 user_info 中不包含的用户数为 10 个，因此截止到 2022-07-11 00:00:00，累计用户总计为 13 个

为了更好地展现 on 中加入创建时间筛选条件的效果，我给大家加入了对比：

SELECT b.cust_num,b.create_time, a.`cust_num`, a.`create_time` FROM user_info a RIGHT JOIN user_info_history b ON a.`cust_num`=b.`cust_num`

SELECT b.cust_num,b.create_time, a.`cust_num`, a.`create_time` FROM user_info a RIGHT JOIN user_info_history b ON a.`cust_num`=b.`cust_num` AND a.`create_time` < '2022-07-11 00:00:00'

优化索引

建议为 user_info 添加索引来提高效率

ALTER TABLE `test`.`user_info`  ADD  INDEX `join_cust_num_create_time` (`cust_num`, `create_time`);