简单的签到代码_开窗排序函数解决连续签到问题|SQL

开窗排序函数除了在处理简单的排序问题外，它的应用场景远比我们想的要多。本文将对两个问题，介绍开窗排序函数的用法。

一、问题

有一张用户签到表【签到表】，标记每天用户是否签到（说明：该表包含所有用户所有工作日的出勤记录） ，包含三个字段：日期【a_date】，用户id【a_id】，是否签到【fis_sign_in】。

问题1:用户最近一次连续签到的天数？
输出表:用户id【a_id】，用户连续签到天数【最近连续签到天数】

问题2:用户最大连续签到天数？

输出表:用户id【a_id】，用户最大连续签到天数【最大连续签到天数】

二、数据集的理解：连续签到问题的数据集分为两种

1、只包含用户签到的日期，用户没有签到的数据不做记录；

2、包含所有工作日的记录，其中有设置字段来记录用户是否签到(例如字段fis_sign _in，0为未签到，1为已签到)。

但第二种数据集的处理很简单，只要在第一种的基础上筛选掉 fis_sign_in=0 就可以了。

二、思路

如果是连续的几天，那么它们日期分别减去排序后的值是相同的，注意这里的排序是连续的。

假设日期为a，a+1，a+2，排名为1，2，3，那么日期分别减去排序的值都为a-1；

假设日期为b，b+2，b+3，排名为，1，2，3，那么日期分别减去排序的值为b-1，b。

但要注意的是！如果数据集含有不同月份的数据，日期不能只是简单的减去排序，因为如果把月份看作数值，那么不同月份之间的数值并不是连续的，比‘2020-03-31’和‘2020-04-01’在数值上就不是连续的，那么在减去连续的排序后，它们的值是不相等的，因此不会把他们归为同一类。

如图前两行：

因此我们要做一个处理，让处理后的rankk还是以日期的形式保留。我们可以在日期列上减去“排序”个天数，这样返回的rankk列就是以日期形式保留了。

date_add(日期, interval -排序 day)
date_add(a_date,interval-(row_number() over (partition by a_id order by a_date))day)

连续签到天数的代码：

分组前：
select a_id,a_date,
date_add(a_date,interval-(row_number() over (partition by a_id order by a_date))day)
as rankk
from 签到表;

分组后：
create view 连续签到表 as
select a_id,max(a_date)as 最后一次签到日期,count(*)as 连续签到天数 from 
(select a_id,a_date,
date_add(a_date,interval-(row_number() over (partition by a_id order by a_date))day)
as rankk
from 签到表) as s1
group by a_id,rankk

签到表

分组后：连续签到表

三、答案

问题1:用户最近一次连续签到的天数？/用户截止今天的连续签到天数？
输出：用户id【id】，用户连续签到天数【最近连续签到天数】

在连续签到基础上，利用关联子查询，找出各用户最后一次签到日期的最大值即可。

select a_id,连续签到天数 as 最近连续签到天数
from 连续签到表 as s1
where 最后一次签到日期>=ALL
(select 最后一次签到日期 from 连续签到表 as s2
where s1.a_id=s2.a_id
group by a_id)

问题2:用户最大连续签到天数？

输出表：用户id【id】，用户最大连续签到天数【最大连续签到天数】

在连续签到基础上，取出max(连续签到天数)

select a_id,max(连续签到天数) as 最大连续签到天数
from 连续签到表
group by a_id;

如果数据集属于第二种数据集，只需在建立连续签到表处添加where fis_sign_in=1即可。

create view 连续签到表 as
select a_id,max(a_date)as 最后一次签到日期,count(*)as 连续签到天数 from 
(select a_id,a_date,
date_add(a_date,interval-(row_number() over (partition by a_id order by a_date))day)
as rankk
from 签到表
where fis_sign_in=1) as s1
group by a_id,rankk