Задний план
Интернет транспортных средств (IoV) — одна из самых горячих тем исследований в области Интернета вещей (IoT). Типичным сценарием для приложений Интернета транспортных средств является сбор данных о маршрутах движения транспортных средств, однако информация о маршрутах транспортных средств обычно не передается в режиме реального времени. Несколько послужных списков могут накапливаться или сообщаться через определенные промежутки времени.
Типичная структура данных выглядит следующим образом:
(car_id, pos geometry, crt_time timestamp)Интенсивный трафик и светофоры часто возникают во время движения транспортного средства. Сообщенные послужной список могут быть следующими:
1, position 1, '2017-01-01 12:00:00'
1, position 1, '2017-01-01 12:00:05 PM'
1, position 1, '2017-01-01 12:00:10 PM'
1, position 1, '2017-01-01 12:00:15 PM'
1, position 1, '2017-01-01 12:00:20 PM'
1, position 2, '2017-01-01 12:00:30 PM'То есть несколько записей в одной и той же позиции могут быть загружены из-за интенсивного трафика или сигналов светофора.
Поэтому возникает требование очистить базу данных от ненужных записей ожидания. Мы храним не более двух записей для данной точки с указанием прихода и отправления.
Эту операцию можно выполнить через оконную функцию.
Конечно, с точки зрения обеспечения наилучшей эффективности разумнее чистить дорожки от терминала. Только две записи будут сохранены для начальной точки позиции.
Пример реализации
1. Создайте структуру таблицы
create table car_trace (cid int, pos point, crt_time timestamp);2. Создайте 10 миллионов тестовых данных и предположите, что имеется 1000 автомобилей (чтобы упростить повторение данных и проверку эффекта, в позиции используются 25 точек).
insert into car_trace select random()*999, point((random()*5)::int, (random()*5)::int), clock_timestamp() from generate_series(1,10000000);3.Создайте индекс
create index idx_car on car_trace (cid, crt_time);4. Запрос макета данных
select * from car_trace where cid=1 order by crt_time limit 1000;1 | (3,1) | 2017-07-22 21:30:09.84984 1 | (1,4) | 2017-07-22 21:30:09.850297 1 | (1,4) | 2017-07-22 21:30:09.852586 1 | (1,4) | 2017-07-22 21:30:09.854155 1 | (1,4) | 2017-07-22 21:30:09.854425 1 | (3,1) | 2017-07-22 21:30:09.854493
Как показано в списке, некоторые части данных повторяются.
5. Фильтровать записи в одной позиции через окно. Будет вестись не более двух записей о приходе и уходе с позиции.
Здесь используются две оконные функции: опережение и запаздывание. Отставание указывает на предыдущую запись текущей записи, а Опережение указывает на следующую запись текущей записи.
Способ определения пунктов прибытия и отправления следующий:
• Текущая позиция не равна предыдущей позиции, что указывает на то, что запись является точкой прибытия текущей позиции.
• Текущая позиция не равна следующей позиции, что указывает на то, что запись является конечной точкой текущей позиции.
• Предыдущая позиция пуста, что указывает на то, что текущая запись является первой записью.
• Следующая позиция пуста, что указывает на то, что текущая запись является последней записью.
select * from
(
select
*,
lag(pos) over (partition by cid order by crt_time) as lag,
lead(pos) over (partition by cid order by crt_time) as lead
from car_trace
where cid=1
and crt_time between '2017-07-22 21:30:09.83994' and '2017-07-22 21:30:09.859735'
) t
where pos <> lag
or pos <> lead
or lag is null
or lead is null;
cid | pos | crt_time | lag | lead
-----+-------+----------------------------+-------+-------
1 | (2,1) | 2017-07-22 21:30:09.83994 | | (3,1)
1 | (3,1) | 2017-07-22 21:30:09.839953 | (2,1) | (5,2)
1 | (5,2) | 2017-07-22 21:30:09.840704 | (3,1) | (4,4)
1 | (4,4) | 2017-07-22 21:30:09.84179 | (5,2) | (5,2)
1 | (5,2) | 2017-07-22 21:30:09.843787 | (4,4) | (1,5)
1 | (1,5) | 2017-07-22 21:30:09.844165 | (5,2) | (0,5)
1 | (0,5) | 2017-07-22 21:30:09.84536 | (1,5) | (4,1)
1 | (4,1) | 2017-07-22 21:30:09.845896 | (0,5) | (3,3)
1 | (3,3) | 2017-07-22 21:30:09.846958 | (4,1) | (3,1)
1 | (3,1) | 2017-07-22 21:30:09.84984 | (3,3) | (1,4)
1 | (1,4) | 2017-07-22 21:30:09.850297 | (3,1) | (1,4)
1 | (1,4) | 2017-07-22 21:30:09.854425 | (1,4) | (3,1)
1 | (3,1) | 2017-07-22 21:30:09.854493 | (1,4) | (3,2)
1 | (3,2) | 2017-07-22 21:30:09.854541 | (3,1) | (2,0)
1 | (2,0) | 2017-07-22 21:30:09.855297 | (3,2) | (4,1)
1 | (4,1) | 2017-07-22 21:30:09.857592 | (2,0) | (4,1)
1 | (4,1) | 2017-07-22 21:30:09.857595 | (4,1) | (0,4)
1 | (0,4) | 2017-07-22 21:30:09.857597 | (4,1) | (3,1)
1 | (3,1) | 2017-07-22 21:30:09.858996 | (0,4) | (3,1)
1 | (3,1) | 2017-07-22 21:30:09.859735 | (3,1) |
(20 rows)Когда очистка гусениц не производится, полученные результаты следующие:
select
*,
lag(pos) over (partition by cid order by crt_time) as lag,
lead(pos) over (partition by cid order by crt_time) as lead
from car_trace
where cid=1
and crt_time between '2017-07-22 21:30:09.83994' and '2017-07-22 21:30:09.859735';
cid | pos | crt_time | lag | lead
-----+-------+----------------------------+-------+-------
1 | (2,1) | 2017-07-22 21:30:09.83994 | | (3,1)
1 | (3,1) | 2017-07-22 21:30:09.839953 | (2,1) | (5,2)
1 | (5,2) | 2017-07-22 21:30:09.840704 | (3,1) | (4,4)
1 | (4,4) | 2017-07-22 21:30:09.84179 | (5,2) | (5,2)
1 | (5,2) | 2017-07-22 21:30:09.843787 | (4,4) | (1,5)
1 | (1,5) | 2017-07-22 21:30:09.844165 | (5,2) | (0,5)
1 | (0,5) | 2017-07-22 21:30:09.84536 | (1,5) | (4,1)
1 | (4,1) | 2017-07-22 21:30:09.845896 | (0,5) | (3,3)
1 | (3,3) | 2017-07-22 21:30:09.846958 | (4,1) | (3,1)
1 | (3,1) | 2017-07-22 21:30:09.84984 | (3,3) | (1,4)
1 | (1,4) | 2017-07-22 21:30:09.850297 | (3,1) | (1,4)
1 | (1,4) | 2017-07-22 21:30:09.852586 | (1,4) | (1,4)
1 | (1,4) | 2017-07-22 21:30:09.854155 | (1,4) | (1,4)
1 | (1,4) | 2017-07-22 21:30:09.854425 | (1,4) | (3,1)
1 | (3,1) | 2017-07-22 21:30:09.854493 | (1,4) | (3,2)
1 | (3,2) | 2017-07-22 21:30:09.854541 | (3,1) | (2,0)
1 | (2,0) | 2017-07-22 21:30:09.855297 | (3,2) | (4,1)
1 | (4,1) | 2017-07-22 21:30:09.857592 | (2,0) | (4,1)
1 | (4,1) | 2017-07-22 21:30:09.857595 | (4,1) | (0,4)
1 | (0,4) | 2017-07-22 21:30:09.857597 | (4,1) | (3,1)
1 | (3,1) | 2017-07-22 21:30:09.858996 | (0,4) | (3,1)
1 | (3,1) | 2017-07-22 21:30:09.859735 | (3,1) |
(22 rows)Запаздывание и опережение используются для очистки записей в процессе пребывания.
Оптимизация базы данных для IoV
В типичном сценарии в бизнесе задействовано множество идентификаторов транспортных средств. Данные, собранные для различных транспортных средств, обычно записываются в базу данных. Если оптимизация не произведена, после ввода в базу данных данные по разным автомобилям могут быть смещены. То есть данные разных транспортных средств могут храниться в одном блоке данных.
При запросе трека одного транспортного средства будет сканироваться множество блоков данных (сканирование усиления ввода-вывода). Существует два метода оптимизации для ускорения процесса запросов.
1. Запишите в базу данных после того, как бизнес-конец соберет группировку и сортировку.
Например, после получения данных от автомобильного терминала программа группирует идентификаторы автомобилей, сортирует их по времени и записывает в базу данных (с помощью вставки в таблицу значений (),(),…();). Таким образом, данные об одном и том же транспортном средстве будут максимально возможно попадать в один и тот же блок данных.
2. Используйте раздел для реорганизации данных в базе данных.
Например, мы можем хранить данные на основе идентификатора транспортного средства, каждого транспортного средства или раздела HASH транспортного средства.
Оба метода относятся к реорганизации данных на основе требований запроса для достижения цели уменьшения операций ввода-вывода при сканировании. Этот метод аналогичен Анализу требований к базе данных индустрии ценных бумаг PostgreSQL и применению (статья на китайском языке).
Ссылка:
