Почему данные двоятся в запросе 1С при работе с несколькими таблицами и как правильно их связать?

← На главную

При работе с запросами в 1С разработчики часто сталкиваются с проблемой задвоения данных, которая может существенно искажать результаты и замедлять работу системы. Давайте рассмотрим типичный случай, когда данные задваиваются, и разберем, как эффективно устранить эту проблему, используя правильные методы соединения таблиц и агрегатные функции.

Наш читатель столкнулся с ситуацией, когда его запрос, казалось бы, простой по своей сути, возвращал гораздо больше строк, чем ожидалось, и данные при этом дублировались. Изначальный запрос выглядел следующим образом:


Запрос = Новый Запрос;
Запрос.Текст = "ВЫБРАТЬ
    | ЗаказПокупателя.Ссылка,
    | УстановкаЗаказовВМаршрут.Ссылка КАК Ссылка1
    |ИЗ
    | Документ.ЗаказПокупателя КАК ЗаказПокупателя,
    | Документ.УстановкаЗаказовВМаршрут КАК УстановкаЗаказовВМаршрут
    |ГДЕ
    | ЗаказПокупателя.Проведен
    | И ЗаказПокупателя.Дата МЕЖДУ &МесяцНазад И &МесяцВперед
    | И УстановкаЗаказовВМаршрут.Проведен";
Запрос.УстановитьПараметр("МесяцНазад", МесяцНазад);
Запрос.УстановитьПараметр("МесяцВперед", ТекущаяДата);
РезультатЗапроса = Запрос.Выполнить;
Выборка = РезультатЗапроса.Выбрать("Ссылка");
кол = 0;
//кол2 = 0;
кол3 = 0;
//кол4 = 0;
кол5 = 0;
кол6 = 0;
кол7 = "";
строкколсамовывоз = 0;
строкколсамовывоз2 = 0;
Пока Выборка.Следующий Цикл
    Если Выборка.Ссылка.Статус = Перечисления.СтатусЗаказа.ГотовКОтбору тогда
        кол = кол+1;
    КонецЕсли;
    //Если Выборка.Ссылка.Статус = Перечисления.СтатусЗаказа.Сформирован тогда
    //    кол2 = кол2+1;
    //КонецЕсли;
    Если Выборка.Ссылка.Статус = Перечисления.СтатусЗаказа.НаСогласовании тогда
        кол6 = кол6+1;
    КонецЕсли;
    //Если Выборка.Ссылка.Статус = Перечисления.СтатусЗаказа.Выполнен тогда
    //    кол4 = кол4+1;
    //КонецЕсли;
    Если Выборка.Ссылка.Статус = Перечисления.СтатусЗаказа.ВОтборе тогда
        кол5 = кол5+1;
    КонецЕсли;
    Если Выборка.Ссылка.Маршрут.Наименование = "Самовывоз (Склад)" тогда
        строкколсамовывоз = строкколсамовывоз + 1;
    КонецЕсли;
    Если Выборка.Ссылка.Маршрут.Наименование = "Самовывоз (Кедышко)" тогда
        строкколсамовывоз2 = строкколсамовывоз2 + 1;
    КонецЕсли;
    да = (Формат(Выборка.Ссылка.Дата, "ДЛФ=Д"));
    текда = (Формат(ТекущаяДата, "ДЛФ=Д"));
    Если Выборка.Ссылка.Статус = Перечисления.СтатусЗаказа.Собран и да = текда тогда
        кол3 = кол3+1;
    КонецЕсли;
    дат = (Формат(Выборка.Ссылка1.Дата, "ДЛФ=Д"));
    текдат = (Формат(ТекущаяДата, "ДЛФ=Д"));
    Если Выборка.Ссылка1.Статус = Перечисления.СтатусМаршрута.Готов И дат = текдат тогда
        кол7 = кол7+Выборка.Ссылка1.МаршрутГород.Наименование;
    КонецЕсли;
КонецЦикла;

Первое, что мы замечаем в этом коде, — это то, что две таблицы Документ.ЗаказПокупателя и Документ.УстановкаЗаказовВМаршрут перечислены в секции ИЗ через запятую, но между ними нет явного условия связи (оператора СОЕДИНЕНИЕ и условия ПО). Именно это является ключевой причиной задвоения данных.

Понимание причины задвоения: декартово произведение

Когда мы перечисляем таблицы в секции ИЗ без указания типа соединения и условия связи, система автоматически выполняет перекрестное соединение (CROSS JOIN), также известное как декартово произведение. Это означает, что каждая строка из первой таблицы соединяется с каждой строкой из второй таблицы. Если в таблице А 1000 строк, а в таблице Б — 500 строк, то результатом декартова произведения будет 1000 * 500 = 500 000 строк, что во много раз больше, чем необходимо, и совершенно не отражает реальную связь между данными. Именно из-за этого данные и двоятся, а точнее, многократно повторяются.

Как устранить задвоение: используем операторы СОЕДИНЕНИЕ

Для корректного получения данных из нескольких таблиц нам необходимо использовать операторы СОЕДИНЕНИЕ (JOIN) с четко определенным условием связи между таблицами. В 1С, как и в большинстве СУБД, существуют различные виды соединений, каждый из которых подходит для своей ситуации.

1. ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВНУТРЕННЕЕ СОЕДИНЕНИЕ / INNER JOIN)

Это самый распространенный тип соединения. Он возвращает только те строки, для которых найдено совпадение в обеих таблицах по указанному условию. Если соответствия в одной из таблиц нет, строка не попадет в результат.

Пример:


ВЫБРАТЬ
    ЗаказПокупателя.Ссылка,
    УстановкаЗаказовВМаршрут.Ссылка КАК Ссылка1
ИЗ
    Документ.ЗаказПокупателя КАК ЗаказПокупателя
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.УстановкаЗаказовВМаршрут КАК УстановкаЗаказовВМаршрут
        ПО ЗаказПокупателя.Ссылка = УстановкаЗаказовВМаршрут.ЗаказПокупателя // Предполагаем, что в УстановкаЗаказовВМаршрут есть реквизит ЗаказПокупателя
ГДЕ
    ЗаказПокупателя.Проведен
    И ЗаказПокупателя.Дата МЕЖДУ &МесяцНазад И &МесяцВперед
    И УстановкаЗаказовВМаршрут.Проведен

В этом примере мы явно указываем, что нам нужны только те записи, где есть соответствующая установка маршрута для заказа покупателя. Условие ПО ЗаказПокупателя.Ссылка = УстановкаЗаказовВМаршрут.ЗаказПокупателя является критически важным, поскольку оно определяет, как строки из одной таблицы соотносятся со строками из другой.

2. ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ (ЛЕВОЕ СОЕДИНЕНИЕ / LEFT [OUTER] JOIN)

Этот тип соединения возвращает все записи из левой таблицы и только те совпадающие записи из правой таблицы, для которых нашлось соответствие по условию. Если для записи из левой таблицы нет совпадения в правой, поля из правой таблицы будут содержать значение NULL (НЕОПРЕДЕЛЕНО в 1С). Это очень полезно, когда нам нужно получить все заказы покупателя, даже если для некоторых из них еще нет установки в маршрут.

Пример:


ВЫБРАТЬ
    ЗаказПокупателя.Ссылка,
    УстановкаЗаказовВМаршрут.Ссылка КАК Ссылка1
ИЗ
    Документ.ЗаказПокупателя КАК ЗаказПокупателя
        ЛЕВОЕ СОЕДИНЕНИЕ Документ.УстановкаЗаказовВМаршрут КАК УстановкаЗаказовВМаршрут
        ПО ЗаказПокупателя.Ссылка = УстановкаЗаказовВМаршрут.ЗаказПокупателя
ГДЕ
    ЗаказПокупателя.Проведен
    И ЗаказПокупателя.Дата МЕЖДУ &МесяцНазад И &МесяцВперед
    И (УстановкаЗаказовВМаршрут.Ссылка ЕСТЬ NULL ИЛИ УстановкаЗаказовВМаршрут.Проведен) // Важно! Условие для правой таблицы, если хотим получить все заказы

Здесь мы получим все проведенные заказы покупателя в заданном периоде. Для тех заказов, у которых нет соответствующей установки в маршрут, поле Ссылка1 будет NULL. В условии ГДЕ мы должны учесть, что для строк, где нет соответствия в правой таблице, поля правой таблицы будут NULL, иначе фильтр по УстановкаЗаказовВМаршрут.Проведен отсечет эти строки.

3. ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ (ПРАВОЕ СОЕДИНЕНИЕ / RIGHT [OUTER] JOIN)

Аналогично левому, но возвращает все записи из правой таблицы и совпадающие из левой. Если для записи из правой таблицы нет совпадения в левой, поля из левой таблицы будут содержать NULL. Используется реже, так как обычно удобнее переставлять таблицы местами и использовать левое соединение.

4. ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ (ПОЛНОЕ СОЕДИНЕНИЕ / FULL [OUTER] JOIN)

Возвращает все записи из обеих таблиц, дополняя значениями NULL там, где нет совпадений. Это соединение используется, когда нам нужны все данные из обеих таблиц, независимо от того, есть ли для них соответствия в другой таблице.

Оптимизация запроса и агрегация данных

Помимо исправления задвоения данных, давайте проанализируем исходный код и выявим возможности для его оптимизации. В оригинальном коде большая часть логики по подсчету и группировке данных выполняется в цикле Пока Выборка.Следующий Цикл на стороне 1С. Это неэффективно, особенно при большом объеме данных, поскольку требует передачи всех строк запроса в память клиента и последующей обработки. Гораздо лучше перенести эту логику непосредственно в запрос, используя агрегатные функции и группировку.

Агрегатные функции (КОЛИЧЕСТВО, СУММА, МАКСИМУМ, МИНИМУМ, СРЕДНЕЕ) позволяют выполнять вычисления над группами строк прямо в запросе, возвращая один результат для каждой группы. Для формирования таких групп используется оператор СГРУППИРОВАТЬ ПО.

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


// Определим параметры для запроса
МесяцНазад = НачалоМесяца(ТекущаяДата()); // Пример: начало текущего месяца
ТекущаяДата = КонецДня(ТекущаяДата()); // Пример: конец текущего дня

Запрос = Новый Запрос;
Запрос.Текст = "ВЫБРАТЬ
    | СУММА(ВЫБОР КОГДА ЗаказПокупателя.Статус = &СтатусГотовКОтбору ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК КолГотовКОтбору,
    | СУММА(ВЫБОР КОГДА ЗаказПокупателя.Статус = &СтатусНаСогласовании ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК КолНаСогласовании,
    | СУММА(ВЫБОР КОГДА ЗаказПокупателя.Статус = &СтатусВОтборе ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК КолВОтборе,
    | СУММА(ВЫБОР КОГДА ЗаказПокупателя.Маршрут.Наименование = &НаименованиеМаршрутаСамовывозСклад ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК КолСамовывозСклад,
    | СУММА(ВЫБОР КОГДА ЗаказПокупателя.Маршрут.Наименование = &НаименованиеМаршрутаСамовывозКедышко ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК КолСамовывозКедышко,
    | СУММА(ВЫБОР КОГДА ЗаказПокупателя.Статус = &СтатусСобран И НАЧАЛОПЕРИОДА(ЗаказПокупателя.Дата, ДЕНЬ) = НАЧАЛОПЕРИОДА(&ТекущаяДата, ДЕНЬ) ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК КолСобранСегодня,
    | ВЫБОР
    |    КОГДА УстановкаЗаказовВМаршрут.Статус = &СтатусМаршрутаГотов И НАЧАЛОПЕРИОДА(УстановкаЗаказовВМаршрут.Дата, ДЕНЬ) = НАЧАЛОПЕРИОДА(&ТекущаяДата, ДЕНЬ)
    |    ТОГДА УстановкаЗаказовВМаршрут.МаршрутГород.Наименование
    |    ИНАЧЕ NULL
    | КОНЕЦ КАК МаршрутГородСегодня
    |ИЗ
    | Документ.ЗаказПокупателя КАК ЗаказПокупателя
    |    ЛЕВОЕ СОЕДИНЕНИЕ Документ.УстановкаЗаказовВМаршрут КАК УстановкаЗаказовВМаршрут
    |    ПО ЗаказПокупателя.Ссылка = УстановкаЗаказовВМаршрут.ЗаказПокупателя // Предполагаем такую связь!
    |ГДЕ
    | ЗаказПокупателя.Проведен
    | И ЗаказПокупателя.Дата МЕЖДУ &МесяцНазад И &МесяцВперед
    | И (УстановкаЗаказовВМаршрут.Ссылка ЕСТЬ NULL ИЛИ УстановкаЗаказовВМаршрут.Проведен)
    |
    |СГРУППИРОВАТЬ ПО
    |    ВЫБОР
    |        КОГДА УстановкаЗаказовВМаршрут.Статус = &СтатусМаршрутаГотов И НАЧАЛОПЕРИОДА(УстановкаЗаказовВМаршрут.Дата, ДЕНЬ) = НАЧАЛОПЕРИОДА(&ТекущаяДата, ДЕНЬ)
    |        ТОГДА УстановкаЗаказовВМаршрут.МаршрутГород.Наименование
    |        ИНАЧЕ NULL
    |    КОНЕЦ";

Запрос.УстановитьПараметр("МесяцНазад", НачалоМесяца(ТекущаяДата()));
Запрос.УстановитьПараметр("МесяцВперед", КонецДня(ТекущаяДата()));
Запрос.УстановитьПараметр("ТекущаяДата", ТекущаяДата()); // Передаем текущую дату для сравнения
Запрос.УстановитьПараметр("СтатусГотовКОтбору", Перечисления.СтатусЗаказа.ГотовКОтбору);
Запрос.УстановитьПараметр("СтатусНаСогласовании", Перечисления.СтатусЗаказа.НаСогласовании);
Запрос.УстановитьПараметр("СтатусВОтборе", Перечисления.СтатусЗаказа.ВОтборе);
Запрос.УстановитьПараметр("НаименованиеМаршрутаСамовывозСклад", "Самовывоз (Склад)");
Запрос.УстановитьПараметр("НаименованиеМаршрутаСамовывозКедышко", "Самовывоз (Кедышко)");
Запрос.УстановитьПараметр("СтатусСобран", Перечисления.СтатусЗаказа.Собран);
Запрос.УстановитьПараметр("СтатусМаршрутаГотов", Перечисления.СтатусМаршрута.Готов);

РезультатЗапроса = Запрос.Выполнить();
Выборка = РезультатЗапроса.Выбрать();

Если Выборка.Следующий() Тогда
    // Теперь все счетчики находятся в одной строке выборки (если нет группировки по МаршрутГородСегодня)
    // Если же есть группировка, то будут отдельные строки по каждому городу + NULL
    // Пример получения:
    // кол = Выборка.КолГотовКОтбору;
    // кол6 = Выборка.КолНаСогласовании;
    // кол5 = Выборка.КолВОтборе;
    // строкколсамовывоз = Выборка.КолСамовывозСклад;
    // строкколсамовывоз2 = Выборка.КолСамовывозКедышко;
    // кол3 = Выборка.КолСобранСегодня;
    // кол7 = Выборка.МаршрутГородСегодня;
КонецЕсли;

Давайте разберем изменения, которые мы внесли в оптимизированный запрос:

Правильное соединение: Мы использовали ЛЕВОЕ СОЕДИНЕНИЕ, чтобы получить все заказы покупателя и, если есть, соответствующие установки маршрутов. Условие ПО ЗаказПокупателя.Ссылка = УстановкаЗаказовВМаршрут.ЗаказПокупателя связывает таблицы. Мы также учли, что УстановкаЗаказовВМаршрут.Проведен должно применяться только к существующим записям, чтобы не отсечь заказы без установок маршрута.
Агрегатные функции и условные выражения: Вместо циклов в 1С мы используем СУММА(ВЫБОР КОГДА ... ТОГДА 1 ИНАЧЕ 0 КОНЕЦ). Эта конструкция позволяет подсчитать количество записей, удовлетворяющих определенному условию, прямо в запросе. Например, для подсчета заказов со статусом "ГотовКОтбору": СУММА(ВЫБОР КОГДА ЗаказПокупателя.Статус = &СтатусГотовКОтбору ТОГДА 1 ИНАЧЕ 0 КОНЕЦ).
Параметры запроса: Все статусы и наименования маршрутов передаются как параметры. Это улучшает читаемость и производительность запроса, поскольку компилятор запросов может оптимизировать его выполнение.
Работа с датами: Для сравнения дат мы используем функции НАЧАЛОПЕРИОДА(Дата, ДЕНЬ). Это гарантирует, что мы сравниваем только даты без учета времени.
Группировка: Если нам нужно получить строку МаршрутГород.Наименование, которая, по сути, является строкой, а не числовым агрегатом, и при этом могут быть разные города для разных установок, нам потребуется сгруппировать результат по этому полю. Если вы хотите получить все города через запятую, это задача для более сложных запросов с использованием функций агрегации строк (например, СТРОКА(КОНТАТЕНИРОВАТЬ), если такая функция доступна в конкретной версии платформы и конфигурации или с помощью временных таблиц). В нашем примере мы сгруппировали по полю МаршрутГородСегодня, что означает, что мы получим одну строку на каждый уникальный город (или NULL, если такого города нет).

Такой оптимизированный запрос будет выполняться значительно быстрее, поскольку вся обработка данных происходит на стороне СУБД, а в 1С передается уже свернутый, агрегированный результат.

Важные рекомендации по работе с запросами в 1С

Всегда связывайте таблицы: Никогда не оставляйте таблицы в секции ИЗ несвязанными, если вы не преследуете цель получить декартово произведение (что бывает крайне редко). Всегда используйте СОЕДИНЕНИЕ с условием ПО.
Используйте конструктор запросов: Конструктор запросов в 1С — мощный инструмент, который поможет вам визуально строить запросы, выбирать поля, устанавливать связи и условия. Он значительно упрощает процесс и помогает избегать ошибок.
Оптимизируйте в запросе: Старайтесь максимально переносить логику обработки и агрегации данных в запрос. Циклы по выборке в 1С следует использовать только для специфических задач, которые невозможно или крайне сложно реализовать средствами языка запросов.
Избегайте разыменования через точку в условиях соединения: По возможности, избегайте условий типа Таблица1.Ссылка = Таблица2.Реквизит.СсылкаНаДругуюТаблицу. Лучше сначала присоединить ДругуюТаблицу, а затем уже использовать ее поля.
Временные таблицы и пакетные запросы: Для очень сложных запросов, которые объединяют много данных или требуют нескольких этапов обработки, рассмотрите использование временных таблиц и пакетных запросов. Это позволяет разбить сложную задачу на несколько простых, повышая читаемость и часто производительность.

Надеемся, эти подробные объяснения помогут вам не только исправить текущую проблему с задвоением данных, но и улучшить общие навыки написания эффективных запросов в 1С. Помните, правильные соединения и агрегация данных — это основа производительной и корректной работы ваших систем.