Блог

Главная - Вопросы по программированию - Использование RANK() в SQL в качестве идентификационного номера для групп записей

Использование RANK() в SQL в качестве идентификационного номера для групп записей

#sql #datetime #window-functions #gaps-and-islands

#sql #datetime #окно-функции #пробелы и острова

Вопрос:

Это моя таблица:

 employeeid  workdate                workstatus
----------- ----------------------- ----------
1           2020-09-01 00:00:00.000 ON 
1           2020-09-02 00:00:00.000 ON 
1           2020-09-03 00:00:00.000 ON 
1           2020-09-04 00:00:00.000 OFF
1           2020-09-05 00:00:00.000 OFF
2           2020-09-01 00:00:00.000 ON 
2           2020-09-02 00:00:00.000 ON 
2           2020-09-03 00:00:00.000 OFF
2           2020-09-04 00:00:00.000 OFF
2           2020-09-05 00:00:00.000 ON

И я выполняю этот запрос:

 select employeeid, workdate, workstatus, rank() over(partition by employeeid, workstatus order by workdate) as cycle
from #workstatus
order by 1, 2

С этим результатом:

 employeeid  workdate                workstatus cycle
----------- ----------------------- ---------- --------------------
1           2020-09-01 00:00:00.000 ON         1
1           2020-09-02 00:00:00.000 ON         2
1           2020-09-03 00:00:00.000 ON         3
1           2020-09-04 00:00:00.000 OFF        1
1           2020-09-05 00:00:00.000 OFF        2
2           2020-09-01 00:00:00.000 ON         1
2           2020-09-02 00:00:00.000 ON         2
2           2020-09-03 00:00:00.000 OFF        1
2           2020-09-04 00:00:00.000 OFF        2
2           2020-09-05 00:00:00.000 ON         3

Моя цель — определить «цикл» включения / выключения работы по уникальному номеру для каждого сотрудника. Таким образом, три рабочих дня для сотрудника 1 будут циклом 1, тогда два ВЫХОДНЫХ дня будут циклом 2.

Первые два рабочих дня для сотрудника 2 будут циклом 1, затем два ВЫХОДНЫХ дня будут циклом 2, а последний РАБОЧИЙ день будет циклом 3.

Я не уверен, могу ли я использовать RANK () для этого, или если есть лучшее решение. Спасибо!

Ответ №1:

Это тип проблемы пробелов и островов. Для этой версии используйте lag() и совокупную сумму:

 select t.*,
       sum(case when prev_ws= workstatus then 0 else 1 end) over 
           (partition by employeeid order by workdate) as ranking
from (select t.*, 
             lag(workstatus) over (partition by employeeid order by workdate) as prev_ws
      from t
     ) t;

Ответ №2:

Используйте dense_rank вместо rank

Ответ №3:

Вы можете использовать оконные функции для решения этой проблемы пробелов и островов. Один из подходов заключается в использовании разницы между номерами строк для построения групп «смежных» записей:

 select employeeid, workdate, workstatus,
    row_number() over(partition by employeeid, workstatus, rn1 - rn2 order by workdate) cycle
from (
    select t.*,
        row_number() over(partition by employeeid order by workdate) rn1,
        row_number() over(partition by employeeid, workstatus order by workdate) rn2
    from mytable t
) t