SQL-запрос, чтобы превратить время входа / выхода контрольного журнала в список местоположений
У меня есть база данных, которая используется для отслеживания местоположения физических объектов, позволяет называть их виджетами. У него есть таблица контрольного журнала, которая отслеживает, когда виджет помещается в какое-то место и когда он покидает какое-либо место (и куда он отправился после этого).
Так что концептуально это выглядит так
Widget ID Date Old Location New Location
1 01-Oct-2013 NULL 101
1 03-Oct-2013 101 108
1 08-Oct-2013 108 101
2 01-oct-2013 NULL 101
2 02-Oct-2013 101 103
3 12-oct-2013 NULL 101
Я хочу иметь возможность запросить список, какие виджеты были в местоположении 101 между начальной и конечной датой, например, 08-09 октября 2013 года, это должен быть виджет 1, а не виджет 2 или 3.
Я не уверен, как получить все эти дела. Я могу получить список виджетов, которые были перемещены до конца, и список виджетов, которые были перемещены до начала, но это также устранит виджет 1, когда он уходит и возвращается.
Я думаю, что мне нужно преобразовать это в таблицу с виджетом, местоположением, датой входа и датой выхода, но я не уверен, как это сделать?
РЕДАКТИРОВАТЬ: Как указано, мои данные были неверны, я обновил, чтобы сделать вопрос с 8 по 9 (это было с 4 по 5). Таким образом, виджет 1 является единственным виджетом в местоположении 101 в этот период.
2 ответа
Попробуйте что-то вроде этого:
select *
from
(select "Widget ID" id,
"New Location" loc,
"Date" start_date,
lead("Date", 1, sysdate) over (partition by "Widget ID" order by "Widget ID") end_date
from widgets) t
where t.loc = 101
and start_date < <<your_ending_date>> and end_date > <<your_starting_date>>
Вот демо sqlfiddle (обратите внимание, что я немного изменил ваши данные)
Таким образом, вам нужно последнее состояние каждого виджета в течение периода.
Вероятно, потребуется оператор подвыбора, который выбирает все виджеты между датами, группирует их по идентификатору, упорядочивает по датам, выбирает первые 1, чтобы вы знали последнее состояние виджета за период.
ОБНОВЛЕНИЕ в соответствии с новыми условиями
Я хочу знать, был ли виджет в этом месте в любое время в течение периода
Вы делаете выбор с разными идентификаторами и подвыбор с EXISTS, который проверяет, присутствует ли в наборе результатов строка с текущим идентификатором и датой в периоде и новым местоположением = X. Это позволит вам узнать, какие предметы поступили в магазин хотя бы 1 раз.