Упрощенный SQL: создание модульных и понятных запросов с помощью CTE – KDnuggets

Изображение по автору 
 

В мире данных SQL по-прежнему остается языком общения с базами данных. 

До сих пор он остается одним из наиболее часто используемых языков для работы с данными и по-прежнему считается обязательным для любого хорошего специалиста по данным. 

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

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

Именно здесь в игру вступают общие табличные выражения (CTE), превращающие искусство написания запросов в более структурированное и доступное ремесло.

Итак, давайте вместе узнаем, как кодировать читаемые и повторно используемые запросы.

Если вам интересно, что такое CTE, вы попали в правильную статью. 

Общее табличное выражение (CTE) — это временный набор результатов, который определяется в области выполнения одного оператора SQL. 

 

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

Таким образом, они являются мощным инструментом для разбиения сложных запросов на более простые части.

Вот почему вам следует рассмотреть возможность использования CTE:

• Модульность: Вы можете разбить сложную логику на читаемые фрагменты.
• Читаемость: Это облегчает понимание потока SQL-запросов.
• Возможность повторного использования: На CTE можно ссылаться несколько раз в одном запросе, избегая повторения.

Волшебство начинается с предложения With, которое предшествует вашему основному запросу и определяет различные временные таблицы (CTE) с псевдонимами.

Поэтому нам всегда нужно начинать наш запрос с команды «WITH», чтобы начать определять собственные CTE. Используя CTE, мы можем разбить любой сложный SQL-запрос на: 

– Небольшие временные таблицы, в которых вычисляются связанные переменные. 

– Итоговая таблица, которая принимает в качестве выходных данных только те переменные, которые нам нужны.

И это именно тот МОДУЛЬНЫЙ подход, который нам нужен в любом коде!

 

Изображение по автору
 

Таким образом, использование CTE в наших запросах позволяет нам:

– Выполните темпоральную таблицу ОДИН РАЗ и обратитесь к ней НЕСКОЛЬКО раз.

– Улучшите читаемость и упростите сложную логику.

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

Чтобы лучше это понять, мы можем взять практический пример списков Airbnb в Барселоне. 

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

Чтобы проиллюстрировать это, мы будем использовать ВнутриAirbnb таблица Барселоны, которая выглядит следующим образом: 

 

 

Наивный подход может привести к созданию вложенных подзапросов, которые быстро превратятся в кошмар обслуживания, подобный следующему:

Код по автору

Вместо этого мы можем использовать CTE для разделения нашего запроса на логические разделы, каждый из которых определяет часть головоломки.

• Данные о районе: Создайте CTE для суммирования данных по районам.
• Информация о квартире и хозяине: Определите CTE для получения подробной информации о квартирах и хозяевах.
• Общегородские показатели: Еще один CTE для сбора статистики на уровне города для сравнения.
• Окончательная сборка: Объедините CTE в последнем операторе SELECT, чтобы представить данные связно.

 

Изображение по автору
 

И в итоге мы получим следующий запрос:

Код по автору

Используя CTE, мы превращаем потенциально чудовищный одиночный запрос в организованный набор модулей данных. Такой модульный подход делает код SQL более интуитивным и адаптируемым к изменениям. 

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

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

Это не только экономит время, но и повышает эффективность вашего кода, поскольку вам не придется повторять один и тот же запрос дважды!

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

Он оптимизирует процесс разработки запросов и упрощает передачу сложной логики поиска данных другим.

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

Ваше будущее «я» и все остальные, кто сможет прочитать ваш код, будут вам благодарны.
 
 

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

• SEO-контент и PR-распределение. Получите усиление сегодня.
• PlatoData.Network Вертикальный генеративный ИИ. Расширьте возможности себя. Доступ здесь.
• ПлатонАйСтрим. Интеллект Web3. Расширение знаний. Доступ здесь.
• ПлатонЭСГ. Углерод, чистые технологии, Энергия, Окружающая среда, Солнечная, Управление отходами. Доступ здесь.
• ПлатонЗдоровье. Биотехнологии и клинические исследования. Доступ здесь.
• Источник: https://www.kdnuggets.com/sql-simplified-crafting-modular-and-understandable-queries-with-ctes?utm_source=rss&utm_medium=rss&utm_campaign=sql-simplified-crafting-modular-and-understandable-queries-with-ctes