«Кто-нибудь знает, как узнать расстояние каждого этапа миссии?» — спрашивает один из них. «Да, одну секунду. Стефан только что прислал его мне на прошлой неделе. Другой отвечает и начинает искать свой электронный ящик. «О, я нашел это, только что отправил вас в Slack». Через несколько минут раздается другой голос: «Как фильтровать авиавылеты? Я не могу вспомнить, что это за поле. «Транспорт в таблице Mission_leg». Кто-то ответил. "О, да. Это поле. Спасибо!" Подобные разговоры происходят почти каждый день в нашей студенческой команде AFW.
Как самый «нагруженный SQL» практический курс в программе UC Davis MSBA, Angel Flight West предоставляет нам все свои данные в базе данных, охватывающие данные за три десятилетия. В базе данных 191 таблица с 8 224 тыс. записей и 3 тыс. характеристик. Среди этих таблиц наш анализ данных в основном сосредоточился на 14 ключевых таблицах с 537 тысячами записей. В задачах, в которых я участвовал, я могу сказать, что мы тратим в среднем 30% времени на получение запроса первой версии и 10% времени на его изменение. Нет никаких сомнений в том, что SQL является одной из самых больших проблем в нашем практическом проекте.
(Каждый кружок представляет собой таблицу в базе данных. Размер кружка представляет количество записей, а цвет кружка представляет количество характеристик в этой таблице. Ключевые таблицы отмечены метками)
Задача исходит из нескольких аспектов:
1. Общие правила работы с таблицами
Наша практика началась в октябре 2018 года, когда я только что выучил одну или две сессии нашего курса по управлению данными (SQL). Я почувствовал вызов, когда столкнулся с такой огромной базой данных. Только что изучив базовый синтаксис, такой как select и join, практика подтолкнула меня пойти намного дальше, чем знания, полученные в классе SQL. В отличие от выбора строк из четырех или пяти таблиц с не более чем 10 полями в каждой, как я делал в классе SQL, было намного больше таблиц, в среднем по 30 полей в каждой таблице для работы. На выходе часто были тысячи строк, в то время как мы обычно имели дело только с 30 или 40 строками в домашнем задании по SQL. Чтобы получить необходимые данные, нам нужно было выяснить взаимосвязь между различными таблицами и значение каждого поля. Например, что означают таблицы маршрут, миссия, миссия_лег и миссия_запрос и какова связь между ними?
2.Подтвердите с нашим клиентом логику запроса в деталях
После того, как мы получили общее представление о правилах в часто используемых таблицах, оставалось еще много деталей, которые нужно было подтвердить в зависимости от различных ситуаций. Процесс подтверждения занимает много времени. Более того, задачи не могли двигаться дальше без извлечения нужных данных. Иногда это может быть очень неприятно. Однако очень важно подтвердить логику. В противном случае мы получим неправильные данные, которые могут быть прямо противоположными тому, что нам нужно. Например, когда-то мы хотели получить все успешные миссии (не отмененные) и использовали условие «отменено = 0» на основе интуитивного вывода для фильтрации данных. Затем в течение пары дней мы использовали результат для визуализации в Tableau, пока не обнаружили, что условие должно быть «отменено = 1». К счастью, ошибка была вовремя замечена, так как мы не показали результат нашему клиенту, который мог быть очень растерян.
Чтобы преодолеть эту проблему, наша команда создала регулярно обновляемый документ, в котором фиксируются все детали бизнес-правил, с которыми мы столкнулись. С этим документом нашей команде будет намного проще обращаться и избегать повторных подтверждений.
3. Пишите очень сложные запросы
По мере продвижения проекта нам нужно писать все более и более сложные запросы для получения необходимых данных. Оконные функции и вложенные запросы становятся повседневной работой. Когда мы проводили анализ активных пилотов и очень активных пилотов, было написано более 100 строк кода для запроса 15 таблиц. Иногда запрашивается несколько таблиц, если нам нужна вычисляемая метрика. В процессе написания сложных запросов наша команда объединила часто используемые вычисляемые поля в список. Мы хотим предоставить этот список нашему клиенту, чтобы он мог создать несколько сводных таблиц, содержащих эти поля в своей базе данных. Таким образом, они могут повысить эффективность внутренних запросов. Между тем, внешняя постоянная команда, такая как мы, может тратить больше времени на добавленную стоимость, экономя время на запросах.
