Как анализировать базу данных

У каждой базы данных (БД) — разные:

• структура 
• количество таблиц, данных, столбцов
• связи и ключи
• актуальность информации, которая хранится в ней

Оксана Носенко, Senior Product Analyst в Jooble , объясняет, как разобраться в новой для вас БД.

Определяем связи в БД
 

Прежде чем писать SQL-запросы и анализировать результаты, надо разобраться в данных и схемах. 

Если вы не можете получить схему данных и связей в базе, определите связи самостоятельно. 

#1. Определите, какие из таблиц базы данных актуальны.

Очень часто в БД могут лежать исторические данные, архивы и локальные копии. Такая информация может вам вообще не понадобиться. Стоит выделить ее сразу, чтобы затем пропускать.

#2. Определите словари. Они обычно содержат 2-4 поля с расшифровкой ключей. Это небольшие таблицы, но их может быть много. Если вы выделите словари в отдельный пул, количество таблиц для потенциального анализа уменьшится.

#3. Найдите таблицы, которые описывают сущности или логи (например, пользователей и их поведение). 
Это основной датасет. В таких таблицах информации и столбцов много, данные актуальны и постоянно дополняются. 

В итоге останутся таблицы с актуальными системными или техническими данными, логика записи которых непонятна. Скорее всего, вы не будете обращаться к ним часто, поэтому на старте не нужно их подробно изучать. 

Рекомендую все выводы конспектировать в документации. Тогда в поиске нужной информации вы будете просматривать небольшой пул таблиц, в котором, скорее всего, найдете ответ.

После определения основного сета таблиц и данных начните их изучать. Обычно такой сет составляет около 50% от базы. Просмотрите каждую таблицу отдельно, примените к ней запросы, чтобы понять, какие данные там хранятся. 

Ответьте на вопросы:

• информация за какой период хранится в таблице;
• какие в ней идентификаторы (поля ids);
• какие события содержит таблица;
• хранится ли там информация по сущностям. Если да — просмотрите ее с агрегацией по разным признакам (например, верифицированные пользователи и нет, удаленные и нет).

Если идентификаторов (ids) много, постарайтесь найти для всех таблицы-словари. Можете ориентироваться на название поля перед id и искать таблицу с похожим названием. Например, если вы увидели в таблице ключ user_id, постарайтесь найти таблицы с 'users' в названии. Скорее всего, расшифровка будет лежать в одной из них.

Если вы заметили, что не все столбцы заполняются, отметьте это в документации. Не обращайтесь к этим столбцам — там вряд ли появится информация. Проверяйте перед написанием запросов, есть ли расшифровка в таблицах словаря.

Когда данные изучены и определены, можно приступать к созданию SQL-запроса.

курсы по теме:

Прикладная математика и статистика для Data Science

Бобровский Сергей

Data Scientist в отделении AI Platforms в Airbus

О курсе

SQL для аналитики

Доарме Виталий

Data Analyst Team Lead в NielsenIQ

О курсе

Составляем схему результирующего набора
 

Обычно задача аналитика не включает список всех данных, которые надо получить.

Но так как SQL-запрос выдаст вам набор из метрик и показателей, надо продумать его заранее. 

#1. Определите список столбцов: метрик и сущностей, которые помогут ответить на вопрос. Не ориентируйтесь на наличие именно этих данных в базе. Не страшно, если придется дополнительно посчитать их. Но помните — если данных для расчета показателя в базе нет, не стоит включать эту метрику в схему результирующего набора (она не будет посчитана).

#2. Напишите во втором столбце (после названия метрик) формулу для расчета показателя. Она должна быть основана на данных из реальных столбцов базы.

#3. Укажите список таблиц, которые необходимы для расчета формулы. Если столбец — это сущность, укажите одну таблицу, из которой нужно его взять.

#4. Вверху схемы пропишите условия, которые применяются к выборке: временной диапазон, ограничения по стране, типам устройств или операционным системам. Это нужно, чтобы исключить неинтересные для анализа данные при написании запроса.

Когда подготовите схему, спросите себя:

• сколько таблиц участвует в запросе?
• возможно ли дублирование данных при соединении этих таблиц? 
• удастся ли избежать его с помощью оператора distinct или придется прибегать к подзапросам / CTE. 
• какой объем информации предстоит запросить у базы? Можно ли его уменьшить? Нужны ли дополнительные ограничения? 
• какие данные должны быть образующими для запроса? Важно ли учесть все сущности словаря или, наоборот, данные обо всех активностях (даже если их расшифровки нет в словарях)? 

Пример заметок перед созданием запроса в новой базе данных

Пишем сложный SQL query
 

Проверьте результирующий набор на тестовом примере. Если запрос сложен, вы можете сделать ошибку, которая будет заметна только при выборочной проверке. 

Поэтому возьмите одного пользователя или одно событие и проверьте результат последовательным набором простых select + where. 

Не применяйте сложные вычисления, в которых можно совершить ошибку (windows functions, having, коррелирующие подзапросы). Ограничьтесь простым select c фильтром. Если вы прошли эту цепочку и получили тот же результат, что и ваш сложный запрос, — высока вероятность, что он написан верно. 

Если вы сделали проверку и результат не совпал, начните отладку запроса. Ее можно делать постепенно — проверяя каждый из этапов на тестовом наборе данных, искусственном фильтре, который даст вам 1-2 результирующие строки. 

Затем рекомендую сохранить запрос с привязкой к задаче, которую он решал. Можете вставить вопрос комментарием или прикрепить запрос в схему — возможно, он пригодится в будущем, а писать его еще раз нецелесообразно.

Как работать с комплексными SQL-запросами:
 

• проверяйте корректность выполнения каждой части — гораздо проще отловить ошибку на промежуточном этапе, чем в конце;
   
• не пишите слишком много вложенных друг в друга подзапросов. Даже вам будет трудно разобраться в таком коде через пару дней. Старайтесь выделять логические части в CTE;
   
• старайтесь не мешать CTE и подзапросы с вложенностью больше трех. Применяйте в коде что-то одно, выносите все сложные части в общие табличные выражения или пишите подзапросы с большой вложенностью;
   
• ставьте фильтры на первых этапах. Чем раньше вы ограничите выборку, тем меньше данных придется вычислять в следующих операциях; 
   
• не переименовывайте столбцы и таблицы без причины. Не стоит давать таблице alias ради сокращения ее названия на 2 символа. Не переименовывайте столбцы из таблиц, к которым не применялась обработка. Используйте псевдонимы при необходимости. Например, если название таблицы очень длинное. Тип псевдонима по первой букве таблицы можно применить, если все в вашей компании знают, что s — sales table. Но если это может быть sales_profit, sales_roi или sales_partners, лучше оставить название без изменения; 
   
• оставляйте комментарии;
   
• не забывайте о форматировании. Его отсутствие может сделать скрипт нечитаемым и привести к неверной интерпретации другим членом команды. Поэтому делайте табуляцию, отступы и не пишите все сплошным текстом. 
   
• выносите отдельно все повторяющиеся части запроса, чтобы к ним можно было обратиться несколько раз, не переписывая заново. 

Всегда просматривайте результат со здоровым скептицизмом — все ли показатели выглядят достижимыми? Нет ли аномалий? Верный запрос не защитит вас от ошибок в данных, которые можно отловить на постанализе.