Регистры накопления. Виртуальные таблицы. Часть №3: Остатки и обороты.

05 июля 2013

Файлы для загрузки

Тестовая конфигурация с примером из статьи

Все действия, проделанные далее в статье, выполнены на этой конфигурации

Виртуальная таблица "Остатки и обороты" и ее параметрыПредисловие

В предыдущих статьях мы рассмотрели работу платформы 1С:Предприятие 8.2 (в статье использовалась версия 8.2.17.169) с базой данных при использовании виртуальных таблиц остатков и оборотов.

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

"Тяжелая" таблица

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

Выполним следующий запрос на языке запросов платформы:

Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
|    ОстаткиНоменклатурыОстаткиИОбороты.Номенклатура,
|    ОстаткиНоменклатурыОстаткиИОбороты.Склад,
|    ОстаткиНоменклатурыОстаткиИОбороты.КоличествоНачальныйОстаток,
|    ОстаткиНоменклатурыОстаткиИОбороты.КоличествоПриход,
|    ОстаткиНоменклатурыОстаткиИОбороты.КоличествоОборот,
|    ОстаткиНоменклатурыОстаткиИОбороты.КоличествоРасход,
|    ОстаткиНоменклатурыОстаткиИОбороты.КоличествоКонечныйОстаток
|ИЗ
|    РегистрНакопления.ОстаткиНоменклатуры.ОстаткиИОбороты(
|                                     &НачалоПериода,
|                                     &КонецПериода, 
|                                     , 
|                                     ,
|                                     Склад = &Склад) 
|                     КАК ОстаткиНоменклатурыОстаткиИОбороты"; 

Параметрам запроса присвоим следующие значения:

Установленные значения параметров запроса

Такие параметры как "Периодичность" и "МетодДополнения" мы оставили без заполнения. Сначала платформа выполнить запрос для получения настроек регистра накопления. Его мы подробно рассмотрели в статье по виртуальной таблице "Обороты", поэтому сейчас останавливаться на нем не будем.

При таких настройках платформа сформирует следующий SQL-запрос для рассматриваемой виртуальной таблицы:

"exec sp_executesql N'
|SELECT
| T1.Fld22RRef,           // Номенклатура
| T1.Fld23RRef,           // Склад
| T1.Fld24InitialBalance_,// КоличествоНачальныйОстаток
| T1.Fld24Receipt_,       // КоличествоПриход
| T1.Fld24Turnover_,      // КоличествоОборот
| T1.Fld24Expense_,       // КоличествоРасход
| T1.Fld24FinalBalance_   // КоличествоКонечныйОстаток
|FROM (
|     SELECT
|      T2.Fld23RRef AS Fld23RRef, // Склад
|      T2.Fld22RRef AS Fld22RRef, // Номенклатура
|      CAST(SUM(T2.Fld24Turnover_) AS NUMERIC(28, 8)) 
|        AS Fld24Turnover_, // КоличествоОборот
|      CAST(SUM(T2.Fld24Receipt_) AS NUMERIC(28, 8))  
|        AS Fld24Receipt_, // КоличествоПриход
|      CAST(SUM(T2.Fld24Expense_) AS NUMERIC(28, 8))  
|        AS Fld24Expense_, // КоличествоРасход
|      CAST(SUM(T2.Fld24Balance_) AS NUMERIC(34, 8))  
|        AS Fld24InitialBalance_, // НачальныйОстаток
|      CAST(SUM(T2.Fld24Balance_ + T2.Fld24Turnover_) 
|        AS NUMERIC(35, 8)) AS Fld24FinalBalance_
|                                // КонечныйОстаток
|     FROM ("+
//         +++ ПОЛУЧЕНИЕ ДАННЫХ ИЗ ТАБЛИЦЫ ДВИЖЕНИЙ +++
"          SELECT
|           T3._Fld23RRef AS Fld23RRef, // Склад
|           T3._Fld22RRef AS Fld22RRef, // Номенклатура
|           // КоличествоОстаток
|           CAST(CAST(SUM(0.0) AS NUMERIC(15, 8)) 
|             AS NUMERIC(22, 2)) AS Fld24Balance_,
|           // КоличествоОборот                    
|           CAST(SUM(CASE WHEN T3._RecordKind = 0.0 
|                         THEN T3._Fld24 
|                         ELSE -T3._Fld24 END) 
|                AS NUMERIC(22, 8)) AS Fld24Turnover_,
|           // Приход
|           CAST(SUM(CASE WHEN T3._RecordKind = 0.0 
|                         THEN T3._Fld24 
|                         ELSE 0.0 END) 
|                    AS NUMERIC(22, 8)) AS Fld24Receipt_,
|           // Расход
|           CAST(SUM(CASE WHEN T3._RecordKind = 0.0 
|                         THEN 0.0 
|                         ELSE T3._Fld24 END) 
|                    AS NUMERIC(22, 8)) AS Fld24Expense_"+
//         Получаем данные из таблицы движений регистра
"          FROM _AccumRg21 T3 WITH(NOLOCK)"+
//         Устанавливаем условия по параметрам вирт. таб.
"          WHERE T3._Period >= @P1 // Начало периода
|                AND T3._Period <= @P2 // Конец периода
|                AND T3._Active = @P3 // Активность
|                AND ((T3._Fld23RRef = @P4)) // Склад"
//         Группируем результат и проверяем, чтобы хотя бы 
//         один ресурс не был равен 0.
"          GROUP BY T3._Fld23RRef,
|                   T3._Fld22RRef
|          HAVING (CAST(CAST(SUM(@P5) AS NUMERIC(15, 8)) 
|          AS NUMERIC(22, 2))) <> @P5 
|          OR (CAST(SUM(CASE WHEN T3._RecordKind = 0.0 
|                            THEN T3._Fld24 
|                            ELSE -T3._Fld24 END) 
|              AS NUMERIC(22, 8))) <> @P5 
|          OR (CAST(SUM(CASE WHEN T3._RecordKind = 0.0 
|                            THEN |T3._Fld24 
|                            ELSE 0.0 END) 
|              AS NUMERIC(22, 8))) <> @P5 
|          OR (CAST(SUM(CASE WHEN |T3._RecordKind = 0.0 
|                            THEN 0.0 
|                            ELSE T3._Fld24 END) 
|                   AS NUMERIC(22, 8))) <> @P5"+
//         --- ПОЛУЧЕНИЕ ДАННЫХ ИЗ ТАБЛИЦЫ ДВИЖЕНИЙ ---
//
//        Объединяем результаты запросов к таб. движений
//        и к таблице остатков
"         UNION ALL"+
//        
//         +++ ПОЛУЧЕНИЕ ДАННЫХ ИЗ ТАБЛИЦЫ ОСТАТКОВ+++
"          SELECT
|           T4._Fld23RRef AS Fld23RRef,// Склад
|           T4._Fld22RRef AS Fld22RRef,// Номенклатура
|           CAST(SUM(T4._Fld24) AS NUMERIC(28, 8)) 
|             AS Fld24Balance_, // КоличествоОстаток
|           CAST(0.0 AS NUMERIC(16, 2)) 
|             AS Fld24Turnover_, // Оборот
|           CAST(0.0 AS NUMERIC(16, 2)) 
|             AS Fld24Receipt_, // Приход
|           CAST(0.0 AS NUMERIC(16, 2)) 
|             AS Fld24Expense_ // Расход"
//         Получаем данные из таблицы остатков
"          FROM _AccumRgT25 T4 WITH(NOLOCK)
|          WHERE T4._Period = @P1 
|                AND ((T4._Fld23RRef = @P4))
|          GROUP BY T4._Fld23RRef,
|                   T4._Fld22RRef
|          HAVING (CAST(SUM(T4._Fld24) 
|                      AS NUMERIC(28, 8))) <> @P5"+
//         --- ПОЛУЧЕНИЕ ДАННЫХ ИЗ ТАБЛИЦЫ ОСТАТКОВ---
"     ) T2
|     GROUP BY T2.Fld23RRef,
|              T2.Fld22RRef
|     HAVING (CAST(SUM(T2.Fld24Turnover_) 
|                  AS NUMERIC(28, 8))) <> @P5 
|     OR (CAST(SUM(T2.Fld24Receipt_) AS NUMERIC(28, 8))) <> @P5 
|     OR (CAST(SUM(T2.Fld24Expense_) AS NUMERIC(28, 8))) <> @P5 
|     OR (CAST(SUM(T2.Fld24Balance_) AS NUMERIC(34, 8))) <> @P5 
|     OR (CAST(SUM(T2.Fld24Balance_ + T2.Fld24Turnover_) 
|              AS NUMERIC(35, 8))) <> @P5
|) T1', 
|N'@P1 datetime, // НачалоПериода
|@P2 datetime, // КонецПериода
|@P3 varbinary(1), // Активность
|@P4 varbinary(16), // Склад
| // Знач. для проверки на 0
|@P5 numeric(1,0)', 
| // НачалоПериода
|{ts '4012-01-01 00:00:00'},
| // КонецПериода
|{ts '4014-01-01 00:00:00'}, 
| // Активность
|0x01, 
| // Склад
|0xBE923860773387FD11E2D2B47CD2CB1E, 
| // Знач. для проверки на 0
|0"

Прокомментировал основные моменты в запросе. Общая схема работы запроса такая:

  1. Получаем обороты регистра по таблице движений за установленный период.
  2. Получаем остатки на значение даты параметра "Начало периода".
  3. Объединяем предыдущие два результата, при этом поле "НачальныйОстаток" - это остаток по данным таблицы остатков, а "КонечныйОстаток" вычисляется как : "НачальныйОстаток" + "Оборот"
  4. Полученные данные группируются по выбранным в запросе измерениям и проверяются на наличие хотя бы одного заполненного ресурса (не равного 0).

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

Сам SQL-запрос может изменяться в зависимости от значений параметров виртуальной таблицы. Например, если мы добавим периодичность, то в запрос будет добавлено дополнительное поле "Период", по которому результат будет группироваться. При установке параметра "МетодДополнения" в запрос будут попадать границы периода, если установлено значение "ДвиженияИГраницыПериода" (именно с таким значением параметра мы анализировали SQL-запрос), иначе в результате запроса будут только движения.

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

Все замечания, вопросы прошу оставлять в комментариях к статье.


comments powered by Disqus