Из бизнеса обратно к коду: как 15 лет управления транспортом заставили меня вспомнить школу оператора ЭВМ и освоить SQL

История о том, можно ли после 15 лет владения транспортным бизнесом вернуться в ИТ и начать проектировать базы данных, которые видят то, что скрыто от глаз бухгалтера?

Вступление

И так возникает вопрос: Можно ли после 15 лет владения транспортным бизнесом вернуться в ИТ и начать проектировать базы данных, которые видят то, что скрыто от глаз бухгалтера?
Мой ответ: "ДА!" и вот почему база оператора ЭВМ заложенная в 90-х (Basic, Pascal), суровая логика пассажирских перевозок на протяжении 15 лет и мощь современного SQL.

Для меня SQL и Python — это логическое продолжение той базы (Basic, Pascal), которую мне заложили еще в 1995-м в лицее. Принципы алгоритмов не изменились, изменились только объемы данных и скорость их обработки. В ИТ принято считать, что аналитика — это удел вчерашних студентов с курсов. Но я смотрю на это иначе. Когда ты 15 лет управляешь транспортным предприятием, ты начинаешь интуитивно чувствовать данные или как говорят «подошвой сапога». Ты знаешь, что за каждой цифрой в отчете стоит реальный объем ГСМ, нервы водителя и амортизация железа. За эти годы я видел сотни «красивых» Excel-таблиц, которые не имели ничего общего с реальностью. Деньги утекали сквозь пальцы, потому что учет был в лучшем случае «на бумаге», а анализ — «на глаз». В какой-то момент я понял: чтобы реально управлять эффективностью, нужно перестать верить словам и начать верить коду, так как я инженер по образованию, я сел за изучение SQL. Мне нужен был инструмент, который не даст соврать ни диспетчеру, ни водителю, ни датчику уровня топлива.

Почему я выбрал именно пассажирские перевозки? Во-первых - это моя юношеская мечта, когда мне в лицее закладывали фундамент по Basics и Pascal, я смотрел в сторону как можно заработать. На тот момент да и сейчас это ликвидный бизнес, людям нужно ехать сегодня, завтра и прямо сейчас, проще говоря, живые деньги. В пассажирских перевозках цена ошибки выше, чем в грузовых. Здесь ты работаешь не с «коробками», а с людьми, где каждый рассчитывает своё время в пределах расписания движения автобуса, а также ответственность за безопасность и жесткими нормативами. Каждый лишний литр расхода на маршруте, помноженный на количество рейсов в месяц, превращается в дыру, в которую может провалиться вся маржинальность бизнеса. Когда я проектировал базу данных, я заложил в неё логику «неизбежности учета».

«Гаражная» логика: Почему СУБД — это тоже механизм?

Проектирование базы данных для транспорта — это как сборка двигателя. Если в коленвале (архитектуре) есть трещина, система развалится на первой же тысяче записей. В своем проекте на GitHub [https://github.com/NaZaR-TruE/transport-data-analytics] я пошел по пути, который продиктовала мне жизнь.

-- Файл проекта: Аналитика пассажирских перевозок. Логика «неизбежности учета» -- Создание таблицы автопарка CREATE TABLE vehicles (vehicle_id SERIAL PRIMARY KEY, model VARCHAR(50) NOT NULL, reg_number VARCHAR(15) UNIQUE NOT NULL, consumption_rate DECIMAL(4,2) -- расход топлива на 100 км ); -- Создание таблицы водителей CREATE TABLE drivers (driver_id SERIAL PRIMARY KEY, full_name VARCHAR(100) NOT NULL, license_category VARCHAR(10), hire_date DATE DEFAULT CURRENT_DATE ); -- Создание таблицы рейсов CREATE TABLE trips (trip_id SERIAL PRIMARY KEY, vehicle_id INTEGER REFERENCES vehicles(vehicle_id), driver_id INTEGER REFERENCES drivers(driver_id), trip_date DATE NOT NULL, distance_km DECIMAL(6,2), revenue DECIMAL(10,2), -- выручка за рейс/билеты/контракты fuel_cost DECIMAL(10,2) -- затраты на топливо );

Схема базы данных (Архитектура)
Бизнес-смысл: Создание цифрового двойника предприятия, где данные нельзя «подрисовать».
Справочники (vehicles, drivers): Это наше «железо» и ресурсы. Мы фиксируем емкость баков и нормы расхода как константы, чтобы водитель не мог сказать: «У меня бак стал больше» или «Норма выросла сама».
Таблица trips: Это фиксация факта. Если поездки нет в базе — её не было в реальности. Это фундамент «неизбежности учета».
Foreign Keys (Связи): Это персональная ответственность. Каждая заправка привязана к конкретной машине и конкретному человеку.

Детектор аномалий: Как SQL ловит то, что пропускает глаз
В логистике есть «серая зона», где цифры вроде бы сходятся, а прибыль падает. Это ошибки ввода, сбои датчиков или старое доброе воровство. Обычный бухгалтер видит итоговую сумму, а системный аналитик видит аномалию. В своем проекте я выделил уровни контроля, которые реализовал в файле 04_data_quality_and_logic.sql:

/* КОНТРОЛЬ КАЧЕСТВА ДАННЫХ И ВЫЯВЛЕНИЕ АНОМАЛИЙ (DATA QUALITY) Цель: Поиск технических ошибок и выявление «скрытых потерь» (бегемотов). */ -- 1. ДЕТЕКТОР АНОМАЛЬНЫХ ЗАТРАТ (Вместо перелива бака) -- Ищем рейсы, где затраты на топливо подозрительно высоки (например, более 5000 руб за рейс) SELECT t.trip_id, v.model, v.reg_number, t.fuel_cost, 'Внимание: Затраты на ГСМ выше лимита' as alert_type FROM trips t JOIN vehicles v ON t.vehicle_id = v.vehicle_id WHERE t.fuel_cost > 5000; -- 2. ДЕТЕКТОР "БЕГЕМОТОВ" (Анализ через стоимость 100 км пути) -- Вычисляем стоимость ГСМ на 100 км. Если она резко выше нормы — это аномалия. -- Предположим, средняя стоимость 100 км пути — 1200 руб. Ищем отклонения. SELECT v.model, v.reg_number, t.trip_date, ROUND((t.fuel_cost / t.distance_km) * 100, 2) as cost_per_100km FROM trips t JOIN vehicles v ON t.vehicle_id = v.vehicle_id WHERE t.distance_km > 0 AND (t.fuel_cost / t.distance_km) * 100 > 1500; -- Порог аномалии в рублях -- 3. ПРОВЕРКА ЛОГИЧЕСКОЙ ЦЕЛОСТНОСТИ -- Рейсы с пробегом, но с нулевыми затратами на ГСМ (ошибка учета или "левая" заправка) SELECT trip_id, vehicle_id, distance_km, fuel_cost FROM trips WHERE distance_km > 0 AND fuel_cost <= 0; -- 4. ДЕТЕКТОР ДУБЛЕЙ -- Поиск идентичных записей по машине и дате SELECT vehicle_id, trip_date, distance_km, COUNT(*) as duplicate_count FROM trips GROUP BY vehicle_id, trip_date, distance_km HAVING COUNT(*) > 1;

Контроль качества данных (Детектор «Бегемотов»)
Контроль емкости бака: Выявление фрода на АЗС (когда по чеку влили больше, чем лезет в бак).
Масложор (1л на 1000 км): Ранняя диагностика износа ЦПГ (колец/колпачков). Мы ловим «бегемота» до того, как он встанет на капремонт.
Дельта расхода (>15%): Поиск неисправных форсунок или свечей. Мы видим поломку не по «чеку» на приборке, а по деньгам в базе.

Здесь я ввел термин «Бегемота». Бегемот — это машина, которая по всем бумажным отчетам «в строю», но по факту пожирает ресурсы предприятия из-за мелких неисправностей или неэффективности. «Водитель не жалуется, машина в рейсе, а по цифрам мы видим — жрет как бегемот»

Оконные функции: Аналитика на скорости 100 км/ч

Многие останавливаются на простых отчетах «сколько потратили за месяц». Но для реального управления этого мало. Нужно видеть динамику. Для этого я использую «оконные функции» (Window Functions). Это уровень, который требует не просто умения писать SELECT, а понимания контекста данных. Например, функция LAG позволяет мне сравнить текущую заправку с предыдущей по этой же машине. Если вчера расход был 12л/100км, а сегодня стал 18л при той же загрузке — это сигнал. Это может быть неисправность топливной системы, которую нужно устранить сегодня, чтобы не менять двигатель завтра.

/* ПРОДВИНУТАЯ АНАЛИТИКА (WINDOW FUNCTIONS & KPI) Цель: Анализ динамики потребления и дисциплины маршрутов. */ -- 1. АНАЛИЗ ДИНАМИКИ ЗАТРАТ НА ГСМ (LAG) -- Сравниваем стоимость заправки текущего рейса с предыдущим по той же машине. SELECT vehicle_id, trip_date, fuel_cost, LAG(fuel_cost) OVER(PARTITION BY vehicle_id ORDER BY trip_date) as prev_trip_cost, ROUND(fuel_cost - LAG(fuel_cost) OVER(PARTITION BY vehicle_id ORDER BY trip_date), 2) as delta_cost FROM trips; -- 2. РЕЙТИНГ ВОДИТЕЛЕЙ ПО ЭФФЕКТИВНОСТИ (DENSE_RANK) -- Кто тратит меньше денег на 100 км пробега. SELECT driver_id, ROUND(AVG(fuel_cost / distance_km * 100), 2) as avg_cost_100km, DENSE_RANK() OVER(ORDER BY AVG(fuel_cost / distance_km * 100) ASC) as rank_position FROM trips WHERE distance_km > 0 GROUP BY driver_id; -- 3. НАКОПИТЕЛЬНЫЙ ИТОГ ЗАТРАТ (SUM OVER) -- Считаем, как рос общий расход бюджета на ГСМ в хронологическом порядке. SELECT trip_date, fuel_cost, SUM(fuel_cost) OVER(ORDER BY trip_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total_cost FROM trips; -- 4. ПОДЗАПРОС: РЕЙСЫ С ЗАТРАТАМИ ВЫШЕ СРЕДНЕГО -- Выделяем поездки, которые обошлись дороже, чем средний показатель по всему парку. SELECT trip_id, vehicle_id, fuel_cost FROM trips WHERE fuel_cost > (SELECT AVG(fuel_cost) FROM trips);

Бизнес-смысл: Анализ динамики и конкуренция.
LAG(): Сравнение заправки «сегодня» с «вчера». Позволяет увидеть резкое падение эффективности в рамках одного маршрута.
DENSE_RANK(): Построение честного рейтинга водителей. Это база для системы премирования: платим больше тем, кто бережет технику.
Подзапросы: Сравнение конкретной машины со средним показателем по всему парку.

В итоге вместо абстрактных учебных задачек я написал реальный аналитический инструмент — модульный сканер критических отклонений в цепочках поставок на базе СУБД PostgreSQL. Скрипт за пару секунд сможет проанализировать терабайты ежедневных логов движения транспорта, выявит аномальные задержки на маршрутах за последний месяц с помощью оконных функций и подсветит критические узлы, и рассчитает просадку эффективности рейсов, и найдет скрытые сбои в логистике.