Как в запросе 1С посчитать количество документов и количество строк в них?

← На главную

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

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

Анализ типичных ошибок в исходном запросе

Проанализируем ситуацию на примере типичного запроса, в котором проявляется проблема:


ВЫБРАТЬ
    ЗаказКлиента.Автор,
    НАЧАЛОПЕРИОДА(ЗаказКлиента.Дата, ДЕНЬ) КАК Дата,
    КОЛИЧЕСТВО(ЗаказКлиента.Ссылка) КАК КолвоДок,
    КОЛИЧЕСТВО(ЗаказКлиентаТовары.НомерСтроки) КАК КолвоСтрок
ИЗ
    Документ.ЗаказКлиента.Товары КАК ЗаказКлиентаТовары
        ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаказКлиента КАК ЗаказКлиента
        ПО ЗаказКлиентаТовары.Ссылка = ЗаказКлиента.Ссылка
ГДЕ
    ЗаказКлиента.ПометкаУдаления = ЛОЖЬ
    И ЗаказКлиента.Дата МЕЖДУ НАЧАЛОПЕРИОДА(&ДатаНачала, ДЕНЬ) И КОНЕЦПЕРИОДА(&ДатаКонца, ДЕНЬ)
СГРУППИРОВАТЬ ПО
    ЗаказКлиента.Автор,
    ЗаказКлиента.Ссылка,
    НАЧАЛОПЕРИОДА(ЗаказКлиента.Дата, ДЕНЬ)

Выясним причину, по которой значения КолвоДок и КолвоСтрок совпадают, а также определим скрытые логические дефекты:

Лишнее поле в секции СГРУППИРОВАТЬ ПО: в списке группировки указано поле ЗаказКлиента.Ссылка. Из-за этого платформа разбивает результат до уровня каждого отдельного документа. В рамках одной строки результата для одного документа число записей шапки всегда равно числу строк табличной части.
Отсутствие ключевого слова РАЗЛИЧНЫЕ: при объединении шапки и табличной части данные шапки дублируются столько раз, сколько строк содержится в документе. Функция КОЛИЧЕСТВО(ЗаказКлиента.Ссылка) подсчитывает все записи выборки, включая повторяющиеся ссылки, а не уникальные документы.
Неверный порядок соединения таблиц: основной таблицей выбрана табличная часть Документ.ЗаказКлиента.Товары. Если документ был создан и записан без строк (с пустой табличной частью), он вообще не попадет в итоговую выборку.

Вариант 1. Прямой запрос с функцией КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...)

Рассмотрим по шагам, как исправить исходный запрос:

Сделаем ведущей таблицей шапку документа Документ.ЗаказКлиента, а табличную часть присоединим через ЛЕВОЕ СОЕДИНЕНИЕ. Это гарантирует учет даже пустых документов.
Уберем поле Ссылка из секции СГРУППИРОВАТЬ ПО, оставив только разрезы агрегации — автора и дату.
Применим агрегатную функцию КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ЗаказКлиента.Ссылка) для подсчета уникальных документов.
Для подсчета строк используем КОЛИЧЕСТВО(ЗаказКлиентаТовары.НомерСтроки). При левом соединении для документов без строк поле НомерСтроки примет значение NULL, которое функция КОЛИЧЕСТВО корректно проигнорирует и вернет 0.

Посмотрим на исправленный текст запроса:


ВЫБРАТЬ
    ЗаказКлиента.Автор КАК Автор,
    НАЧАЛОПЕРИОДА(ЗаказКлиента.Дата, ДЕНЬ) КАК ПериодДень,
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ЗаказКлиента.Ссылка) КАК КолвоДок,
    КОЛИЧЕСТВО(ЗаказКлиентаТовары.НомерСтроки) КАК КолвоСтрок
ИЗ
    Документ.ЗаказКлиента КАК ЗаказКлиента
        ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаказКлиента.Товары КАК ЗаказКлиентаТовары
        ПО ЗаказКлиента.Ссылка = ЗаказКлиентаТовары.Ссылка
ГДЕ
    ЗаказКлиента.ПометкаУдаления = ЛОЖЬ
    И ЗаказКлиента.Дата МЕЖДУ НАЧАЛОПЕРИОДА(&ДатаНачала, ДЕНЬ) И КОНЕЦПЕРИОДА(&ДатаКонца, ДЕНЬ)
СГРУППИРОВАТЬ ПО
    ЗаказКлиента.Автор,
    НАЧАЛОПЕРИОДА(ЗаказКлиента.Дата, ДЕНЬ)

Вариант 2. Оптимизация через временные таблицы для больших объемов данных

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

Для повышения производительности разберем по шагам построение запроса через пакетное вычисление:

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

Посмотрим на пример пакета запросов:


ВЫБРАТЬ
    Товары.Ссылка КАК Ссылка,
    КОЛИЧЕСТВО(Товары.НомерСтроки) КАК КоличествоСтрок
ПОМЕСТИТЬ ВТ_СтрокиПоДокументам
ИЗ
    Документ.ЗаказКлиента.Товары КАК Товары
ГДЕ
    Товары.Ссылка.ПометкаУдаления = ЛОЖЬ
    И Товары.Ссылка.Дата МЕЖДУ НАЧАЛОПЕРИОДА(&ДатаНачала, ДЕНЬ) И КОНЕЦПЕРИОДА(&ДатаКонца, ДЕНЬ)
СГРУППИРОВАТЬ ПО
    Товары.Ссылка
ИНДЕКСИРОВАТЬ ПО
    Ссылка
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ЗаказКлиента.Автор КАК Автор,
    НАЧАЛОПЕРИОДА(ЗаказКлиента.Дата, ДЕНЬ) КАК ПериодДень,
    КОЛИЧЕСТВО(ЗаказКлиента.Ссылка) КАК КолвоДок,
    СУММА(ЕСТЬNULL(ВТ_СтрокиПоДокументам.КоличествоСтрок, 0)) КАК КолвоСтрок
ИЗ
    Документ.ЗаказКлиента КАК ЗаказКлиента
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_СтрокиПоДокументам КАК ВТ_СтрокиПоДокументам
        ПО ЗаказКлиента.Ссылка = ВТ_СтрокиПоДокументам.Ссылка
ГДЕ
    ЗаказКлиента.ПометкаУдаления = ЛОЖЬ
    И ЗаказКлиента.Дата МЕЖДУ НАЧАЛОПЕРИОДА(&ДатаНачала, ДЕНЬ) И КОНЕЦПЕРИОДА(&ДатаКонца, ДЕНЬ)
СГРУППИРОВАТЬ ПО
    ЗаказКлиента.Автор,
    НАЧАЛОПЕРИОДА(ЗаказКлиента.Дата, ДЕНЬ)

Вариант 3. Реализация в Системе компоновки данных (СКД)

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

В наборе данных выбираются поля Ссылка, Автор, Дата, а также поле Товары.НомерСтроки.
На вкладке Ресурсы настраиваются формулы расчета:
Для поля Ссылка задается выражение Количество(Различные Ссылка).
Для поля НомерСтроки задается выражение Количество(НомерСтроки).
В настройках структуры отчета добавляется группировка по автору и дате. Компоновщик данных самостоятельно вычислит корректные итоги для каждого уровня.