Каталог решений - Запросы. Временные Таблицы. Сравнение методов создания ВТ

Итак, приступим. Где обычно нам требуются сложные запросы?

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

В указанном примере из книги Радченко/Хрусталева на стр 429 мы должны в процедуре проведения документа "Оказание услуг" найти среднюю стоимость списываемой документом номенклатуры. Для этого мы используем данные из двух регистров остатков:СтоимостьМатериалов и СтоимостьМатериаловОстатки.

Из регистра остатков материала — мы берем общее количество данного списываемого товара, а из регистра стоимость материалов — мы берем стоимость этого товара (стоимость товара в регистр записывается при проведении Приходной накладной).

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

Собственно все просто: взяли два регистра, поставили условие по товару из документа — и получили требуемый результат. Но если мы в реальной базе, где данных в регистрах может быть сотни тысяч записей — будем перебирать в результативном цикле ВСЕ записи регистров, чтобы сверить их с перечнем товара из нашего документа, то это будет очень долго и трудоемко!

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

И вот для решения задачи оптимизации проведения документа — нам и требуется создать некий фильтр из списка товара по накладной. Для этого мы создаем таблицу, выгружаем в нее номенклатуру из текущего документа. Далее к этой таблице в запросе Левым соединением прикрепляем наши регистры, после чего в итоговую выборку запроса попадают только данные, отфильтрованные перечнем товара из накладной! Ничего лишнего, все работает быстро!

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

В приведенном учебнике — нам дают следующий вариант создания Временных Таблиц в запросах:

Для работы с запросами есть объект МенеджерВременныхТаблиц, о нем можно прочитать в Синтаксис-помощнике:Работа с запросами=>МенеджерВременныхТаблиц (см.рис)

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

По примеру из урока — мы обозначаем эту самую область, в которую далее создадим и загрузим данными нашу табличку:

//Создать менеджер временных таблиц
	  МенеджерВТ=Новый МенеджерВременныхТаблиц;
	
	Запрос = Новый Запрос;
	
	//Укажем какой Менеджер временных таблиц использует этот запрос
	Запрос.МенеджерВременныхТаблиц=МенеджерВТ;
	
	Запрос.Текст = 

 Далее по уроку — мы, используя меню под правой кнопкой мыши, — открываем КонструкторЗапроса:

Теперь здесь в запросе — мы полностью формируем со всеми условиями вид нашей временной таблицы, а так же заполняем ее сразу данными. В этом запросе мы выбираем табличную часть документа "ОказаниеУслуг", выбираем необходимые нам поля из этого документа:Номенклатуру, ВидНоменклатуры, Количество по документу и Сумму по документу.

Далее в этом запросе мы накладываем условия, что работаем только с текущим конкретным документом "ОказаниеУслуг", а не со всеми этими документами сразу (в условии запроса ставим параметр &Ссылка на конкретный документ)

В этом же запросе — мы добавляем группировку (и суммирование по группировке) по Номенклатуре и ее виду, т.к. в документе у нас может быть задвоенная позиция по одному Товару.

И вот мы добрались до вкладки "Дополнительно" в Конструкторе запросов, где непосредственно указываем, что мы создаем временную таблицу, и ее новое имя "НоменклатураДокумента":

Текст Запроса с созданной временной таблицей по уроку из книги Радченко/Хрусталева будет следующим:

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

Вот таким довольно интересным способом по уроку уважаемых преподавателей Максима Григорьевича Радченко и Елены Юрьевны Хрусталевой мы создали временную таблицу, в которую поместили данные о номенклатуре из проводимого нами документа.

Далее по занятию — нам следует связать нашу созданную временную таблицу с двумя регистрами (Остатков материалов и Стоимостью номенклатуры).

Для этого мы создаем второй Запрос, к которому сразу привяжем наш менеджер Временных таблиц, в котором, собственно, и хранится наша таблица:

	Запрос2 = Новый Запрос;
	
	//Укажем какой Менеджер временных таблиц использует этот запрос
	Запрос2.МенеджерВременныхТаблиц=МенеджерВТ;
	
	Запрос2.Текст =

Так же с помощью меню под правой кнопкой мыши — выберем пункт КонструкторЗапросов. Здесь во втором запросе — мы добавляем наши два регистра по Остаткам и Стоимости. А вот чтобы привязать их к созданной предыдущим запросом ВременнойТаблице, которая у нас хранится в МенеджереВременныхТаблиц, нам нужно нажать на кнопку в КонструктореЗапросов "Создать описание временной таблицы" — и полностью прописать название полей и их тип, которые есть в той Временной таблице, что мы создали первым запросом:

Данный второй запрос очень простой — мы просто создаем в КонструктореЗапросов на вкладке "Связи" — связь нашей временной таблицы с двумя регистрами ЛЕВЫМ СОЕДИНЕНИЕМ. Только в этом случае у нас справа будет наша ВременнаяТаблица, а слева от нее все равно ВЕСЬ набор данных регистров!!! Поэтому, если так и оставим, то оптимизации работы процедуры проведения документа — мы так и не добьемся!

Значит нам нужно в КонструктореЗапросов в параметры регистров наложить ограничение выборки данных четко по данным из ВременнойТаблицы. Для этого выделим в КонструктореЗапросов каждый из регистров и нажмем на кнопку "Параметры виртуальной таблицы" (см.рис), далее в поле "Условие" нажмем на кнопку выборки и в открывшемся окне "ПроизвольноеВыражение" запишем условие:

Материал В
	(ВЫБРАТЬ
		НоменклатураДокумента.Номенклатура
	ИЗ
		НоменклатураДокумента)

***********Весь листинг с двумя запросами по учебнику выглядит следующим образом:

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

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

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Сделаем первоначальные выводы — каким образом мы создавали Временную таблицу и связывали ее с регистрами:

1.Явно прописали МенеджерВременныхТаблиц

2.Отдельным первым запросом создали Временную таблицу и заполнили ее данными

3.Переподключили МенеджерВременныхТаблиц на второй запрос

4.Во втором запросе пришлось:

1) в ручную прописывать структуру нашей ВременнойТаблицы с обязательным указанием Типов полей;

2) создать связи между временной таблицей и двумя регистрами;

3) на каждый из регистров наложить предварительно фильтр из данных временной таблицы;

4) накладываемый фильтр на регистры — нам пришлось писать в ручную

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

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

У нас есть очень хороший инструмент для создания запросов — КонсольЗапросов. Я пользуюсь своей //sale.itcity.ru/public/719293/  Вы можете использовать любую КонсольЗапросов, которая Вам нравится.

Итак, откроем 1с в пользовательском режиме, вызовем КонсольЗапросов. И начнем сразу же с создания так необходимой нам временной таблицы (создайте поля и тип данных точно такой, как указан в Конфигураторе в документе "ОказанияУслуг" в его табличной части!):

Здесь нет ничего сложного, кроме одной очень важной ремарки — перед наименованием нашей таблички мы ставим амперсант!

&ВнешниеДанные

С помощью амперсанта — мы конкретно указываем, что создаем не просто табличку, а Параметр, который необходим к заполнению при отработке нашего запроса.

Далее идем в КонструктореЗапроса на вкладку "Дополнительно" — и здесь явно указываем что наш созданный набор данных — это ВременнаяТаблица! Назовем эту самую временную таблицу точно так же, как и в уроке по книжке: "НоменклатураДокумента".

Все, временная таблица есть, не нужно более над нею думать)). Но чтобы все заработало, причем в ОДНОМ запросе, нам нужно создать пакет запросов. Для этого в КонструктореЗапросов переходим на вкладку "ПакетЗапросов", нажимаем Добавить, и уже в полученном втором пакете запросов мы имеем возможность видеть,выбирать и работать с только что созданной нами временной таблицей!!!

Поэтому добавим в данный второй пакет запроса нашу ВременнуюТаблицу и нужных два регистра. Установим точно так же связи ВременнойТаблицы Левым Сооединением с регистрами.

Наш запрос практически готов, осталось только на сами регистры наложить фильтр из ВременнойТаблицы. Для этого точно так же выделяем каждый регистр в КонструктореЗапросов и жмем кнопку "Параметры виртуальной таблицы", далее в поле условие нажимаем на выборку и попадаем в окно "Произвольное выражение".

Здесь перетягиваем в расчетное окно реквизит Материал регистра, пишем одну единственную буковку В, ставим скобки и внутри скобок правой клавишей мыши — вызываем еще один КонструкторЗапросов, в котором просто из нашей временной таблицы выбираем поле Номенклатура,все сохраняем и точно так же накладываем Параметр на второй регистр.

Второй КонструкторЗапросов создаст нам следующий код, который и будет условием/фильтром на регистре:

Материал В
ВЫБРАТЬ
	НоменклатураДокумента.Номенклатура
ИЗ
	НоменклатураДокумента КАК НоменклатураДокумента

Все, запрос полностью готов, сохраняем,закрываем КонструкторЗапросов. Можно, конечно еще на выбранные поля в регистров в КонструктореЗапросов поставить условие ЕстьNull. Можем так же во втором пакете сгрупировать поля по Номенклатуре и просуммировать все остальные поля.

***********Вот такой запрос в единственном экземпляре у нас получился более простым и наглядным методом:

ВЫБРАТЬ
	ВнешниеДанные.Номенклатура,
	ВнешниеДанные.Количество КАК КоличествоВДокументе,
	ВнешниеДанные.Сумма КАК СуммаВДокументе
ПОМЕСТИТЬ НоменклатураДокумента
ИЗ
	&ВнешниеДанные КАК ВнешниеДанные
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	НоменклатураДокумента.Номенклатура,
	СУММА(НоменклатураДокумента.КоличествоВДокументе) КАК КоличествоВДокументе,
	СУММА(НоменклатураДокумента.СуммаВДокументе) КАК СуммаВДокументе,
	СУММА(ЕСТЬNULL(ОстаткиМатериаловОстатки.КоличествоОстаток, 0)) КАК Количество,
	СУММА(ЕСТЬNULL(СтоимостьМатериаловОстатки.СтоимостьОстаток, 0)) КАК Стоимость
ИЗ
	НоменклатураДокумента КАК НоменклатураДокумента
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СтоимостьМатериалов.Остатки(
				,
				Материал В
					(ВЫБРАТЬ
						НоменклатураДокумента.Номенклатура
					ИЗ
						НоменклатураДокумента КАК НоменклатураДокумента)) КАК СтоимостьМатериаловОстатки
		ПО НоменклатураДокумента.Номенклатура = СтоимостьМатериаловОстатки.Материал
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиМатериалов.Остатки(
				,
				Материал В
					(ВЫБРАТЬ
						НоменклатураДокумента.Номенклатура
					ИЗ
						НоменклатураДокумента КАК НоменклатураДокумента)) КАК ОстаткиМатериаловОстатки
		ПО НоменклатураДокумента.Номенклатура = ОстаткиМатериаловОстатки.Материал

СГРУППИРОВАТЬ ПО
	НоменклатураДокумента.Номенклатура

Только в данном варианте при создании сложного запроса с ВременнымиТаблицами в КонсолиЗапросов — мы имеем преимущество — это наглядно запустить наш запрос и увидеть сразу же результат его работы!!!!!!

Для этого нажмите все соответствующие кнопки в КонсолиЗапросов (я рассказывала как ею пользоваться с вариантом  ВременныхТаблиц вот здесь //sale.itcity.ru/public/719293/): НайтиПараметры, потом в поле "ТаблицаЗначений",на вкладке "ВремТаблицы" — заполните экспериментальные данные товара. И вот результат — все считает, все видно, все наглядно:

Осталось уже в модуле документа в процедуре Проведения — точно так же сделать обход цикла по результатам запроса и все готово. Да в данном варианте нужно будет перед запуском запроса — создать Таблицу значений и выгрузить в нее колонки табличной части документа.

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Сделаем выводы по поводу второго метода создания временных таблиц в запросе, которым пользуюсь я:

1.Один единственный запрос с двумя пакетами, создается не в ручную, а в КонсолиЗапросов.

В первом пакете запросов — создали временную таблицу, которая наглядна видна при работе со вторым пакетом запросов!

2.Во втором пакете запросе пришлось:

1) Просто перетянули мышкой готовую ВременнуюТаблицу

2) создать связи между временной таблицей и двумя регистрами;

3) на каждый из регистров наложить предварительно фильтр из данных временной таблицы;

4) накладываемый фильтр на регистры — мы создавали автоматически,вызвав еще один КонструкторЗапросов!

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Итого, вывод — в учебнике  "1С_ Предприятие 8.3. Практическое пособие разработчика. Примеры и типовые приемы" Радченко/Хрусталева показан очень интересный способ создания Временных таблиц для Сложных запросов, но все-таки на практике проще всего работать с тем, что можно сразу проверить,пощупать,отредактировать….

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

В загрузке прилагается учебная база данных (создавала с нуля на Платформе 1с8.3)по урокам из книги  ("1С_ Предприятие 8.3. Практическое пособие разработчика. Примеры и типовые приемы" Радченко/Хрусталева) + описанный здесь мой вариант создания Временных таблиц (в проц.Проведения док."ОказаниеУслуг"). На данный момент как раз до уровня середины книги, то есть по описанный здесь урок о Временных таблицах в запросе.

Напомню, что данный пример рассматривается в процедуре Проведения документа "ОказаниеУслуг". В модуле этой процедуры Вы найдете оба варианта, описанного здесь: Попробуйте в режиме Предприятия посмотреть как работает один вариант, потом его заремарьте и снимите ремарку со второго варианта — посмотрите как работает другой — абсолютно индентично!

 ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////