Як аналізувати базу даних

У кожної бази даних (БД) — різні:

• структура
• кількість таблиць, даних, стовпців
• зв’язки та ключі
• актуальність інформації, яка зберігається в ній

Оксана Носенко, Senior Product Analyst в Jooble, пояснює, як розібратися в новій для вас БД.

Визначаємо зв’язки в БД
 

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

Якщо ви не можете отримати схему даних та зв’язків у базі, визначте зв’язки самостійно.

#1. Визначте, які з таблиць бази даних є актуальними.

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

#2. Визначте словники. Вони зазвичай містять 2–4 поля з розшифровкою ключів. Це невеликі таблиці, але їх може бути багато. Якщо ви виділите словники в окремий пул, кількість таблиць потенційного аналізу зменшиться.

#3. Знайдіть таблиці, які описують сутності чи логи (наприклад, користувачів та їхню поведінку). 
Це основний датасет. У таких таблицях інформації та стовпців багато, дані актуальні й постійно доповнюються.

У результаті залишаться таблиці з актуальними системними чи технічними даними, логіка запису яких ― незрозуміла. Швидше за все, ви не звертатиметеся до них часто, тому на старті не потрібно буде їх докладно вивчати.

Рекомендую всі висновки конспектувати в документації. Тоді під час пошуку потрібної інформації ви переглядатимете невеликий пул таблиць, у якому, швидше за все, ви знайдете відповідь.

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

Дайте відповідь на запитання:

• інформація за який період зберігається в таблиці;
• які в ній ідентифікатори (поля ids);
• які події містить таблиця;
• чи зберігається там інформація щодо сутностей. Якщо так, перегляньте її з агрегацією за різними ознаками (наприклад, верифіковані користувачі та ні, видалені та ні).

Якщо ідентифікаторів (ids) багато, постарайтеся знайти для всіх таблиці-словники. Можете орієнтуватися на назву поля перед ID і шукати таблицю зі схожою назвою. Наприклад, якщо ви побачили в таблиці ключ user_id, спробуйте знайти таблиці з 'users' у назві. Швидше за все, розшифровка лежатиме в одній із них.

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

Коли дані вивчені та визначені, можна приступати до створення SQL-запиту.

курси на тему:

Математика та статистика для Data Science

Ісакова Кристина

Data Scientist у Holidu

Про курс

Складаємо схему результівного набору
 

Зазвичай, завдання аналітика не включає список усіх даних, які треба отримати.

Але оскільки 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, краще залишити назву без зміни.
• Залишайте коментарі.
• Не забувайте про форматування. Його відсутність може зробити скрипт нечитабельним та призвести до неправильної інтерпретації іншим членом команди. Тому робіть табуляцію, відступи та не пишіть усе суцільним текстом.
• Виносите окремо всі частини запиту, які повторюються, щоби до них можна було звернутися кілька разів, не переписуючи все спочатку.

Завжди переглядайте результат зі здоровим скептицизмом — чи всі показники виглядають досяжними? Чи немає аномалій? Правильний запит не захистить вас від помилок у даних, які можна виловити на постаналізі.