gifts2017

Срез последних на каждую дату в СКД и в запросе

Опубликовал Алексей Орлов (_also) в раздел Программирование - Практика программирования

С задачей получения среза последних на каждую дату рано или поздно сталкиваются все. Безусловно, это задача достаточно просто решается запросом с соединением по максимальной дате из меньших или равных дат. Но эту же задачу можно решить и с помощью компоновки данных. Не будем рассуждать о том, какой из способов более производителен, все может зависеть от конкретной задачи.

Первый набор данных:

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

Создадим набор данных-запрос «ПродажиОбороты»:

ВЫБРАТЬ
ПродажиОбороты.Период КАК Дата,
ПродажиОбороты.Контрагент КАК Контрагент,
ПродажиОбороты.Номенклатура КАК Номенклатура,
ПродажиОбороты.КоличествоОборот КАК Количество,
ПродажиОбороты.СтоимостьОборот КАК Стоимость
{ВЫБРАТЬ
Дата,
Контрагент.*,
Номенклатура.*,
Количество,
Стоимость}
ИЗ
РегистрНакопления.Продажи.Обороты({(&НачалоПериода)},
{(&КонецПериода)}, День, ) КАК ПродажиОбороты
{ГДЕ
ПродажиОбороты.Период,
ПродажиОбороты.Контрагент.*,
ПродажиОбороты.Номенклатура.*,
ПродажиОбороты.КоличествоОборот,
ПродажиОбороты.СтоимостьОборот}

 

Сейчас наш отчет будет иметь следующий вид:

 

Теперь необходимо добавить в отчет колонку «Цена по прайсу», которая будет подтягиваться из регистра сведений «Цены номенклатуры» на дату продажи.

Второй набор данных:

Добавим второй набор данных-запрос «Цены», цены будем брать для фиксированного типа цен:

ВЫБРАТЬ
&Дата КАК Дата,
ЦеныНоменклатурыСрезПоследних.Номенклатура КАК Номенклатура,
ЦеныНоменклатурыСрезПоследних.Цена КАК Цена
{ВЫБРАТЬ
Дата,
Номенклатура.*,
Цена}
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
&Дата,
Номенклатура = &Номенклатура
И ТипЦен = &ТипЦен) КАК ЦеныНоменклатурыСрезПоследних
{ГДЕ
(&Дата),
ЦеныНоменклатурыСрезПоследних.Номенклатура.*,
ЦеныНоменклатурыСрезПоследних.Цена}

В данном наборе данных три параметра: Дата, Номенклатура и тип цен. Из низ самые интересные Дата и Номенклатура. Они будут использованы при соединении наборов данных, причем параметр данных присутствует как в параметрах виртуальной таблицы, так и в выбранных полях.

Соединения наборов:

Приступим к основной «фишке» данного метода – соединениям наборов:

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

Далее добавим поле цена в ресурсы и в выбранные поля.

Результат:

Теперь можно формировать отчет. Проверим правильность работы отчета на примере «Дивана для отдыха».

В запросе:

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

Текст запроса:

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

СГРУППИРОВАТЬ ПО
ПродажиОбороты.Период,
ПродажиОбороты.Контрагент,
ПродажиОбороты.Номенклатура

ИНДЕКСИРОВАТЬ ПО
Номенклатура,
Дата,
Контрагент
;

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

СГРУППИРОВАТЬ ПО
втБезЦены.Дата,
втБезЦены.Контрагент,
втБезЦены.Номенклатура,
втБезЦены.Количество,
втБезЦены.Стоимость

ИНДЕКСИРОВАТЬ ПО
Номенклатура,
Дата,
Контрагент,
Период
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
втМаксПериод.Дата,
втМаксПериод.Контрагент,
втМаксПериод.Номенклатура,
втМаксПериод.Количество,
втМаксПериод.Стоимость,
ЦеныНоменклатуры.Цена
ИЗ
втМаксПериод КАК втМаксПериод
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ПО втМаксПериод.Номенклатура = ЦеныНоменклатуры.Номенклатура
И втМаксПериод.Период = ЦеныНоменклатуры.Период
ГДЕ
ЦеныНоменклатуры.ТипЦен = &ТипЦен
АВТОУПОРЯДОЧИВАНИЕ

Данный пакетный запрос содержит три подзапроса. Рассмотрим их подробнее.

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

Во втором запросе пакета мы соединяем временную таблицу с регистром сведений «ЦеныНоменклатуры» при этом из регистра сведений мы выбираем МАКСИМАЛЬНУЮ дату из меньших или равных дат.  Результат этого запроса также помещаем во временную таблицу (втМаксПериод). Посмотрим, какие данные попадают в эту таблицу:

В последнем запросе пакета, мы еще раз соединяем временную таблицу с таблицей цен номенклатуры. На этот мы соединяем таблице по номенклатуре и периоду.

Итоговый результат запроса:

Как мы видим результаты вывода цен в обоих случаях (через СКД и через запрос) оказались одинаковы.

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Игорь Исхаков (Ish_2) 02.11.10 06:57
Мелкие замечания.
1. На мой взгляд , лучше говорить "второй запрос в пакете" , а не " второй подзапрос".
2. Как-то надо обозначить опасность применения в запросе соединения по неравенству.
При розничной торговле с гибкой политикой цен пользователь может быть сильно разочарован таким отчетом. "В среднем " более надежно отсечь период до "Даты начала" и вначале использовать соединение с виртуальной таблицей СрезПоследних и получить цены на "дату начала" для каждой номенклатуры. А затем уже делать соединение по неравенству с регистром , используя условие для записей регистра "Где Период >= &ДатаНачала"
2. Алексей Орлов (_also) 02.11.10 09:18
1. Спасибо, поправил
2. Ну все таки отчет приведенный в статье не имеет более общий характер. Соглашусь с Вашим мнением, но все же это уже относиться к реальным задачам. Статья - просто пример ;)
3. Сергей Старых (tormozit) 02.11.10 13:58
Метод, примененный в СКД очень неэффективен, т.е. будет работать значительно медленнее, чем пакете/запросе.
PhoenixAOD; zqzq; echo77; AllexSoft; Gilev.Vyacheslav; theshadowco; _also; artbear; +8 Ответить 2
4. Валерий Гайдабура (director04) 02.11.10 14:13
Спасибо, очень интересные решения. Кстати, в одной из задач на экзамене по Бух есть именно такая задача по построению отчета.
Находил иные решения на Мисте, но против данных решений - они просто монстры.
Красиво и просто
5. МagIvan (RailMen) 02.11.10 14:21
Реализация всевдо среза первых и последних записей на каждый день периода для расчета среднесписочной численности: http://infostart.ru/public/77462/
6. Алексей Орлов (_also) 02.11.10 15:10
(3) в СКД можно добиться некоторых плюсов, если некоторые данные не нужны. У них можно установить флаги ограничений, и СКД не будет их получать. А так согласен в подавляющем большинстве случаев запрос эффективнее.
7. Ivon (Ivon) 02.11.10 16:15
Вот тут http://infostart.ru/public/21181/ моя статья по той же теме.
8. Игорь <...> (I_G_O_R) 02.11.10 21:40
Запросы это хорошо , обожаю запросы :) , вот вам еще один вариант, может кто сможет еще короче написать:
ВЫБРАТЬ
	Продажи.Период,
	Продажи.Контрагент,
	Продажи.Номенклатура,
	Продажи.КоличествоОборот КАК Количество,
	Продажи.СтоимостьОборот КАК Стоимость,
	ЦеныНоменклатуры.Цена
ИЗ
	РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК Продажи
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
		ПО Продажи.Номенклатура = ЦеныНоменклатуры.Номенклатура
			И (ЦеныНоменклатуры.ТипЦен = &ТипЦен)
			И (ЦеныНоменклатуры.Период В
				(ВЫБРАТЬ ПЕРВЫЕ 1
					Цены.Период
				ИЗ
					РегистрСведений.ЦеныНоменклатуры КАК Цены
				ГДЕ
					Цены.Период <= Продажи.Период
					И Цены.Номенклатура = Продажи.Номенклатура
					И Цены.ТипЦен = &ТипЦен
				УПОРЯДОЧИТЬ ПО
					Цены.Период УБЫВ))
...Показать Скрыть

philya; batman2004; sommid; Flashback1979SE; +4 Ответить 5
9. Игорь <...> (I_G_O_R) 02.11.10 22:04
Кстати во втором запросе есть ошибки и недочеты;
1) в первом запросе данные группировать НЕ надо! т.к. данные из виртуальной таблицы уже сгруппированы
2) во втором запросе не учитывается типцен, поэтому он выдаст направильный результат, если есть несколько типов цен
3) в третьем запросе условие на типцен должно быть в соединении а не в условии, поэтому третий запрос вернет только продажи той номенклатуры, для который установлена цена &ТипЦен
10. Игорь Исхаков (Ish_2) 03.11.10 00:44
(10) Проверить не могу. Навскидку :
Выглядит симпатично. Могу предположить , что работает быстрее ,чем пример в статье.
И думаю, вот почему : твой запрос не требует создания дополнительных временных индексов , а использует уже существующие индексы регистра. Насколько я понимаю, все выражения в условии соединения составлены так , что не требуют "дополнительных" усилий от оптимизатора запроса. Ну , а сам то ты сравнивал по быстродействию свой запрос и запрос из статьи ?
11. Игорь Исхаков (Ish_2) 03.11.10 00:47
(9) ух.. глазастый какой.

Смотри (10).
12. Игорь <...> (I_G_O_R) 03.11.10 01:19
(11) сравнивал, но на небольшой базе, использовал модифицированный запрос из (0), убрал АВТОУПОРЯДОЧИВАНИЕ и исправил ошибки, в итоге мой запрос совсем чуть-чуть быстрее, а если добавить АВТОУПОРЯДОЧИВАНИЕ, то мой запрос заметно проигрывает, прямо даже на глаз видно, не знаю с чем это связано. Буду на работе попробую, если будет время, на большой базе. Есть у нас клиент, у которого очень много типов цен, интересно на такой базе проверить. Я думаю, что в такой базе, мой запрос может показать лучшие результаты, из-за того что в запросе из (0) второй запрос по идее должен выполнятся долго, т.к. это та же проблема что и с нарастающими итогами.
13. Игорь Исхаков (Ish_2) 03.11.10 07:02
(12) Ага. Интересно проверить и сравнить оптимальный, исправленный запрос из (0) и твой. Причем в твоем запросе , та же проблема что и с нарастающими итогами.
Если таблицу регистра цен увеличить в 2 раза то длительность твоего запроса , думаю, увеличится как минимум в 3 раза.
И вот еще что . Как и в (0) нужно , на мой взгляд, отсечь период до "Даты начала" сделав вначале соединение со срезом последних регистра цен.
14. Игорь Исхаков (Ish_2) 03.11.10 08:18
(12) +13 Ведь твой вложенный запрос в (8) выполнится столько раз - сколько записей в регистре ЦеныНоменклатуры (если продажи анализируются для всей номенклатуры и без ограничения периода). Еще раз повторяю - выглядит симпатично , но нужно проверять.

С другой стороны, если бы поле выборки языка запроса 1с могло быть запросом (как во всех диалектах SQL), то :

ВЫБРАТЬ 
   Продажи.Период, 
   Продажи.Контрагент, 
   Продажи.Номенклатура, 
   Продажи.КоличествоОборот КАК Количество, 
   Продажи.СтоимостьОборот КАК Стоимость, 
   (ВЫБРАТЬ ПЕРВЫЕ 1 
               Цены.Цена 
            ИЗ 
               РегистрСведений.ЦеныНоменклатуры КАК Цены 
            ГДЕ 
               Цены.Период <= Продажи.Период 
               И Цены.Номенклатура = Продажи.Номенклатура 
               И Цены.ТипЦен = &ТипЦен 
            УПОРЯДОЧИТЬ ПО 
               Цены.Период УБЫВ) как Цена 
ИЗ 
   РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК Продажи 
...Показать Скрыть


В этом случае вложенный запрос выполнится столько раз сколько записей в основном запросе.
15. Игорь Исхаков (Ish_2) 03.11.10 12:38
А вообще, пост (14) об относительности совершенства любого алгоритма.
С точки зрения специалиста 1с - приведенный в (0) алгоритм можно считать образцом.
Cпециалист же знакомый с SQL воскликнет :
"Какое извращение ! Из за ограничений языка запросов 1с ребятам приходится кувыркаться и изобретать велосипед с квадратными колесами (0)"
16. Игорь <...> (I_G_O_R) 03.11.10 14:39
(12) насчет среза согласен, но в этом случае я бы делал как в статье, а то мой запрос из красивого и короткого превратится длинный и некрасивый
17. VVV (V_V_V) 03.11.10 16:15
Народ, ну вам же не надо объяснять, что "длинный" и "некрасивый" запрос 1С 8 - это НЕ всегда неоптимально и малоэффективно... :D
18. Rabajaba Caspersky (Rabajaba) 04.11.10 10:32
Тоже долго думал как собрать среднесписочное количество сотрудников, добавил справочник "ПериодыУчета" с 3-мя полями: "ДатаНачала", "ДатаОкончания", "ВидПериода"(День,Месяц,Год). В результате получить по дням можно все что угодно, при этом запрос остается читабельным и компактным. Ну а для регистров накопления конечно лучше использовать параметр виртуальной таблицы "День".
19. Игорь Исхаков (Ish_2) 06.11.10 05:49
(12) Так, что , Игорь , поговорили ... и всё ?
В (8) ты привел свой альтернативный вариант запроса. Давай уж выясним до конца :
Быстрее ли он работает , чем вариант приведенный в статье (0) ?
На больших объемах данных. Что скажешь ?
20. Игорь <...> (I_G_O_R) 06.11.10 16:45
(19) может быть и не быстрее, зато красиво. С группировками способ все знают, а мой наверное меньше. Я не против проверить, но бызы нет под рукой, могу только на работе. Вообще идея просто сгенерировать базу, в которой будет много номенклатуры(? только сколько)и например каждый день записана цена и каждая номенклатура продавалась каждый день 1 раз. А вообще я ща в гости иду, так что сегодня некогда.
21. Игорь Исхаков (Ish_2) 06.11.10 16:47
(20) Много не пей. Послезавтра ..проверять запрос.
22. Игорь <...> (I_G_O_R) 14.11.10 14:42
(21) уже заждался наверное результатов, а результаты очень прикольные. Тесты проводил на сгенерированной базе: регистры были в точности перенесены из УТ, только удалены измерения, которых нет в запросе, вообщем все свойства этих измерений(индексировать, ведущее) совпадают. В базе 10000 номенклатуры, 500 контрагентов, с начала 2010 года на каждый день для каждой номенклатуры есть запись в регистры ЦеныНоменклатуры, в регистре Продажи есть одна на запись на каждый день одной номенклатуры и случайным образом подобранным контрагентом(т.е за один день 10000 записей а кому она продалась это как повезет). Для замеров использовались запросы:

1) это запрос из статьи с исправлением ошибок и без индексов(без индексов показал лучшие результаты):
ВЫБРАТЬ
	ПродажиОбороты.Период КАК Дата,
	ПродажиОбороты.Контрагент КАК Контрагент,
	ПродажиОбороты.Номенклатура КАК Номенклатура,
	ПродажиОбороты.КоличествоОборот КАК Количество,
	ПродажиОбороты.СтоимостьОборот КАК Стоимость
ПОМЕСТИТЬ втБезЦены
ИЗ
	РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК ПродажиОбороты

;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	втБезЦены.Дата КАК Дата,
	втБезЦены.Контрагент КАК Контрагент,
	втБезЦены.Номенклатура КАК Номенклатура,
	втБезЦены.Количество,
	втБезЦены.Стоимость,
	МАКСИМУМ(ЦеныНоменклатуры.Период) КАК Период
ПОМЕСТИТЬ втМаксПериод
ИЗ
	втБезЦены КАК втБезЦены
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
		ПО втБезЦены.Номенклатура = ЦеныНоменклатуры.Номенклатура
			И втБезЦены.Дата >= ЦеныНоменклатуры.Период
			И ЦеныНоменклатуры.Период <= &КонецПериода
			И (ЦеныНоменклатуры.ТипЦен = &ТипЦен)

СГРУППИРОВАТЬ ПО
	втБезЦены.Дата,
	втБезЦены.Контрагент,
	втБезЦены.Номенклатура,
	втБезЦены.Количество,
	втБезЦены.Стоимость

;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	втМаксПериод.Дата,
	втМаксПериод.Контрагент,
	втМаксПериод.Номенклатура,
	втМаксПериод.Количество,
	втМаксПериод.Стоимость,
	ЦеныНоменклатуры.Цена
ИЗ
	втМаксПериод КАК втМаксПериод
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
		ПО втМаксПериод.Номенклатура = ЦеныНоменклатуры.Номенклатура
			И втМаксПериод.Период = ЦеныНоменклатуры.Период
			И (ЦеныНоменклатуры.ТипЦен = &ТипЦен)
...Показать Скрыть


2) мой:
ВЫБРАТЬ
	Продажи.Период,
	Продажи.Контрагент,
	Продажи.Номенклатура,
	Продажи.КоличествоОборот КАК Количество,
	Продажи.СтоимостьОборот КАК Стоимость,
	ЦеныНоменклатуры.Цена
ИЗ
	РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК Продажи
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
		ПО Продажи.Номенклатура = ЦеныНоменклатуры.Номенклатура
			И (ЦеныНоменклатуры.ТипЦен = &ТипЦен)
			И (ЦеныНоменклатуры.Период В
				(ВЫБРАТЬ ПЕРВЫЕ 1
					Цены.Период
				ИЗ
					РегистрСведений.ЦеныНоменклатуры КАК Цены
				ГДЕ
					Цены.Период <= Продажи.Период
					И Цены.Номенклатура = Продажи.Номенклатура
					И Цены.ТипЦен = &ТипЦен
				УПОРЯДОЧИТЬ ПО
					Цены.Период УБЫВ))
...Показать Скрыть


замеры делал за январь, таким образом получилось, что истории цен не было. В файловой базе я вообще не мог дождаться результатов, поэтому результаты для серверной, 1С 82 и MS SQL Express 2008R2, тестировал на своем ноуте. sql сервер каждый перегружал, поэтому первый резльтат для холодного сервера, а второй для горячего
Вот собственно и результаты за январь:
1 запрос: 1 запуск - 90 сек, 2 запуск и последующие 27 сек
2 запрос: 1 запуск - 400 сек, 2 запуск 10 сек

и результаты за октябрь:
1 запрос: 1 запуск - 210 сек, 2 запуск и последующие 155 сек
2 запрос: 1 запуск - 400 сек, 2 запуск 10 сек

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

запрос из статьи необходимо оптимизировать, ограничить выборку цен срезом последних, я этого не делал, но думаю что результаты буду сравнимы с тестом за январь, когда истории цен не было.
Bold Enough; SamMix; vasyak319; WellMaster; serge_focus; Zircool; SirYozha; +7 Ответить 2
23. Игорь Исхаков (Ish_2) 14.11.10 16:39
(22) Знал, что ты ответишь !
Задал ты задачку.
Так что же лучше "в среднем" для обычной работы в УТ ?

Очень похоже , что при отработке твоего запроса при первом запуске sql-сервер создает и грузит в память ВЕСЬ индекс регистра по полю Период. Именно работа по этому индексу съедает 90% времени. При последующих запусках этого не происходит поэтому и время всего 10 сек.
НО.
Если после первого запуска произошли серьезные изменения в регистре цен (пользователи начали работу), то старый индекс в кэше не может быть использован и он должен быть пересоздан. И тогда при следующем запуске твоего отчета - снова тормоза ?
Свою первую статью помнишь ? С тех пор прогнозирую с опаской.
Так что = это всего лишь предположения.
Если они верны , то тогда я бы отдал предпочтение запросу приведенному в статье ( со всеми необходимыми исправлениями и добавлениями)


24. Игорь Исхаков (Ish_2) 14.11.10 17:45
(0) Про команду "Индексировать".
На мой взгляд явно создавать индекс при помощи этой команды нужно только при многократном обращении к таблице . При однократном обращении этого делать не нужно - оптимизатор запроса сам создаст нужный индекс в нужном ему виде.
Казалось бы это замечание малосущественно. Но.
Мало того , что явное создание индекса командой "Индексировать" при однократном обащении не дает выигрыша -
оно еще и даёт очень существенный проигрыш.
На этот эффект мы натолкнулись с Alex-is при тестировании Примера 3 в его "Заметочках...".
sergos3331; +1 Ответить 1
25. Игорь <...> (I_G_O_R) 14.11.10 18:17
(23) а как узнать точно что скл делал всё это время, план выполнения я смотрел и он не меняется
(24) а насчет индексов: с индексами где-то раза в два медленнее выполнялся запрос
26. Игорь Исхаков (Ish_2) 14.11.10 18:28
(25) Прав я или нет - можно узнать если на горячем сервере после первого запуска твоего отчета в регистр Цены занести 1000 новых записей. И после этого запустить твой отчет .
Если время будет 400 сек - то я прав. Если 10 сек - то я не прав.
27. Игорь <...> (I_G_O_R) 14.11.10 22:03
(26) да оказался прав но на 50% )), занес 10000 записей в один день и время запроса составило 175 сек, а потом опять 10. Потом я переписал на временные таблицы свой запрос и добавил в запрос из топика срез последних, в итоге на холодном сервере время выполнения обоих запросов 45-50 сек, на горячем 35-40. Не пойму только почему раньше были более стабильные результаты. Много тестов не делал, той мой запрос выиграет, то из топика, вообщем буду считать что одинаково. В последних запросах теперь мне не нравится, что в регистре Цены номенклатуры куча индексов (в моей базе только по ЦеныНоменклатуры данные - 236 Мб, а индексы 730 Мб), которые почти никак не используются
28. Игорь Исхаков (Ish_2) 14.11.10 22:31
(27) Сбил с толку. Совсем.
Смутил этими 50 %(175 сек) , малопонятны пока и (45-50),(35-40).
29. Игорь <...> (I_G_O_R) 14.11.10 23:06
(28)
малопонятны пока и (45-50),(35-40)

что непонятного , один раз запущу запрос выполнился за 45 сек, другой раз за 50 а третий может быть за 47. т.к. повторов делал мало написал через тире минимальное и максимальное занчение.
30. Игорь Исхаков (Ish_2) 15.11.10 00:04
(29) Да, нет.
Малопонятен сам такой эффект . Почему так получилось ... такие диапазоны значений.
31. Виталий (vitaliyua) 17.01.11 21:18
Подскажите пожалуйста. Сделал по вашему примеру отчет в наборе данных. После добавления цены в ресурсы и выбранные поля, в отчете в строках цены пусто. Если убираю галочку "рассчитывать по" номенклатуре напротив цены, тогда цена в строках появляется.
32. Сергей (ildarovich) 21.04.12 18:49
(8) Вот еще короче
ВЫБРАТЬ
	Продажи.Период,
	Продажи.Контрагент,
	Продажи.Номенклатура,
	СУММА(Продажи.КоличествоОборот) КАК Количество,
	СУММА(Продажи.СтоимостьОборот) КАК Стоимость,
	МИНИМУМ(РАЗНОСТЬДАТ(ЦеныНоменклатуры.Период, Продажи.Период, ДЕНЬ) * &Много + ЦеныНоменклатуры.Цена) 
- МИНИМУМ(РАЗНОСТЬДАТ(ЦеныНоменклатуры.Период, Продажи.Период, ДЕНЬ) * &Много) КАК Цена
ИЗ
	РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК Продажи
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
		ПО Продажи.Номенклатура = ЦеныНоменклатуры.Номенклатура
			И (ЦеныНоменклатуры.ТипЦен = &ТипЦен)
			И (ЦеныНоменклатуры.Период < = Продажи.Период)

СГРУППИРОВАТЬ ПО
	Продажи.Период,
	Продажи.Контрагент,
	Продажи.Номенклатура
...Показать Скрыть

Здесь вообще один запрос. Идея в том, чтобы вместо поиска периода, на котором начинается актуальная цена, искать саму цену. Для этого подобрана функция, которая "отправляет" цену прошлого периода на свой "эшелон", высота которого зависит от древности цены. Ширина "эшелона" задается параметром "Много" - это величина, гарантированно перекрывающая диапазон изменения цены (1000000, 10000000 и т.п.). После нахождения ближайшей (нижайшей) "летящей" цены, высота соответствующего "эшелона" вычитается и цена "опускается на землю".
Можно подобрать функцию для дат, булевых и строковых значений.
Кроме краткости записи, других достоинств у запроса нет. Минусы - некоторый проигрыш по времени "классическому" запросу из-за большего объема вычислений в группировках, необходимость думать над значением "Много", работа только с простыми типами.
33. Матти Нюкянен (Nykyanen) 29.06.12 12:43
Аналогичная задача на запросе.
Тут остатки на каждый день * цены номенклатуры на дату остатком,
там суммы документов в у.е. * курсы валют на день документа.

"Курсы валют на разные даты в одном запросе. Делаем свой нестандартный срез последних."
http://infostart.ru/public/140933/
34. Артем Гусаров (Flashback1979SE) 15.08.12 05:01
ну ваще ниньзя

PS: тоже люблю максимально работать запросами
35. Александр Никитин (ManyakRus) 28.12.12 14:43
всё правильно, только не забыть:
1) в первом запросе обязательно сгруппировать данные,
в несгруппированных все цифры потом задвоятся, затроятся.
2) в третьем запросе главное не сгруппировывать или обязательно сгруппировывать по периоду
(я сам не понял почему)
3) все группировки из первого запроса должны быть и во втором, чтоб цифры не задвоились.
(а может и нет)

что не нравится:
группировать по числовым колонкам нежелательно,
это тоже самое что написать
ТЗ.Свернуть("Товар,СуммаРуб","СуммаШт") - СуммаРуб должна быть в правой части а не в левой.
36. Сергей Ожерельев (Поручик) 01.11.13 20:23
Спасибо автору, пригодилось для разработки. Почему-то с соединением средствами СКД не взлетело, а чисто на запросе получилось.
Spacer; _also; +2 Ответить 1
37. Сергей Ожерельев (Поручик) 05.11.13 11:40
Пришлось воспользоваться советом из поста (8), иначе при некоторых условиях выдавались не совсем верные цифры
38. Сергей Ожерельев (Поручик) 05.11.13 12:21
(32)
Делал отчет по перемещениям товаров на основе материалов статьи. Так как в моей базе данных немного, то отчёт работал нормально. Но клиент прислал замечания.

исходная ситуация № 1
1) товар используется в документе условно 23 10 2013
2) цена на начало месяца 1796

смотрим отчет - всё ок

исходная ситуация № 2
1) товар используется в документе условно 23 10 2013
2) цена на начало месяца 1796
3) установка цен 20 10 2013 меняет цены продажи на 2000

смотрим отчет - всё ок

исходная ситуация № 3
1) товар используется в документе условно 23 10 2013
2) цена на начало месяца 1796
3) установка цен 25 10 2013!!!!!!!!!! меняет цены продажи на 2000 (то есть дата больше документа. Та же проблема если изменить дату документа установки и на ноябрь)

смотрим отчет: цена = 1796 + 2000 = 3796 (сумма цен на начало месяца и цены, установленной после документа отчета)

Времени на изучение ситуации и переделку запроса не было, поэтому взял совет из поста 8.
39. Андрей К. (Abbra) 08.01.14 19:44
Для тех, кто не понимает (пока?) вложенные запросы и соединения есть еще вариант для СКД - Вычисляемые поля.
Делаем вычисляемое поле Цена и в Выражение ставим - Ценообразование.ПолучитьЦенуНоменклатуры(Субконто1, Субконто2.ТипЦенРозничнойТорговли, Период, Константы.ВалютаРегламентированногоУчета.Получить(), 1, 1)
Это частный пример для бухгалтерии 2.0.
И очень надеемся на кеширование. )
PowerBoy; +1 Ответить
40. Юрий Гуреев (Gureev) 18.02.14 13:31
Столкнулся с проблемой в СКД.

Ни в какую не хочет показывать данные на дату. Показывает только срез на текущую дату.

Как только не вертел, не пойму в чем причина.
Такое ощущение, что из источника в приемник параметр не передается.
41. Юрий Гуреев (Gureev) 18.02.14 15:03
Решил проблему.
Похоже, в соединении наборов данных, первую связь нужно делать по периоду, вторую по другим полям.
Так заработало... колдунство какое-то.
42. Александр Пузаков (puzakov) 07.03.14 10:13
Во втором наборе данных нужно было передавать параметры через расширение языка запросов.


РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
            {(&Дата)},
            {(Номенклатура = &Номенклатура
                И ТипЦен = &ТипЦен)})


Иначе отработает неправильно.
43. Александр Пузаков (puzakov) 07.03.14 10:16
(3) не согласен. В данном конкретном случае пакетный запрос проигрывает по производительности СКД.
44. Сергей (ildarovich) 07.03.14 11:01
45. Александр Пузаков (puzakov) 07.03.14 11:15
(44) почему? В данном случае создаются две временные таблицы, а это не самая производительная операция. СКД же обойдется без записи данных на диск.
46. Сергей (ildarovich) 07.03.14 11:44
(45) Мои умозаключения покажутся Вам такими же умозрительными, как и мне сейчас кажутся Ваши. Просто проведите эксперимент. Он, скорее всего, будет в мою пользу и Вам легче будет поверить объяснениям.
47. Александр Пузаков (puzakov) 07.03.14 12:05
(46) по поводу производительности я уже экспериментировал, и не однократно. Может когда-то там, в далекие времена 8.1, СКД и была тормозной, сейчас это не так.
48. Сергей (ildarovich) 07.03.14 14:15
Тогда поподробнее расскажите как тестировали, может быть запрос не оптимально написали, на каких данных тестировали, файловый или серверный вариант, схему компоновки и запрос приведите. Действительно интересно. Просто я сравнивал как-то разные варианты "нарезки" последних. Выяснил, что классический метод из двух запросов очень эффективен - там практически нет потерь времени. Поэтому не представляю, где может быть выигрыш. А то, что Вы говорите в памяти или на диске может не быть решающим фактором.
49. Александр Пузаков (puzakov) 07.03.14 16:22
50. Игорь Чайкин (ЧИА) 13.04.14 21:49
(48) ildarovich,
Выяснил, что классический метод из двух запросов очень эффективен - там практически нет потерь времени. Поэтому не представляю, где может быть выигрыш.


если в СКД отборы, то фильтр дает ускорение
но - это не совсем корректное сравнение - запрос без отбора и СКД с отбором

если в СКД есть отборы, то в текст запроса надо их тоже добавить, программно, например "из остатки(,"+отбор+")"
и тогда сравнивать
51. Игорь Чайкин (ЧИА) 13.04.14 22:11
_also,

В том виде, что есть сейчас, если опубликованный запрос запустить на УТ 10 и т.п., неподготовленный человек сильно удивится )

Во все три выборки надо вставить характеристику
52. Алексей Орлов (_also) 15.04.14 09:32
(51) ЧИА, насколько помню, демо-пример вообще на демо УПП писал. Да и учет по характеристикам может быть включен или не включен.
53. Александр Жерздев (Al777) 16.07.14 13:59
Статья мне сильно помогла при разработке отчёта на СКД, но с более широкими возможностями. Сделал по второму варианту, но так как выводились не все товары с несуществующими ценами, то сделал связь по типу первого варианта. Чисто первый вариант работает очень долго по всей номенклатуре, поэтому от него отказался, хотя он и проще и быстрее в реализации.
54. Вика Козлова (natarezn) 01.09.14 10:35
(36) Поручик, что делать, если дата пустая ?
55. Вика Козлова (natarezn) 01.09.14 10:36
спасибо за запросы - пригодились!
56. Виталий Б. (FlyVodolaz) 02.12.14 10:05
Запрос должен быть совсем другим, не нужно группировать лишнюю информацию и по максимум сократить подзапрос втМаксПериод

ВЫБРАТЬ
    ПродажиОбороты.Период КАК Дата,
    ПродажиОбороты.Контрагент КАК Контрагент,
    ПродажиОбороты.Номенклатура КАК Номенклатура,
    ПродажиОбороты.КоличествоОборот КАК Количество,
    ПродажиОбороты.СтоимостьОборот КАК Стоимость
ПОМЕСТИТЬ втБезЦены
ИЗ
    РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК ПродажиОбороты


;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
   втБезЦены.Номенклатура,
   втБезЦены.Дата КАК Дата,
   МАКСИМУМ(ЦеныНоменклатуры.Период) КАК Период
ПОМЕСТИТЬ втМаксПериод
ИЗ
    втБезЦены КАК втБезЦены
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
        ПО втБезЦены.Номенклатура = ЦеныНоменклатуры.Номенклатура
            И ЦеныНоменклатуры.ТипЦен = &ТипЦен
            И втБезЦены.Дата >= ЦеныНоменклатуры.Период

СГРУППИРОВАТЬ ПО
    втБезЦены.Дата,
    втБезЦены.Номенклатура
;

ВЫБРАТЬ
   втМаксПериод.Номенклатура,
   втМаксПериод.Дата КАК Дата,
   ЦеныНоменклатуры.Цена КАК Цена
ПОМЕСТИТЬ втЦеныНаДату
ИЗ
    втМаксПериодКАК втМаксПериод
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
        ПО втМаксПериод.Номенклатура = ЦеныНоменклатуры.Номенклатура
            И втМаксПериод.Период = ЦеныНоменклатуры.Период
            И ЦеныНоменклатуры.ТипЦен = &ТипЦен

ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    Дата

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
    втБезЦены.Дата,
    втБезЦены.Контрагент,
    втБезЦены.Номенклатура,
    втБезЦены.Количество,
    втБезЦены.Стоимость,
    втЦеныНаДату.Цена
ИЗ
    втБезЦены КАК втБезЦены
        ЛЕВОЕ СОЕДИНЕНИЕ втЦеныНаДату КАК втЦеныНаДату
        ПО втБезЦены.Номенклатура = втЦеныНаДату.Номенклатура
            И втБезЦены.Дата = втЦеныНаДату.Дата

АВТОУПОРЯДОЧИВАНИЕ
...Показать Скрыть
57. WellMaster (WellMaster) 10.12.14 18:48
(22) I_G_O_R,
Дополню.
Если подобная конструкция используется в Универсальном отчете (или вообще через ПостроительОтчетов), то может оказаться, что пользователь не задал "Период" как одно из измерений строк. При этом отчет будет ругаться на строчку
Цены.Период <= Продажи.Период

(не будет находить Поле "Период" таблицы "Продажи").

Решением проблемы может стать способ
Цены.Период <= Продажи.ДокументПродажи.Дата


З.Ы. Проверено на себе
58. Александр Пузаков (puzakov) 26.12.14 06:19
(57) WellMaster,

Цены.Период <= Продажи.ДокументПродажи.Дата


слишком злобная конструкция. Задачу-то она может и позволит решить, да только в большой базе может сильно ударить по производительности...
59. WellMaster (WellMaster) 26.12.14 08:32
(58) puzakov, не спорю. Но хоть что-то.
60. AK UI (ui69) 02.03.15 20:20
А как вычислить стоимость Цена*Количество, если Количество в первом наборе данных, а Цена во втором?
61. Leo Grant (KNOSSOS) 10.07.15 15:25
(8) I_G_O_R,
ВЫБРАТЬ
    Продажи.Период,
    Продажи.Контрагент,
    Продажи.Номенклатура,
    Продажи.КоличествоОборот КАК Количество,
    Продажи.СтоимостьОборот КАК Стоимость,
    ЦеныНоменклатуры.Цена
ИЗ
    РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК Продажи
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
        ПО Продажи.Номенклатура = ЦеныНоменклатуры.Номенклатура
            И (ЦеныНоменклатуры.ТипЦен = &ТипЦен)
            И (ЦеныНоменклатуры.Период В
                (ВЫБРАТЬ ПЕРВЫЕ 1
                    Цены.Период
                ИЗ
                    РегистрСведений.ЦеныНоменклатуры КАК Цены
                ГДЕ
                    Цены.Период <= Продажи.Период
                    И Цены.Номенклатура = Продажи.Номенклатура
                    И Цены.ТипЦен = &ТипЦен
                УПОРЯДОЧИТЬ ПО
                    Цены.Период УБЫВ))
...Показать Скрыть

Вышел на эту тему по поисковику. Предложенный запрос очень лаконичен, но сильно просаживает производительность. Причем на файловом варианте 8.3 на порядок медленнее чем на MS SQL2012.
А вот так будет стрелять везде:
ВЫБРАТЬ
    Продажи.Период,
    Продажи.Контрагент,
    Продажи.Номенклатура,
    Продажи.КоличествоОборот КАК Количество,
    Продажи.СтоимостьОборот КАК Стоимость,
    ЦеныНоменклатуры.Цена
ИЗ
    РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК Продажи
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
        ПО Продажи.Номенклатура = ЦеныНоменклатуры.Номенклатура
            И (ЦеныНоменклатуры.ТипЦен = &ТипЦен)
            И (ЦеныНоменклатуры.Период В
                (ВЫБРАТЬ МАКСИМУМ(Цены.Период)
                ИЗ
                    РегистрСведений.ЦеныНоменклатуры КАК Цены
                ГДЕ
                    Цены.Период <= Продажи.Период
                    И Цены.Номенклатура = Продажи.Номенклатура
                    И Цены.ТипЦен = &ТипЦен
))
...Показать Скрыть

zetovich@mail.ru; dr2c; sergelemon; i_lo; tehas; Ivanovag123; adhocprog; +7 Ответить 2
62. Сергей Ожерельев (Поручик) 10.07.15 22:16
(61) Вот примерно так я и сделал года два назад в паре своих отчётов.
63. Игорь <...> (I_G_O_R) 11.07.15 00:27
(61) Ха! Тема еще жива))
Сам тоже по возможности пользуюсь именно вторым вариантом. Но что еще интересно, сами 1С такие запросы до сих пор не юзают, наверное не на всех СУБД такие запросы нормально работают, но я сейчас пишу только под ms sql, там отлично работают.
64. Глеб Мазалов (Chandr73) 16.02.16 12:53
Спасибо большое, пригодилось!
65. Сергеевич Александр (aleksdiez) 11.05.16 14:01
Условие по ТипуЦен надо указывать в связи пацаны ;)
66. Sasha User (BAE1234567) 16.08.16 16:01
Оч. здорово! Спасибки за статью!