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

← На главную

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

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

Определение основного условия: "значение присутствует сразу и в 0x81AE...D и в 0x81AE...E"

Центральной частью нашего запроса является условие, требующее, чтобы значение поля _Value2_RRRef было связано одновременно с двумя различными значениями поля _AccountRRef (0x81AE38D5471A68A211E8F9FEE2BB117D и 0x81AE38D5471A68A211E8F9FEE2BB117E). Для реализации этого мы можем использовать несколько подходов в SQL. Рассмотрим каждый из них подробно.

1. Использование GROUP BY и HAVING

Этот метод является одним из наиболее эффективных и интуитивно понятных для решения нашей задачи. Мы сначала отфильтруем все записи, где поле _AccountRRef соответствует одному из наших целевых значений. Затем мы сгруппируем результаты по полю _Value2_RRRef. Внутри каждой группы мы проверим, присутствует ли в ней достаточное количество различных значений _AccountRRef (в нашем случае — два, по одному для каждого из искомых счетов).

Разберем по шагам:

Первичная фильтрация: Отбираем все записи из таблицы _AccRgAT333430, где _AccountRRef входит в наш список целевых значений (0x81AE38D5471A68A211E8F9FEE2BB117D и 0x81AE38D5471A68A211E8F9FEE2BB117E).
Группировка: Группируем полученные записи по полю _Value2_RRRef. Это позволит нам рассматривать все записи, связанные с одним и тем же уникальным значением _Value2_RRRef.
Условие HAVING: Применяем условие HAVING COUNT(DISTINCT _AccountRRef) = 2. Это условие проверяет, что внутри каждой группы _Value2_RRRef мы нашли ровно два различных значения _AccountRRef из нашего первоначального фильтра. То есть, если для какого-либо _Value2_RRRef мы нашли записи и со значением 0x81AE...D, и со значением 0x81AE...E, то COUNT(DISTINCT _AccountRRef) будет равно 2.
Финальный подсчет: Внешний запрос COUNT(DISTINCT Subquery._Value2_RRRef) подсчитает количество уникальных _Value2_RRRef, которые прошли все предыдущие фильтры и условия.

Пример SQL-кода с использованием GROUP BY и HAVING:


SELECT COUNT(DISTINCT Subquery._Value2_RRRef)
FROM (
    SELECT _Value2_RRRef
    FROM _AccRgAT333430
    WHERE _AccountRRef IN (0x81AE38D5471A68A211E8F9FEE2BB117D, 0x81AE38D5471A68A211E8F9FEE2BB117E)
    -- Добавляем условие по дате, как мы его рассмотрим ниже
    AND _Period = '5999-11-01 00:00:00'
    GROUP BY _Value2_RRRef
    HAVING COUNT(DISTINCT _AccountRRef) = 2
) AS Subquery;

Здесь _Period — это типичное название поля даты в регистрах 1С.

2. Использование EXISTS с подзапросами

Метод с использованием EXISTS позволяет нам проверить наличие связанных записей для каждого условия. Мы будем искать записи с первым значением _AccountRRef и для каждой такой записи проверять, существует ли соответствующая запись с тем же _Value2_RRRef, но уже со вторым значением _AccountRRef.

Как это работает:

Основной запрос: Выбираем _Value2_RRRef из таблицы _AccRgAT333430 (псевдоним T1) для первого целевого значения _AccountRRef (0x81AE...D).
Подзапрос EXISTS: Для каждой записи из основного запроса выполняется подзапрос EXISTS. Этот подзапрос ищет в той же таблице (псевдоним T2) запись, где _Value2_RRRef совпадает с текущим T1._Value2_RRRef, а _AccountRRef равно второму целевому значению (0x81AE...E).
Логика EXISTS: Если подзапрос EXISTS находит хотя бы одну такую запись, условие EXISTS возвращает TRUE, и запись T1 включается в результирующий набор.
Финальный подсчет: Внешний COUNT(DISTINCT T1._Value2_RRRef) подсчитывает уникальные значения, которые прошли проверку EXISTS.

Пример SQL-кода с использованием EXISTS:


SELECT COUNT(DISTINCT T1._Value2_RRRef)
FROM _AccRgAT333430 AS T1
WHERE T1._AccountRRef = 0x81AE38D5471A68A211E8F9FEE2BB117D
-- Добавляем условие по дате
AND T1._Period = '5999-11-01 00:00:00'
AND EXISTS (
    SELECT 1
    FROM _AccRgAT333430 AS T2
    WHERE T2._Value2_RRRef = T1._Value2_RRRef
      AND T2._AccountRRef = 0x81AE38D5471A68A211E8F9FEE2BB117E
      -- Добавляем условие по дате для связанной записи
      AND T2._Period = '5999-11-01 00:00:00'
);

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

3. Использование INTERSECT

Оператор INTERSECT позволяет нам найти общие элементы между двумя наборами данных. В нашем случае, мы можем получить два списка _Value2_RRRef: один для первого _AccountRRef, второй для второго _AccountRRef, а затем найти пересечение этих списков.

Принцип работы:

Первый набор данных: Выбираем уникальные _Value2_RRRef, которые связаны с первым _AccountRRef (0x81AE...D) и удовлетворяют условию по дате.
Второй набор данных: Выбираем уникальные _Value2_RRRef, которые связаны со вторым _AccountRRef (0x81AE...E) и удовлетворяют условию по дате.
Пересечение: Оператор INTERSECT возвращает только те _Value2_RRRef, которые присутствуют в обоих наборах.
Финальный подсчет: Внешний COUNT(*) подсчитывает количество элементов в результирующем наборе пересечения.

Пример SQL-кода с использованием INTERSECT:


SELECT COUNT(*) FROM (
    SELECT _Value2_RRRef
    FROM _AccRgAT333430
    WHERE _AccountRRef = 0x81AE38D5471A68A211E8F9FEE2BB117D
    AND _Period = '5999-11-01 00:00:00'
    INTERSECT
    SELECT _Value2_RRRef
    FROM _AccRgAT333430
    WHERE _AccountRRef = 0x81AE38D5471A68A211E8F9FEE2BB117E
    AND _Period = '5999-11-01 00:00:00'
) AS IntersectedValues;

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

Типы данных для значений ссылок 1С (0x81AE...)

Значения, подобные 0x81AE38D5471A68A211E8F9FEE2BB117D, являются стандартным представлением внутренних ссылок 1С на объекты (счета, элементы справочников, документы и т.д.). В SQL Server эти значения обычно хранятся в полях с типом данных VARBINARY(16) или BINARY(16). Критически важно правильно указывать эти значения в запросе, передавая их в бинарном формате. Если вы просто вставите строку без префикса 0x или используете неправильный формат, SQL Server не сможет найти соответствующие записи.

Поле _AccountRRef в таблице _AccRgAT333430 — это ссылка на элемент справочника "ПланыСчетов", который представляет собой счет бухгалтерского учета. Поле _Value2_RRRef — это также ссылка, вероятно, на объект аналитики, например, элемент справочника "Контрагенты" или "Номенклатура", в зависимости от субконто счета.

Условие по дате: 01.11.5999 0:00:00

Дата 01.11.5999 0:00:00 является знаковой в системах 1С. Она часто используется как "бесконечная" или "максимальная" дата для обозначения остатков на конец света, записей, актуальных навсегда, или итоговых значений на самый отдаленный период. В регистрах бухгалтерии и накопления 1С для хранения даты используется поле _Period. Поэтому условие по дате в нашем запросе будет выглядеть так:

AND _Period = '5999-11-01 00:00:00'

Убедитесь, что формат даты, который вы используете в запросе (например, 'YYYY-MM-DD HH:MI:SS'), соответствует типу данных поля _Period в вашей базе данных (обычно DATETIME или DATETIME2 в SQL Server).

Выполнение запроса к 100 базам данных

Самой трудоемкой частью задачи является необходимость выполнения запроса не к одной, а к 100 различным базам данных. Здесь мы рассмотрим наиболее распространенные и эффективные подходы.

1. Связанные серверы (Linked Servers)

Если все 100 баз данных находятся на доступных экземплярах SQL Server, вы можете настроить "связанные серверы" (Linked Servers) в вашем основном SQL Server. Связанный сервер позволяет обращаться к таблицам в удаленных базах данных так, как если бы они были локальными, используя четырехчастные имена.

Настройка: Для каждой из 100 баз данных настраиваем отдельный связанный сервер. Это делается через SQL Server Management Studio (SSMS) в разделе "Server Objects" -> "Linked Servers" или с помощью системных хранимых процедур, таких как sp_addlinkedserver.
Обращение к данным: После настройки вы можете обращаться к таблицам удаленных баз данных, используя синтаксис [ИмяСвязанногоСервера].[ИмяБазыДанных].[Схема].[ИмяТаблицы]. Например: [LINKED_SERVER_NAME].[MyDB].[dbo].[_AccRgAT333430].
Преимущества: Позволяет писать единый запрос, который потенциально может объединять данные из разных источников.
Недостатки: Настройка 100 связанных серверов может быть трудоемкой. Производительность может зависеть от сетевых задержек и эффективности обработки запроса удаленным сервером.

Пример части запроса с использованием связанного сервера:


SELECT COUNT(DISTINCT Subquery._Value2_RRRef)
FROM (
    SELECT _Value2_RRRef
    FROM [LINKED_SERVER_NAME_1].[DataBase_1].[dbo].[_AccRgAT333430]
    WHERE _AccountRRef IN (0x81AE38D5471A68A211E8F9FEE2BB117D, 0x81AE38D5471A68A211E8F9FEE2BB117E)
    AND _Period = '5999-11-01 00:00:00'
    GROUP BY _Value2_RRRef
    HAVING COUNT(DISTINCT _AccountRRef) = 2
) AS Subquery;

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

2. Динамический SQL или скриптинг

Этот подход предполагает формирование SQL-запроса в цикле. Мы можем написать скрипт на языке, который умеет подключаться к SQL Server (например, PowerShell, Python, C# или даже T-SQL с использованием курсоров и динамических запросов), и для каждой из 100 баз данных выполнить наш основной запрос, собирая результаты.

Список баз данных: Нам понадобится список серверов и/или баз данных.
Цикл: Скрипт будет итерировать по этому списку, для каждой базы данных:
- Устанавливать соединение.
- Выполнять наш SQL-запрос.
- Сохранять полученный результат (например, в временную таблицу или в файл).
- Закрывать соединение.
Агрегация: После выполнения всех запросов, мы агрегируем собранные данные.

Преимущества: Большая гибкость, не требует предварительной настройки 100 связанных серверов. Позволяет лучше управлять ошибками и таймаутами для каждой базы отдельно.

Недостатки: Требует написания дополнительного кода/скрипта вне SQL Server (или сложного динамического T-SQL).

Пример T-SQL для динамического формирования запроса (упрощенно, для одной СУБД с множеством баз):


DECLARE @db_name NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);
DECLARE @TotalCount INT = 0;

-- Создаем временную таблицу для хранения результатов
IF OBJECT_ID('tempdb..#ResultCounts') IS NOT NULL DROP TABLE #ResultCounts;
CREATE TABLE #ResultCounts (DatabaseName NVARCHAR(128), ValueCount INT);

-- Курсор по всем пользовательским базам данных
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb'); -- Исключаем системные базы

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = '
    USE [' + @db_name + '];
    INSERT INTO #ResultCounts (DatabaseName, ValueCount)
    SELECT ''' + @db_name + ''', COUNT(DISTINCT Subquery._Value2_RRRef)
    FROM (
        SELECT _Value2_RRRef
        FROM _AccRgAT333430
        WHERE _AccountRRef IN (0x81AE38D5471A68A211E8F9FEE2BB117D, 0x81AE38D5471A68A211E8F9FEE2BB117E)
        AND _Period = ''5999-11-01 00:00:00''
        GROUP BY _Value2_RRRef
        HAVING COUNT(DISTINCT _AccountRRef) = 2
    ) AS Subquery;';

    BEGIN TRY
        EXEC sp_executesql @sql;
    END TRY
    BEGIN CATCH
        PRINT 'Ошибка в базе данных ' + @db_name + ': ' + ERROR_MESSAGE();
    END CATCH;

    FETCH NEXT FROM db_cursor INTO @db_name;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

-- Суммируем результаты со всех баз
SELECT @TotalCount = SUM(ValueCount) FROM #ResultCounts;
SELECT 'Общее количество уникальных значений по всем базам: ' AS Description, @TotalCount AS TotalUniqueCount;
-- SELECT * FROM #ResultCounts; -- Можно посмотреть результаты по каждой базе

Этот пример адаптирован для баз данных на одном экземпляре SQL Server. Для разных экземпляров вам потребуется использовать OPENROWSET с параметрами или подключаться из внешнего скрипта.

3. Central Management Server (CMS) и Multi-Server Queries

Если все 100 баз данных находятся на разных экземплярах SQL Server, вы можете использовать возможности Central Management Server (CMS). CMS позволяет вам организовать группы серверов и выполнять запросы к нескольким серверам одновременно. Результаты выполнения запроса будут объединены в одну сетку.

Преимущества: Удобный инструмент для администраторов SQL Server, позволяющий выполнять однотипные запросы на множестве серверов без написания сложного кода.

Недостатки: Требует предварительной настройки CMS, не всегда подходит для сложной логики агрегации или обработки ошибок. Все серверы должны быть зарегистрированы в CMS.

Оптимизация производительности

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

Индексы: Убедитесь, что в таблице _AccRgAT333430 созданы соответствующие индексы на полях, участвующих в условиях WHERE, GROUP BY и потенциально JOIN/EXISTS:
- _AccountRRef (для быстрого отбора по счетам)
- _Value2_RRRef (для быстрой группировки и подсчета уникальных)
- _Period (для быстрого отбора по дате)
Комбинированные индексы, такие как ( _AccountRRef, _Period, _Value2_RRRef ) или ( _Period, _AccountRRef, _Value2_RRRef ), могут быть особенно эффективными, так как они покрывают сразу несколько условий запроса.
План выполнения запроса: Всегда анализируйте план выполнения запроса (Execution Plan). Это можно сделать в SQL Server Management Studio (SSMS), включив опцию "Include Actual Execution Plan" или используя команду SET SHOWPLAN_ALL ON. План выполнения покажет, какие операции занимают больше всего времени и ресурсов (сканирование таблиц, поиск по индексам, сортировки, хеширование). Это поможет вам выявить "узкие места" и определить, где можно применить дополнительные индексы или переписать часть запроса.
Размер таблиц и фильтрация: Если таблица _AccRgAT333430 очень большая (что характерно для регистров 1С), постарайтесь максимально сузить объем данных на ранних этапах запроса. Условие по дате _Period = '5999-11-01 00:00:00' уже является хорошим фильтром, но если у вас есть возможность добавить другие ограничения (например, по организации, виду операции, если они доступны в таблице и применимы к задаче), это может значительно уменьшить количество обрабатываемых строк до выполнения ресурсоемких операций GROUP BY или DISTINCT.
Оценка ресурсов: При выполнении запросов к 100 базам данных, особенно через связанные серверы, внимательно отслеживайте загрузку сети, процессора и дисковой подсистемы как на стороне инициатора запроса, так и на стороне целевых серверов. Параллельное выполнение запросов (если вы используете скриптинг) может быть быстрее, но требует более тщательного управления ресурсами.

Заключение

Мы с вами подробно рассмотрели, как сформировать SQL-запрос для подсчета уникальных значений в поле _Value2_RRRef регистра бухгалтерии 1С, удовлетворяющих сложному условию одновременной связи с несколькими счетами и определенной датой. Мы изучили три основных метода SQL (GROUP BY/HAVING, EXISTS, INTERSECT), разобрались с особенностями работы с бинарными ссылками 1С и полем даты _Period. Кроме того, мы обсудили стратегии выполнения таких запросов к большому количеству баз данных, а также ключевые аспекты оптимизации производительности. Применяя эти знания, вы сможете эффективно извлекать необходимые данные из вашей инфраструктуры 1С через SQL.