跟踪缺失SQL的连续实例 [英] Tracking a continuous instance of absence SQL

问题描述

我有一个名为 sickness 的表，该表记录了员工何时下班病假.看起来像这样:

I have a table called sickness which is a record of when an employee is off work sick. It looks like this:

Date_Sick      Employee_Number
----------     ----------------
2020-06-08     001
2020-06-10     001
2020-06-11     001
2020-06-12     001
2020-06-08     002
2020-06-09     002

我想做的是添加一个具有唯一ID的新列，以标识一个缺勤的唯一实例.缺席的一个独特实例是连续工作日不间断运行的实例.因此，我的输出表应如下所示:

What I'm trying to do is add a new column with a unique ID to identify a unique instance of absence. A unique instance of absence is one that runs in consecutive weekdays with no breaks. Hence my output table should look like this:

Date_Sick      Employee_Number   Sickness_ID
----------     ----------------  -----------
2020-06-08     001               1
2020-06-10     001               2
2020-06-11     001               2
2020-06-12     001               2
2020-06-08     002               3
2020-06-09     002               3

我尝试使用 LEAD/LAG 创建各种分区，以检查下一个日期是否只有1天，但是我无法使其正常工作.

I've tried creating various partitions using LEAD/LAG to check if the next date is only 1 day away however I'm failing to get it to work.

修改，这还需要考虑个人的工作天数，我可以将其添加到表格中.因此，对于任何日期，我都可以添加一个标记，说"Y"或"N"，以说明是否期望该员工在办公室.因此，周末通常是'N'.

AMENDMENT this also needs to factor in only the days an individual would be working, which I can add to the table. So for any date I can add a flag to say 'Y' or 'N' to state if the employee would be expected to be in the office. So weekends would typically be a 'N'.

有什么想法吗?

推荐答案

这是一个孤岛问题.

在这里，我认为最简单的方法是 row_number()和日期算术:

Here, I think the simplest approach is row_number() and date arithmetics:

select date_sick, employee_number,
    dense_rank() over(order by employee_number,  dateadd(day, -rn, date_sick)) as sickness_id
from (
    select s.*,
        row_number() over(partition by employee_number order by date_sick) as rn
    from sickness s
) s
order by employee_number, date_sick

这可以通过将 date_sick 与递增的ID进行比较，然后使用该信息对记录进行排名来实现.

This works by comparing date_sick against an incrementing id, then using that information to rank the records.

DB Fiddle上的演示 首先由Larnu生成DDL:

Demo on DB Fiddle - with credits to Larnu for generating the DDL in the first place:

date_sick  | employee_number | sickness_id
:--------- | :-------------- | ----------:
2020-06-08 | 001             |           1
2020-06-10 | 001             |           2
2020-06-11 | 001             |           2
2020-06-12 | 001             |           2
2020-06-08 | 002             |           3
2020-06-09 | 002             |           3

这篇关于跟踪缺失SQL的连续实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！