导读
最近多次看到用SQL查询连续打卡信息问题，自己也实践一波。抛开问题本身，也是对MySQL窗口函数和自定义变量用法的一种练习。
1CREATE TABLE `testd` (
2 `id` int NOT NULL AUTO_INCREMENT,
3 `userid` int NOT NULL,
4 `dday` date DEFAULT(CURRENT_DATE),
5 `flag` tinyint(1) DEFAULT(0),
6 PRIMARY KEY (`id`)
7) ENGINE=InnoDB
1INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (1, 1, '2020-03-31', 1);
2INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (2, 1, '2020-04-01', 0);
3INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (3, 1, '2020-04-02', 1);
4INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (4, 1, '2020-04-03', 1);
5INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (5, 1, '2020-04-04', 1);
6INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (6, 1, '2020-04-05', 1);
7INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (7, 1, '2020-04-07', 0);
8INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (8, 1, '2020-04-08', 1);
9INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (9, 1, '2020-04-09', 1);
10INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (10, 1, '2020-04-10', 1);
11INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (11, 1, '2020-04-11', 0);
12INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (12, 2, '2020-03-31', 0);
13INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (13, 2, '2020-04-01', 1);
14INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (14, 2, '2020-04-02', 1);
15INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (15, 2, '2020-04-03', 1);
16INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (16, 2, '2020-04-04', 1);
17INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (17, 2, '2020-04-05', 0);
18INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (18, 2, '2020-04-07', 0);
19INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (19, 2, '2020-04-08', 1);
20INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (20, 2, '2020-04-09', 1);
21INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (21, 2, '2020-04-10', 1);
22INSERT INTO `testd`(`id`, `userid`, `dday`, `flag`) VALUES (22, 2, '2020-04-11', 1);
MySQL8.0支持窗口函数
但对于这个具体需求，似乎现有窗口函数并不能直接得到答案，所以我们考虑退而求其次——采用自定义变量的方法曲线求解。
为简单起见，我们采取先单用户再多用户的思路逐步分析。
02 单用户打卡查询单用户情况下，求解连续打卡信息意味着，在对日期顺序排序的基础上：如果用户今天打卡：
如果昨天也打卡，则今天连续打卡天数是在昨天基础上+1
如果昨天未打卡，则连续打卡天数从1开始，计数1
如果未打卡，则记连续打卡天数为0
进一步地，我们发现在在定义用户未打卡时打卡天数=0的基础上，当用户打卡时无论前一天是否打卡，其打卡天数均为前一天+1(即要么是0+1，要么是N+1)
进而，可以写出如下SQL语句：1SELECT
2 userid, dday, flag, @pre_check := IF(flag, @pre_check + 1, 0) AS 'check_days'
3FROM
4 testd, (SELECT @pre_check := 0 ) init
5WHERE
6 userid = 1
7ORDER BY
8 dday
其中限定userid=1是为了仅考虑单用户情形，自定义变量@pre_check表示前一天打卡天数， init子表用于初始化变量@pre_check=0。得到查询结果：
单用户连续打卡信息
得到这个结果，那么对于单用户时求其打卡信息就简单多了，例如求其最近连续打卡天数，则用如下SQL： 1SELECT
2 userid, dday, check_days
3FROM
4 (
5 SELECT
6 userid, dday, flag, @pre_check := IF( flag, @pre_check + 1, 0 ) AS 'check_days'
7 FROM
8 testd, (SELECT @pre_check := 0 ) init
9 WHERE
10 userid = 1
11 ORDER BY
12 dday
13 ) tmp
14WHERE
15 check_days > 0
16ORDER BY
17 dday DESC
18LIMIT 1
单用户最近连续打卡信息那么如果要查询历史打卡天数最长的日期呢？只需要按打卡天数降序排列即可：
1SELECT
2 userid, dday, check_days
3FROM
4 (
5 SELECT
6 userid, dday, flag, @pre_check := IF( flag, @pre_check + 1, 0 ) AS 'check_days'
7 FROM
8 testd, (SELECT @pre_check := 0 ) init
9 WHERE
10 userid = 1
11 ORDER BY
12 dday
13 ) tmp
14ORDER BY
15 check_days DESC
16LIMIT 1
单用户历史最长打卡信息
03 多用户打卡查询在单用户打卡查询的基础上，其实多用户打卡查询的思路是一致的，只不过为了区分用户维度，需要再增加一个自定义变量。对用户和日期进行排序，而后采取以下逻辑：
如果当前记录的用户与上一个用户相同：
如果该用户当天打卡，则其打卡天数是前一天打卡天数+1
否则，即当天未打卡，则打卡天数为0
如果当前记录用户是新用户：
如果打卡，则打卡计数为1
否则，计数为0
1SELECT
2 userid, dday, flag,
3 @pre_check := IF(userid = @pre_userid, IF(flag, @pre_check + 1, 0), IF(flag, 1, 0)) AS 'check_days',
4 @pre_userid := userid AS 'Pre_user'
5FROM
6 testd, (SELECT @pre_check := 0, @pre_userid := null ) init
7ORDER BY
8 userid, dday
多用户连续打卡信息
在获得各用户连续打卡天数信息后，如果是查询各用户最近连续打卡天数，则可依据用户进行分组后查询该用户最近连续打卡天数大于0的信息 (为表述简单，记前面查询到的衍生表为tmp表)：1SELECT
2 userid, dday, check_days
3FROM
4 tmp
5WHERE
6 (userid, dday) in ( SELECT userid, max(dday)
7 FROM tmp
8 WHERE check_days>
9 GROUP BY userid )
多用户最近连续打卡信息
类似的，如果要查询各用户的历史最长连续打卡信息，依然采取类似思路，有：1SELECT
2 userid, dday, check_days
3FROM
4 tmp
5WHERE
6 (userid, check_days) in ( SELECT userid, max(check_days)
7 FROM tmp
8 GROUP BY userid )
多用户历史最长连续打卡信息
其中，注意到用户2有两次历史连续打卡天数为4的记录，且都是该用户最长打卡记录。
04 各用户所有连续打卡信息以上是查询了各用户1次特定的打卡信息(要么是最近，要么是历史最长)，如果要查询各用户所有连续打卡信息呢？例如，某用户在'2020-04-01'至'2020-04-04'连续4天打卡、在'2020-04-06'至'2020-04-10'连续5天打卡，则最终显示的2020-04-04的4天和2020-04-10的6天两条信息。
实际上，在以上查询的基础上，这样的查询就是在多用户连续打卡信息表(03部分第一张结果)中筛选出其后一天打卡为0的记录。也就是说，如果当前记录的下一天仍然是连续打卡，那么当前记录不作为最终结果；如果下一天打卡为0，才是最终想提取的信息。
为了实现这一需求，如果是8.0版本，可直接借助窗口函数lead()进行判断。例如： 1SELECT
2 userid, dday, check_days,
3 lead(flag) over(partition by userid) as 'nxt_flag'
4FROM
5 (SELECT
6 userid, dday, flag,
7 @pre_check := IF(userid = @pre_userid, IF(flag, @pre_check + 1, 0), IF(flag, 1, 0)) AS 'check_days',
8 @pre_userid := userid AS 'Pre_user'
9 FROM
10 testd, (SELECT @pre_check := 0, @pre_userid := null ) init
11 ORDER BY
12 userid, dday )tmp
带次日打卡信息的多用户连续打卡记录
基于此衍生表，进一步查出次日当日连续打卡>0且次日打卡为0或者为null的记录 (null代表当前是最后一天)即可。其SQL语句： 1SELECT userid, dday, check_days
2FROM
3 (
4 SELECT
5 userid, dday, check_days,
6 lead(flag) over(partition by userid) as 'nxt_flag'
7 FROM
8 (
9 SELECT
10 userid, dday, flag,
11 @pre_check := IF(userid = @pre_userid, IF(flag, @pre_check + 1, 0), IF(flag, 1, 0)) AS 'check_days',
12 @pre_userid := userid AS 'Pre_user'
13 FROM
14 testd, (SELECT @pre_check := 0, @pre_userid := null ) init
15 ORDER BY
16 userid, dday
17 ) tmp
18 ) tt
19WHERE check_days and (nxt_flag is null or nxt_flag=0)
各用户连续打卡记录
当然，如果是MySQL8.0以下版本，是没有lead()窗口函数可以直接调用的，次此时可借助连接查询或者子查询，设定连接条件是表1和表2用户相同、日期相差为1即可。
05 总结本文对MySQL中查询用户连续打卡这一问题进行了分析，主要是基于自定义变量的方式，实现了以下问题：
查询各用户每天的连续打卡信息(包括未打卡时记为0)
查询各用户最近连续打卡信息
查询各用户历史最长打卡信息
查询各用户所有打卡记录信息
相关阅读：
听说数据分析师挺火，我们来数据分析一下
5分钟速览python正则表达式常用函数
多种爬虫方式对比
用pyquery5行代码爬取百度热点新闻
生成词云的几种方式
划重点！你还在困惑MySQL中的"锁"吗？
一文解决所有MySQL分类排名问题
MySQL模糊搜索的几种姿势
674
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
