Занятие 26. Табличные выражения

💻 Рабочие примеры

Пример 1. Топ-10 продуктов по заказам и выручке

USE northwind;

WITH product_summary AS (
    SELECT product_id,
           COUNT(*) AS total_orders,
           SUM(unit_price * quantity) AS total_revenue
    FROM order_details
    GROUP BY product_id
)
SELECT p.product_name, ps.total_orders, ps.total_revenue
FROM product_summary AS ps
JOIN products AS p ON ps.product_id = p.id
ORDER BY ps.total_orders DESC
LIMIT 10;

CTE агрегирует данные по продуктам; внешний запрос подтягивает названия и сортирует результат.

Пример 2. Строки дороже средней цены

USE northwind;

WITH avg_price AS (
    SELECT AVG(unit_price) AS ap
    FROM order_details
)
SELECT *
FROM order_details
WHERE unit_price > (SELECT ap FROM avg_price);

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

Пример 3. Строки в диапазоне от средней до 1.5× средней цены

USE northwind;

WITH avg_price AS (
    SELECT AVG(unit_price) AS ap
    FROM order_details
)
SELECT *
FROM order_details
WHERE unit_price > (SELECT ap FROM avg_price)
  AND unit_price < (SELECT ap * 1.5 FROM avg_price);

Один CTE используется дважды: для нижней и верхней границы.

Пример 4. Заказы Sales Representative

USE northwind;

WITH sales_repr AS (
    SELECT id
    FROM employees
    WHERE job_title = 'Sales Representative'
)
SELECT *
FROM orders
WHERE employee_id IN (SELECT id FROM sales_repr);

CTE возвращает список идентификаторов сотрудников; внешний запрос фильтрует заказы через IN.

Пример 5. Заказы клиентов из Лос-Анджелеса

USE northwind;

WITH la_clients AS (
    SELECT id
    FROM customers
    WHERE city = 'Los Angeles'
)
SELECT *
FROM orders
WHERE customer_id IN (SELECT id FROM la_clients);

Несмотря на то что в CTE один столбец, в условии нужно явно указывать SELECT id FROM la_clients, а не просто имя CTE.