Сгруппировать периоды в запросе.

Форум Программирование Общие вопросы программирования (Dev)
1. 🅵🅾️🆇 523 22.11.17 12:56 Сейчас в теме
День добрый, у меня есть регистр сведений с данными по использованию дверей, скажем в таком виде: 
10.11.2017	1 эт. Механичка	Петя	Вход	22:12:44
12.11.2017	1 эт. Центральный вход	Вася	Вход	0:19:02


Есть желание на основе этих данных получать период нахождения на рабочем месте.
Усложняется тем, что существуют "ночные смены" и человек, скажем, может уйти с утра, а вечером вернуться на работу.

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

Естественно можно выбрать из РС интересующие меня данные, а потом перебрать в цикле, но это не очень изящное решение, хотелось бы решить в запросе.

Пока пришел к такому запросу: 
ВЫБРАТЬ
	Период, Сотрудник,
	Час(Время)	КАК Час,
	Минимум(ВЫБОР
		КОГДА Направление = Значение(Перечисление.НаправлениеПроходаСигур.Вход)
			ТОГДА Минута(Время)*60 + Секунда(Время)
		ИНАЧЕ NULL
	КОНЕЦ)		КАК Вход,
	Максимум(ВЫБОР
		КОГДА Направление = Значение(Перечисление.НаправлениеПроходаСигур.Выход)
			ТОГДА Минута(Время)*60 + Секунда(Время)
		ИНАЧЕ NULL
	КОНЕЦ)		КАК Выход
ИЗ
	РегистрСведений.ПроходыСигур
ГДЕ
	Дверь В(Значение(Перечисление.ДвериСигур.Дверь03), Значение(Перечисление.ДвериСигур.Дверь07))
	И Период Между &ДатаН И &ДатаО
	И Сотрудник.Наименование ПОДОБНО "%Вадим%"
СГРУППИРОВАТЬ ПО
	Период,
	Сотрудник,
	Час(Время)
Показать

Результат: 
Период		Сотрудник	Час	Вход	Выход
16.11.2017		Вадим		8	2 033	2 777
16.11.2017		Вадим		9	NULL	3 158
16.11.2017		Вадим		10	287		2 787
16.11.2017		Вадим		14	581		2 979
16.11.2017		Вадим		15	1 274	NULL
16.11.2017		Вадим		17	326		380
16.11.2017		Вадим		18	187		1 053


Как бы мне сгруппировать данные по тем часам, между которыми разница меньше 6?
Тобишь из серии:

1
2
3
12
13
14
15


Надо получить итоги или сгруппировать в запросе как:

Группа 1:
1
2
3

Группа 2:
12
13
14
15


Заранее спасибо за помощь :3
+ –
По теме из базы знаний
Вознаграждение за ответ
Показать полностью
Найденные решения
12. ildarovich 7861 22.11.17 15:21 Сейчас в теме +1 $m
Могу предложить вот такой вариант (два первых подзапроса - это формирование исходных данных для проверки в консоли запросов)
ВЫБРАТЬ
	ДАТАВРЕМЯ(2017, 11, 22) КАК Период,
	"Петя" КАК Сотрудник,
	ДАТАВРЕМЯ(1, 1, 1, 14, 31, 0) КАК Время
ПОМЕСТИТЬ ПроходыСигур

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	ДАТАВРЕМЯ(2017, 11, 22),
	"Петя",
	ДАТАВРЕМЯ(1, 1, 1, 14, 32, 0)

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	ДАТАВРЕМЯ(2017, 11, 22),
	"Петя",
	ДАТАВРЕМЯ(1, 1, 1, 21, 32, 0)

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	ДАТАВРЕМЯ(2017, 11, 22),
	"Петя",
	ДАТАВРЕМЯ(1, 1, 1, 22, 35, 0)
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	ДОБАВИТЬКДАТЕ(ПроходыСигур.Период, СЕКУНДА, РАЗНОСТЬДАТ(ДАТАВРЕМЯ(1, 1, 1), ПроходыСигур.Время, СЕКУНДА)) КАК Дата,
	ПроходыСигур.Сотрудник
ПОМЕСТИТЬ Дано
ИЗ
	ПроходыСигур КАК ПроходыСигур
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	МАКСИМУМ(Раньше.Дата) КАК От,
	Дано.Дата КАК До,
	Дано.Сотрудник
ПОМЕСТИТЬ Отсутствия
ИЗ
	Дано КАК Раньше
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ Дано КАК Дано
		ПО Раньше.Сотрудник = Дано.Сотрудник
			И Раньше.Дата < Дано.Дата

СГРУППИРОВАТЬ ПО
	Дано.Сотрудник,
	Дано.Дата

ИМЕЮЩИЕ
	РАЗНОСТЬДАТ(МАКСИМУМ(Раньше.Дата), Дано.Дата, ЧАС) > 6

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ РАЗЛИЧНЫЕ
	&ДатаНачала,
	&ДатаНачала,
	ПроходыСигур.Сотрудник
ИЗ
	ПроходыСигур КАК ПроходыСигур

ОБЪЕДИНИТЬ

ВЫБРАТЬ РАЗЛИЧНЫЕ
	&ДатаОкончания,
	&ДатаОкончания,
	ПроходыСигур.Сотрудник
ИЗ
	ПроходыСигур КАК ПроходыСигур
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	МАКСИМУМ(Раньше.До) КАК От,
	Отсутствия.От КАК До,
	Отсутствия.Сотрудник
ИЗ
	Отсутствия КАК Отсутствия
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ Отсутствия КАК Раньше
		ПО Отсутствия.Сотрудник = Раньше.Сотрудник И Раньше.До < Отсутствия.От  

СГРУППИРОВАТЬ ПО
	Отсутствия.От,
	Отсутствия.Сотрудник
Показать
Эта задача относится к классу "Islands and Gaps". В запросе сначала определяются все отсутствия сотрудника, а затем периоды между отсутствиями считаются периодами работы.
Отсутствиями считаются все периоды неактивности длиннее шести часов.
То есть предлагается вообще отказаться от анализа типов событий: вход, выход, так как непонятно внешние ли это двери и всегда ли срабатывает регистрация. Любое событие считается активностью. Время соединяется с датой. Это важно для круглосуточности.

Если все же двери строго внешние и жалко терять информацию о типе события, то считаю, что "математически" правильно будет разделять последовательные вход-вход или выход-выход противоположным событием, расположенным строго посередине интервала равновероятности времени. В среднем это будет сводить к минимуму ошибку домысливания пропущенного при регистрации события.
Aleskey_K; 🅵🅾️🆇; sm.artem; +3 –
Остальные ответы
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
2. sm.artem 14 22.11.17 13:04 Сейчас в теме
Добрый день!

Если "в лоб", то можно, например в том же запросе добавить служебное, куда бы записывался результат сравнения и затем выполнить группировку по этому полю.
Если я правильно понял этот вопрос.

Например, 

ВЫБОР 
  КОГДА Разница > 6
  ТОГДА "Работал" // (или ИСТИНА)
  ИНАЧЕ "Не работал" // (или ЛОЖЬ)
КОНЕЦ КАК СтатусСотрудника
+ –
3. 🅵🅾️🆇 523 22.11.17 13:14 Сейчас в теме
(2) Ну вот в том и проблема, что надо неким образом сравнить различные строки.
Я не представляю как получить вот эту "Разницу" =(

Еслиб все люди работали как обычно принято, скажем с 8 утра до 17 вечера, я бы просто из всех проходов туда-сюда получил самый первый вход (Минимум(ВремяВхода)) и самый последний выход (Максимум(ВремяВыхода)).

Но дело в том, что человек может работать с 17 вечера до 8 утра и даже в один календарный день, у него может получиться 2 рабочих. Поэтому я и придумал логику - отделять рабочие дни друг от друга промежутком в 6 часов отсутсвия на территории. Очень хотелось бы сделать это через запрос, если это возможно, наверняка это было бы изящнее, чем перебирать все данные в цикле последовательно сравнивая строки с предидущими.
+ –
4. VmvLer 22.11.17 13:17 Сейчас в теме
думаю ИМЕЮЩИЕ решает задачу
+ –
5. Artemka20121 6 22.11.17 13:21 Сейчас в теме
(0) Если у тебя железно все отмечаются на вход -выход
собираешь таблицу в одну временную далее разделяешь ее на 2 (одна на вход вторая на выход - по каждому сотруднику) сортируешь их по времени .... далее нумеруешь обе таблицы ... вот этот номер строки у тебя и будет ключиком для связки вход- выход
далее объединение этих двух таблиц
в итоге получаем
Петров - вход - выход
далее разностьдат = получаешь время и далее группируешь по периоду агрегируя время
если нужно время менее 6 часов пользуешься ИМЕЮЩИЕ
примерно как то так :)
+ –
6. 🅵🅾️🆇 523 22.11.17 13:29 Сейчас в теме
(5) К сожалению по нумерации не связать, количество "входов" может быть не равно количеству "выходов" и они могут быть не последовательны:
1) Человек может дважды приложить карточку к двери на вход
2) Может пройти за кем то не прикладывая (причем если он просто вышел в середине дня покурить или там до машины добежать, ваша логика нарушиться, в то время как данных для вычисления рабочего времени по нему по прежнему достаточно).
+ –
7. Artemka20121 6 22.11.17 13:38 Сейчас в теме
(6) значит в запросе надо получить итоговую таблицу входов выходов без лишних строк ... и далее как я описал в (5)
+ –
11. sm.artem 14 22.11.17 14:50 Сейчас в теме
Примерно, как предложили в (5) :

Только, формируете одну таблицу временную, где ранжируете строки по увеличению времени

Далее, нумеруете полученные строки подряд


Период Сотрудник Событие Время
1 16.11.2017 Вадим Вход 8:00
2 16.11.2017 Вадим Выход 8:15
3 16.11.2017 Вадим Выход 9:30
4 16.11.2017 Вадим Вход 10:11
5 16.11.2017 Вадим Выход 10:45
6 16.11.2017 Вадим Вход 14:03
7 16.11.2017 Вадим Выход 14:59
8 16.11.2017 Вадим Вход 15:14
9 16.11.2017 Вадим Вход 17:15
10 16.11.2017 Вадим Выход 17:30
11 16.11.2017 Вадим Вход 18:16
12 16.11.2017 Вадим Выход 18:53

Далее делаете вторую таблицу из первой с левым соединением ее же, но по условию, что номер строки присоединяемой таблицы равен номеру первой таблицы +1

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

Период Сотрудник Событие Время1 ЭтоВыход1 Номер2 ЭтоВход2 Время2
1 16.11.2017 Вадим Вход 8:00 Нет 2 Нет 8:15
2 16.11.2017 Вадим Выход 8:15 Да 3 Нет 9:30
3 16.11.2017 Вадим Выход 9:30 Да 4 Да 10:11
4 16.11.2017 Вадим Вход 10:11 Нет 5 Нет 10:45
5 16.11.2017 Вадим Выход 10:45 Да 6 Да 14:03
6 16.11.2017 Вадим Вход 14:03 ......
7 16.11.2017 Вадим Выход 14:59 ...и так далее....
8 16.11.2017 Вадим Вход 15:14
9 16.11.2017 Вадим Вход 17:15
10 16.11.2017 Вадим Выход 17:30
11 16.11.2017 Вадим Вход 18:16
12 16.11.2017 Вадим Выход 18:53

Ну а далее дело техники, отбираете только те варианты, где Поля ЭтоВыход1 и ЭтоВход2 оба Истина (так как нам надо узнавать разницу между выходом и входом) и находите разницу между полями Время1 и Время2 (при условии что ЭтоВыход1 и ЭтоВход2 = ИСТИНА)

При формировании таблица могла сбиться, прикладываю картинку
Прикрепленные файлы:
🅵🅾️🆇; +1 –
8. Artemka20121 6 22.11.17 13:44 Сейчас в теме
(7) "Может пройти за кем то не прикладывая" тогда зачем вам вообще карточки ? наведите порядок с отметками в таком случае
+ –
9. 🅵🅾️🆇 523 22.11.17 13:46 Сейчас в теме
(8) Ну не вижу ничего плохого, если человек по чужой карточке выйдет "покурить", а зайдет по своей - кроме вреда здоровью, разумеется. Этим самым он же не отмечается об уходе насовсем.
+ –
10. Artemka20121 6 22.11.17 13:55 Сейчас в теме
(8) у вас есть все инструменты чтобы удалить лишние записи из таблиц и вывести итоговую таблицу периодов по каждому сотруднику (6)
+ –
12. ildarovich 7861 22.11.17 15:21 Сейчас в теме +1 $m
Могу предложить вот такой вариант (два первых подзапроса - это формирование исходных данных для проверки в консоли запросов)
ВЫБРАТЬ
	ДАТАВРЕМЯ(2017, 11, 22) КАК Период,
	"Петя" КАК Сотрудник,
	ДАТАВРЕМЯ(1, 1, 1, 14, 31, 0) КАК Время
ПОМЕСТИТЬ ПроходыСигур

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	ДАТАВРЕМЯ(2017, 11, 22),
	"Петя",
	ДАТАВРЕМЯ(1, 1, 1, 14, 32, 0)

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	ДАТАВРЕМЯ(2017, 11, 22),
	"Петя",
	ДАТАВРЕМЯ(1, 1, 1, 21, 32, 0)

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	ДАТАВРЕМЯ(2017, 11, 22),
	"Петя",
	ДАТАВРЕМЯ(1, 1, 1, 22, 35, 0)
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	ДОБАВИТЬКДАТЕ(ПроходыСигур.Период, СЕКУНДА, РАЗНОСТЬДАТ(ДАТАВРЕМЯ(1, 1, 1), ПроходыСигур.Время, СЕКУНДА)) КАК Дата,
	ПроходыСигур.Сотрудник
ПОМЕСТИТЬ Дано
ИЗ
	ПроходыСигур КАК ПроходыСигур
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	МАКСИМУМ(Раньше.Дата) КАК От,
	Дано.Дата КАК До,
	Дано.Сотрудник
ПОМЕСТИТЬ Отсутствия
ИЗ
	Дано КАК Раньше
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ Дано КАК Дано
		ПО Раньше.Сотрудник = Дано.Сотрудник
			И Раньше.Дата < Дано.Дата

СГРУППИРОВАТЬ ПО
	Дано.Сотрудник,
	Дано.Дата

ИМЕЮЩИЕ
	РАЗНОСТЬДАТ(МАКСИМУМ(Раньше.Дата), Дано.Дата, ЧАС) > 6

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ РАЗЛИЧНЫЕ
	&ДатаНачала,
	&ДатаНачала,
	ПроходыСигур.Сотрудник
ИЗ
	ПроходыСигур КАК ПроходыСигур

ОБЪЕДИНИТЬ

ВЫБРАТЬ РАЗЛИЧНЫЕ
	&ДатаОкончания,
	&ДатаОкончания,
	ПроходыСигур.Сотрудник
ИЗ
	ПроходыСигур КАК ПроходыСигур
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	МАКСИМУМ(Раньше.До) КАК От,
	Отсутствия.От КАК До,
	Отсутствия.Сотрудник
ИЗ
	Отсутствия КАК Отсутствия
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ Отсутствия КАК Раньше
		ПО Отсутствия.Сотрудник = Раньше.Сотрудник И Раньше.До < Отсутствия.От  

СГРУППИРОВАТЬ ПО
	Отсутствия.От,
	Отсутствия.Сотрудник
Показать
Эта задача относится к классу "Islands and Gaps". В запросе сначала определяются все отсутствия сотрудника, а затем периоды между отсутствиями считаются периодами работы.
Отсутствиями считаются все периоды неактивности длиннее шести часов.
То есть предлагается вообще отказаться от анализа типов событий: вход, выход, так как непонятно внешние ли это двери и всегда ли срабатывает регистрация. Любое событие считается активностью. Время соединяется с датой. Это важно для круглосуточности.

Если все же двери строго внешние и жалко терять информацию о типе события, то считаю, что "математически" правильно будет разделять последовательные вход-вход или выход-выход противоположным событием, расположенным строго посередине интервала равновероятности времени. В среднем это будет сводить к минимуму ошибку домысливания пропущенного при регистрации события.
Aleskey_K; 🅵🅾️🆇; sm.artem; +3 –
14. 🅵🅾️🆇 523 23.11.17 10:40 Сейчас в теме
(12)
(13)
(11)

Спасибо за подсказки, завтра подробнее почитаю.
+ –
15. 🅵🅾️🆇 523 24.11.17 11:47 Сейчас в теме
(12)
Большое спасибо, думаю никто не будет возражать, что это больше всего походит на решение. Есть несколько недочетов-особенностей, попробую сам с ними разобраться.
+ –
13. user596430_gleb21 3 22.11.17 15:33 Сейчас в теме
Если я правильно понял, то нужно посчитать часы на работе.
Вт - таблица с часами. Соединяется с собой же.
На выходе часы на работе . Надо 1 прибавить . 

ВЫБРАТЬ
	Вт.Час КАК Час,
	МИНИМУМ(Вт1.Час) КАК Час1,
	ВЫБОР
		КОГДА МИНИМУМ(Вт1.Час) - Вт.Час < 6
			ТОГДА МИНИМУМ(Вт1.Час) - Вт.Час
		ИНАЧЕ 0
	КОНЕЦ КАК Работал
ИЗ
	Вт КАК Вт
		ЛЕВОЕ СОЕДИНЕНИЕ Вт КАК Вт1
		ПО Вт.Час < Вт1.Час

СГРУППИРОВАТЬ ПО
	Вт.Час
Показать
🅵🅾️🆇; +1 –
Внимание! Тема сдана в архив

Для получения уведомлений об ответах подключите телеграм бот:
Инфостарт бот