Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 возможность запроса для сложного случая  [new]
alex1010
Member

Откуда:
Сообщений: 19
Подскажите, сложных запросов никогда не составлял,
возможно ли вообще в данной ситуации обойтись одним sql-запросом?

Есть БД описывающая охранную систему. Пропускные карточки, несколько комнат (зон) с дверями, сенсоры по карточкам на вход и выход.
Каждое прикладывание карточки к сенсору отмечается event`ом в базе.

Структура БД -

Таблицы:
Person - id, name, cardNumber
Sensors - id, description
Events - id, SensorId (ключ к Sensors), PersonId (ключ к Person), Time
Zone - id, Name
SensorsZone - SensorId(ключ к Sensors), ToZoneId(ключ к Zone), FromZoneId(ключ к Zone)

Можно ли составить один sql-запрос, показывающий, кто в какой комнате (zone) находится?

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

Если ответ явно "нет", надо будет прогу писать, которая сама будет из вытянутых данных это анализировать...
27 сен 16, 14:31    [19713652]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
alex1010,

можно, приступайте
27 сен 16, 14:32    [19713665]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
aleks2
Guest
alex1010
Можно ли составить один sql-запрос, показывающий, кто в какой комнате (zone) находится?

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

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


Чегож тут сложного?
Все входы "без выхода".
И фсе.
27 сен 16, 15:09    [19713875]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
buven
Member

Откуда:
Сообщений: 792
alex1010,
Постановка должна быть точнее.
Есть понятие проходной комнаты? Фойе или коридор обозначены в Zone? Нужно ли фиксировать, что человек сейчас в корридоре или интересуют только конечные комнаты?
Самое сложное у вас будет - построить дерево маршрутов и определить комнаты, у которых вход=выход.

Приведите пример заполнения таблиц и результат, который вы хотите получить. Будет проще помочь.
27 сен 16, 15:10    [19713879]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
aleks2
Guest
Или еще проще
Последнее событие для каждой карточки, которое "не выход".
27 сен 16, 15:15    [19713910]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
buven
Member

Откуда:
Сообщений: 792
buven,
Хотя о чем это я...

aleks2
Чегож тут сложного?
Все входы "без выхода".
И фсе.

Тут даже понятия выхода не нужно. Оно же будет с максимальным временем в Event. Причем всегда знаем куда он вышел по ToZoneId.
У автора видимо проблема с join, goup by и having. Может для красоты вывода еще придется CTE покурить.

alex1010, ключевые слова я указал. Гугл в руки и ждем публикации вашей первой попытки.
27 сен 16, 15:20    [19713949]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
SFlash
Member

Откуда:
Сообщений: 143
alex1010
Можно ли составить один sql-запрос, показывающий, кто в какой комнате (zone) находится?

Это заранее провальный отчет. На моей памяти и безопасники и кадры несколько раз такие запрашивали, но достоверность 50/50, или нужный товарищ в комнате или нет. Народ часто ходит группами. Пошла группа товарищей на перекур совещание в совещательную комнату, на выход карточку приложил один, а при возвращении другой.
27 сен 16, 15:35    [19714074]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
buven
Member

Откуда:
Сообщений: 792
SFlash,
+1. Как то краем уха слышал, как нач охраны отчитывал дежурного, за то что у него, согласно данным системы, переночевало в офисе 12 человек :)
27 сен 16, 15:39    [19714101]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
автор
Это заранее провальный отчет

это не проблема разработчика отчёта
27 сен 16, 15:48    [19714154]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
кролик-зануда
Guest
TaPaK
это не проблема разработчика отчёта

только если сферического разработчика в вакууме.
в моей практике доказательство того что функционал работает корректно, а некорректны сами данные, ложится на разработчика.
27 сен 16, 15:51    [19714179]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
buven
Member

Откуда:
Сообщений: 792
TaPaK
автор
Это заранее провальный отчет

это не проблема разработчика отчёта

Проблема разработчика не в самом отчете, а в том что тут нужно убедить заказчика в его бесполезности.
Хотя для тренировки ТС - весьма годная задача. Почти все грабли собираешь.
27 сен 16, 15:55    [19714211]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
buven
TaPaK
пропущено...

это не проблема разработчика отчёта

Проблема разработчика не в самом отчете, а в том что тут нужно убедить заказчика в его бесполезности.
Хотя для тренировки ТС - весьма годная задача. Почти все грабли собираешь.


пчёлы против мёда... заказчик платит, ты его убеждаешь не платить... получив отчёт он быстрее приведёт в порядок охрану или то отчего зависит отчёт
27 сен 16, 15:58    [19714240]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
buven
Member

Откуда:
Сообщений: 792
TaPaK
пчёлы против мёда... заказчик платит, ты его убеждаешь не платить...

Не примите на личный счет, подход очень похож на "эффективное" менеджерство.
Дали бюджет, сказали пилить кнопку. Мы взяли и пилим, не посмотрев на рынок и не поняв, что за 1\10 бюджета можно купить кнопку со всеми потрохами. А проанализировав процесс можно было вообще обойтись без затрат, изменив регламент.
ИМХО, специалист, который может аргументированно доказать неадекватность задачи инструменту или бюджету, причем и в ту и в другую сторону, стоит немного дороже, чем "дали мяч - фигач" и он фигачит.
27 сен 16, 16:12    [19714347]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
buven
TaPaK
пчёлы против мёда... заказчик платит, ты его убеждаешь не платить...

Не примите на личный счет, подход очень похож на "эффективное" менеджерство.
Дали бюджет, сказали пилить кнопку. Мы взяли и пилим, не посмотрев на рынок и не поняв, что за 1\10 бюджета можно купить кнопку со всеми потрохами. А проанализировав процесс можно было вообще обойтись без затрат, изменив регламент.
ИМХО, специалист, который может аргументированно доказать неадекватность задачи инструменту или бюджету, причем и в ту и в другую сторону, стоит немного дороже, чем "дали мяч - фигач" и он фигачит.

достаточно странно разработчику обсуждать нужна ли задача клиенту, не находите?
27 сен 16, 16:15    [19714368]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
buven
Member

Откуда:
Сообщений: 792
TaPaK
достаточно странно разработчику обсуждать нужна ли задача клиенту, не находите?


Даже совсем наоборот.
Но это уже оффтоп. Понятно, что подходы обсуждаем разные, но цели переманить собеседника на свою сторону ни у кого нет:)
27 сен 16, 16:33    [19714482]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
alex1010
Member

Откуда:
Сообщений: 19
buven
alex1010,
Постановка должна быть точнее.
Есть понятие проходной комнаты? .


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

В принципе, да - самое очевидное, брать последний event.
28 сен 16, 11:49    [19717412]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
alex1010
Member

Откуда:
Сообщений: 19
buven
Тут даже понятия выхода не нужно. Оно же будет с максимальным временем в Event. Причем всегда знаем куда он вышел по ToZoneId.
У автора видимо проблема с join, goup by и having. .


да пока со всем проблемы )

SELECT      
			Events1.PersonID AS Person
			, MAX( Events1.Time ) AS EventTime
			, Zones.Name AS ZoneName

FROM        Events AS Events1 
			INNER JOIN Person ON Events1.PersonID = Person.ID 
			INNER JOIN Sensors  ON Events1.SensorID = Sensors.ID 
			INNER JOIN SensorsZone ON Sensors.ID = SensorsZone.SensorID 
			INNER JOIN Zones ON SensorsZone.ToZoneID = Zones.ID
WHERE	(Events1.Time BETWEEN '09/19/2016' AND '09/20/2016')  -- для отладки
GROUP BY Events1.PersonID, Zones.Name


ну вот это компилится и выполняется по-крайней мере. Только результат не тот.
хотя указано выбрать MAX time, но PersonId встречается больше одного раза. Вероятно из-за Zones.Name в GROUP BY, но без него не компилится.
Как тут HAVING прикрутить, непонятно, нам не нужно дополнительное ограничение по времени, просто максимальное и все. В примерах вроде этого -
http://2sql.ru/basic/sql-having/
простые условия.
28 сен 16, 12:06    [19717489]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
buven
Member

Откуда:
Сообщений: 792
alex1010,
Вы просто сделали выборку со столбцом, где выведен максимальное время в разрезе. А вам нужно оставить только те строки, у которых это время максимально.
Для гугла поиск вот такой "MS sql выбор записи с максимальным значением поля"
Там найдете много вариаций под разные версии сервера.
Для наглядности приведу свой пример через CTE.
create table #Table  (ID int,Name varchar(10),DT datetime)
insert into #table
select 1,'AAA','2016-09-28 12:21:46.907'
union all
select 2,'AAA','2016-09-28 13:21:46.907'
union all
select 3,'BBB','2016-09-28 12:21:46.907'
union all
select 4,'BBB','2016-09-28 13:21:46.907'

-- ваш вариант: Выводим ID т.к. без него дальше join не получится, но и его же нужно указать в группировке,
-- но к сожалению поставленной задачи скрипт не решает. MS SQL делает именно то что мы ему сказали. 
-- просто выводит столбец с максимальным временем для ID (указали в GROUP BY). 
-- Оно, как не удивительно, равно исходному DT, т.к. ID - уникальный
go 
select ID,NAME,max(DT) from #Table group by ID,Name
-- как получить то что нужно через CTE
GO
with CTE (NAME,DT) as --выбрали максимальное время для каждого NAME
	(
	select NAME,max(DT) from #Table group by NAME
	)

select t.* from #Table t join CTE t1 on t.Name=t1.NAME and t.DT=t1.DT --получили только те ID,NAME у которых DT - максимальное

drop table #Table
28 сен 16, 12:44    [19717658]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
alex1010
Member

Откуда:
Сообщений: 19
buven
alex1010,
Вы просто сделали выборку со столбцом, где выведен максимальное время в разрезе. А вам нужно оставить только те строки, у которых это время максимально.
Для гугла поиск вот такой "MS sql выбор записи с максимальным значением поля"


честно сказать не понял, что такое "в разрезе".

о какое решение нашлось в гугле, в одну строку, WHERE меняем на -
WHERE Events1.Time IN (SELECT MAX(Events.Time) FROM Events GROUP BY Events.PersonID )

использование WITH CTE - надо почитать что это )

и непонятно почему не компилится ORDER BY Events1.Time если это добавить сюда,
таблица-то итоговая уже создана, почему бы ее теперь не отсортировать по колонке времени?
28 сен 16, 14:22    [19718332]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
aleks2
Guest
alex1010
buven
alex1010,
Вы просто сделали выборку со столбцом, где выведен максимальное время в разрезе. А вам нужно оставить только те строки, у которых это время максимально.
Для гугла поиск вот такой "MS sql выбор записи с максимальным значением поля"


честно сказать не понял, что такое "в разрезе".

о какое решение нашлось в гугле, в одну строку, WHERE меняем на -
WHERE Events1.Time IN (SELECT MAX(Events.Time) FROM Events GROUP BY Events.PersonID )

использование WITH CTE - надо почитать что это )

и непонятно почему не компилится ORDER BY Events1.Time если это добавить сюда,
таблица-то итоговая уже создана, почему бы ее теперь не отсортировать по колонке времени?



Осподе, откуда такое пристрастие к группировке?
Серверу заняться нечем?

SELECT  *    
  FROM  Events as e
  WHERE not exists( select * from Events as e1 where e1.PersonID = e.PersonID and e1.Time > e.Time )
28 сен 16, 15:14    [19718694]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
buven
Member

Откуда:
Сообщений: 792
aleks2
Осподе, откуда такое пристрастие к группировке?
Серверу заняться нечем?


В начале пути оно как то понятнее читается и, соответственно, пишется:)
28 сен 16, 16:09    [19719122]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
alex1010
Member

Откуда:
Сообщений: 19
Еще один вопрос, эх, я даже не знаю пока как его сформулировать для поисковый системы.

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

Даже не учитывая когда вход есть, а выхода нет (для сотрудников у которых рабочее время скажем в ночную смену, т.е переходит на другой день), возникает вопрос - а как проводить вычитание из одной найденной строки - другой (по столбцу времени конечно)?
Ну или шире - когда мы работаем с БД, то выбрав какой-то кусок данных/набор строк (скажем ограничив по времени - за месяц), как обрабатывать не всю выборку, а только отдельные строки, причем сложным порядоком - когда из строки с таким-то атрибутом (выход), вычитается строка с другим (вход)?
4 окт 16, 16:50    [19743002]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
правильный проходящий.
Guest
alex1010
вопрос - а как проводить вычитание из одной найденной строки - другой
А не надо их вычитать. Надо их правильно друг с другом сджойнить. Дальше разъяснять?
4 окт 16, 17:00    [19743069]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8839
alex1010,

для этого Вы должны пометить строки требуемым образом при добавлении в таблицу.
Таблицы - это математические множества, соответственно, SQL алгебра позволяет выполнять операции над этими множествами.
4 окт 16, 17:03    [19743081]     Ответить | Цитировать Сообщить модератору
 Re: возможность запроса для сложного случая  [new]
alex1010
Member

Откуда:
Сообщений: 19
правильный проходящий.
alex1010
вопрос - а как проводить вычитание из одной найденной строки - другой
А не надо их вычитать. Надо их правильно друг с другом сджойнить. Дальше разъяснять?


возможно я коряво объяснил,
надо-то получать разницу во времени (по полю Time) между одним ивентом (т.е одной строкой в БД) и другим. Тут JOIN не представляю как может помочь - это же объединение разных таблиц.
5 окт 16, 11:26    [19745278]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить