如何在 SQL 中获取连续天数并在不再连续时中断

Question

我正在尝试创建一个查询来计算员工连续工作的天数。当连续几天有休息并且我无法让它工作时，它应该重置。任何想法将不胜感激！

这是我的查询。我正在尝试使用 row_number 函数，但我不确定这是否是正确的做法：

WITH CTE AS (
select distinct TOT.EMPLOYEEID, TOT.DATE
from TOTALS TOT
where TOT.EMPLOYEEID IN ('020576','1200823') and
TOT.TIMEINSECONDS >= 14400 and
TOT.DATE >= '2019-01-01'
)
SELECT CTE.*,
ROW_NUMBER() OVER (PARTITION BY EMPLOYEEID ORDER BY DATE) AS CONSECUTIVEDAYS
FROM CTE
ORDER BY EMPLOYEEID, DATE;

结果如下：

EMPLOYEEID        DATE        CONSECUTIVEDAYS
020576        2019-01-01 00:00:00.000        1
020576        2019-01-02 00:00:00.000        2
020576        2019-01-03 00:00:00.000        3
020576        2019-01-04 00:00:00.000        4
020576        2019-01-07 00:00:00.000        5 <---- THIS SHOULD BE 1. THE COUNT SHOULD RESET BECAUSE THE DATES ARE NO LONGER CONSECUTIVE.
020576        2019-01-08 00:00:00.000        6
020576        2019-01-09 00:00:00.000        7
020576        2019-01-10 00:00:00.000        8
020576        2019-01-11 00:00:00.000        9
020576        2019-01-14 00:00:00.000        10 <---THIS SHOULD BE 1. THE COUNT SHOULD RESET BECAUSE THE DATES ARE NO LONGER CONSECUTIVE.
020576        2019-01-15 00:00:00.000        11
020576        2019-01-16 00:00:00.000        12
020576        2019-01-17 00:00:00.000        13
020576        2019-01-21 00:00:00.000        14 <---THIS SHOULD BE 1. THE COUNT SHOULD RESET BECAUSE THE DATES ARE NO LONGER CONSECUTIVE.
020576        2019-01-22 00:00:00.000        15
020576        2019-01-23 00:00:00.000        16
020576        2019-01-24 00:00:00.000        17
020576        2019-01-25 00:00:00.000        18
020576        2019-01-28 00:00:00.000        19 <---THIS SHOULD BE 1. THE COUNT SHOULD RESET BECAUSE THE DATES ARE NO LONGER CONSECUTIVE.
020576        2019-01-29 00:00:00.000        20
020576        2019-01-30 00:00:00.000        21
020576        2019-01-31 00:00:00.000        22
1200823        2019-01-01 00:00:00.000        1
1200823        2019-01-02 00:00:00.000        2
1200823        2019-01-03 00:00:00.000        3
1200823        2019-01-04 00:00:00.000        4
1200823        2019-01-07 00:00:00.000        5 <---THIS SHOULD BE 1. THE COUNT SHOULD RESET BECAUSE THE DATES ARE NO LONGER CONSECUTIVE.
1200823        2019-01-08 00:00:00.000        6
1200823        2019-01-09 00:00:00.000        7
1200823        2019-01-10 00:00:00.000        8
1200823        2019-01-11 00:00:00.000        9
1200823        2019-01-14 00:00:00.000        10 <---THIS SHOULD BE 1. THE COUNT SHOULD RESET BECAUSE THE DATES ARE NO LONGER CONSECUTIVE.
1200823        2019-01-15 00:00:00.000        11
1200823        2019-01-16 00:00:00.000        12
1200823        2019-01-18 00:00:00.000        13 <---THIS SHOULD BE 1. THE COUNT SHOULD RESET BECAUSE THE DATES ARE NO LONGER CONSECUTIVE.
1200823        2019-01-21 00:00:00.000        14 <---THIS SHOULD BE 1. THE COUNT SHOULD RESET BECAUSE THE DATES ARE NO LONGER CONSECUTIVE.
1200823        2019-01-22 00:00:00.000        15
1200823        2019-01-23 00:00:00.000        16
1200823        2019-01-24 00:00:00.000        17
1200823        2019-01-25 00:00:00.000        18
1200823        2019-01-28 00:00:00.000        19 <---THIS SHOULD BE 1. THE COUNT SHOULD RESET BECAUSE THE DATES ARE NO LONGER CONSECUTIVE.
1200823        2019-01-29 00:00:00.000        20
1200823        2019-01-30 00:00:00.000        21

Answer 1

您可以通过不同的方式标识相邻行号的组。 一种方法是使用LAG()并确定组的起点。 然后，累积总和定义组。

最后一步是ROW_NUMBER()每个组，以获得所需的编号：

WITH CTE AS (
select distinct TOT.EMPLOYEEID, TOT.DATE
from TOTALS TOT
where TOT.EMPLOYEEID IN ('020576','1200823') and
TOT.TIMEINSECONDS >= 14400 and
TOT.DATE >= '2019-01-01'
)
SELECT t.*,
ROW_NUMBER() OVER PARTITION BY EMPLOYEEID, GRP ORDER BY Date) as seqnum
FROM (SELECT CTE.*,
SUM(CASE WHEN prev_date < date - interval '1 day' THEN 1 ELSE 0 END) OVER (PARTITION BY EMPLOYEEID ORDER BY DATE) as grp
ROW_NUMBER() OVER (PARTITION BY EMPLOYEEID ORDER BY DATE) AS CONSECUTIVEDAYS
FROM (SELECT CTE.*,
LAG(DATE) OVER (PARTITION BY EMPLOYEEID ORDER BY DATE) as prev_date
FROM CTE
) t
) t
ORDER BY EMPLOYEEID, DATE;

请注意，这使用通用日期函数。 众所周知，日期处理是特定于数据库的。 它应该很容易适应您实际使用的数据库。