针对员工最大连续打卡天数等类似问题SQL求解,完整SQL代码见文尾。思路如下,
示例数据如下,
id | date | success |
1 | 2020/4/2 | 1 |
1 | 2020/4/3 | 1 |
1 | 2020/4/4 | 1 |
1 | 2020/4/5 | 0 |
1 | 2020/4/6 | 1 |
1 | 2020/4/7 | 1 |
2 | 2020/4/2 | 1 |
2 | 2020/4/3 | 1 |
1)通过窗口函数对员工打卡数据进行排序
id | date | rn |
1 | 2020/4/2 | 1 |
1 | 2020/4/3 | 2 |
1 | 2020/4/4 | 3 |
1 | 2020/4/6 | 4 |
1 | 2020/4/7 | 5 |
2 | 2020/4/2 | 1 |
2 | 2020/4/3 | 2 |
2)计算当前打卡日期与序号差值,我们可以看到如果是连续打卡,则label_date值是相同的
id | date | rn | label_date |
1 | 2020/4/2 | 1 | 2020/4/1 |
1 | 2020/4/3 | 2 | 2020/4/1 |
1 | 2020/4/4 | 3 | 2020/4/1 |
1 | 2020/4/6 | 4 | 2020/4/2 |
1 | 2020/4/7 | 5 | 2020/4/2 |
2 | 2020/4/2 | 1 | 2020/4/1 |
2 | 2020/4/3 | 2 | 2020/4/1 |
3)对label_date进行计数,取计数结果count_day的最大值即为最大连续打卡天数
id | label_date | count_day |
1 | 2020/4/1 | 3 |
1 | 2020/4/2 | 2 |
2 | 2020/4/1 | 2 |
SQL代码如下,
select
c.id,max(count_day) as max_day
from
(
select
b.id,b.label_date,count(*) as count_day
from
(
select
a.id,a.date,date_sub(a.date, cast(rn as int)) as label_date
from
(
select
id,date,
row_number() over(partition by id order by date) as rn
from events where success=1
)a
)b
group by b.id,b.label_date
)c
group by c.id;