SQL подсчет непрерывного присутствия/отсутсвия

Question

Есть таблица присутствия/отсутствия сотрудника в рабочий день офиса в виде:

user_position 1 - присутсвовал, 2 - отсутствовал

Запросом нужно вывести дату начала вхождения в позицию position_start и дату окончания вхождения в позицию position_end по каждому сотруднику и в разрезе user_position. То что должно получиться

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

select *,
    dense_rank() over(partition by user_id order by date_period) as rank
from User_p

Answer 1

Создадим следующую таблицу с нашими исходными данными

CREATE TABLE your_table_name (
    user_id NUMBER,
    user_position NUMBER,
    date_position DATE
);

Вставим данные:

INSERT ALL
    INTO your_table_name (user_id, user_position, date_position) VALUES (1, 1, TO_DATE('01.01.2019', 'DD.MM.YYYY'))
    INTO your_table_name (user_id, user_position, date_position) VALUES (1, 1, TO_DATE('02.01.2019', 'DD.MM.YYYY'))
    INTO your_table_name (user_id, user_position, date_position) VALUES (1, 2, TO_DATE('03.01.2019', 'DD.MM.YYYY'))
    INTO your_table_name (user_id, user_position, date_position) VALUES (1, 2, TO_DATE('04.01.2019', 'DD.MM.YYYY'))
    INTO your_table_name (user_id, user_position, date_position) VALUES (1, 1, TO_DATE('05.01.2019', 'DD.MM.YYYY'))
    INTO your_table_name (user_id, user_position, date_position) VALUES (1, 1, TO_DATE('06.01.2019', 'DD.MM.YYYY'))
    INTO your_table_name (user_id, user_position, date_position) VALUES (2, 1, TO_DATE('01.01.2019', 'DD.MM.YYYY'))
    INTO your_table_name (user_id, user_position, date_position) VALUES (2, 1, TO_DATE('02.01.2019', 'DD.MM.YYYY'))
    INTO your_table_name (user_id, user_position, date_position) VALUES (2, 1, TO_DATE('03.01.2019', 'DD.MM.YYYY'))
    INTO your_table_name (user_id, user_position, date_position) VALUES (2, 2, TO_DATE('04.01.2019', 'DD.MM.YYYY'))
    INTO your_table_name (user_id, user_position, date_position) VALUES (2, 2, TO_DATE('05.01.2019', 'DD.MM.YYYY'))
    INTO your_table_name (user_id, user_position, date_position) VALUES (2, 2, TO_DATE('08.01.2019', 'DD.MM.YYYY'))
    INTO your_table_name (user_id, user_position, date_position) VALUES (2, 2, TO_DATE('10.01.2019', 'DD.MM.YYYY'))
SELECT * FROM DUAL;

Работаю с Oracle, поэтому запрос будет с использованием его функционала. В подзапросе используем оконную функцию row_number() чтобы пронумеровать строки в каждой группе по user_id и user_position, а затем вычеслим разницу между этой нумерацией по user_id без учёта user_position. Так мы создадим группы для агрегации. Сгруппируем в последсвтии результаты по данному аргументу агреации, чтобы получить начальную и конечную даты. Затем выведем.

SELECT user_id, user_position, MIN(date_position) AS date_start, MAX(date_position) AS date_end
FROM (
    SELECT user_id, user_position, date_position,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date_position) -
           ROW_NUMBER() OVER (PARTITION BY user_id, user_position ORDER BY date_position) AS grp
    FROM your_table_name
)
GROUP BY user_id, user_position, grp
ORDER BY user_id, MIN(date_position);

dbfiddle для понимания того, как это будет работать.

БЛОГ НА HUSL

SQL подсчет непрерывного присутствия/отсутсвия

Ответы (1 шт):