mysql check 长度_MySQL查询连续打卡信息?

导读

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

152a700a5a0169f5a2f414469140f779.png

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中运用窗口函数就非常简单。

4afdc580deca1577cbd445b376bc1c47.png

MySQL8.0支持窗口函数

但对于这个具体需求,似乎现有窗口函数并不能直接得到答案,所以我们考虑退而求其次——采用自定义变量的方法曲线求解。

为简单起见,我们采取先单用户再多用户的思路逐步分析。

02 单用户打卡查询单用户情况下,求解连续打卡信息意味着,在对日期顺序排序的基础上:
  1. 如果用户今天打卡:

    1. 如果昨天也打卡,则今天连续打卡天数是在昨天基础上+1

    2. 如果昨天未打卡,则连续打卡天数从1开始,计数1

  2. 如果未打卡,则记连续打卡天数为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。得到查询结果:

d6fb4e65d7d285e8f21d5a13360e576f.png

单用户连续打卡信息

得到这个结果,那么对于单用户时求其打卡信息就简单多了,例如求其最近连续打卡天数,则用如下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天打卡的日期。得到查询结果: 1f8a83988ffc46032a1616b222c402f4.png 单用户最近连续打卡信息那么如果要查询历史打卡天数最长的日期呢?只需要按打卡天数降序排列即可:
 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
得到结果:

95042e391de6ff5fe617cca238fc87eb.png

单用户历史最长打卡信息

03 多用户打卡查询

在单用户打卡查询的基础上,其实多用户打卡查询的思路是一致的,只不过为了区分用户维度,需要再增加一个自定义变量。对用户和日期进行排序,而后采取以下逻辑:

  1. 如果当前记录的用户与上一个用户相同:

    1. 如果该用户当天打卡,则其打卡天数是前一天打卡天数+1

    2. 否则,即当天未打卡,则打卡天数为0

  2. 如果当前记录用户是新用户:

    1. 如果打卡,则打卡计数为1

    2. 否则,计数为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,表示当前行的前一条记录用户信息。得到查询结果:

4e9eb0850bc95b147d8f4aefb6f84899.png

多用户连续打卡信息

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

319f6620e9d50db079259afa1b852a51.png

多用户最近连续打卡信息

类似的,如果要查询各用户的历史最长连续打卡信息,依然采取类似思路,有:
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 )
得到查询结果:

455f47a6b42f628d19381427c1536ffc.png

多用户历史最长连续打卡信息

其中,注意到用户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
得到结果:

185d864297ca7cac331d76f121f10467.png

带次日打卡信息的多用户连续打卡记录

基于此衍生表,进一步查出次日当日连续打卡>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)
最终,得到查询结果:

b09d03e8eb5143b151662e69c8c13d05.png

各用户连续打卡记录

当然,如果是MySQL8.0以下版本,是没有lead()窗口函数可以直接调用的,次此时可借助连接查询或者子查询,设定连接条件是表1和表2用户相同、日期相差为1即可。

05 总结

本文对MySQL中查询用户连续打卡这一问题进行了分析,主要是基于自定义变量的方式,实现了以下问题:

  • 查询各用户每天的连续打卡信息(包括未打卡时记为0)

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

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

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

f61479b029c43bedaed535c92a87c388.png

相关阅读:

  • 听说数据分析师挺火,我们来数据分析一下

  • 5分钟速览python正则表达式常用函数

  • 多种爬虫方式对比

  • 用pyquery5行代码爬取百度热点新闻

  • 生成词云的几种方式

  • 划重点!你还在困惑MySQL中的"锁"吗?

  • 一文解决所有MySQL分类排名问题

  • MySQL模糊搜索的几种姿势

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值