а) Пользователи с просроченными платежами
SQL (вариант с JOIN):
SELECT DISTINCT u.*
FROM Users u
JOIN Orders o ON o.user_id = u.id
JOIN Payments p ON p.order_id = o.id
WHERE p.status = 'overdue';
Альтернатива с EXISTS (часто эффективнее, избегает дубликатов):
SELECT u.*
FROM Users u
WHERE EXISTS (
SELECT 1
FROM Orders o
JOIN Payments p ON p.order_id = o.id
WHERE o.user_id = u.id
AND p.status = 'overdue'
);
Пояснение:
- JOIN-версия возвращает пользователей, у которых есть связанный платеж со статусом 'overdue'; DISTINCT убирает дубли.
- EXISTS проверяет наличие хотя бы одной строки и обычно быстрее при большом числе платежей.
- Если у вас в Payments есть поле с датой срока (например due_date), лучше фильтровать по условию типа `p.due_date < now() AND p.status 'paid'`.
б) Средняя сумма заказов по пользователю
SQL (включая пользователей без заказов):
SELECT u.id, u.name, AVG(o.amount) AS avg_order_amount, COUNT(o.id) AS orders_count
FROM Users u
LEFT JOIN Orders o ON o.user_id = u.id
GROUP BY u.id, u.name;
Если нужны только пользователи с заказами — используйте INNER JOIN или добавьте HAVING:
... GROUP BY u.id, u.name
HAVING COUNT(o.id) > 0;
Пояснение:
- Используется агрегат $\mathrm{AVG}(o.amount)$ и $\mathrm{COUNT}(o.id)$.
- LEFT JOIN даёт значения NULL для $\mathrm{AVG}$ у пользователей без заказов; INNER JOIN исключит таких пользователей.
Индексация и проблемы масштабирования (кратко)
Рекомендованные индексы:
- индекс для связи Orders -> Users: $\text{Orders(user\_id)}$ (если нет FK-индекса).
- индекс для связи Payments -> Orders: $\text{Payments(order\_id)}$ (обычно FK уже индексирован).
- для фильтрации по статусу (запрос а): $\text{Payments(status)}$ или более предпочтительно составной/частичный индекс $\text{Payments(status, order\_id)}$ или частичный индекс на часто запрашиваемом статусе (например только для 'overdue').
- для агрегирования можно добавить индекс по $\text{Orders(user\_id)}$; но агрегаты типа $\mathrm{AVG}$ обычно обходят весь набор строк и не полностью выигрывают от одиночного индекса.
Проблемы и решения при росте данных:
- Сканирование больших таблиц для агрегатов — дорого. Решения: предварительно агрегированные таблицы/материализованные представления, инкрементальные счётчики (denormalized sum и count в Users), или OLAP-реплика.
- Горизонтальное масштабирование: шардинг по user_id (если пользователи независимы) или партиционирование (range по дате или hash по user_id).
- Горизонтальное чтение: read replicas для аналитических запросов.
- Индексы занимают место и замедляют запись — балансируйте (например, частичные/композитные индексы).
- Мониторинг запросов (EXPLAIN/EXPLAIN ANALYZE) и планов — обязательно профилировать тяжелые запросы.
- Кэширование часто запрашиваемых агрегатов в Redis или CDN для уменьшения нагрузки.
Короткие практические советы:
- Для запроса (а) используйте EXISTS + индекс по Payments(status, order_id) или частичный индекс на 'overdue'.
- Для (б) при большом объёме держите агрегаты в materialized view или обновляйте денормализованные поля (sum, count) при вставках/обновлениях.