Как исправить дублирование данных в SQL-запросе с LEFT JOIN LATERAL

При работе со сложными SQL-запросами, объединяющими несколько таблиц через LEFT JOIN LATERAL, часто возникает проблема дублирования строк. В данной статье разберем типичную ситуацию: запрос для получения деталей компании, включая сумму транзакций, количество операций и связанные категории. Вы узнаете, почему счетчик транзакций завышается и как это исправить с помощью корректного использования агрегатных функций и DISTINCT.

Почему возникает дублирование в SQL-запросе?

Основная причина - множественные LEFT JOIN LATERAL, которые создают декартово произведение между подзапросами. В исходном примере запрос выбирает данные компании с id = 17 и присоединяет две латеральные выборки: одну для транзакций, другую для категорий. Если у компании есть несколько категорий и несколько транзакций, каждая строка из одной выборки умножается на каждую строку из другой. В результате:

• COUNT(t) показывает не 4 транзакции, а 12 (3 категории × 4 транзакции).
• Сумма total_amount утраивается, так как агрегатные функции применяются к дублированным строкам.

Это классическая проблема дублирования при JOIN, когда группировка выполняется после объединения всех таблиц.

Как исправить запрос: пошаговая инструкция

1. Используйте DISTINCT для агрегации массивов

В исходном коде уже применен DISTINCT внутри ARRAY_AGG для транзакций:

array_to_json(ARRAY_AGG(DISTINCT to_jsonb(t))) AS transactions

Это убирает дублирование в результирующем массиве, но не влияет на COUNT(t) и SUM, так как они считаются до применения DISTINCT. Поэтому для подсчета количества транзакций нужно использовать отдельный подзапрос.

2. Вынесите подсчет количества в отдельный LATERAL-подзапрос

Лучший способ - вычислить количество транзакций до объединения с категориями. Добавьте еще один LEFT JOIN LATERAL только для подсчета:

LEFT JOIN LATERAL (SELECT COUNT(*) AS transaction_count FROM company_transactions WHERE company_id = co.id) tc ON TRUE

Тогда в основном запросе вы сможете использовать tc.transaction_count вместо COUNT(t).

3. Исправьте суммирование с помощью вложенного запроса

Чтобы избежать дублирования сумм, вычислите total_amount для компании в отдельном подзапросе, который не зависит от категорий:

LEFT JOIN LATERAL (SELECT COALESCE(SUM(CASE WHEN type = 'INCOME' THEN amount ELSE 0 END) - SUM(CASE WHEN type = 'EXPENSE' THEN amount ELSE 0 END), 0.00) AS total FROM company_transactions WHERE company_id = co.id) ts ON TRUE

После этого в основном SELECT используйте ts.total.

4. Оптимизируйте группировку

Убедитесь, что GROUP BY co.id остается единственной группировкой, и все агрегатные функции применяются к корректным наборам данных. После выноса подсчетов в латеральные запросы, COUNT(t) и SUM из основного запроса можно заменить на значения из подзапросов.

Пример исправленного запроса

Ниже представлен корректный SQL-запрос, который возвращает правильные суммы и количество транзакций без дублирования:

SELECT co.id, co.title, co.description, co.created_at, co.updated_at, COALESCE(ts.total, 0.00) AS total_amount, tc.transaction_count, array_to_json(ARRAY_AGG(DISTINCT to_jsonb(t))) AS transactions, array_to_json(ARRAY_AGG(DISTINCT to_jsonb(c))) AS categories FROM companies co LEFT JOIN LATERAL (SELECT id, description, amount, created_at, type FROM company_transactions WHERE company_id = co.id) t ON TRUE LEFT JOIN LATERAL (SELECT id, title, COALESCE(SUM(CASE WHEN tr.type = 'INCOME' THEN tr.amount ELSE 0 END) - SUM(CASE WHEN tr.type = 'EXPENSE' THEN tr.amount ELSE 0 END), 0.00) AS total_amount FROM company_categories c LEFT JOIN company_transactions tr ON c.id = tr.category_id WHERE c.company_id = co.id GROUP BY c.id) c ON TRUE LEFT JOIN LATERAL (SELECT COUNT(*) AS transaction_count FROM company_transactions WHERE company_id = co.id) tc ON TRUE LEFT JOIN LATERAL (SELECT COALESCE(SUM(CASE WHEN type = 'INCOME' THEN amount ELSE 0 END) - SUM(CASE WHEN type = 'EXPENSE' THEN amount ELSE 0 END), 0.00) AS total FROM company_transactions WHERE company_id = co.id) ts ON TRUE WHERE co.id = 17 GROUP BY co.id, ts.total, tc.transaction_count;

Теперь total_amount и transaction_count берутся из независимых подзапросов, поэтому значения будут точными. Массивы категорий и транзакций формируются с DISTINCT, что исключает дубли внутри JSON.

Советы по профилактике дублирования

• Избегайте множественных LATERAL JOIN с разными таблицами, если это возможно. Вместо этого используйте подзапросы в SELECT или WITH (CTE).
• Применяйте DISTINCT в ARRAY_AGG и STRING_AGG, чтобы убрать дубли в массивах.
• Выносите агрегатные функции в отдельные латеральные запросы, чтобы они не зависели от других JOIN.
• Тестируйте запрос на небольших наборах данных, проверяя количество строк до и после группировки.

Следуя этим рекомендациям, вы сможете избежать ошибок дублирования в сложных SQL-запросах и получать точные данные для отчетов, аналитики или API-ответов, соответствующих интерфейсам вроде ICompanyDetails.