mysql 连续打卡_MySQL查询连续打卡信息？

最近多次看到用SQL查询连续打卡信息问题，自己也实践一波。抛开问题本身，也是对MySQL窗口函数和自定义变量用法的一种练习。

01 建表 所用数据库为MySQL8.0，简单而不失一般性，建立一个仅有记录id、用户id、日期和打卡标记共4个字段的数据表。建表语句为：

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);

查询目标是获得用户的连续打卡天数，包括最近连续打卡信息、历史最长打卡信息和所有连续打卡信息。 02 分析 对于这种类型的需求，个人认为应该属于滑动窗口问题，即满足同一取值的最大窗口长度(打卡情况的窗口长度)，MySQL自从8.0版本开始，提供了常用的窗口函数用法，像之前的3种排名(参考一文解决所有MySQL分类排名问题)问题在8.0中运用窗口函数就非常简单。

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

实际上就是在前一步得到的衍生表基础上，筛选打卡天数大于0的日期按降序排列，取出最近一条记录即为最近的连续打卡日期。筛选条件改成大于7就是最近的连续7天打卡的日期。得到查询结果：

单用户最近连续打卡信息 那么如果要查询历史打卡天数最长的日期呢？只需要按打卡天数降序排列即可：

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

基于以上思路，可写出基本的SQL语句如下：

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

其中增加了一个自定义变量@preuser，表示当前行的前一条记录用户信息。得到查询结果：

多用户连续打卡信息

在获得各用户连续打卡天数信息后，如果是查询各用户最近连续打卡天数，则可依据用户进行分组后查询该用户最近连续打卡天数大于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)

查询各用户最近连续打卡信息

查询各用户历史最长打卡信息

查询各用户所有打卡记录信息