В области анализа данных есть два классных инструмента — общие табличные выражения (CTE) и подзапросы, играющие важную роль в структурировании и обработке информации. Их правильное применение может значительно повысить эффективность ваших запросов.
CTE — это временные именованные наборы результатов, к которым можно обращаться внутри операторов SELECT, INSERT, UPDATE или DELETE. Они обеспечивают структурированный подход и делают код более читаемым и легким для анализа.
Плюсы:
— Повышают читаемость и структурированность кода.
— Их можно многократно использовать в одном запросе, сокращая повторения и облегчая отладку.
— Незаменимы для рекурсивных запросов и работы с иерархическими данными.
Минусы:
— Могут быть менее эффективны при неоптимальном использовании, особенно в больших базах данных из-за временного хранения результатов.
Пример:
WITH category_sales AS (
SELECT p.category,
SUM(s.quantity) AS total_quantity
FROM sales s
JOIN products p
ON s.product_id = p.product_id
GROUP BY p.category
)
SELECT p.product_name,
p.category,
SUM(s.quantity) AS product_quantity,
cs.total_quantity AS category_total_quantity
FROM sales s
JOIN products p ON s.product_id = p.product_id
JOIN category_sales cs ON p.category = cs.category
GROUP BY p.product_name, p.category, cs.total_quantity;
В этом примере в CTE category_sales вычисляется общее количество продаж товаров для каждой категории. Затем основной запрос использует эту информацию для вывода количества продаж по каждому продукту вместе с общим количеством продаж по его категории. Таким образом, мы можем увидеть как общие продажи по категориям, так и детальные продажи по каждому продукту. Это облегчает анализ эффективности продаж в разрезе категорий. Этот пример также можно реализовать с помощью оконных функций, но об этом в другой раз.
Чтобы поэкспериментировать самостоятельно, используйте песочницу https://sqliteonline.com и мой код для создания таблиц из вложения.
Подзапросы – это запросы, вложенные в другой запрос. Они предоставляют набор результатов для использования в основном запросе. Звучит сложно? На самом деле всё просто, сначала делается выборка по подзапросу, а результаты этой выборки используются для дальнейших вычислений.
Плюсы:
— Простота использования и понимания.
— Идеально подходят для извлечения данных в рамках ограниченного контекста.
Минусы:
— Ограниченные возможности повторного использования и рекурсии по сравнению с CTE.
— Могут привести к ухудшению читаемости кода при сложных вложениях.
Пример:
SELECT p.product_name, p.price
FROM products p
WHERE p.price > (
SELECT AVG(price) -- Вычисление средней цены по категории
FROM products
WHERE category = p.category
);
В этом примере подзапрос в WHERE-части вычисляет среднюю цену по категориям. Затем основной запрос сравнивает цену товара и выводит только ту, чья цена выше средней.
Чтобы поэкспериментировать самостоятельно, используйте песочницу https://sqliteonline.com и мой код для создания таблиц из вложения.
В боевых решениях я рекомендую использовать CTE, так как это упрощает чтение и отладку кода. Но в общем случае ориентироваться нужно в первую очередь, конечно же, на саму задачу.
#sql