У меня есть две таблицы:
- Locations — хранит географическое местоположение пользователя и метку времени.
- События — хранит географические границы события, а также время начала и окончания рок-концерта.
Есть ли запрос, который будет получать время начала и окончания всех пользователей на мероприятиях?
Вот пример данных:
CREATE TABLE locations (
user_id INT NOT NULL,
timestamp DATETIME NOT NULL,
latitude FLOAT NOT NULL,
longitude FLOAT NOT NULL
);
INSERT INTO locations (user_id, timestamp, latitude, longitude)
VALUES
(1, '2013-11-22 01:12:23', 37.7674, -122.439),
(1, '2013-11-22 01:13:24', 37, -122),
(1, '2013-11-22 01:14:25', 37.7674, -122.439),
(2, '2013-11-25 01:12:23', 37.7674, -122.439),
(2, '2013-11-25 01:13:24', 37, -122),
(2, '2013-11-25 01:14:25', 37.7674, -122.439);
CREATE TABLE events (
event_id INT NOT NULL,
begin_time DATETIME NOT NULL,
end_time DATETIME NOT NULL,
min_latitude FLOAT NOT NULL,
max_latitude FLOAT NOT NULL,
min_longitude FLOAT NOT NULL,
max_longitude FLOAT NOT NULL
);
INSERT INTO events (event_id, begin_time, end_time, min_latitude, max_latitude, min_longitude, max_longitude)
VALUES
(1, '2013-11-22 01:00:00', '2013-11-22 02:00:00', 37.7673, 37.7675, -122.440, -122.439),
(2, '2013-11-25 01:00:00', '2013-11-25 02:00:00', 37.7674, 37.7674, -122.439, -122.439);
Проблема состоит из двух частей:
- Первая часть включает в себя поиск всех строк, в которых пользователи были на событиях.
- Вторая часть включает в себя поиск первой метки времени, в которой пользователь вошел в событие (затем игнорируются все последующие строки, в которых пользователь остается в событии), а затем получение строки, в которой пользователь покинул событие. Это было бы легко, если бы мы просто получили все строки, в которых пользователь находился внутри события.
Следующее даст мне все строки, в которых местоположение пользователя совпадает с событием.
select * from locations
join events on
locations.timestamp between events.begin_time and events.end_time and
locations.latitude between events.min_latitude and events.max_latitude and
locations.longitude between events.min_longitude and events.max_longitude
Однако я не могу найти хороший способ получить только время начала и окончания пользователя на мероприятии. Кроме того, (я не знаю, делает ли это проблему более интересной) пользователи могут уйти и вернуться к одному и тому же событию.
(Я использую MySQL, но приму ответы на любом варианте SQL.)
locations
по метке времени, еслиlatitude
,longitude
когда-либо находится в пределахevents
, а затем выходит за пределыevents
, и наоборот. Мы можем определить, что пользователь ушел и вернулся к событию. Спасибо. - person Glenn   schedule 26.11.2013