Спрощений SQL: створення модульних і зрозумілих запитів за допомогою CTE - KDnuggets

Перевидано Платоном

читають: 0

Спрощений SQL: створення модульних і зрозумілих запитів за допомогою CTE
Зображення автора

У світі даних SQL все ще залишається лінгва франка для взаємодії з базами даних.

Сьогодні вона залишається однією з найбільш використовуваних мов для роботи з даними, і все ще вважається обов’язковою для будь-якого хорошого спеціаліста з обробки даних.

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

Ось чому сьогодні недостатньо знати SQL, нам потрібно вміти створювати запити. І це, власне, вид мистецтва.

Саме тут вступають у дію загальні табличні вирази (CTE), які перетворюють мистецтво написання запитів на більш структуроване та доступне ремесло.

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

Якщо вам цікаво, що таке CTE, ви обрали правильну статтю.

Загальний табличний вираз (CTE) — це тимчасовий набір результатів, визначений у межах виконання окремого оператора SQL.

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

Тому вони є потужним інструментом для розбиття складних запитів на простіші частини.

Ось чому вам варто розглянути можливість використання CTE:

Модульність: Ви можете розбити складну логіку на зрозумілі частини.
читаність: Це полегшує розуміння потоку запитів SQL.
Багаторазовість: На CTE можна посилатися кілька разів в одному запиті, уникаючи повторення.

Магія починається з пропозиції WITH, яка передує вашому основному запиту та визначає різні часові таблиці (CTE) з псевдонімами.

Тому нам завжди потрібно починати запит із команди «WITH», щоб почати визначення власних CTE. Використовуючи CTE, ми можемо розбити будь-який складний SQL-запит на:

– Невеликі часові таблиці, які обчислюють пов’язані змінні.

– Підсумкова таблиця, яка приймає лише ті змінні, які нам потрібні як вихідні дані.

І це саме той МОДУЛЬНИЙ підхід, який ми хочемо в будь-якому коді!

Спрощений SQL: створення модульних і зрозумілих запитів за допомогою CTE
Зображення автора

Отже, використання CTE у наших запитах дозволяє нам:

– Виконайте часову таблицю ОДИН РАЗ і посилайтеся на неї КІЛЬКА разів.

– Покращте читабельність і спростіть складну логіку.

– Сприяти повторному використанню коду та модульному дизайну.

Щоб краще зрозуміти це, ми можемо взяти практичний приклад оголошень Airbnb у Барселоні.

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

Щоб проілюструвати це, ми будемо використовувати InsideAirbnb таблиця Барселони, яка виглядає наступним чином:

Спрощений SQL: створення модульних і зрозумілих запитів за допомогою CTE

Наївний підхід може призвести до створення вкладених підзапитів, які швидко стануть кошмаром для обслуговування, подібним до наступного:

Код за автором

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

Дані околиць: Створіть CTE, щоб узагальнити дані за околицями.
Інформація про квартиру та господаря: Визначте CTE для детальної інформації про квартири та господарів.
Загальноміські показники: Інший CTE для збору статистики на рівні міста для порівняння.
Фінальна збірка: Об’єднайте CTE в остаточному операторі SELECT, щоб представити дані зв’язано.

Спрощений SQL: створення модульних і зрозумілих запитів за допомогою CTE
Зображення автора

І ми отримали б такий запит:

Код за автором

Використовуючи CTE, ми перетворюємо потенційно жахливий єдиний запит на організований набір модулів даних. Цей модульний підхід робить код SQL більш інтуїтивно зрозумілим і адаптованим до змін.

Якщо з’являться нові вимоги, ви можете налаштувати або додати CTE, не переглядаючи весь запит.

Після того, як ви встановили свої CTE, ви можете повторно використовувати їх для виконання порівняльного аналізу. Наприклад, якщо ви хочете порівняти дані околиць із загальноміськими показниками, ви можете посилатися на свої CTE у серії операцій JOIN.

Це не тільки економить час, але й забезпечує ефективність коду, оскільки вам не доведеться повторювати один і той самий запит двічі!

CTE є свідченням принципу, що невелика структура має велике значення в програмуванні. Використовуючи CTE, ви можете писати чіткіші, зручніші та багаторазово використовувані запити SQL.

Це спрощує процес розробки запитів і полегшує передачу складної логіки пошуку даних іншим.

Пам’ятайте, що наступного разу, коли ви збираєтеся почати писати монстр вкладеного підзапиту з кількома з’єднаннями, подумайте про те, щоб розбити його за допомогою CTE.

Ваше майбутнє — і будь-хто інший, хто може прочитати ваш код — буде вам вдячний.

Хосеп Феррер – інженер-аналітик із Барселони. Він закінчив інженер-фізику та зараз працює в галузі Data Science, що стосується людської мобільності. Він неповний робочий день створює контент, який зосереджується на науці даних і технологіях. Ви можете зв'язатися з ним на LinkedIn, Twitter or Medium.