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

Программист 1С v8.3 (Управляемые формы) 1С:Управление торговлей Управленческий учет Торговля и дистрибуция
← К списку

В процессе работы с системой 1С:Предприятие 8 мы часто сталкиваемся с необходимостью извлекать и анализировать данные из различных источников. Запросы являются мощным инструментом для решения таких задач. Давайте вместе разберем, как объединить информацию из нескольких документов, подсчитать количество определенных элементов (например, контрагентов) и при этом учесть важный аспект – временной период.

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

Базовое решение: Объединение данных и группировка

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


ОБЪЕДИНИТЬ ВСЕ
, которая позволяет слить результаты нескольких запросов в одну временную таблицу. Затем мы сгруппируем эти данные по контрагенту и подсчитаем количество вхождений.

Рассмотрим структуру запроса, предложенную на форуме, и поясним каждый ее элемент:


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

Давайте разберем этот запрос по шагам:

  1. Вложенный запрос: 
    
(ВЫБРАТЬ
    Контрагент КАК Контрагент
ИЗ
    Документ.Поступление
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
    Контрагент КАК Контрагент
ИЗ
    Документ.Реализация) КАК ВложенныйЗапрос

    Здесь мы сначала формируем временный набор данных. Мы выбираем поле Контрагент из 

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

  2. Основной запрос: 
    
ВЫБРАТЬ
    Контрагент,
    КОЛИЧЕСТВО(*) КАК КоличествоВхождений
ИЗ
    ...
СГРУППИРОВАТЬ ПО
    Контрагент

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

    
ВЫБРАТЬ
    присутствуют неагрегированные поля, они обязательно должны быть перечислены в секции 
    
СГРУППИРОВАТЬ ПО
    . В нашем случае это поле Контрагент.

Учет временного периода: Добавляем фильтрацию

Как справедливо заметил один из участников форума, в подобных запросах очень важно учитывать временной период. Без него мы получим данные за весь период существования базы, что не всегда информативно. Давайте дополним наш запрос возможностью фильтрации по дате.

Для этого мы добавим в каждый подзапрос секцию 


ГДЕ
(WHERE), используя параметры для указания начала и конца периода. Параметры запроса в 1С обозначаются символом &.


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

Теперь, прежде чем выполнить этот запрос, нам потребуется установить значения для параметров &НачалоПериода и &КонецПериода с помощью встроенного языка 1С. Рассмотрим пример, как это делается:


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

Запрос.УстановитьПараметр("НачалоПериода", НачалоДня(ТекущаяДата() - 30 * 24 * 3600)); // Например, последние 30 дней
Запрос.УстановитьПараметр("КонецПериода", КонецДня(ТекущаяДата()));

РезультатЗапроса = Запрос.Выполнить();
Выборка = РезультатЗапроса.Выбрать();

Пока Выборка.Следующий() Цикл
    Сообщить("Контрагент: " + Выборка.Контрагент + ", Количество вхождений: " + Выборка.КоличествоВхождений);
КонецЦикла;

В этом примере мы создаем объект Запрос, присваиваем ему текст нашего запроса и затем устанавливаем значения параметров. Для удобства работы с датами в 1С существуют функции 


НачалоДня()
и 

КонецДня()
, которые позволяют точно определить границы дня.

Расширяем горизонты: Важные элементы запросов 1С

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

Язык запросов 1С, хоть и похож на SQL, имеет свои особенности, ориентированные на бизнес-задачи.

  1. Секция 
    
ВЫБРАТЬ
    (SELECT):

    Эта секция определяет, какие поля будут включены в результирующую таблицу. Вы можете выбрать все поля с помощью * или перечислить конкретные поля через запятую. Мы также можем использовать псевдонимы (КАК) для полей, чтобы сделать результат более читаемым, как мы делали с КОЛИЧЕСТВО(*) КАК КоличествоВхождений.

  2. Секция 
    
ИЗ
    (FROM):

    Указывает источники данных. Это могут быть таблицы документов, справочников, регистров, а также виртуальные таблицы регистров или результаты других запросов (как в нашем примере с вложенным запросом).

  3. 
ОБЪЕДИНИТЬ
    и 
    
ОБЪЕДИНИТЬ ВСЕ
    (UNION / UNION ALL):

    Мы уже использовали 

    
ОБЪЕДИНИТЬ ВСЕ
    , которое включает все строки, в том числе дубликаты. Если же нам нужно получить только уникальные строки после объединения, мы используем просто 
    
ОБЪЕДИНИТЬ
    . Важно, чтобы объединяемые запросы имели одинаковое количество полей и совместимые типы данных в соответствующих полях.

  4. Секция 
    
ГДЕ
    (WHERE):

    Позволяет фильтровать данные по заданным условиям. Мы можем использовать операторы сравнения (=, <>, >, >=, <, <=), логические операторы (И, ИЛИ, НЕ), а также конструкции МЕЖДУ, В, ПОДОБНО и другие.

  5. Секция 
    
УПОРЯДОЧИТЬ ПО
    (ORDER BY):

    Сортирует результат запроса по одному или нескольким полям. По умолчанию сортировка идет по возрастанию (ВОЗР), но мы можем явно указать УБЫВ для сортировки по убыванию.

  6. 
ВЫБРАТЬ РАЗЛИЧНЫЕ
    (SELECT DISTINCT):

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

  7. 
ПОМЕСТИТЬ
    (INTO) для временных таблиц:

    Это очень мощный инструмент для работы со сложными запросами. 

    
ПОМЕСТИТЬ
    позволяет сохранить результат одного запроса во временную таблицу, которую затем можно использовать как источник данных для последующих запросов. Это значительно улучшает читаемость и производительность сложных многошаговых запросов.

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

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

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

  8. 
СОЕДИНЕНИЕ
    (JOIN):

    Позволяет объединять данные из нескольких таблиц по определенному условию. Существуют различные типы соединений:

    • 
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
      (INNER JOIN): Возвращает только те строки, для которых есть совпадения в обеих таблицах.
    • 
ЛЕВОЕ СОЕДИНЕНИЕ
      (LEFT JOIN): Возвращает все строки из левой таблицы и совпадающие строки из правой. Если совпадений в правой таблице нет, поля правой таблицы будут содержать NULL.
    • 
ПРАВОЕ СОЕДИНЕНИЕ
      (RIGHT JOIN): Аналогично левому, но приоритет отдается правой таблице.
    • 
ПОЛНОЕ СОЕДИНЕНИЕ
      (FULL JOIN): Возвращает все строки из обеих таблиц, дополняя NULL там, где нет совпадений.

    Условие соединения задается после ключевого слова ПО.

  9. Функции работы с датами:

    В 1С запросах доступны функции для манипуляции датами, которые полезны при построении отчетов по периодам. Например:

    • 
НАЧАЛОПЕРИОДА(<Дата>, <Период>)
      : Возвращает начало указанного периода (Минута, Час, День, Неделя, Месяц, Квартал, Год и т.д.) для заданной даты.
    • 
КОНЕЦПЕРИОДА(<Дата>, <Период>)
      : Возвращает конец указанного периода для заданной даты.
    • 
ГОД(<Дата>)
      , 
      
МЕСЯЦ(<Дата>)
      , 
      
ДЕНЬ(<Дата>)
      и т.д.: Извлекают соответствующую часть даты.

Рекомендации по оптимизации запросов 1С

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

  1. Избегайте "разыменования через точку" в условиях 
    
ГДЕ
    для полей составного типа:

    Обращение к реквизитам через несколько точек (например, Документ.Контрагент.Партнер) может приводить к неявным соединениям и значительно замедлять выполнение запроса, особенно если поле имеет составной тип. Вместо этого рекомендуем явно использовать соединения (

    
СОЕДИНЕНИЕ
    ) или функцию 
    
ВЫРАЗИТЬ
    для приведения поля к конкретному типу.

  2. Осторожно используйте 
    
ИЛИ
    (OR) и 
    
В
    (IN) на больших объемах данных:

    Эти операторы могут приводить к полному сканированию таблиц и снижать производительность. В некоторых случаях их можно переформулировать с помощью 

    
ОБЪЕДИНИТЬ
    или временных таблиц.

  3. Применяйте временные таблицы (
    
ПОМЕСТИТЬ
    ):

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

  4. Корректно используйте виртуальные таблицы:

    Виртуальные таблицы (например, регистров накопления, регистров сведений) созданы для удобства, но при их использовании всегда необходимо указывать условия отбора (особенно по периоду), чтобы избежать выборки излишнего объема данных.

  5. Избегайте запросов в цикле:

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

  6. Используйте параметры запроса:

    Передача значений через параметры запроса (&Параметр) позволяет платформе кэшировать план запроса, что ускоряет его повторное выполнение. Это также повышает безопасность и читаемость кода.

Мы рассмотрели, как объединять данные из разных источников, группировать их и подсчитывать необходимые значения, а также как учитывать временные периоды. Помните, что практика и эксперименты – ваши лучшие помощники в освоении языка запросов 1С. Успехов в работе!

← К списку