Скользящая сумма, когда дата непрерывна

Я пытаюсь выяснить, сколько дней люди непрерывно работали в SQL. Я думаю, что скользящая сумма может быть решением, но я не знаю, как ее решить.

Мой пример данных

| Employee | work_period |
| 1        | 2019-01-01  |
| 1        | 2019-01-02  |
| 1        | 2019-01-03  |
| 1        | 2019-01-04  |
| 1        | 2019-01-05  |
| 1        | 2019-01-10  |
| 1        | 2019-01-11  |
| 1        | 2019-01-12  |
| 2        | 2019-01-20  |
| 2        | 2019-01-22  |
| 2        | 2019-01-23  |
| 2        | 2019-01-24  |

Назначенный результат должен быть

| Employee | work_period | Continuous Days |
| 1        | 2019-01-01  | 1               |
| 1        | 2019-01-02  | 2               |
| 1        | 2019-01-03  | 3               |
| 1        | 2019-01-04  | 4               |
| 1        | 2019-01-05  | 5               |
| 1        | 2019-01-10  | 1               |
| 1        | 2019-01-11  | 2               |
| 1        | 2019-01-12  | 3               |
| 2        | 2019-01-20  | 1               |
| 2        | 2019-01-22  | 1               |
| 2        | 2019-01-23  | 2               |
| 2        | 2019-01-24  | 3               |

Если дни не являются непрерывными, непрерывный отсчет возобновится с 1.

Всего 3 ответа


Просто еще один вариант ... Очень похоже на Gaps-and-Islands, но без окончательной агрегации.

пример

Select Employee
      ,work_period
      ,Cont_Days = row_number() over (partition by Employee,Grp Order by Work_Period)
 From  (
        Select *
              ,Grp = datediff(day,񟫜-01-01',work_period) - row_number() over (partition by Employee Order by Work_Period) 
          From YourTable
       ) A

Возвращает

Employee    work_period Cont_Days
1           2019-01-01  1
1           2019-01-02  2
1           2019-01-03  3
1           2019-01-04  4
1           2019-01-05  5
1           2019-01-10  1
1           2019-01-11  2
1           2019-01-12  3
2           2019-01-20  1
2           2019-01-22  1
2           2019-01-23  2
2           2019-01-24  3

Это похоже на ответ Джона, но немного проще.

Вы можете идентифицировать группы смежных строк, вычитая последовательность чисел - разница постоянна. Так:

select Employee, work_period,
       row_number9) over (partition by employee, grp order by work_period) as day_counter
      ,Cont_Days = row_number() over (partition by Employee,Grp Order by Work_Period)
from (select t.*,
             dateadd(day,
                     - row_number() over (partition by employee order by work_period),
                     work_period
                    ) as grp
      from t
     ) t;

Еще один интересный способ сделать это - определить строки, с которых начинаются «острова», а затем использовать datediff() :

select t.*,
       datediff(day,
                max(case when island_start_flag = 1 then workperiod end) over (partition by employee order by workperiod),
                workperiod
               ) + 1 as days_counter
from (select t.*,
             (case when lag(workperiod) over (partition by employee order by workperiod) >= dateadd(day, -1, workperiod)
                   then 0 else 1
              end) as island_start_flag
      from t
     ) t;

Сначала вы можете использовать lag() чтобы проверить, имеет ли предыдущая строка (как отсортировано по work_period ) на сотрудника день, а не текущую строку. Используйте это в выражении CASE которое возвращает 0 если условие истинно, и 0 противном случае. Затем используйте оконную версию sum() чтобы суммировать 0 с и 1 с на сотрудника в порядке work_period . Это дает вам число на группу непрерывных дней для каждого сотрудника. Затем вы можете использовать этот номер группы для PARTITION BY дополнительно к пользователю в оконной версии sum() добавляя 1 для каждой строки в разделе, упорядоченном по work_period .

SELECT employee,
       work_period,
       sum(1) OVER (PARTITION BY employee,
                                 g
                    ORDER BY work_period) continuous_days
       FROM (SELECT employee,
                    work_period,
                    sum(c) OVER (PARTITION BY employee
                                 ORDER BY work_period) g
                    FROM (SELECT employee,
                                 work_period,
                                 CASE
                                   WHEN lag(work_period) OVER (PARTITION BY employee
                                                               ORDER BY work_period) = dateadd(day, -1, work_period) THEN
                                     0
                                   ELSE
                                     1
                                 END c
                                 FROM elbat) x) y;

дб <> скрипка


Есть идеи?

10000