Как получить остатки на каждый день прямым SQL-запросом к MS SQL Server

При работе с высоконагруженными базами данных 1С, построении внешних аналитических систем (BI, хранилищ данных) или интеграции с корпоративными порталами стандартных механизмов платформы 1С может не хватать по производительности. В таких ситуациях разработчики прибегают к прямым запросам к СУБД MS SQL Server. Однако получение остатков на каждый день напрямую из SQL — задача нетривиальная. Платформа 1С берет на себя колоссальный объем работы по расчету итогов, обращению к виртуальным таблицам и оптимизации запросов.

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

Внутренняя структура хранения регистров накопления в MS SQL

Прежде чем приступать к написанию запроса, нам необходимо детально проанализировать, из каких физических таблиц СУБД состоит регистр накопления остатков. Для каждого такого регистра платформа 1С создает три ключевые таблицы:

1. Таблица движений (_AccumRg<ID>) — хранит все записи (регистрации событий прихода и расхода), которые проводятся документами. Именно здесь содержатся детальные данные по каждой хозяйственной операции.
2. Таблица итогов (_AccumRgT<ID>) — хранит рассчитанные остатки на начало каждого месяца (точнее, на 1-е число каждого месяца в 00:00:00). Это служебная таблица, оптимизирующая расчет остатков на произвольные даты.
3. Таблица настроек итогов (_AccumRgOpt<ID>) — содержит вспомогательную информацию, например, дату актуальности итогов и периодичность их разделения.

Идентификаторы таблиц (например, _AccumRg1234) уникальны для каждой информационной базы. Чтобы узнать точные имена физических таблиц вашего регистра в MS SQL, выполните в 1С метод ПолучитьСтруктуруХраненияБазыДанных() или воспользуйтесь специальными обработками по просмотру структуры метаданных.

Обратим внимание на ключевые поля в таблице движений _AccumRg<ID>:

• _Period — дата и время совершения движения (содержит смещение дат, о котором поговорим ниже).
• _Active — признак активности записи. При расчете остатков обязательно нужно накладывать фильтр _Active = 0x01 (активные записи). Неактивные движения (например, у непроведенных или помеченных на удаление документов) учитывать нельзя.
• _RecordKind — направление движения: 0 — Расход, 1 — Приход.
• Поля аналитики (измерения) имеют префикс _Fld<ID>_RRef для ссылочных типов (хранятся в бинарном формате binary(16)) или просто _Fld<ID> для примитивных типов.
• Поля ресурсов (например, количество или сумма) хранятся в полях вида _Fld<ID> с числовым типом данных.

Специфика хранения дат в 1С: Смещение на 2000 лет

Выясним важную техническую деталь: практически во всех базах данных 1С на MS SQL включена опция смещения дат на 2000 лет. Это сделано для корректной обработки "пустых" дат (01.01.0001), так как минимальная дата, которую поддерживает тип данных DATETIME в MS SQL Server — это 01.01.1753.

Это означает, что реальная дата 15 октября 2023 года запишется в таблицу MS SQL как 15 октября 4023 года. Соответственно, при передаче параметров в SQL-запрос мы обязаны прибавлять 2000 лет к искомой дате с помощью функции DATEADD, а при выводе результатов пользователю — вычитать обратно те же 2000 лет.

Как платформа 1С считает остатки: Алгоритм работы СУБД

Платформа 1С использует комбинацию двух стратегий для получения остатков на дату $D$:

1. Расчет "вперед" от прошлых итогов: СУБД выбирает ближайшую точку итогов из таблицы _AccumRgT<ID> на начало месяца, в котором находится дата $D$. Затем к этой точке прибавляются все приходы и вычитаются все расходы из таблицы движений _AccumRg<ID> за период от начала этого месяца до даты $D$.
2. Расчет "назад" от будущих (актуальных) итогов: Платформа берет остатки на максимальную точку итогов (в 1С это виртуальная дата 5999-11-01 при смещении на 2000 лет) и последовательно вычитает из них обороты в обратном хронологическом порядке до даты $D$. Этот метод наиболее эффективен, если дата $D$ близка к текущему времени, так как объем движений "в будущем" минимален.

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

Пошаговая методология построения T-SQL запроса

Для реализации запроса нам потребуются следующие шаги:

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

Проанализируем пример комплексного T-SQL запроса. Предположим, что у нас есть регистр накопления "ТоварыНаСкладах" со следующими параметрами в SQL:

• Таблица движений: _AccumRg1200
• Таблица итогов: _AccumRgT1201
• Поле "Склад" (измерение): _Fld1202_RRef
• Поле "Номенклатура" (измерение): _Fld1203_RRef
• Поле "Количество" (ресурс): _Fld1204

Посмотрим на пример T-SQL кода, который решает поставленную задачу:

-- Объявляем переменные для периода (вводятся без смещения)
DECLARE @StartDateParam DATETIME = '2023-10-01 00:00:00';
DECLARE @EndDateParam DATETIME = '2023-10-31 23:59:59';

-- Применяем смещение на 2000 лет для работы с физическими таблицами 1С
DECLARE @StartDate DATETIME = DATEADD(year, 2000, @StartDateParam);
DECLARE @EndDate DATETIME = DATEADD(year, 2000, @EndDateParam);

-- Шаг 1: Генерируем календарь дней в рамках периода с помощью рекурсивного CTE
WITH Calendar AS (
    SELECT @StartDate AS [DateOnly]
    UNION ALL
    SELECT DATEADD(day, 1, [DateOnly])
    FROM Calendar
    WHERE [DateOnly] < DATEADD(day, -1, @EndDate)
),

-- Шаг 2: Получаем уникальный список аналитики (Склад, Номенклатура), по которым были остатки или движения
ActiveAnalytics AS (
    SELECT DISTINCT _Fld1202_RRef AS WarehouseRRef, _Fld1203_RRef AS ItemRRef
    FROM _AccumRg1200
    WHERE _Active = 0x01
),

-- Шаг 3: Рассчитываем начальный остаток на дату @StartDate
-- Берем итоги на начало месяца и корректируем движениями до даты старта
InitialBalance AS (
    SELECT 
        WarehouseRRef,
        ItemRRef,
        SUM(StartQty) AS StartBalanceQty
    FROM (
        -- Точка 1: Остаток из таблицы месячных итогов
        SELECT 
            _Fld1202_RRef AS WarehouseRRef,
            _Fld1203_RRef AS ItemRRef,
            _Fld1204 AS StartQty
        FROM _AccumRgT1201
        WHERE _Period = DATEADD(month, DATEDIFF(month, 0, @StartDate), 0) -- Начало месяца стартовой даты

        UNION ALL

        -- Точка 2: Обороты от начала месяца до @StartDate с обратным знаком для выравнивания
        SELECT 
            _Fld1202_RRef AS WarehouseRRef,
            _Fld1203_RRef AS ItemRRef,
            CASE WHEN _RecordKind = 0 THEN _Fld1204 ELSE -_Fld1204 END AS StartQty
        FROM _AccumRg1200
        WHERE _Active = 0x01
          AND _Period >= DATEADD(month, DATEDIFF(month, 0, @StartDate), 0)
          AND _Period < @StartDate
    ) AS SubSelect
    GROUP BY WarehouseRRef, ItemRRef
),

-- Шаг 4: Собираем ежедневные обороты за указанный период
DailyTurnovers AS (
    SELECT 
        DATEADD(day, DATEDIFF(day, 0, _Period), 0) AS [Day],
        _Fld1202_RRef AS WarehouseRRef,
        _Fld1203_RRef AS ItemRRef,
        SUM(CASE WHEN _RecordKind = 1 THEN _Fld1204 ELSE -_Fld1204 END) AS DailyTurnoverQty
    FROM _AccumRg1200
    WHERE _Active = 0x01
      AND _Period >= @StartDate
      AND _Period <= @EndDate
    GROUP BY DATEADD(day, DATEDIFF(day, 0, _Period), 0), _Fld1202_RRef, _Fld1203_RRef
),

-- Шаг 5: Формируем полную сетку "День - Аналитика" для правильного распределения нарастающего итога
Grid AS (
    SELECT 
        c.[DateOnly] AS [Date],
        a.WarehouseRRef,
        a.ItemRRef
    FROM Calendar c
    CROSS JOIN ActiveAnalytics a
),

-- Шаг 6: Соединяем сетку с начальными остатками и ежедневными оборотами
PreCalculated AS (
    SELECT 
        g.[Date],
        g.WarehouseRRef,
        g.ItemRRef,
        ISNULL(ib.StartBalanceQty, 0) AS StartBalanceQty,
        ISNULL(dt.DailyTurnoverQty, 0) AS DailyTurnoverQty
    FROM Grid g
    LEFT JOIN InitialBalance ib ON g.WarehouseRRef = ib.WarehouseRRef AND g.ItemRRef = ib.ItemRRef
    LEFT JOIN DailyTurnovers dt ON g.[Date] = dt.[Day] AND g.WarehouseRRef = dt.WarehouseRRef AND g.ItemRRef = dt.ItemRRef
)

-- Финальный шаг: Расчет остатка на каждый день с помощью оконной функции нарастающего итога
SELECT 
    DATEADD(year, -2000, p.[Date]) AS [Date], -- Возвращаем дату к реальному исчислению 1С
    p.WarehouseRRef AS Warehouse_ID,
    p.ItemRRef AS Item_ID,
    -- К начальному остатку прибавляем сумму оборотов по текущий день включительно
    p.StartBalanceQty + SUM(p.DailyTurnoverQty) OVER (
        PARTITION BY p.WarehouseRRef, p.ItemRRef 
        ORDER BY p.[Date] 
        ROWS UNBOUNDED PRECEDING
    ) AS BalanceQty
FROM PreCalculated p
ORDER BY Warehouse_ID, Item_ID, [Date]
OPTION (MAXRECURSION 0);

Разбор ключевых элементов запроса

Проанализируем наиболее важные и сложные части представленного T-SQL кода:

• Рекурсивный CTE Calendar: Генерирует непрерывную последовательность дат. Обратите внимание на директиву оптимизации OPTION (MAXRECURSION 0) в самом конце запроса. Она необходима для снятия ограничения SQL Server на количество уровней рекурсии (по умолчанию равно 100), чтобы мы могли формировать отчеты за периоды более 100 дней без ошибок СУБД.
• Подзапрос InitialBalance: Здесь реализована математика расчета остатков. Мы извлекаем базовый срез итогов из таблицы _AccumRgT1201 на начало месяца даты старта. Так как эта таблица хранит данные именно на начало месяца, мы должны скорректировать эту цифру. Для этого мы берем все движения с начала этого месяца до точного времени старта периода. Если движение имело вид "Расход" (_RecordKind = 0), мы прибавляем его к начальному остатку, а если "Приход" (_RecordKind = 1) — вычитаем (мы делаем ретроспективный "откат" назад во времени).
• Оконная функция SUM(...) OVER(...): Это ядро расчета ежедневного баланса. С помощью конструкции PARTITION BY p.WarehouseRRef, p.ItemRRef мы указываем СУБД, что нарастающий итог должен рассчитываться строго индивидуально для каждого сочетания склада и номенклатуры. Конструкция ORDER BY p.[Date] ROWS UNBOUNDED PRECEDING гарантирует последовательное суммирование оборотов от первого дня периода до текущего.

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

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

1. Сужение аналитики (Фильтрация): Построение полной сетки CROSS JOIN между всеми датами и всей активной номенклатурой базы данных в CTE Grid может породить гигантский массив строк (миллионы записей) в памяти СУБД. Настоятельно рекомендуется ограничивать выборку в CTE ActiveAnalytics конкретным списком складов, номенклатуры или групп товаров, передавая их уникальные идентификаторы (_RRef) в качестве параметров.
2. Использование индексов СУБД: Платформа 1С автоматически создает кластерные индексы по полям _Period и измерениям. SQL-сервер наиболее эффективен, когда вы используете точные фильтры по датам и первому измерению индекса (обычно это Склад или Организация). Убедитесь, что ваш запрос выполняет операцию Index Seek вместо ресурсоемкого сканирования Index Scan.
3. Фильтрация нулевых остатков: После выполнения нарастающего итога в результирующей выборке появится множество строк, где конечный остаток равен 0 (например, товар был продан в начале месяца, а по календарю пустая строка продолжает тянуться до конца месяца). Для оптимизации сетевого трафика и разгрузки принимающей стороны добавьте финальный фильтр, отсекающий записи с нулевым значением BalanceQty.

Как быстро найти готовый SQL-код запроса 1С

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

Способ 1: Получение плана выполнения через Консоль запросов

Разобранный на форуме метод отлично подходит для быстрой отладки:

1. Откройте универсальную консоль запросов 1С (например, встроенную в БСП).
2. Напишите стандартный запрос 1С к виртуальной таблице остатков регистра накопления.
3. В настройках или командной панели консоли установите флаг Показывать план выполнения запроса.
4. Выполните запрос. На вкладке плана выполнения вы увидите детальный граф операций СУБД, а также полный и точный T-SQL текст запроса, который платформа отправила на сервер MS SQL.

Способ 2: Использование MS SQL Server Profiler

Если вам нужно перехватить реальный запрос "на лету" в процессе работы системы:

1. Запустите утилиту SQL Server Profiler и подключитесь к вашему серверу баз данных.
2. Настройте фильтрацию событий: выберите событие SQL:BatchCompleted или RPC:Completed. В фильтрах столбцов укажите имя вашей базы данных (DatabaseName) и добавьте текстовый фильтр, например, TextData LIKE %_AccumRg%, чтобы отсечь лишний трафик.
3. В конфигураторе 1С установите точку останова в коде непосредственно перед выполнением интересующего вас запроса.
4. Запустите отладку 1С, остановитесь на точке, запустите трассировку в профайлере и сделайте один шаг отладки вперед (выполнив запрос).
5. В окне профайлера появится точный системный T-SQL код, сгенерированный сервером приложений 1С. Скопируйте его и используйте в качестве шаблона для построения собственных аналитических скриптов.