При работе с дополнительными реквизитами и сведениями в 1С программисты часто сталкиваются с задачей "разворачивания" строк в колонки (так называемый Pivoting). Например, когда данные по различным свойствам сотрудника (серия паспорта, номер, дата выдачи) хранятся в виде отдельных строк в подчиненной табличной части, а нам необходимо собрать их в одну красивую строку отчета, где каждому сотруднику соответствует ровно одна запись со всеми заполненными реквизитами.
Если решать эту задачу «в лоб» через объединение запросов (ОБЪЕДИНИТЬ или ОБЪЕДИНИТЬ ВСЕ), то на выходе мы получим «шахматку» — таблицу, где для одного и того же сотрудника будет несколько строк, в каждой из которых заполнено только одно свойство, а остальные содержат пустые строки или пустые даты. В этой статье мы подробно разберем, как правильно сгруппировать такие данные, а также рассмотрим более производительные альтернативные варианты построения подобных запросов.
Это классический и самый простой способ «схлопывания» строк, который отлично подходит для решения задачи, описанной на форуме. Суть метода заключается в том, что пустая строка "" или пустая дата ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0) в базе данных всегда лексикографически меньше, чем любая заполненная строка или реальная дата. Применив группировку по ключевому полю и агрегатную функцию МАКСИМУМ к остальным полям, мы заставим СУБД отбросить все пустые значения и оставить только заполненные.
Рассмотрим, как будет выглядеть итоговый запрос из примера на форуме после добавления группировки во второй пакет запроса:
ВЫБРАТЬ
Свойства.Код КАК Код,
МАКСИМУМ(Свойства.Серия) КАК Серия,
МАКСИМУМ(Свойства.Номер) КАК Номер,
МАКСИМУМ(Свойства.Категории) КАК Категории,
МАКСИМУМ(Свойства.Кем) КАК Кем,
МАКСИМУМ(Свойства.ДатаНачала) КАК ДатаНачала,
МАКСИМУМ(Свойства.ДатаОкончания) КАК ДатаОкончания
ИЗ
Свойства КАК Свойства
СГРУППИРОВАТЬ ПО
Свойства.Код
Важные нюансы использования функции МАКСИМУМ:
МАКСИМУМ молча выберет то значение, которое больше по алфавиту. Ошибки при этом не возникнет, но часть данных будет скрыта от пользователя.Значение в табличной части ДополнительныеРеквизиты обычно имеет составной тип. При использовании МАКСИМУМ СУБД сравнивает значения между собой. Если типы данных в колонке различаются (например, строка и число), поведение СУБД может быть непредсказуемым. Чтобы избежать проблем, рекомендуется приводить значение к нужному типу с помощью конструкции ВЫРАЗИТЬ.ВЫРАЗИТЬ(Свойства.Значение КАК СТРОКА(1000)).Хотя первый метод полностью рабочий, исходная схема запроса с шестью блоками ОБЪЕДИНИТЬ крайне неэффективна. Платформа 1С для выполнения такого запроса будет вынуждена шесть раз сканировать одну и ту же таблицу дополнительных реквизитов. Мы можем значительно оптимизировать этот процесс, выполнив всего один проход по таблице с помощью конструкции ВЫБОР КОГДА (CASE в SQL).
Проанализируем пример более изящного и быстрого запроса:
ВЫБРАТЬ
ДопРеквизиты.Ссылка КАК Сотрудник,
МАКСИМУМ(ВЫБОР
КОГДА ДопРеквизиты.Свойство = &СвойствоСерия
ТОГДА ДопРеквизиты.Значение
ИНАЧЕ ""
КОНЕЦ) КАК Серия,
МАКСИМУМ(ВЫБОР
КОГДА ДопРеквизиты.Свойство = &СвойствоНомер
ТОГДА ДопРеквизиты.Значение
ИНАЧЕ ""
КОНЕЦ) КАК Номер,
МАКСИМУМ(ВЫБОР
КОГДА ДопРеквизиты.Свойство = &СвойствоДатаНачала
ТОГДА ВЫРАЗИТЬ(ДопРеквизиты.Значение КАК ДАТАВРЕМЯ)
ИНАЧЕ ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)
КОНЕЦ) КАК ДатаНачала
ИЗ
Справочник.Сотрудники.ДополнительныеРеквизиты КАК ДопРеквизиты
ГДЕ
ДопРеквизиты.Свойство В(&СписокНужныхСвойств)
СГРУППИРОВАТЬ ПО
ДопРеквизиты.Ссылка
Этот подход работает в разы быстрее, так как СУБД накладывает фильтр по списку свойств &СписокНужныхСвойств, читает данные один раз, а затем "на лету" распределяет их по колонкам и сразу выполняет группировку.
Еще один популярный вариант решения задачи — последовательное присоединение таблицы дополнительных реквизитов к основной таблице сотрудников. Этот метод исключает необходимость последующей группировки, так как мы сразу получаем одну строку для каждого сотрудника.
Посмотрим на пример реализации такого запроса:
ВЫБРАТЬ
Сотрудники.Ссылка КАК Сотрудник,
ЕСТЬNULL(ТаблицаСерия.Значение, "") КАК Серия,
ЕСТЬNULL(ТаблицаНомер.Значение, "") КАК Номер
ИЗ
Справочник.Сотрудники КАК Сотрудники
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Сотрудники.ДополнительныеРеквизиты КАК ТаблицаСерия
ПО Сотрудники.Ссылка = ТаблицаСерия.Ссылка
И ТаблицаСерия.Свойство = &СвойствоСерия
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Сотрудники.ДополнительныеРеквизиты КАК ТаблицаНомер
ПО Сотрудники.Ссылка = ТаблицаНомер.Ссылка
И ТаблицаНомер.Свойство = &СвойствоНомер
Преимущества метода: запрос очень легко читать и отлаживать. Нет агрегатных функций, что упрощает понимание логики.
Ограничения: если вам нужно вытащить большое количество свойств (более 5–7), обилие левых соединений (LEFT JOIN) может перегрузить планировщик запросов СУБД, что приведет к снижению производительности. В таких случаях лучше использовать Решение №2.
В обсуждении на форуме справедливо заметили, что конструкция вида Свойство.Наименование = &Наименование1 — это серьезная ошибка проектирования (как выразился один из участников: "за такое надо линейкой по пальцам").
Выясним причины, почему так делать нельзя:
Свойство.Наименование заставляет СУБД делать неявное соединение с таблицей плана видов характеристик (или справочника свойств) при проверке каждой строки. Это колоссально замедляет запрос.Как сделать правильно? Всегда передавайте в параметры запроса сами объекты (ссылки на элементы свойств ПВХ/Справочника), которые вы можете получить программно, либо используйте встроенные механизмы БСП (Библиотеки стандартных подсистем), если конфигурация построена на её основе. Например, механизм "Характеристики" в СКД позволяет выводить дополнительные реквизиты в отчеты вообще без ручного написания сложных запросов.