Как решить SQL задачку про пропускную систему?

Question

[Timur4D]

Здравствуйте. Задали задачку по sql. Не могу допереть как средствами только SQL решить.

Есть пропускная система на предприятии, которая сохраняет в БД только ID пропуска и время когда им воспользовались. Сотрудники могут выходить и входить во время рабочего дня (допустим покурить).
---- необходимо вывести рабочий табель сотрудника по дням. То есть отработанное время. У меня проблема с условием, что сотрудники могут выходить и входить во время работы. То есть самое простой вариант из макс времени вычесть минимум не совсем верный.

Вот запрос создании таблицы:


Вот данные таблицы:


Прошу помощи...

Answer 1

[ComodoHacker]

Задачка-то не простая.

Для начала нужно определить событие, которое было зарегистрировано: вход или выход. Это можно сделать, соединив таблицу саму с собой по условию "справа все события текущего дня, предшествующие событию слева". Подсчитав количество таких событий и приняв, что первое событие дня это вход, можно определить тип текущего события по четности или нечетности этого количества.

Затем нужно выбрать пары "вход" — "следующий выход" и подсчитать разницу времени между ними. Опять используем левое соединение со всеми последующими событиями дня нужного типа, берем минимум.

Затем просуммировать в пределах дня.

Comments

[Akina]

А почему соединение - левое?

[ComodoHacker]

Akina, На тот случай, если было одно событие за день.

Answer 2

[oleg_ods]

Алгоритм будет примерно следующий:

1. Выбираешь все записи использования пропуска у сотрудника.
2. Группируешь по дате.
3. Сортируешь по возрастанию времени.
4. Если предположить что рабочий день всегда начинается с входа, а заканчивается выходом, то можно вычислить периоды «рабочего» времени(от времени 2 записи отнять время 1, от 4 отнять 3 и тд).
5. Суммируешь полученные периоды.

Для решения задачи почитай про GROUP BY, ORDER BY

Comments

[Timur4D]

Может я что то не так понял, но если мы групируем по дате, то допустим если в таблице записи за 3 дня, то он не беря во внимания пункты 4 и 5 из вашего алгоритма, он выведет первое вхождение записи за каждый день , то есть будет всего лишь 3 записи

[oleg_ods]

Таблица


Запрос:

select * from pass_usage where id_person = 1 group by id, date(used_at) order by used_at;

Выборка:

Answer 3

[Akina]

Если пронумеровать записи для одного работника в течение одного дня, то нечётные - это входы, чётные соответственно выходы.

CTE, ROW_NUMBER().

Comments

[Timur4D]

Я использую MySQL в нем вроде нет ROW_NUMBER, ну вот я заменил переменной. Отсортировал. Да, до меня это тоже дошло. Но вот как вычислить да еще и средствами SQL понять все не могу.

[Vitsliputsli]

Timur4D, переведите время в интервалы от начала дня, нечетные умножьте на -1, и все их сложите.
Т.е. как-то так:
27.06.2021 09:00 => -9*60
27.06.2021 13:00 => 13*60
27.06.2021 14:00 => -14*60
27.06.2021 18:00 => 18*60
(-9) + (13) + (-14) + 18 = 8

[Timur4D]

Vitsliputsli, То есть грубо говоря взять часы нечетные сделать отрицательные и сложить?

[Akina]

Timur4D,

Я использую MySQL в нем вроде нет ROW_NUMBER

https://dev.mysql.com/doc/refman/8.0/en/window-fun...

Answer 4

[d-stream]

Как показала практика: точный учет входов и выходов для каждого сотрудника получается только на очень режимных предприятиях где стоит турникет "елочка" куда может протиснуться лишь один человек и за этой елочкой с двух сторон наблюдают вооруженные охранники.

Во всех остальных случаях ситуации "два выхода, один вход" и т.п. - достаточно регулярная ситуация.

Чуть облегчает картину, если есть разделение событий входа и выхода...

Ну а так - раздолье безопасникам, кадровикам и прочим вахтерам:
по каждому сотруднику дневные события классифицируются на корректные - число входов совпадает с числом выходов и отсутствуют комбинации вход-вход и выход-выход и некорректные
по корректным автоматически считается "трудовая дисциплина", по некорректным - кто-то из вахтеров втыкает в некорректный лог, смотрит записи, пытает сотрудника и например удаляет лишний вход из-за двойного махания картой -> лог по сотруднику становится корректным...

В общем:
- первый уровень выбор 100% корректных выборок сотрудник-день
- автокоррекция очевидных некорректностей типа два входа подряд в течение минуты
- ручная коррекция остального

ну а дальше снюхивать это с табелями, потом зазоры-допуски на опоздания до 3 минут например, вариации допустимости сдвигов перерывов на обед и как именно трактовать (нарушение или нет) ранний на 3 минуты уход на обед, но возврат на 15 минут раньше и засчитывать ли в рабочее время то что сотрудник пришел на час раньше, а ушел на час позже и не ходил на обед))))))

А потом все это сломается об круглосуточную работу предприятия где день и смена несколько не совпадают)

p.s. без оконных функций такое считать тяжко