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

← На главную

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

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

Анализируем постановку задачи

Представим, что у нас есть список периодов (например, полученный из табличной части документа НачислениеОтпускаРаботникамОрганизаций.РасчетСреднего или переданный через параметр ТаблицаЗначений):

01.01.2016
01.02.2016
01.03.2016
01.04.2016

В регистре сведений при этом записи зарегистрированы только на две даты: 01.01.2016 (две записи) и 01.03.2016 (три записи). Наша цель — получить итоговую таблицу со всеми четырьмя датами, где напротив пустых периодов не будет значений (или будет выведен ноль), а напротив заполненных — фактическое число записей.

Пошаговое построение запроса

Рассмотрим решение по шагам: сначала выделим исходный список периодов, затем свяжем его с регистром сведений и выполним агрегацию.

Шаг 1. Получение таблицы периодов во временную таблицу

Если периоды выбираются из документа, рекомендуется поместить их во временную таблицу и сразу убрать дубли с помощью ключевого слова РАЗЛИЧНЫЕ:


ВЫБРАТЬ РАЗЛИЧНЫЕ
    РасчетСреднего.БазовыйПериодНачало КАК Период
ПОМЕСТИТЬ ВТ_Периоды
ИЗ
    Документ.НачислениеОтпускаРаботникамОрганизаций.РасчетСреднего КАК РасчетСреднего
ГДЕ
    РасчетСреднего.Ссылка = &Ссылка
    И РасчетСреднего.ВидРасчета = &ВидРасчета
;

Шаг 2. Левое соединение с регистром сведений

Для сохранения всех исходных дат базовую таблицу периодов соединяем с регистром сведений через ЛЕВОЕ СОЕДИНЕНИЕ. При внутреннем соединении даты 01.02.2016 и 01.04.2016 выпали бы из выборки, так как записей в регистре для них нет.

Шаг 3. Группировка и подсчет количества

Группируем результат по полю ВТ_Периоды.Период. Чтобы подсчитать количество записей из регистра сведений, крайне важно выбрать правильное выражение для агрегации.


ВЫБРАТЬ
    ВТ_Периоды.Период КАК Период,
    КОЛИЧЕСТВО(РегистрСведений.Период) КАК КоличествоЗаписей
ИЗ
    ВТ_Периоды КАК ВТ_Периоды
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ПлановыеНачисленияРаботниковОрганизаций КАК РегистрСведений
        ПО ВТ_Периоды.Период = РегистрСведений.Период
СГРУППИРОВАТЬ ПО
    ВТ_Периоды.Период
УПОРЯДОЧИТЬ ПО
    Период

Типичные ошибки при подсчете строк и работе с NULL

Разберем тонкости, которые часто приводят к неверным результатам в запросах такого типа:

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

Вариант соединения по интервалу дат

В документе расчета среднего заработка часто присутствуют две границы: БазовыйПериодНачало и БазовыйПериодКонец. Если требуется проверить попадание записей регистра в заданный интервал, условие соединения меняется на оператор МЕЖДУ:


ВЫБРАТЬ
    ТаблицаГраниц.БазовыйПериодНачало КАК НачалоИнтервала,
    ТаблицаГраниц.БазовыйПериодКонец КАК КонецИнтервала,
    КОЛИЧЕСТВО(Регистр.Период) КАК КоличествоЗаписей
ИЗ
    ВТ_Границы КАК ТаблицаГраниц
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ГрафикиРаботы КАК Регистр
        ПО Регистр.Период МЕЖДУ ТаблицаГраниц.БазовыйПериодНачало И ТаблицаГраниц.БазовыйПериодКонец
СГРУППИРОВАТЬ ПО
    ТаблицаГраниц.БазовыйПериодНачало,
    ТаблицаГраниц.БазовыйПериодКонец

При использовании проверки через МЕЖДУ всегда следите за тем, чтобы граница БазовыйПериодКонец включала конец периода (например, через КОНЕЦПЕРИОДА(БазовыйПериодКонец, ДЕНЬ)), иначе записи за последнюю дату интервала со временем, отличным от полуночи, не попадут в выборку.

Особенности передачи внешнего списка дат через Таблицу Значений

Если таблица периодов формируется программно во встроенном языке и передается в запрос через параметр, важно соблюдать два правила:

Колонка даты должна быть строго типизирована объектом ОписаниеТипов с квалификатором даты (Новый ОписаниеТипов("Дата")). Если колонка останется без типа, запрос завершится ошибкой.
В тексте запроса внешнюю таблицу необходимо поместить во временную таблицу и проиндексировать поле даты с помощью директивы ИНДЕКСИРОВАТЬ ПО, что обеспечит максимальное быстродействие при соединении с регистром:
```
ВЫБРАТЬ
    ТЗ.Период КАК Период
ПОМЕСТИТЬ ВТ_Периоды
ИЗ
    &ТаблицаПериодов КАК ТЗ
ИНДЕКСИРОВАТЬ ПО
    Период
;
```

Таким образом, комбинация временных таблиц, левого соединения и агрегатной функции КОЛИЧЕСТВО(ПолеРегистра) позволяет получить полный список исходных дат с точным распределением данных из регистра сведений.