При разработке отчетов в системе компоновки данных (СКД) программисты часто сталкиваются с задачей транспонирования данных. Типичный пример: в базе данных хранится история событий (например, медицинских осмотров сотрудников), где каждое событие — это отдельная строка с датой. Нам же требуется вывести эти данные в виде горизонтальной таблицы, где для каждого сотрудника будет выделено ровно две (или более) колонки под даты: «Дата первого осмотра» и «Дата второго осмотра».
В этой статье мы подробно разберем, как решить эту задачу несколькими способами. Мы проанализируем базовый вариант с форума, выявим его критические недостатки, а затем изучим два профессиональных и надежных метода: классический Pivot-запрос с нумерацией строк и использование встроенных возможностей кросс-таблицы СКД.
Представим, что исходные данные в нашей временной таблице ВТ_Осмотры выглядят следующим образом:
| Физическое лицо | Дата осмотра |
|---|---|
| Иванов И.И. | 01.10.2023 |
| Иванов И.И. | 15.10.2023 |
| Петров П.П. | 05.10.2023 |
| Сидоров С.С. | 10.10.2023 |
| Сидоров С.С. | 20.10.2023 |
| Сидоров С.С. | 25.10.2023 |
Наша цель — получить на выходе структуру, в которой каждому физическому лицу соответствует ровно одна строка, а даты распределены по двум колонкам:
| Физическое лицо | Дата 1 | Дата 2 |
|---|---|---|
| Иванов И.И. | 01.10.2023 | 15.10.2023 |
| Петров П.П. | 05.10.2023 | Пусто (NULL) |
| Сидоров С.С. | 10.10.2023 | 20.10.2023 |
---
Рассмотрим вариант решения, предложенный в исходном обсуждении на форуме. Идея заключается в том, чтобы взять исходную таблицу с датами и соединить ее саму с собой по условию «меньше».
Давайте посмотрим на пример реализации такого запроса:
ВЫБРАТЬ
ЗаключениеМедицинскогоОсмотра.ДатаОсмотра КАК ДатаОсмотра,
ЗаключениеМедицинскогоОсмотра.ФизическоеЛицо КАК ФизическоеЛицо
ПОМЕСТИТЬ ВТ_Осмотры
ИЗ
Документ.ЗаключениеМедицинскогоОсмотра КАК ЗаключениеМедицинскогоОсмотра
ГДЕ
ЗаключениеМедицинскогоОсмотра.Проведен;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ1.ФизическоеЛицо КАК ФизическоеЛицо,
ВТ1.ДатаОсмотра КАК Дата1,
ВТ2.ДатаОсмотра КАК Дата2
ИЗ
ВТ_Осмотры КАК ВТ1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Осмотры КАК ВТ2
ПО ВТ1.ФизическоеЛицо = ВТ2.ФизическоеЛицо
И ВТ1.ДатаОсмотра < ВТ2.ДатаОсмотра
ГДЕ
НЕ ВТ2.ДатаОсмотра ЕСТЬ NULL
Проанализируем работу этого запроса и выясним, почему данный подход крайне не рекомендуется использовать в реальных задачах.
Критические недостатки метода:
ГДЕ НЕ ВТ2.ДатаОсмотра ЕСТЬ NULL. Если у сотрудника (например, у Петрова П.П. из нашего примера) был только один медицинский осмотр, то левое соединение не найдет для него запись ВТ2 с большей датой. В результате поле ВТ2.ДатаОсмотра примет значение NULL. Из-за жесткого условия в секции ГДЕ этот сотрудник будет полностью исключен из итогового отчета.ВТ1.ДатаОсмотра < ВТ2.ДатаОсмотра породит избыточные строки. Мы получим комбинации: (10.10 и 20.10), (10.10 и 25.10), (20.10 и 25.10). Отчет станет некорректным и нечитаемым.Таким образом, данное решение работоспособно исключительно в идеальных условиях, когда у каждого сотрудника в базе строго два документа. В реальной учетной системе такие условия гарантировать невозможно.
---
Чтобы решить задачу надежно и не потерять сотрудников с одной датой осмотра, нам необходимо применить классический паттерн проектирования SQL-запросов — динамическую нумерацию строк внутри группы с последующей группировкой (сворачиванием) данных.
Поскольку в языке запросов 1С отсутствует аналитическая функция ROW_NUMBER() OVER (PARTITION BY ...), мы реализуем нумерацию самостоятельно с помощью левого соединения таблицы самой с собой по условию >=.
Разберем этот процесс по шагам.
Мы соединяем таблицу ВТ_Осмотры с ее копией ВТ_Сравнение по сотруднику и условию, что дата в основной таблице больше или равна дате во вспомогательной. Количество совпадений по этому условию и даст нам порядковый номер строки.
ВЫБРАТЬ
ВТ.ФизическоеЛицо КАК ФизическоеЛицо,
ВТ.ДатаОсмотра КАК ДатаОсмотра,
СУММА(1) КАК НомерОсмотра
ПОМЕСТИТЬ ВТ_СНумерацией
ИЗ
ВТ_Осмотры КАК ВТ
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Осмотры КАК ВТ_Сравнение
ПО ВТ.ФизическоеЛицо = ВТ_Сравнение.ФизическоеЛицо
И ВТ.ДатаОсмотра >= ВТ_Сравнение.ДатаОсмотра
СГРУППИРОВАТЬ ПО
ВТ.ФизическоеЛицо,
ВТ.ДатаОсмотра
;
Давайте посмотрим, как преобразуются наши тестовые данные после этого шага:
НомерОсмотра = 1 (так как только одна дата удовлетворяет условию 01.10 >= дата_сравнения). Дата 15.10.2023 получит НомерОсмотра = 2 (условию удовлетворяют две даты: 15.10 >= 01.10 и 15.10 >= 15.10).НомерОсмотра = 1.Теперь мы можем применить конструкцию ВЫБОР для распределения дат по колонкам в зависимости от их порядкового номера, а затем «схлопнуть» строки с помощью агрегатной функции МАКСИМУМ.
ВЫБРАТЬ
ВТ_СНумерацией.ФизическоеЛицо КАК ФизическоеЛицо,
МАКСИМУМ(ВЫБОР КОГДА ВТ_СНумерацией.НомерОсмотра = 1 ТОГДА ВТ_СНумерацией.ДатаОсмотра ИНАЧЕ НЕОПРЕДЕЛЕНО КОНЕЦ) КАК Дата1,
МАКСИМУМ(ВЫБОР КОГДА ВТ_СНумерацией.НомерОсмотра = 2 ТОГДА ВТ_СНумерацией.ДатаОсмотра ИНАЧЕ НЕОПРЕДЕЛЕНО КОНЕЦ) КАК Дата2
ИЗ
ВТ_СНумерацией КАК ВТ_СНумерацией
СГРУППИРОВАТЬ ПО
ВТ_СНумерацией.ФизическоеЛицо
Почему этот подход эффективен:
Дата1, а в колонке Дата2 выведется пустое значение (NULL или Неопределено).Дата3, просто дописав соответствующую строку в запрос).---
Система компоновки данных 1С проектировалась как мощный аналитический инструмент, способный выполнять разворот строк в колонки «на лету» без усложнения структуры SQL-запроса. Если мы используем СКД, то нам не нужно жестко прописывать колонки Дата1 и Дата2 в тексте запроса. Платформа сделает это автоматически при выводе отчета.
Рассмотрим по шагам, как настроить такую схему компоновки.
В схеме компоновки данных мы используем запрос с нумерацией строк, полученный на Шаге 1 предыдущего метода. Нам важно передавать в СКД плоскую таблицу, содержащую физическое лицо, дату осмотра и его порядковый номер:
ВЫБРАТЬ
ВТ_СНумерацией.ФизическоеЛицо КАК ФизическоеЛицо,
ВТ_СНумерацией.ДатаОсмотра КАК ДатаОсмотра,
ВТ_СНумерацией.НомерОсмотра КАК НомерОсмотра
ИЗ
ВТ_СНумерацией КАК ВТ_СНумерацией
Для того чтобы СКД понимала, какие данные выводить на пересечении строк и колонок кросс-таблицы, нам необходимо определить ресурсы:
ДатаОсмотра в список ресурсов.Максимум(ДатаОсмотра).Вместо стандартного линейного детального списка мы настроим структуру отчета в виде двумерной таблицы:
ФизическоеЛицо.НомерОсмотра.ДатаОсмотра.При формировании такого отчета СКД автоматически создаст столько колонок, сколько уникальных номеров осмотров присутствует в нашей выборке. Если у всех сотрудников максимум по два осмотра, колонок с датами будет ровно две. Если у кого-то появится третий осмотр, автоматически создастся колонка «3», не ломая структуру отчета.
Выводить в шапку отчета простые цифры «1» или «2» не совсем красиво с точки зрения пользователя. Мы можем легко переопределить представление этих колонок:
НомерОсмотра."Дата " + Строка(НомерОсмотра).Теперь в шапке отчета вместо сухих чисел пользователь увидит понятные названия колонок: «Дата 1», «Дата 2» и так далее.
---
Мы детально изучили три способа разворота дат в колонки отчета. Выбор конкретного метода зависит от архитектуры вашего решения: