Здесь я распишу тезисно, как можно работать с этими данными через СУБД sqlite3.
Инструкция будет в виде практического примера поиска расписания по реальному номеру маршрута, инструкцию вы при желании сможете адаптировать под другие задачи поиска.
Про автоматизацию будет сказано ниже.
Вам понадобятся:
DB Browser отсюда: https://sqlitebrowser.org/dl/
Расширение text.dll отсюда: https://github.com/nalgeon/sqlean/releases/tag/0.21.10 (документация: https://antonz.ru/sqlean-text/ )
Этап 1: подготовительный
- загружаем данные csv на ПК по ссылкам Ильи
- если нет нужных данных csv, их можно сконвертировать из xlsx в csv с помощью сервиса: https://convertio.co/ru/xlsx-csv/
В DB Browser:
- создать новую БД
- загрузить csv в БД (файл - импорт), "имена столбцов в первой строке" - ДА, и изменить разделитель на ";" (для csv с сайта data.mos.ru)
для нашего примера понадобится загрузить data-60661-2024-02-08.csv и data-60664-2024-03-01.csv
- загружаем расширение text.dll в DB Browser (инструменты - загрузить расширение - text.dll)
В базе данных сцеплены id маршрута, id расписания, номер рейса и номер выхода в одну строку. Для удобства поиска их надо расцепить, для этого мы создадим новые столбцы:
alter table "data-60661-2024-02-08"
add column route_id text as (
case when trip_id like '%_%' then
text_split(trip_id, "_", 1)
else trip_id
end
);
Этот sql-запрос нужно выполнять во вкладке SQL. После этого во вкладке "данные" для таблицы data-60661-2024-02-08 появится дополнительный столбец route_id. Это виртуальный столбец, который генерируется "на лету", используя данные столбца trip_id. Размер БД от этой операции не увеличивается. Функция text_split(str, sep, n) бьет строку по разделителю и возвращает n-ю часть.
Ещё замечание - имена таблиц содержат символ "-", поэтому в запросах имена приходится обрамлять кавычками. Если переименовать имена без "-", двойные кавычки можно не писать.
Аналогично можно вычленить и добавить столбцы service_id, num_trip, vyhod.
Для ускорения по полю route_id можно создать индекс, но можно и не создавать, если вы не знаете, что это такое и как это делать.
Базу данных можно сохранить на диск, чтобы не проделывать эти шаги каждый раз.
Этап 2: улучшаем человекочитаемость данных
В таблице data-60661-2024-02-08 маршруты заданы через id, исправим это. Объединим таблицы data-60661-2024-02-08 и data-60664-2024-03-01 следующим запросом:
select *
from "data-60661-2024-02-08"
join "data-60664-2024-03-01" on "data-60661-2024-02-08".route_id = "data-60664-2024-03-01".route_id
или скопируем результат этого запроса в отдельную таблицу, синтаксис: CREATE TABLE name_newtable AS SELECT ...
create table copied AS
select *
from "data-60661-2024-02-08"
join "data-60664-2024-03-01" on "data-60661-2024-02-08".route_id = "data-60664-2024-03-01".route_id
where "data-60664-2024-03-01".route_short_name = "с962"
(в примере строкой where мы дополнительно ограничили выборку маршрутом "c962")
Для дальнейшего поиска в DB Browser`e результат лучше скопировать в отдельную таблицу.
Этот метод добавляеь столбец route_short_name, по которому можно дополнительно фильтровать данные, см. далее.
Этап 3. поиск информации и просмотр информации в DB Browser`e
DB Browser имеет такую удобную фичу, как фильтры. Фильтры поддерживают регулярные выражения.
С помощью фильтров можно быстро достать только нужные данные и отфильтровать ненужные и результат тут же отобразится в браузере DB.
Для примера, я в таблице data-60661-2024-02-08 поставил два фильтра:
на stop_sequence установил "=1" (два символа без кавычек) - начальная остановка
на trip_id: /^107_.*702$/
символы ^,$ - начало и конец строки
комбинация .* - любая последовательность посередине, которая игнорируется
после id я поставил _, чтобы не захватить 1071, 1072 и т.д.
107 - это id маршрута 27, результат - DB Browser отобразил 7 рейсов выхода 702 маршрута 27:
Результат на скриншоте:
Это очень гибкая и наглядная система. Для повышения читаемости колонки в DB Browser можно сортировать.
После того, как вы объедините таблицы на этапе 2, фильтровать данные станет ещё проще. Необязательно использовать такие сложные регулярки.
Но я бы не рекомендовал объединять всё, так как такая таблица будет занимать несколько десятков гигабайт и работать очень медленно.
Строго говоря, это неправильный метод работы, а правильный и профессиональный - через написание запросов select с условиями where и сортировкой order by. Но он требует навыков.
Этап 4, автоматизация.
К сожалению, этот этап объёмный и его описание нельзя вместить в один пост, и у меня сейчас нет времени сделать такую автоматизацию самостоятельно. Но если коротко, это проще всего сделать на python или на perl.
Нужно подготовить базу данных (этапы 1,2) и дополнительные колонки с помощью DB Browser, (дополнительные таблицы не создавать), сохранить, затем написать скрипт на питоне, который:
- подключится к этой подготовленной БД
- сделает нужные выборки select
- выведет в файл / на экран информацию в виде простой html страницы
- закроет соединение
(в сети есть достаточно подробные учебные руководства по подобной тематике на русском языке).
sqlite поддерживает агрегирующие функции, поэтому просто будет посчитать кол-во рейсов, среднее время работы, продолжительность обедов, максимальное число остановок и прочую любознательную статистику.
Здесь я распишу тезисно, как можно работать с этими данными через СУБД sqlite3. Инструкция будет в виде практического примера поиска расписания по реальному номеру маршрута, инструкцию вы при желании сможете адаптировать под другие задачи поиска. Про автоматизацию будет сказано ниже. Вам понадобятся: DB Browser отсюда: https://sqlitebrowser.org/dl/ Расширение text.dll отсюда: https://github.com/nalgeon/sqlean/releases/tag/0.21.10 (документация: https://antonz.ru/sqlean-text/ ) Этап 1: подготовительный - загружаем данные csv на ПК по ссылкам Ильи - если нет нужных данных csv, их можно сконвертировать из xlsx в csv с помощью сервиса: https://convertio.co/ru/xlsx-csv/ В DB Browser: - создать новую БД - загрузить csv в БД (файл - импорт), "имена столбцов в первой строке" - ДА, и изменить разделитель на ";" (для csv с сайта data.mos.ru) для нашего примера понадобится загрузить data-60661-2024-02-08.csv и data-60664-2024-03-01.csv - загружаем расширение text.dll в DB Browser (инструменты - загрузить расширение - text.dll) В базе данных сцеплены id маршрута, id расписания, номер рейса и номер выхода в одну строку. Для удобства поиска их надо расцепить, для этого мы создадим новые столбцы: alter table "data-60661-2024-02-08" add column route_id text as ( case when trip_id like '%_%' then text_split(trip_id, "_", 1) else trip_id end ); Этот sql-запрос нужно выполнять во вкладке SQL. После этого во вкладке "данные" для таблицы data-60661-2024-02-08 появится дополнительный столбец route_id. Это виртуальный столбец, который генерируется "на лету", используя данные столбца trip_id. Размер БД от этой операции не увеличивается. Функция text_split(str, sep, n) бьет строку по разделителю и возвращает n-ю часть. Ещё замечание - имена таблиц содержат символ "-", поэтому в запросах имена приходится обрамлять кавычками. Если переименовать имена без "-", двойные кавычки можно не писать. Аналогично можно вычленить и добавить столбцы service_id, num_trip, vyhod. Для ускорения по полю route_id можно создать индекс, но можно и не создавать, если вы не знаете, что это такое и как это делать. Базу данных можно сохранить на диск, чтобы не проделывать эти шаги каждый раз. Этап 2: улучшаем человекочитаемость данных В таблице data-60661-2024-02-08 маршруты заданы через id, исправим это. Объединим таблицы data-60661-2024-02-08 и data-60664-2024-03-01 следующим запросом: select * from "data-60661-2024-02-08" join "data-60664-2024-03-01" on "data-60661-2024-02-08".route_id = "data-60664-2024-03-01".route_id или скопируем результат этого запроса в отдельную таблицу, синтаксис: CREATE TABLE name_newtable AS SELECT ... create table copied AS select * from "data-60661-2024-02-08" join "data-60664-2024-03-01" on "data-60661-2024-02-08".route_id = "data-60664-2024-03-01".route_id where "data-60664-2024-03-01".route_short_name = "с962" (в примере строкой where мы дополнительно ограничили выборку маршрутом "c962") Для дальнейшего поиска в DB Browser`e результат лучше скопировать в отдельную таблицу. Этот метод добавляеь столбец route_short_name, по которому можно дополнительно фильтровать данные, см. далее. Этап 3. поиск информации и просмотр информации в DB Browser`e DB Browser имеет такую удобную фичу, как фильтры. Фильтры поддерживают регулярные выражения. С помощью фильтров можно быстро достать только нужные данные и отфильтровать ненужные и результат тут же отобразится в браузере DB. Для примера, я в таблице data-60661-2024-02-08 поставил два фильтра: на stop_sequence установил "=1" (два символа без кавычек) - начальная остановка на trip_id: /^107_.*702$/ символы ^,$ - начало и конец строки комбинация .* - любая последовательность посередине, которая игнорируется после id я поставил _, чтобы не захватить 1071, 1072 и т.д. 107 - это id маршрута 27, результат - DB Browser отобразил 7 рейсов выхода 702 маршрута 27: Результат на скриншоте: Это очень гибкая и наглядная система. Для повышения читаемости колонки в DB Browser можно сортировать. После того, как вы объедините таблицы на этапе 2, фильтровать данные станет ещё проще. Необязательно использовать такие сложные регулярки. Но я бы не рекомендовал объединять всё, так как такая таблица будет занимать несколько десятков гигабайт и работать очень медленно. Строго говоря, это неправильный метод работы, а правильный и профессиональный - через написание запросов select с условиями where и сортировкой order by. Но он требует навыков. Этап 4, автоматизация. К сожалению, этот этап объёмный и его описание нельзя вместить в один пост, и у меня сейчас нет времени сделать такую автоматизацию самостоятельно. Но если коротко, это проще всего сделать на python или на perl. Нужно подготовить базу данных (этапы 1,2) и дополнительные колонки с помощью DB Browser, (дополнительные таблицы не создавать), сохранить, затем написать скрипт на питоне, который: - подключится к этой подготовленной БД - сделает нужные выборки select - выведет в файл / на экран информацию в виде простой html страницы - закроет соединение (в сети есть достаточно подробные учебные руководства по подобной тематике на русском языке). sqlite поддерживает агрегирующие функции, поэтому просто будет посчитать кол-во рейсов, среднее время работы, продолжительность обедов, максимальное число остановок и прочую любознательную статистику.
