Высчитать общее время временных диапозонов
Имеется БД MSql, в ней хранятся промежутки временных диапазонов (начало, конец)
На примере офиса:
1) Вася пришёл в офис 24.01.2020 09:30 и ушёл 24.01.2020 11:00 (1 (09:30-11:00)
2) Петя пришёл в офис 24.01.2020 12:00 и ушёл 24.01.2020 13:00 (2
3) Саша пришёл в офис 24.01.2020 12:30 и ушёл 24.01.2020 17:00 (2
4) Гена пришёл в офис 24.01.2020 13:30 и ушёл 24.01.2020 14:00 (2 (12:00-17:00)
5) Вася пришёл в офис 25.01.2020 10:00 и ушёл 25.01.2020 15:00 (3 (10:00-15:00)
Общее время (сумма 1-2-3 диапазонов) = 1.5 + 5 + 5 = 21.5 часов.
На примере всего 5 записей и 3 интервала времени, по факту их может быть 1000+
Как я понимаю, надо все диапазоны положить в одну плоскость/линию и посчитать суммарное время
SQL запрос или LINQ или ещё что? в какую сторону смотреть, чтобы реализовать данный подсчёт?
Ответы (2 шт):
Каждая запись порождает два события - приход и уход:
24.01.2020 09:30 Вася пришёл 24.01.2020 11:00 Вася ушёл 24.01.2020 12:00 Петя пришёл 24.01.2020 13:00 Петя ушёл 24.01.2020 12:30 Саша пришёл 24.01.2020 17:00 Саша ушёл 24.01.2020 13:30 Гена пришёл 24.01.2020 14:00 Гена ушёл 25.01.2020 10:00 Вася пришёл 25.01.2020 15:00 Вася ушёл
Отсортируем их по времени и добавим в таблицу число работников на работе:
0 на работе 24.01.2020 09:30 Вася пришёл 1 на работе 24.01.2020 11:00 Вася ушёл 0 на работе 24.01.2020 12:00 Петя пришёл 1 на работе 24.01.2020 12:30 Саша пришёл 2 на работе 24.01.2020 13:00 Петя ушёл 1 на работе 24.01.2020 13:30 Гена пришёл 2 на работе 24.01.2020 14:00 Гена ушёл 1 на работе 24.01.2020 17:00 Саша ушёл 0 на работе 25.01.2020 10:00 Вася пришёл 1 на работе 25.01.2020 15:00 Вася ушёл 0 на работе
Вычеркнем события когда на работе людей стало или было больше одного (должны остаться события вида "первый пришёл" и "последний ушёл"):
24.01.2020 09:30 Вася пришёл 1 на работе 24.01.2020 11:00 Вася ушёл 0 на работе 24.01.2020 12:00 Петя пришёл 1 на работе 24.01.2020 17:00 Саша ушёл 0 на работе 25.01.2020 10:00 Вася пришёл 1 на работе 25.01.2020 15:00 Вася ушёл 0 на работе
События соберём в пары. Для каждой пары вычислим длину промежутка и сложим их:
24.01.2020 09:30 Вася пришёл 1 на работе 24.01.2020 11:00 Вася ушёл 0 на работе 1.5 часа 24.01.2020 12:00 Петя пришёл 1 на работе 24.01.2020 17:00 Саша ушёл 0 на работе 5 часов 25.01.2020 10:00 Вася пришёл 1 на работе 25.01.2020 15:00 Вася ушёл 0 на работе 5 часов сумма 11.5 часов
Коротко
из промежутков наделать событий,
отсортировать,
подсчитать число работников на работе,
убрать события, когда работников много,
оставшиеся события попарно вычесть,
разности сложить.
Технология называется заметание.
Пример на PSQL
Исходная таблица:
@sv=# select * from ranges; who | enter | leave -------+---------------------+--------------------- Vasya | 2020-01-24 09:30:00 | 2020-01-24 11:00:00 Petya | 2020-01-24 12:00:00 | 2020-01-24 13:00:00 Sasha | 2020-01-24 12:30:00 | 2020-01-24 17:00:00 Gena | 2020-01-24 13:30:00 | 2020-01-24 14:00:00 Vasya | 2020-01-25 10:00:00 | 2020-01-25 15:00:00 (5 rows)
Таблица с событиями. Приходы помечаются как -1, уходы как 1. Почему так станет ясно позже:
@sv=# create view events as select -1 as tag, enter as time from ranges union select 1 as tag, leave as time from ranges order by time; CREATE VIEW @sv=# select * from events; tag | time -----+--------------------- -1 | 2020-01-24 09:30:00 1 | 2020-01-24 11:00:00 -1 | 2020-01-24 12:00:00 -1 | 2020-01-24 12:30:00 1 | 2020-01-24 13:00:00 -1 | 2020-01-24 13:30:00 1 | 2020-01-24 14:00:00 1 | 2020-01-24 17:00:00 -1 | 2020-01-25 10:00:00 1 | 2020-01-25 15:00:00 (10 rows)
Таблица со счётчиками присутствия:
@sv=# create view counts as select time, tag, sum(tag) over (rows unbounded preceding) as count from events; CREATE VIEW @sv=# select * from counts; time | tag | count ---------------------+-----+------- 2020-01-24 09:30:00 | -1 | -1 2020-01-24 11:00:00 | 1 | 0 2020-01-24 12:00:00 | -1 | -1 2020-01-24 12:30:00 | -1 | -2 2020-01-24 13:00:00 | 1 | -1 2020-01-24 13:30:00 | -1 | -2 2020-01-24 14:00:00 | 1 | -1 2020-01-24 17:00:00 | 1 | 0 2020-01-25 10:00:00 | -1 | -1 2020-01-25 15:00:00 | 1 | 0 (10 rows)
Фильтр "первый пришёл"/"последний ушёл". Обратите внимание как расположены ярлыки. Знаки чередуются: -1, 1, -1, 1, -1, 1. Это не случайно:
@sv=# select * from counts where (tag = -1 and count = -1) or (tag = 1 and count = 0); time | tag | count ---------------------+-----+------- 2020-01-24 09:30:00 | -1 | -1 2020-01-24 11:00:00 | 1 | 0 2020-01-24 12:00:00 | -1 | -1 2020-01-24 17:00:00 | 1 | 0 2020-01-25 10:00:00 | -1 | -1 2020-01-25 15:00:00 | 1 | 0 (6 rows)
Время присутствия на работе. Времена умножаются на ярлычки прихода/ухода. Теперь ясно зачем приход обозначался как -1, а уход как 1. Результат получается в секундах, делим его 3600:
@sv=# select sum((extract(epoch from time)) * tag) / 3600 from counts where (tag = -1 and count = -1) or (tag = 1 and count = 0); ?column? ---------- 11.5 (1 row)
Мне стало интересно, можно ли решить этот вопрос не сильно усложняя sql запрос. В итоге пришла идея ? не без минусов конечно, но работающая.
Минусы этой идеи в том что:
- понадобится дополнительная таблица в базе
- от количества записей в этой таблице зависит точность и диапазон
| Диапазон | Точность в минутах | Кол-во записей |
|---|---|---|
| c 9:00 до 17:30 | 30 минут | 17 |
| с 0:00 до 23:55 | 5 минут | 288 |
| с 0:00 до 23:59 | 1 минута | 1440 |
На этой картинке я попытался изобразить как учитываются полу часы. на примере отвлеченных данных (не из таблицы ниже)
Алгоритм:
- Сначала выбираются диапазоны которые либо содержат
интервалы присутсвия на работелибо частично в них включены. таким образом на каждые пол часа есть статистика сколько человек присутствует. - После чего мы выбираем только полу часы присутствия
existт.е. те что > 0 - Остается подсчитать их количество
COUNT(in_time.exist)и умножить на0.5что является обозначением полу часа.
Ниже идет пример дополнительной таблицы интервалов (9:00 - 17:30 / 30 мин) :
CREATE TABLE intervals (
id int,
start time,
end TIME
);
INSERT INTO intervals VALUES (0, '09:00:00', '09:30:00');
INSERT INTO intervals VALUES (0, '09:30:00', '10:00:00');
INSERT INTO intervals VALUES (1, '10:00:00', '10:30:00');
INSERT INTO intervals VALUES (2, '10:30:00', '11:00:00');
INSERT INTO intervals VALUES (3, '11:00:00', '11:30:00');
INSERT INTO intervals VALUES (4, '11:30:00', '12:00:00');
INSERT INTO intervals VALUES (5, '12:00:00', '12:30:00');
INSERT INTO intervals VALUES (6, '12:30:00', '13:00:00');
INSERT INTO intervals VALUES (7, '13:00:00', '13:30:00');
INSERT INTO intervals VALUES (8, '13:30:00', '14:00:00');
INSERT INTO intervals VALUES (9, '14:00:00', '14:30:00');
INSERT INTO intervals VALUES (10, '14:30:00', '15:00:00');
INSERT INTO intervals VALUES (11, '15:00:00', '15:30:00');
INSERT INTO intervals VALUES (12, '15:30:00', '16:00:00');
INSERT INTO intervals VALUES (13, '16:00:00', '16:30:00');
INSERT INTO intervals VALUES (14, '16:30:00', '17:00:00');
INSERT INTO intervals VALUES (15, '17:00:00', '17:30:00');
Далее таблица нашего журнала (2 колонки времени - имена не важны):
CREATE TABLE journal (
id int,
start time,
end TIME
);
INSERT INTO journal VALUES(0, '9:30:00', '11:30:00');
INSERT INTO journal VALUES(1, '12:00:00', '13:00:00');
INSERT INTO journal VALUES(2, '12:30:00', '17:00:00');
INSERT INTO journal VALUES(3, '13:30:00', '14:00:00');
INSERT INTO journal VALUES(4, '10:00:00', '15:00:00');
Ну и под конец самое интересное - запрос, в данном случае мы получаем 7.5 часов.
SELECT COUNT(in_time.exist) * 0.5 as result_hours
FROM (
SELECT start as s, end as e,
(
SELECT COUNT(start) as counter
FROM journal
WHERE
( journal.start = s OR journal.end = e )
OR
( journal.start < s AND journal.end > e )
) exist
FROM intervals HAVING exist > 0
) as in_time
| result_hours |
|---|
| 7.5 |