При работе с регистрами сведений в 1С часто возникает задача не просто выбрать данные, а получить статистику: сколько записей соответствует тому или иному значению измерения или реквизита. На первый взгляд задача кажется тривиальной, но при наличии множества полей или необходимости вывести количество рядом с детальными записями могут возникнуть трудности. В этой статье мы разберем, как правильно использовать механизмы языка запросов 1С для решения этой задачи.
Если нам необходимо получить простой список вида «Значение — Количество вхождений», мы воспользуемся конструкцией СГРУППИРОВАТЬ ПО. Это наиболее эффективный способ, который выполняется на уровне СУБД. Давайте проанализируем простейший запрос, который решает задачу из первого сообщения темы.
Предположим, у нас есть регистр сведений, и мы хотим узнать, сколько записей создано каждым пользователем (поле Редактор). Рассмотрим следующий программный код:
ВЫБРАТЬ
РегистрСведенийИмя.Редактор КАК Редактор,
КОЛИЧЕСТВО(*) КАК КоличествоЗаписей
ИЗ
РегистрСведений.РегистрСведенийИмя КАК РегистрСведенийИмя
СГРУППИРОВАТЬ ПО
РегистрСведенийИмя.Редактор
Разберем по шагам, что здесь происходит. Функция КОЛИЧЕСТВО(*) подсчитывает все строки, попавшие в выборку. Если мы используем СГРУППИРОВАТЬ ПО, система «схлопывает» все одинаковые значения в поле Редактор в одну строку, а агрегатная функция подсчитывает их число. Важно помнить: если вы укажете КОЛИЧЕСТВО(Редактор) вместо звездочки, то записи, где редактор не заполнен (равен NULL), не будут учтены в итоговой цифре.
Иногда задача усложняется: нам нужно видеть не только итоговую цифру по редактору, но и все остальные поля регистра, при этом в каждой строке должно стоять общее количество записей этого редактора. В этом случае обычная группировка не подойдет, так как она скроет детальные данные. Проанализируем ситуацию и применим левое соединение с вложенным запросом.
Посмотрим на пример реализации такого запроса:
ВЫБРАТЬ
ТаблицаРегистра.Редактор,
ТаблицаРегистра.Период,
ТаблицаРегистра.Значение1,
ВложенныйЗапрос.ОбщееКолвоПоРедактору
ИЗ
РегистрСведений.РегистрСведенийИмя КАК ТаблицаРегистра
ЛЕВОЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ
Редактор КАК Редактор,
КОЛИЧЕСТВО(*) КАК ОбщееКолвоПоРедактору
ИЗ
РегистрСведений.РегистрСведенийИмя
СГРУППИРОВАТЬ ПО
Редактор
) КАК ВложенныйЗапрос
ПО ТаблицаРегистра.Редактор = ВложенныйЗапрос.Редактор
В данном примере мы сначала создаем «виртуальную» таблицу во вложенном запросе, которая содержит только уникальных редакторов и их количество. Затем мы соединяем её с основной физической таблицей регистра по полю Редактор. Это позволяет нам получить полную картину: и детальные данные записи, и агрегированный показатель в одной строке.
Если ваш регистр содержит сотни тысяч записей, выполнение группировки «на лету» может замедлить работу системы. Давайте выясним причину: СУБД приходится сканировать всю таблицу для подсчета. Чтобы ускорить процесс, мы можем использовать временные таблицы и индексацию.
Рассмотрим правильный алгоритм работы с большими данными:
ИНДЕКСИРОВАТЬ ПО для поля, по которому будем делать соединение.Такой подход значительно снижает нагрузку на сервер 1С и СУБД, так как объем данных, участвующих в соединении, сокращается заранее. Для проверки и отладки промежуточных данных подойдёт расширение для просмотра содержимого менеджера временных таблиц.
При использовании группировки важно помнить о типах полей. В 1С нельзя группировать данные по полям неограниченной длины (например, строки неограниченной длины или ХранилищеЗначения). Если вам необходимо сгруппировать по такому полю, предварительно приведите его к фиксированной длине с помощью функции ВЫРАЗИТЬ.
ВЫБРАТЬ
ВЫРАЗИТЬ(РегистрСведенийИмя.Комментарий КАК СТРОКА(100)) КАК КомментарийКратко,
КОЛИЧЕСТВО(*) КАК Колво
ИЗ
РегистрСведений.РегистрСведенийИмя КАК РегистрСведенийИмя
СГРУППИРОВАТЬ ПО
ВЫРАЗИТЬ(РегистрСведенийИмя.Комментарий КАК СТРОКА(100))
Также, если ваш регистр является периодическим, определитесь, нужно ли вам общее количество записей за все время или только количество уникальных состояний на текущий момент. В последнем случае вместо физической таблицы РегистрСведений.ИмяРегистра следует обращаться к виртуальной таблице РегистрСведений.ИмяРегистра.СрезПоследних(). Это не только даст корректный результат с точки зрения логики учета, но и отработает быстрее, так как платформа сама оптимизирует выборку актуальных данных.
Бывают ситуации, когда нужно получить количество записей по разным условиям в рамках одного запроса. Например, сколько записей у пользователя имеют статус «Активно», а сколько — «В архиве». Вместо создания нескольких подзапросов, мы можем использовать изящный прием с функцией СУММА и условием ВЫБОР.
ВЫБРАТЬ
Редактор,
СУММА(ВЫБОР КОГДА Статус = ЗНАЧЕНИЕ(Перечисление.Статусы.Активен) ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК КолвоАктивных,
СУММА(ВЫБОР КОГДА Статус = ЗНАЧЕНИЕ(Перечисление.Статусы.Архив) ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК КолвоАрхивных
ИЗ
РегистрСведений.РегистрСведенийИмя
СГРУППИРОВАТЬ ПО
Редактор
Этот метод позволяет за один проход по таблице собрать сложную статистику, что является признаком хорошего тона в разработке на платформе 1С. Мы рассмотрели основные и продвинутые техники получения количества записей — от простой группировки до сложных аналитических выборок. Используйте эти инструменты в зависимости от объема ваших данных и сложности бизнес-задачи. Для интерактивного составления и тестирования таких конструкций можно использовать инженерную консоль запросов для 1С.