Отчёт показывает выручку вдвое больше настоящей. Запрос не менялся, данные не портились, а сумма выросла. Почти всегда причина одна: строки размножились при соединении таблиц, и никто этого не заметил, потому что глазами проверяли на трёх записях.
Что на самом деле делает соединение
Соединение — не «приклеить колонки справа». Это перебор пар: каждая строка левой таблицы сопоставляется с каждой подходящей строкой правой. Если подходящих строк несколько, левая строка попадёт в результат столько раз, сколько нашлось пар.
Отсюда простое правило, из которого следует почти всё остальное: число строк результата определяется не таблицами, а кратностью связи между ними. Один заказ и три его позиции дадут три строки, а не одну. Сумма по такому результату посчитает заказ трижды.
Пока связь один к одному, интуиция не подводит и об этом можно не думать. Стоит появиться связи один ко многим — а она появляется почти везде, — как результат начинает жить своей жизнью.
Тупик: убрать дубликаты
Первое, что пробуют, увидев повторяющиеся строки, — добавить требование уникальности к результату. Строки схлопываются, отчёт выглядит правильно, задача закрыта.
Это не помогает, и хуже того — маскирует проблему. Уникальность убирает строки, полностью совпадающие по всем колонкам. Если в результате есть хоть одна отличающаяся колонка — идентификатор позиции, дата, цена, — строки не совпадают и остаются. Сумма по-прежнему завышена, но теперь она завышена незаметно, потому что явных дублей не видно.
Второй частый ход — фильтровать по чему-нибудь после соединения, чтобы «оставить только нужное». Иногда угадывают. Чаще получают другой неверный ответ, потому что условие подобрано под конкретные данные и перестаёт работать, как только данные меняются.
Общая ошибка обоих ходов: они правят результат, не разобравшись в причине его формы.
Как понять, что произойдёт, до выполнения
Есть приём, который занимает минуту и снимает большую часть таких ошибок. Перед тем как писать соединение, ответьте на один вопрос: сколько строк правой таблицы соответствует одной строке левой?
Если ответ «ровно одна» — соединение безопасно, число строк не изменится. Если «ноль или одна» — тоже безопасно, но появятся пустые значения, и их надо будет обработать. Если «сколько угодно» — результат размножится, и любая сумма по нему будет неверной без дополнительной работы.
Проверить ответ можно, не строя отчёт: посчитайте число строк в каждой таблице и число строк результата. Если результат больше левой таблицы, размножение произошло. Это одна строчка проверки, которая делается один раз и экономит разбирательство с расхождением в отчётности.
Полезно и посмотреть на ограничения самой базы. Уникальный ключ на поле, по которому идёт соединение, — это гарантия связи один к одному, данная схемой, а не вашей памятью. Если такого ограничения нет, связь «один ко многим» надо считать возможной, даже когда сегодня в данных повторов нет: их заведёт первая же повторная загрузка.
Что делать, когда связь один ко многим
Вариантов немного, и выбирают по тому, что должно оказаться в результате.
Сначала свернуть, потом соединять. Если нужна сумма по позициям заказа, посчитайте её отдельно, а к заказам присоединяйте уже готовое число. Тогда связь становится один к одному, и результат не размножается. Это самый надёжный путь и обычно самый быстрый.
Считать по уникальным значениям. Если сворачивать неудобно, суммируйте не все строки подряд, а различные значения нужного поля. Работает, но требует внимания: как только в запросе появится ещё одно соединение, придётся пересматривать всё заново.
Взять одну строку из многих. Иногда нужна не сумма, а последняя запись: последний платёж, актуальный адрес. Это отдельная задача — выбрать по одной строке на группу, — и решать её фильтром «где дата максимальная» опасно: при совпадении дат вернётся несколько строк, и вы снова получите размножение, только реже и незаметнее.
Где это ломается тише всего
Опасны не те запросы, где дубли видно, а те, где их не видно.
Соединение через промежуточную таблицу связей. Кажется, что связь один к одному, но если в таблице связей есть повторы — а они там заводятся при повторной загрузке данных, — результат тихо удваивается.
Соединение по неполному ключу. Совпадение по одному полю там, где связь определяется двумя. Работает, пока в данных нет пересечений, и ломается, когда они появляются.
Левое соединение с последующим условием на правую таблицу. Условие, поставленное не в том месте, превращает левое соединение в обычное и молча выбрасывает строки, которые должны были остаться с пустыми значениями. Строк становится меньше, а не больше, и это ещё труднее заметить: недостача не бросается в глаза так, как удвоение.
Как проверять
Проверять надо не на трёх записях. На маленьком наборе связь один ко многим часто вырождается в один к одному, и ошибка не проявляется.
Рабочий приём: возьмите одну сущность, у которой заведомо несколько связанных записей, и прогоните запрос только по ней. Ответ должен совпасть с посчитанным вручную. Это дольше, чем посмотреть на первые строки результата, и надёжнее на порядок.
Второй приём — сверять итог двумя способами. Сумма, полученная запросом с соединениями, и сумма, полученная без них по одной таблице, должны совпадать. Расхождение — это и есть размножение, найденное до того, как его увидел заказчик отчёта.
Такую сверку стоит оставлять в коде, а не выполнять разово. Отчёт, который сам сравнивает итог двумя путями и жалуется при расхождении, ловит проблему в тот день, когда она появилась, — а появляется она обычно не при написании запроса, а через полгода, когда в данные добавили новую связь и никто не подумал про старый отчёт.
Позиция редакции
Мы считаем, что кратность связи надо проговаривать вслух до написания запроса, а не выяснять по расхождению в отчёте. Практический вывод: если в запросе есть соединение и агрегат, сверьте число строк до и после — это одна проверка, которая ловит почти все случаи.
Мы неправы, если все связи в вашей схеме строго один к одному и это гарантировано ограничениями базы, а не устной договорённостью: там перечисленные предосторожности — лишняя работа, а запрос честно читается как приклеивание колонок, каковым он и является.