Как правильно посчитать количество уникальных документов и суммарное количество номенклатуры в прямом SQL-запросе 1С 7.7

← На главную

При работе с прямыми SQL-запросами в среде 1С 7.7 (используя 1C++ или аналогичные компоненты) разработчики часто сталкиваются с проблемой «раздувания» выборки. Типичная ситуация: вам нужно получить список документов по определенной номенклатуре, посчитать общее количество товара и количество самих документов. Однако, если одна и та же номенклатура встречается в табличной части (ТЧ) документа несколько раз, стандартный запрос может выдать некорректные результаты, считая каждую строку ТЧ как отдельный документ.

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

Разбор проблемы: Почему возникают дубли?

Рассмотрим ситуацию на примере запроса к документу ПоступлениеТМЦ_Склад. Когда мы соединяем таблицу журнала _1SJourn с таблицей строк документа ($ДокументСтроки), SQL-сервер формирует плоскую таблицу. Если в одном документе (один IDDoc) номенклатура «Болт» указана в двух строках с разным количеством, то при простой выборке вы получите две записи. Если вы попытаетесь применить COUNT, вы получите «2», хотя документ на самом деле один.

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


 SELECT
   $Документ.ПоступлениеТМЦ_Склад.Контрагент      AS [Контрагент],
   $Док.Номенклатура [Номенклатура],
   $Док.Количество   [Количество],
   count(distinct Док.IDDOC)       AS [КолДок]
FROM
   _1SJourn Жур
INNER JOIN
    $ДокументСтроки.ПоступлениеТМЦ_Склад AS Док ON Док.IDDoc = Жур.IDDoc  
GROUP BY $ПоступлениеТМЦ_Склад.Контрагент, $Док.Номенклатура, $Док.Количество

Ошибка здесь заключается в том, что поле $Док.Количество включено в блок GROUP BY. Это заставляет SQL-сервер разделять строки даже одного документа, если в них разное количество товара. Кроме того, поле Количество выводится «как есть», без агрегации, что не позволяет получить общую сумму по всем строкам документа.

Решение 1: Правильная агрегация и группировка

Первый и самый важный шаг — это использование функции SUM для числовых показателей и удаление этих показателей из блока группировки. Для подсчета уникальных документов мы продолжаем использовать COUNT(DISTINCT IDDOC).

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


 SELECT
   $ПоступлениеТМЦ_Склад.Контрагент AS [Контрагент],
   $Док.Номенклатура [Номенклатура],
   SUM($Док.Количество) AS [Количество], -- Суммируем количество
   COUNT(DISTINCT Жур.IDDOC) AS [КолДок] -- Считаем только уникальные ID документов
FROM
   _1SJourn Жур
LEFT JOIN
   $Документ.ПоступлениеТМЦ_Склад AS ПоступлениеТМЦ_Склад ON Жур.IDDOC = ПоступлениеТМЦ_Склад.IDDOC     
INNER JOIN
   $ДокументСтроки.ПоступлениеТМЦ_Склад AS Док ON Док.IDDoc = Жур.IDDoc  
WHERE
   Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~ AND
   Жур.iddocdef = $ВидДокумента.ПоступлениеТМЦ_Склад AND
   ($Док.Номенклатура IN (SELECT Val FROM #TempTable))
GROUP BY 
   $ПоступлениеТМЦ_Склад.Контрагент, 
   $Док.Номенклатура

В данном случае, благодаря SUM($Док.Количество), все строки табличной части с одинаковой номенклатурой внутри одного документа (или разных документов, попавших в одну группу по контрагенту) будут сложены. А COUNT(DISTINCT Жур.IDDOC) гарантирует, что каждый физический документ будет учтен только один раз, сколько бы строк в его табличной части не соответствовало условиям фильтра.

Решение 2: Предварительная агрегация (подзапросы)

Если ваша база данных содержит миллионы строк, использование DISTINCT внутри COUNT может существенно замедлить выполнение запроса, так как SQL-серверу приходится строить временные таблицы в памяти для поиска уникальных значений. В таких случаях эффективнее использовать вложенные запросы.

Разберем этот подход подробнее:

Сначала мы создаем внутренний запрос, который агрегирует данные на уровне строк документа (группируем по IDDoc и Номенклатура).
Затем внешний запрос соединяет эти результаты с журналом документов и справочниками для получения финальных итогов.

Пример структуры такого запроса:


SELECT 
    T.Контрагент,
    T.Номенклатура,
    SUM(T.СуммаКол) as ОбщееКоличество,
    COUNT(T.IDDoc) as КолДокументов
FROM (
    SELECT 
        $Шапка.Контрагент as Контрагент,
        $Строки.Номенклатура as Номенклатура,
        Строки.IDDoc,
        SUM($Строки.Количество) as СуммаКол
    FROM $ДокументСтроки.ПоступлениеТМЦ_Склад AS Строки
    INNER JOIN $Документ.ПоступлениеТМЦ_Склад AS Шапка ON Строки.IDDoc = Шапка.IDDoc
    WHERE $Строки.Номенклатура IN (SELECT Val FROM #TempTable)
    GROUP BY $Шапка.Контрагент, $Строки.Номенклатура, Строки.IDDoc
) AS T
GROUP BY T.Контрагент, T.Номенклатура

Такой подход избавляет от необходимости использовать DISTINCT во внешнем запросе, так как подзапрос уже выдает одну уникальную строку на каждую пару «Документ-Номенклатура».

Важные нюансы оптимизации в 1С 7.7

Выясним, на что еще стоит обратить внимание при написании прямых запросов к SQL-версии 1С 7.7:

1. Фильтрация состояния документа.
В исходном примере автора используется условие (Жур.CLOSED & 1 = 1). Это означает выборку только проведенных документов. Однако мы рекомендуем также проверять флаг пометки на удаление IsMark. В идеале условие должно выглядеть так: (Жур.CLOSED & 1 = 1) AND (Жур.IsMark = 0). Это исключит ситуации, когда из-за программных сбоев документ числится проведенным, но помечен на удаление.

2. Использование прямых таблиц шапок (DH).
Если вы фильтруете данные только по одному виду документа, иногда быстрее обращаться напрямую к таблице шапки $Документ.ИмяДокумента (в базе это DHxxxx), минуя общий журнал _1SJourn. Таблица шапки уже содержит IDDoc и все реквизиты, специфичные для этого вида документа.

3. Оптимизация работы с временными таблицами.
Если ваш список номенклатуры в #TempTable содержит сотни или тысячи позиций, обязательно создайте индекс по полю Val в этой таблице перед основным запросом. Также старайтесь заменять оператор IN (SELECT Val FROM #TempTable) на INNER JOIN #TempTable ON .... Для оптимизатора SQL-сервера JOIN обычно является более прозрачным и позволяет построить лучший план выполнения.

4. Обращение к регистрам.
Как верно заметили опытные разработчики в обсуждении, если вам нужно получить итоги по количеству в разрезе номенклатуры и контрагентов, стоит рассмотреть возможность запроса к таблицам регистров (движения RA* или итоги RG*). Регистры в 1С изначально спроектированы для быстрого получения агрегированных данных. Если в регистре не хватает какого-то измерения (например, контрагента), его можно «подтянуть» через соединение с таблицей документа-регистратора по ссылке.

Резюме

Для корректного подсчета уникальных документов при одновременном суммировании их содержимого:

Никогда не включайте суммируемые ресурсы (количество, сумма) в GROUP BY.
Используйте SUM() для ресурсов и COUNT(DISTINCT IDDOC) для подсчета документов.
Для повышения производительности на больших объемах данных используйте вложенные запросы с предварительной группировкой по документам.
Всегда проверяйте статусы документов (проведен/удален) в журнале.

Следуя этим правилам, вы получите точные данные и обеспечите стабильную работу системы даже при больших объемах информации в табличных частях.