В процессе работы с информационными системами 1С:Предприятие часто возникает необходимость анализировать кадровые данные, в частности, распределение сотрудников по различным демографическим признакам. Одним из таких типовых запросов является отчет, который показывает распределение сотрудников по возрастным группам в разрезе месяцев рождения. Перед нами стоит задача создать такой отчет, используя только один запрос и без какой-либо дополнительной обработки результата запроса в модуле.
Такой отчет будет иметь вид кросс-таблицы, где строками будут возрастные группы (например, 0-10 лет, 11-20 лет и т.д.), а колонками — месяцы года (Январь, Февраль и т.д.), а на пересечении будет отображаться количество сотрудников, соответствующих этим критериям.
Мы рассмотрим два основных подхода к решению этой задачи:
Для нашей задачи мы будем использовать данные из справочника ФизическиеЛица (или Сотрудники, в зависимости от конфигурации), который содержит поле ДатаРождения типа «Дата».
Реализация кросс-отчета (или «сводной таблицы», аналога SQL-оператора PIVOT) исключительно на языке запросов 1С может быть довольно громоздкой и не всегда очевидной, поскольку прямого оператора PIVOT в нем нет. Однако, мы можем достичь желаемого результата, используя временные таблицы и множество операторов ОБЪЕДИНИТЬ ВСЕ.
Разберем процесс по шагам.
Первым делом нам необходимо определить возраст каждого сотрудника на заданную дату (например, &ТекущаяДата) и выделить месяц его рождения. Важно точно рассчитать возраст в полных годах, учитывая месяц и день рождения. Простое вычитание годов (ГОД(&ТекущаяДата) - ГОД(ДатаРождения)) может быть неточным, если день рождения в текущем году еще не наступил.
Для точного расчета полных лет мы будем использовать функцию РАЗНОСТЬДАТ, корректируя результат, если месяц или день рождения еще не наступил в текущем году. Также мы отфильтруем некорректные данные, такие как удаленные элементы, группы или записи без указанной даты рождения.
ВЫБРАТЬ
ФизическиеЛица.Ссылка КАК Ссылка,
ФизическиеЛица.ДатаРождения КАК ДатаРождения,
РАЗНОСТЬДАТ(ФизическиеЛица.ДатаРождения, &ТекущаяДата, ГОД)
- ВЫБОР
КОГДА МЕСЯЦ(ФизическиеЛица.ДатаРождения) > МЕСЯЦ(&ТекущаяДата)
ТОГДА 1
КОГДА МЕСЯЦ(ФизическиеЛица.ДатаРождения) = МЕСЯЦ(&ТекущаяДата)
И ДЕНЬ(ФизическиеЛица.ДатаРождения) > ДЕНЬ(&ТекущаяДата)
ТОГДА 1
ИНАЧЕ 0
КОНЕЦ КАК Возраст,
МЕСЯЦ(ФизическиеЛица.ДатаРождения) КАК МесяцРождения
ПОМЕСТИТЬ ВТ_ВозрастИМесяц
ИЗ
Справочник.ФизическиеЛица КАК ФизическиеЛица
ГДЕ
НЕ ФизическиеЛица.ЭтоГруппа
И НЕ ФизическиеЛица.ПометкаУдаления
И НЕ ФизическиеЛица.ДатаРождения = &ПустаяДата
И РАЗНОСТЬДАТ(ФизическиеЛица.ДатаРождения, &ТекущаяДата, ГОД) > 0 // Фильтрация будущих или 'неродившихся' сотрудников
;
Обратите внимание на параметр &ПустаяДата, который обычно равен ДАТА(1,1,1), и фильтр РАЗНОСТЬДАТ(...) > 0 для исключения сотрудников, дата рождения которых указывает на будущее или слишком далекое прошлое, делая возраст отрицательным или некорректным.
На этом шаге мы определим возрастные группы и посчитаем количество сотрудников в каждой группе для каждого месяца рождения. Мы используем оператор ВЫБОР КОГДА для категоризации по возрасту.
ВЫБРАТЬ
ВЫБОР
КОГДА ВТ_ВозрастИМесяц.Возраст МЕЖДУ 0 И 10 ТОГДА "Группа 0-10 лет"
КОГДА ВТ_ВозрастИМесяц.Возраст МЕЖДУ 11 И 20 ТОГДА "Группа 11-20 лет"
КОГДА ВТ_ВозрастИМесяц.Возраст МЕЖДУ 21 И 30 ТОГДА "Группа 21-30 лет"
КОГДА ВТ_ВозрастИМесяц.Возраст МЕЖДУ 31 И 40 ТОГДА "Группа 31-40 лет"
КОГДА ВТ_ВозрастИМесяц.Возраст МЕЖДУ 41 И 50 ТОГДА "Группа 41-50 лет"
ИНАЧЕ "Старше 50 лет"
КОНЕЦ КАК ГруппаВозраста,
ВТ_ВозрастИМесяц.МесяцРождения КАК МесяцРождения,
СУММА(1) КАК Количество
ПОМЕСТИТЬ ВТ_КоличествоПоГруппамИМесяцам
ИЗ
ВТ_ВозрастИМесяц КАК ВТ_ВозрастИМесяц
СГРУППИРОВАТЬ ПО
ВЫБОР
КОГДА ВТ_ВозрастИМесяц.Возраст МЕЖДУ 0 И 10 ТОГДА "Группа 0-10 лет"
КОГДА ВТ_ВозрастИМесяц.Возраст МЕЖДУ 11 И 20 ТОГДА "Группа 11-20 лет"
КОГДА ВТ_ВозрастИМесяц.Возраст МЕЖДУ 21 И 30 ТОГДА "Группа 21-30 лет"
КОГДА ВТ_ВозрастИМесяц.Возраст МЕЖДУ 31 И 40 ТОГДА "Группа 31-40 лет"
КОГДА ВТ_ВозрастИМесяц.Возраст МЕЖДУ 41 И 50 ТОГДА "Группа 41-50 лет"
ИНАЧЕ "Старше 50 лет"
КОНЕЦ,
ВТ_ВозрастИМесяц.МесяцРождения
;
Использование ВЫБОР КОГДА ... МЕЖДУ ... И ... является более читабельным и корректным, чем вложенные ВЫБОР КОГДА.
ОБЪЕДИНИТЬ ВСЕ.
Это самый объемный и повторяющийся шаг, где мы "разворачиваем" данные из строк (месяцев) в колонки. Для каждого месяца мы выбираем данные, устанавливая значение в соответствующую колонку, а в остальные – ноль. Затем объединяем все эти выборки.
ВЫБРАТЬ
ВТ_КоличествоПоГруппамИМесяцам.ГруппаВозраста КАК ГруппаВозраста,
СУММА(ВТ_КоличествоПоГруппамИМесяцам.Количество) КАК Январь,
0 КАК Февраль,
0 КАК Март,
0 КАК Апрель,
0 КАК Май,
0 КАК Июнь,
0 КАК Июль,
0 КАК Август,
0 КАК Сентябрь,
0 КАК Октябрь,
0 КАК Ноябрь,
0 КАК Декабрь
ПОМЕСТИТЬ ВТ_ВременнаяТаблица
ИЗ
ВТ_КоличествоПоГруппамИМесяцам КАК ВТ_КоличествоПоГруппамИМесяцам
ГДЕ
ВТ_КоличествоПоГруппамИМесяцам.МесяцРождения = 1
СГРУППИРОВАТЬ ПО
ВТ_КоличествоПоГруппамИМесяцам.ГруппаВозраста
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ВТ_КоличествоПоГруппамИМесяцам.ГруппаВозраста,
0,
СУММА(ВТ_КоличествоПоГруппамИМесяцам.Количество),
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
ИЗ
ВТ_КоличествоПоГруппамИМесяцам КАК ВТ_КоличествоПоГруппамИМесяцам
ГДЕ
ВТ_КоличествоПоГруппамИМесяцам.МесяцРождения = 2
СГРУППИРОВАТЬ ПО
ВТ_КоличествоПоГруппамИМесяцам.ГруппаВозраста
// ... повторяем блок ОБЪЕДИНИТЬ ВСЕ для каждого месяца с 3 по 12 ...
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ВТ_КоличествоПоГруппамИМесяцам.ГруппаВозраста,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
СУММА(ВТ_КоличествоПоГруппамИМесяцам.Количество)
ИЗ
ВТ_КоличествоПоГруппамИМесяцам КАК ВТ_КоличествоПоГруппамИМесяцам
ГДЕ
ВТ_КоличествоПоГруппамИМесяцам.МесяцРождения = 12
СГРУППИРОВАТЬ ПО
ВТ_КоличествоПоГруппамИМесяцам.ГруппаВозраста
;
Этот блок кода будет очень длинным, так как его нужно повторить 12 раз (для каждого месяца), изменяя условие ГДЕ МесяцРождения = N и положение СУММА(Количество) в списке полей.
Наконец, мы суммируем значения по каждой колонке для каждой возрастной группы из нашей временной таблицы, чтобы получить итоговый кросс-отчет.
ВЫБРАТЬ
ВТ_ВременнаяТаблица.ГруппаВозраста КАК Группа,
СУММА(ВТ_ВременнаяТаблица.Январь) КАК Январь,
СУММА(ВТ_ВременнаяТаблица.Февраль) КАК Февраль,
СУММА(ВТ_ВременнаяТаблица.Март) КАК Март,
СУММА(ВТ_ВременнаяТаблица.Апрель) КАК Апрель,
СУММА(ВТ_ВременнаяТаблица.Май) КАК Май,
СУММА(ВТ_ВременнаяТаблица.Июнь) КАК Июнь,
СУММА(ВТ_ВременнаяТаблица.Июль) КАК Июль,
СУММА(ВТ_ВременнаяТаблица.Август) КАК Август,
СУММА(ВТ_ВременнаяТаблица.Сентябрь) КАК Сентябрь,
СУММА(ВТ_ВременнаяТаблица.Октябрь) КАК Октябрь,
СУММА(ВТ_ВременнаяТаблица.Ноябрь) КАК Ноябрь,
СУММА(ВТ_ВременнаяТаблица.Декабрь) КАК Декабрь
ИЗ
ВТ_ВременнаяТаблица КАК ВТ_ВременнаяТаблица
СГРУППИРОВАТЬ ПО
ВТ_ВременнаяТаблица.ГруппаВозраста
УПОРЯДОЧИТЬ ПО
Группа
;
Преимущества чистого запроса:
Недостатки чистого запроса:
ОБЪЕДИНИТЬ ВСЕ.Система Компоновки Данных (СКД) — это мощный и гибкий инструмент в 1С:Предприятии для построения сложных отчетов. Использование СКД идеально соответствует условию "используя только один запрос без обработки результата запроса в модуле", так как СКД сама генерирует и выполняет оптимальный запрос на основе заданных настроек и формирует отчет без написания кода вручную. При этом СКД предоставляет гораздо более удобные средства для создания кросс-таблиц.
Разберем процесс создания отчета в СКД по шагам.
Мы начинаем с создания новой схемы компоновки данных (СКД) и определения набора данных — запроса к справочнику ФизическиеЛица (или Сотрудники). В этом запросе мы выбираем только необходимые поля и применяем базовые фильтры.
ВЫБРАТЬ
ФизическиеЛица.Ссылка,
ФизическиеЛица.Наименование,
ФизическиеЛица.ДатаРождения
ИЗ
Справочник.ФизическиеЛица КАК ФизическиеЛица
ГДЕ
НЕ ФизическиеЛица.ЭтоГруппа
И НЕ ФизическиеЛица.ПометкаУдаления
И НЕ ФизическиеЛица.ДатаРождения = &ПустаяДата
Параметры &ТекущаяДата и &ПустаяДата будут доступны в СКД. Мы также можем добавить фильтрацию по диапазону дат, если требуется ограничить выборку сотрудников.
В СКД мы можем легко создавать новые поля, используя выражения. Это позволяет нам рассчитать возраст, определить возрастную группу и получить месяц рождения для дальнейшего построения отчета.
Для точного расчета возраста на &ТекущаяДата (дата формирования отчета), учитывая месяц и день рождения, используем следующее выражение:
РАЗНОСТЬДАТ(ФизическиеЛица.ДатаРождения, &ТекущаяДата, ГОД)
- ВЫБОР
КОГДА МЕСЯЦ(ФизическиеЛица.ДатаРождения) > МЕСЯЦ(&ТекущаяДата)
ТОГДА 1
КОГДА МЕСЯЦ(ФизическиеЛица.ДатаРождения) = МЕСЯЦ(&ТекущаяДата)
И ДЕНЬ(ФизическиеЛица.ДатаРождения) > ДЕНЬ(&ТекущаяДата)
ТОГДА 1
ИНАЧЕ 0
КОНЕЦ
Назовем это поле, например, Возраст.
Если же нам не принципиален день месяца и возраст должен рассчитываться просто на начало месяца &ТекущаяДата, выражение упрощается:
РАЗНОСТЬДАТ(ФизическиеЛица.ДатаРождения, НАЧАЛОПЕРИОДА(&ТекущаяДата, МЕСЯЦ), ГОД)
Это выражение может быть немного менее точным, но в некоторых случаях приемлемо.
После расчета возраста, мы можем определить возрастную группу с помощью оператора ВЫБОР КОГДА, основываясь на поле Возраст. Назовем это поле ВозрастнаяГруппа.
ВЫБОР
КОГДА Возраст <= 10 ТОГДА "Группа 0-10 лет"
КОГДА Возраст <= 20 ТОГДА "Группа 11-20 лет"
КОГДА Возраст <= 30 ТОГДА "Группа 21-30 лет"
КОГДА Возраст <= 40 ТОГДА "Группа 31-40 лет"
КОГДА Возраст <= 50 ТОГДА "Группа 41-50 лет"
ИНАЧЕ "Старше 50 лет"
КОНЕЦ
Для формирования колонок с месяцами нам понадобится поле, представляющее месяц рождения. Мы можем использовать НАЧАЛОПЕРИОДА(ФизическиеЛица.ДатаРождения, МЕСЯЦ). Это поле будет иметь тип «Дата», но в настройках СКД мы сможем задать его форматирование так, чтобы выводилось только название месяца.
Назовем это поле МесяцРождения. В свойствах поля МесяцРождения в СКД можно указать формат ДФ='ММММ', чтобы в заголовках колонок выводились полные названия месяцев (Январь, Февраль и т.д.).
В СКД мы используем тип отчета «Таблица». Это позволяет нам легко строить кросс-таблицы.
ВозрастнаяГруппа.МесяцРождения.Количество.Количество используется функция СУММА(). Выражением для ресурса может быть Количество, если мы хотим считать число записей. СКД автоматически подсчитает количество элементов для каждой ячейки кросс-таблицы. Если нам нужно количество уникальных сотрудников, можно использовать КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Ссылка).Мы должны настроить параметры СКД:
ТекущаяДата (или КонДата) с типом «Дата» и установить для него значение по умолчанию (например, НАЧАЛОДНЯ(«ТекущаяДата») или текущая дата на момент открытия отчета).ПустаяДата с типом «Дата» и значением ДАТА(1,1,1).Фильтры, которые мы добавили в основной запрос СКД (НЕ ФизическиеЛица.ЭтоГруппа, НЕ ФизическиеЛица.ПометкаУдаления, НЕ ФизическиеЛица.ДатаРождения = &ПустаяДата), будут применяться автоматически. Дополнительные фильтры, например по диапазону дат для отчета, можно настроить на вкладке «Параметры» и «Фильтры» СКД.
Одно из значительных преимуществ СКД — это возможность автоматически выводить все 12 месяцев в колонках отчета, даже если по некоторым месяцам нет данных. Для этого в настройках поля МесяцРождения в группировке колонок (на вкладке «Настройки» СКД, в свойствах поля МесяцРождения в разделе «Группировка») можно включить опцию «Дополнительные поля группировки» или настроить «Дополнение периодов» (если поле даты). СКД сама сгенерирует все необходимые месяцы.
Преимущества СКД:
Мы рассмотрели два подхода к созданию отчета по распределению сотрудников по возрастным группам и месяцам рождения. Подход с использованием чистого языка запросов, хотя и соответствует строгому требованию "один запрос без модуля", является трудоемким в реализации, сложным для чтения и поддержки. Он требует многократного повторения блоков кода с ОБЪЕДИНИТЬ ВСЕ для каждого месяца, что делает запрос очень объемным.
Напротив, использование Системы Компоновки Данных (СКД) является гораздо более эффективным и рекомендованным решением. СКД позволяет наглядно настроить структуру отчета, легко создавать вычисляемые поля для возраста и групп, а также автоматически формировать кросс-таблицы с учетом всех месяцев. При этом СКД генерирует единый оптимальный запрос, полностью соответствуя условию задачи "используя только один запрос без обработки результата запроса в модуле". Это делает разработку отчета быстрой, его поддержку — простой, а сам отчет — гибким и мощным инструментом анализа.
В большинстве реальных проектов 1С:Предприятия именно СКД будет предпочтительным инструментом для создания подобных аналитических отчетов.