При разработке отчетов на системе компоновки данных (СКД) программисты часто сталкиваются с задачей построения кросс-таблиц. Типичный пример — вывод расписания или графиков работы. Предположим, у нас есть измерения Филиал, День (день недели или дата) и ресурсное поле Час (время работы). Если в рамках одного дня у филиала есть несколько разных рабочих часов (например, в понедельник филиал работает в 10:00 и в 17:00), то стандартная кросс-таблица попытается "схлопнуть" эти записи в одну строку, применив агрегатную функцию (например, максимум), либо выведет их в разные строки, но с пустыми ячейками.
В этой статье мы подробно разберем, как обойти это ограничение платформы и заставить СКД выводить несколько детальных записей в рамках одной группировки строки. Мы проанализируем различные подходы: от классической нумерации внутри запроса до современных платформенных механизмов.
Если мы настроим структуру СКД стандартным образом, где строками будет являться Филиал, а колонками — День, то на пересечении мы получим только одну ячейку. СКД требует, чтобы в пересечении находился ресурс. Если мы сделаем ресурс из поля Час с функцией Максимум(Час), то для понедельника выведется только одно значение — "17", а значение "10" потеряется. Если же мы добавим Час в группировку строк (сделаем иерархию Филиал -> Час), то отчет разрастется по вертикали, и данные по другим дням недели будут разнесены по разным строкам, что сделает таблицу нечитаемой.
Для красивого "уплотненного" вывода нам необходимо ввести дополнительное искусственное поле группировки строк — Номер строки по порядку (НомПП) в разрезе комбинации Филиал + День. Тогда структура строк нашей таблицы примет вид: Филиал -> НомПП, а колонками останется День. В этом случае СКД корректно распределит данные по строкам 1, 2, 3 и так далее.
Рассмотрим базовый алгоритм нумерации строк, который можно использовать в любом SQL-запросе 1С. Суть метода заключается в том, что мы соединяем таблицу саму с собой по ключевым полям связи, а условием соединения делаем неравенство по значению сортировки (в данном случае — по часам).
Проанализируем пример запроса, который формирует нумерацию:
ВЫБРАТЬ
Расписание.День КАК День,
Расписание.Филиал КАК Филиал,
Расписание.Час КАК Час
ПОМЕСТИТЬ ВТ_Расписание
ИЗ
РегистрСведений.Расписание КАК Расписание
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.Филиал КАК Филиал,
Т1.День КАК День,
Т1.Час КАК Час,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Т2.Час) КАК НомПП
ИЗ
ВТ_Расписание КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Расписание КАК Т2
ПО Т1.Филиал = Т2.Филиал
И Т1.День = Т2.День
И Т1.Час >= Т2.Час
ГРУППИРОВАТЬ ПО
Т1.Филиал,
Т1.День,
Т1.Час
Как работает этот запрос: для каждой записи из левой таблицы (Т1) мы подбираем все записи из правой таблицы (Т2), у которых филиал и день совпадают, а значение часов меньше или равно текущему. Сгруппировав результат и подсчитав количество таких сопоставленных записей через КОЛИЧЕСТВО(...), мы получаем порядковый номер часа для конкретного филиала в конкретный день.
После этого в настройках СКД мы задаем следующую структуру:
Филиал.НомПП (номер строки).День.Час добавляем в панель "Ресурсы" с выражением Максимум(Час).Такой подход отлично решит задачу, если все значения часов внутри одного дня гарантированно уникальны.
У предыдущего метода есть существенный недостаток, который часто упускают из виду. Что произойдет, если в один и тот же день у одного филиала будут две записи с абсолютно одинаковым количеством часов? Например, два разных занятия или события, назначенные на "10:00". В этом случае условие соединения Т1.Час >= Т2.Час вернет одинаковый номер строки для обоих событий, и в итоговом отчете они сольются в одну запись.
Чтобы решить эту проблему, нам нужен гарантированно уникальный идентификатор для каждой строки исходных данных. Начиная с платформы 1С 8.3.14, в языке запросов появилась замечательная функция АВТОНОМЕРЗАПИСИ(), которая позволяет пронумеровать все строки во временной таблице еще до выполнения соединений.
Разберем по шагам, как написать такой запрос:
ВЫБРАТЬ
Расписание.Филиал КАК Филиал,
Расписание.День КАК День,
Расписание.Час КАК Час,
АВТОНОМЕРЗАПИСИ() КАК УникальныйИд
ПОМЕСТИТЬ ВТ_Исходные
ИЗ
РегистрСведений.Расписание КАК Расписание
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.Филиал КАК Филиал,
Т1.День КАК День,
Т1.Час КАК Час,
КОЛИЧЕСТВО(Т2.УникальныйИд) КАК НомПП
ИЗ
ВТ_Исходные КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Исходные КАК Т2
ПО Т1.Филиал = Т2.Филиал
И Т1.День = Т2.День
И Т1.УникальныйИд >= Т2.УникальныйИд
ГРУППИРОВАТЬ ПО
Т1.Филиал,
Т1.День,
Т1.Час,
Т1.УникальныйИд
Важная деталь работы функции: в зависимости от СУБД, функция АВТОНОМЕРЗАПИСИ() может генерировать непоследовательные номера (например, начать отсчет с произвольного числа). Однако при левом соединении по условию неравенства >= мы считаем именно количество подходящих под условие записей. Независимо от того, какие именно числа сгенерировала функция, количество записей всегда вернет последовательный ряд чисел: 1, 2, 3 и т.д. Это гарантирует стабильную работу отчета без дублирования или потери данных.
Предыдущие варианты требовали усложнения SQL-запроса, что может негативно сказаться на производительности базы данных при больших объемах информации. Если у вас нет жесткого требования выводить значения строго в разных строках сетки, а достаточно просто показать список часов в одной ячейке, идеальным выходом будет использование встроенной функции СКД СоединитьСтроки().
Рассмотрим этот вариант подробнее. Нам вообще не придется изменять запрос или нумеровать строки. Мы делаем простейший выбор данных из регистра:
ВЫБРАТЬ
Расписание.Филиал КАК Филиал,
Расписание.День КАК День,
Расписание.Час КАК Час
ИЗ
РегистрСведений.Расписание КАК Расписание
Затем в схеме компоновки данных переходим на вкладку Ресурсы и настраиваем расчет поля Час:
Час в список ресурсов.СоединитьСтроки(Час, ", ", "Час").Филиал и День).На выходе мы получим изящный компактный отчет, в котором для каждого филиала будет ровно одна строка, а на пересечении с днями недели все часы запишутся аккуратно через запятую (например: "10, 15, 17"). Это решение работает в разы быстрее любых запросов с левыми соединениями.
Левое соединение больших таблиц по условию "больше или равно" имеет квадратичную сложность алгоритма — O(N²). Если в вашем регистре расписания хранятся десятки тысяч записей, отчет может начать сильно "зависать" или вызывать избыточную нагрузку на сервер СУБД.
Чтобы избежать проблем с производительностью, мы можем перенести расчет порядкового номера строки из SQL-запроса в код на встроенном языке 1С. Алгоритм в коде выполняется линейно за время O(N), что гарантирует мгновенный расчет.
Проанализируем, как реализовать такую схему программно:
// 1. Получаем исходные данные простым и быстрым запросом
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Расписание.Филиал КАК Филиал,
| Расписание.День КАК День,
| Расписание.Час КАК Час
|ИЗ
| РегистрСведений.Расписание КАК Расписание
|УПОРЯДОЧИТЬ ПО
| Филиал,
| День,
| Час";
ТаблицаДанных = Запрос.Выполнить().Выгрузить();
// 2. Добавляем колонку для номера строки
ТаблицаДанных.Колонки.Добавить("НомПП", Новый ОписаниеТипов("Число"));
// 3. Заполняем номера строк в цикле
ТекущийФилиал = Неопределено;
ТекущийДень = Неопределено;
СчетчикСтрок = 0;
Для Каждого Строка Из ТаблицаДанных Цикл
Если Строка.Филиал <> ТекущийФилиал Или Строка.День <> ТекущийДень Тогда
ТекущийФилиал = Строка.Филиал;
ТекущийДень = Строка.День;
СчетчикСтрок = 1;
Иначе
СчетчикСтрок = СчетчикСтрок + 1;
КонецЕсли;
Строка.НомПП = СчетчикСтрок;
КонецЦикла;
// 4. Передаем готовую таблицу в СКД как внешний набор данных
ВнешниеНаборыДанных = Новый Структура;
ВнешниеНаборыДанных.Вставить("ТаблицаРасписания", ТаблицаДанных);
СхемаКомпоновкиДанных = ПолучитьМакет("ОсновнаяСхемаКомпоновкиДанных");
Настройки = СхемаКомпоновкиДанных.НастройкиПоУмолчанию;
КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновкиДанных, Настройки);
ПроцессорКомпоновкиДанных = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновкиДанных.Инициализировать(МакетКомпоновки, ВнешниеНаборыДанных);
// Далее выводим отчет в табличный документ стандартным образом
В самой СКД в этом случае мы описываем Набор данных - объект с именем ТаблицаРасписания и настраиваем структуру кросс-таблицы с группировкой строк по полям Филиал и вычисленному в коде НомПП.
Мы рассмотрели четыре различных способа решения задачи "уплотнения" строк в кросс-таблицах СКД. Какой из них выбрать в реальной работе?
АВТОНОМЕРЗАПИСИ(), чтобы избежать склеивания одинаковых значений).СоединитьСтроки(). Это самый простой в поддержке и производительный метод.