Построение сложного SQL-запроса с вложенными JOIN, фильтрацией (WHERE), группировкой (GROUP BY) и агрегатными функциями — это типичная задача при аналитике или разработке отчетов на больших, взаимосвязанных данных. Ниже подробно объясню, как подходить к таким запросам, на примере и с описанием каждого этапа.

Исходные таблицы для примера

Предположим, у нас есть следующая структура данных:

1. **users
   **

id | name | country

-------------------------

1 | Alice | USA

2 | Bob | Canada

3 | Charlie | USA

1. **orders
   **

id | user_id | order_date | total_amount

----------------------------------------

1 | 1 | 2023-01-01 | 100

2 | 1 | 2023-01-03 | 200

3 | 2 | 2023-01-02 | 150

4 | 3 | 2023-01-04 | 300

1. **order_items
   **

id | order_id | product_id | quantity | price_per_unit

-------------------------------------------------------

1 | 1 | 101 | 2 | 25

2 | 1 | 102 | 1 | 50

3 | 2 | 103 | 3 | 40

4 | 3 | 104 | 1 | 150

1. **products
   **

id | name | category

-----------------------------

101 | Laptop | Electronics

102 | Mouse | Electronics

103 | T-Shirt | Apparel

104 | Headphones | Electronics

Цель запроса

Получить агрегированную информацию:

• по каждой стране,

• только по заказам с общей суммой более 100,

• в разбивке по категориям товаров,

• с суммой проданных товаров (total_sales),

• количеством проданных единиц (total_quantity).

Построение SQL-запроса поэтапно

Шаг 1: Соединяем таблицы

Для этого используем несколько JOIN, чтобы получить все необходимые поля:

SELECT
u.country,
p.category,
SUM(oi.quantity \* oi.price_per_unit) AS total_sales,
SUM(oi.quantity) AS total_quantity
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id

Шаг 2: Добавляем фильтрацию

Нам нужны только те заказы, где общая сумма заказа > 100. Это значит, мы не можем просто использовать o.total_amount, потому что его может не быть (в реальных БД total_amount может быть вычисляемым).

Мы можем:

• либо использовать подзапрос (CTE) для фильтрации по total,

• либо фильтровать постфактум.

В данном случае — воспользуемся подзапросом с WHERE и EXISTS.

Вариант 1: Через подзапрос-CTE для фильтрации заказов

WITH valid_orders AS (
SELECT id
FROM orders
WHERE total_amount > 100
)
SELECT
u.country,
p.category,
SUM(oi.quantity \* oi.price_per_unit) AS total_sales,
SUM(oi.quantity) AS total_quantity
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN valid_orders vo ON o.id = vo.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY u.country, p.category
ORDER BY u.country, total_sales DESC;

Вариант 2: Через EXISTS (если у orders нет total_amount, нужно вычислить на лету)

Допустим, total_amount не хранится в таблице, а считается как сумма price * quantity. Тогда:

WITH order_totals AS (
SELECT
o.id AS order_id,
SUM(oi.quantity \* oi.price_per_unit) AS total_amount
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id
),
valid_orders AS (
SELECT order_id
FROM order_totals
WHERE total_amount > 100
)
SELECT
u.country,
p.category,
SUM(oi.quantity \* oi.price_per_unit) AS total_sales,
SUM(oi.quantity) AS total_quantity
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN valid_orders vo ON o.id = vo.order_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY u.country, p.category
ORDER BY u.country, total_sales DESC;

Пояснение по используемым компонентам

• JOIN:

  • Соединяет таблицы users, orders, order_items, products.

  • Благодаря JOIN, мы получаем всю цепочку от клиента до категории продукта.

• WITH (CTE):

  • Удобен для разбиения сложной логики.

  • В первом CTE вычисляется сумма заказа.

  • Во втором CTE — фильтруются только те заказы, где сумма > 100.

• WHERE:

  • Применяется к отфильтрованным заказам через CTE или EXISTS.

• GROUP BY:

  • Используется по country и category, т.е. итоговые агрегаты на уровне "страна-продуктовая категория".

• Агрегатные функции SUM():

  • SUM(quantity * price) — выручка по товарам.

  • SUM(quantity) — количество проданных единиц.

• ORDER BY:

  • Сортировка по стране и убыванию выручки.

Альтернативные подходы

Если нужно ещё более сложное поведение (например, фильтрация внутри агрегатов), можно использовать HAVING:

GROUP BY u.country, p.category
HAVING SUM(oi.quantity \* oi.price_per_unit) > 100

Но HAVING применяется после агрегации, а в нашей задаче фильтрация применяется к заказу, а не к группе.

Частые ошибки

1. Забывают фильтрацию до JOIN, и потом агрегаты искажаются.

2. Не учитывают дубли при JOIN, особенно при соединении 1:N.

3. Группируют не по всем неагрегированным полям, что вызывает ошибки в некоторых СУБД (например, PostgreSQL требует, чтобы все SELECT-поля были в GROUP BY, если не агрегат).

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