Запрос (обычный вариант):
SELECT c.country, SUM(o.amount) AS total_revenue
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
GROUP BY c.country
ORDER BY total_revenue DESC
LIMIT $3$;
Вариант с предагрегацией (лучше на больших объёмах — уменьшает количество строк при джойне):
WITH per_cust AS (
SELECT customer_id, SUM(amount) AS total_by_cust
FROM Orders
GROUP BY customer_id
)
SELECT c.country, SUM(pc.total_by_cust) AS total_revenue
FROM per_cust pc
JOIN Customers c ON pc.customer_id = c.customer_id
GROUP BY c.country
ORDER BY total_revenue DESC
LIMIT $3$;
Рекомендации по индексам и трансформациям (кратко и по существу):
- Индексы
- Customers(customer_id) как PRIMARY KEY (обычно уже есть).
- Индекс на Orders(customer_id) для ускорения джойна.
- Покрывающий индекс на Orders(customer_id, amount) — даст index-only scan и ускорит агрегирование по клиентам.
- При денормализации: если country добавлен в Orders, индекс по Orders(country) поможет прямому GROUP BY по стране.
- Трансформации данных и архитектура
- Предагрегация: сначала GROUP BY customer_id в Orders, затем JOIN к Customers и GROUP BY country (см. второй запрос). Сильно сокращает объём данных при большом количестве заказов на клиента.
- Материализованные таблицы / агрегаты: поддерживать периодически (или триггером/ETL) таблицу country_revenue(country, total_revenue) и читать её для быстрых top‑$3$.
- Денормализация: хранить country в Orders, чтобы избежать джойна при агрегации по стране.
- Партиционирование Orders (по дате или по hash(customer_id)) для уменьшения сканируемых частей.
- Планирование выполнения и настройки СУБД
- Разрешить параллельные агрегаты, увеличить memory (work_mem / sort_memory) для хеш-агрегации, избегать внешних сортировок.
- Для очень больших данных — использовать внешние/streaming-агрегаты или OLAP‑хранилище / колоночную СУБД для ускорения суммирования.
- Прочее
- Хранить amount в целых (копейки) вместо decimal для быстрее вычислений и компактности.
- Если важны равенства при связке топов (ties), использовать RANK()/DENSE_RANK() вместо LIMIT $3$.
Эти меры в комбинации (первичная агрегация + покрывающий индекс или материализованный агрегат) обычно дают наибольший прирост на больших объёмах.