Заметки по SQL: Генерация ряда дат и данные из периодических регистров на каждый день

03.07.19

Разработка - Механизмы платформы 1С

В публикации описывается запрос на классическом SQL, позволяющий получить периодические данные на каждый день на примере формирования цен номенклатуры и остатков регистра накопления "ТоварыНаСкладах". Генератор ряда дат для запроса, построен на основе порождающего запроса.
1. Генерация ряда дат.

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

     Предлагаемый вариант генератора дат построен на публикации "Порождающий запрос" в которой предложен запрос формирующий числа натурального ряда в диапазоне 0 - 1 048 576. Поскольку нам такой большой диапазон не потребуется, то сократим этот запрос на один разряд до диапазона  0-65 536 чисел.   Фактически это будет чуть больше 184 лет, что для большинства вариантов использования более чем достаточно.

ВЫБРАТЬ
	0 КАК Х
ПОМЕСТИТЬ Регистр1
ОБЪЕДИНИТЬ
ВЫБРАТЬ
	1
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	Младшие.Х + 2 * Старшие.Х КАК Х
ПОМЕСТИТЬ Регистр2
ИЗ
	Регистр1 КАК Младшие,
	Регистр1 КАК Старшие
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	Младшие.Х + 4 * Старшие.Х КАК Х
ПОМЕСТИТЬ Регистр4
ИЗ
	Регистр2 КАК Младшие,
	Регистр2 КАК Старшие
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	Младшие.Х + 16 * Старшие.Х КАК Х
ПОМЕСТИТЬ Регистр8
ИЗ
	Регистр4 КАК Младшие,
	Регистр4 КАК Старшие
ГДЕ
	Младшие.Х + 16 * Старшие.Х <= РАЗНОСТЬДАТ(&НачалоПериода, &КонецПериода, ДЕНЬ)
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, Младшие.Х + 256 * Старшие.Х) КАК Дата
ИЗ
	Регистр8 КАК Младшие,
	Регистр8 КАК Старшие
ГДЕ
	Младшие.Х + 256 * Старшие.Х <= РАЗНОСТЬДАТ(&НачалоПериода, &КонецПериода, ДЕНЬ)

 Как видно из приведенного кода, запрос выглядит более компактно чем в публикации "Работаем с датами в запросе"

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

ВЫБРАТЬ
	0 КАК Х
ПОМЕСТИТЬ Регистр1

ОБЪЕДИНИТЬ

ВЫБРАТЬ
	1
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	Младшие.Х + 2 * Старшие.Х КАК Х
ПОМЕСТИТЬ Регистр2
ИЗ
	Регистр1 КАК Младшие,
	Регистр1 КАК Старшие
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	Младшие.Х + 4 * Старшие.Х КАК Х
ПОМЕСТИТЬ Регистр4
ИЗ
	Регистр2 КАК Младшие,
	Регистр2 КАК Старшие
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ДОБАВИТЬКДАТЕ(КОНЕЦПЕРИОДА(&ДатаНачала, МЕСЯЦ), МЕСЯЦ, Младшие.Х + 16 * Старшие.Х) КАК ДатаСреза
ИЗ
	Регистр4 КАК Младшие,
	Регистр4 КАК Старшие
ГДЕ
	Младшие.Х + 16 * Старшие.Х <= РАЗНОСТЬДАТ(&ДатаНачала, &ДатаКонца, МЕСЯЦ)

Запрос генерирует максимально ряд из 256 месяцев

    Сравнение быстродействий запросов генераторов дат.

    Общие условия тестирования предложенного запроса и запроса "Работаем с датами в запросе": Платформа - 1С:Предприятие 8.3 (8.3.12.1595), диапазон дат в запросах 01.01.2019-01.01.2185 (60 632 дней).

1. Файловая база данных, 1с сервер, клиент и база на одной ПВМ:  вариант Работаем с датами в запросе быстрее предложенного на 20-25%.

2. База данных MS SQL, SQL сервер, 1с сервер и клиент на разных ПВМ: вариант Работаем с датами в запросе медленнее предложенного на 25-30%.

 

2. Цены на каждый день.

    Для построения запроса цен на каждый день был использован запрос получения слеза последних из введения к публикации Заметки по SQL: Срез последних - аналог запроса. Построим по этому принципу запрос среза последних для регистра цен.

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

СГРУППИРОВАТЬ ПО
	ЦеныНоменклатуры.ВидЦены,
	ЦеныНоменклатуры.Номенклатура,
	ЦеныНоменклатуры.Характеристика,
	ЦеныНоменклатуры.Цена,
	ЦеныНоменклатуры.Период

ИМЕЮЩИЕ
	МАКСИМУМ(ЦеныНоменклатуры1.Период) = ЦеныНоменклатуры.Период

УПОРЯДОЧИТЬ ПО
	Номенклатура,
	Характеристика,
	Период

Теперь добавим в этот запрос наш генератор ряда дат.

ВЫБРАТЬ
	0 КАК Х
ПОМЕСТИТЬ Регистр1
ОБЪЕДИНИТЬ
ВЫБРАТЬ
	1
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	Младшие.Х + 2 * Старшие.Х КАК Х
ПОМЕСТИТЬ Регистр2
ИЗ
	Регистр1 КАК Младшие,
	Регистр1 КАК Старшие
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	Младшие.Х + 4 * Старшие.Х КАК Х
ПОМЕСТИТЬ Регистр4
ИЗ
	Регистр2 КАК Младшие,
	Регистр2 КАК Старшие
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	Младшие.Х + 16 * Старшие.Х КАК Х
ПОМЕСТИТЬ Регистр8
ИЗ
	Регистр4 КАК Младшие,
	Регистр4 КАК Старшие
ГДЕ
	Младшие.Х + 16 * Старшие.Х <= РАЗНОСТЬДАТ(&НачалоПериода, &КонецПериода, ДЕНЬ)
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, Младшие.Х + 256 * Старшие.Х) КАК Дата
ПОМЕСТИТЬ РядДат
ИЗ
	Регистр8 КАК Младшие,
	Регистр8 КАК Старшие
ГДЕ
	Младшие.Х + 256 * Старшие.Х <= РАЗНОСТЬДАТ(&НачалоПериода, &КонецПериода, ДЕНЬ)
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ЦеныНоменклатуры.ВидЦены КАК ВидЦены,
	ЦеныНоменклатуры.Номенклатура КАК Номенклатура,
	ЦеныНоменклатуры.Характеристика КАК Характеристика,
	ЦеныНоменклатуры.Период КАК Период,
	РядДат.Дата КАК Дата,
	ЦеныНоменклатуры.Цена КАК Цена
ИЗ
	РядДат КАК РядДат
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
			ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры1
			ПО ЦеныНоменклатуры.ВидЦены = ЦеныНоменклатуры1.ВидЦены
				И ЦеныНоменклатуры.Номенклатура = ЦеныНоменклатуры1.Номенклатура
				И ЦеныНоменклатуры.Характеристика = ЦеныНоменклатуры1.Характеристика
		ПО (РядДат.Дата >= ЦеныНоменклатуры1.Период)
ГДЕ
	ЦеныНоменклатуры.ВидЦены = &ВидЦены

СГРУППИРОВАТЬ ПО
	РядДат.Дата,
	ЦеныНоменклатуры.ВидЦены,
	ЦеныНоменклатуры.Номенклатура,
	ЦеныНоменклатуры.Характеристика,
	ЦеныНоменклатуры.Цена,
	ЦеныНоменклатуры.Период

ИМЕЮЩИЕ
	МАКСИМУМ(ЦеныНоменклатуры1.Период) = ЦеныНоменклатуры.Период

УПОРЯДОЧИТЬ ПО
	Номенклатура,
	Характеристика,
	Период,
	Дата

    Почему в запросе по ценам нет отбора по периоду регистра сведений?. Потому что роль этого отбора фактически играет запрос генерирующий ряд дат. Условие "РядДат.Дата >= ЦеныНоменклатуры1.Период" выполняет эту функцию, так как даты меньше "&НачалоПериода" не создаются. За счет этого запрос имеет очень хорошую скорость. К сожалению автор не имеет данных, на которых можно было бы протестировать  быстродействие этого запроса. Единственное, что могу сказать, что тесты аналогичного запроса проводились на БД Oracle 9i, база не 1с. В качестве сравнения брался запрос построенный на аналитической функции. Скорость предложенного запроса оказалась  выше на 20-25% 

    Данный запрос можно достаточно просто переделать для нахождения курсов валют на каждый день  путем замены РегистрСведений.ЦеныНоменклатуры на РегистрСведений.КурсыВалют. Таким же образом данные, любого периодического регистра сведений можно получить на каждый день.

 
3. Остатки на каждый день.

     В качестве примера получим остатки на каждый день из регистра накопления "ТоварыНаСкладах". Для начала подготовим данные. Остатки можно рассчитать как обороты по регистру накопления с начала ведения учета суммой нарастающим итогом. Естественно, что таким образом рассчитывать остатки долго и затратно, тем более, что мы собираемся работать в заданном периоде. Поэтому рассчитаем обороты, как остаток на начало периода плюс обороты в периоде. Для простоты анализа отбор сделаем по заданной номенклатуре. Поскольку остатки мы будем рассчитывать за день, то приведем поле "период" к виду: 

НАЧАЛОПЕРИОДА(ТоварыНаСкладах.Период, ДЕНЬ) КАК Период

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

СГРУППИРОВАТЬ ПО
	ВложенныйЗапрос.Номенклатура,
	ВложенныйЗапрос.Характеристика,
	ВложенныйЗапрос.Период
;

Далее, получаем сумму нарастающим итогом - собственно остатки по периоду день.

ВЫБРАТЬ
	ОборотПериод.Номенклатура КАК Номенклатура,
	ОборотПериод.Характеристика КАК Характеристика,
	ОборотПериод.Период КАК Период,
	СУММА(ОборотПериод1.Оборот) КАК Остаток
ПОМЕСТИТЬ ОстаткиПоПериоду
ИЗ
	ОборотПериод КАК ОборотПериод
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ ОборотПериод КАК ОборотПериод1
		ПО ОборотПериод.Номенклатура = ОборотПериод1.Номенклатура
			И ОборотПериод.Характеристика = ОборотПериод1.Характеристика
			И ОборотПериод.Период >= ОборотПериод1.Период

СГРУППИРОВАТЬ ПО
	ОборотПериод.Номенклатура,
	ОборотПериод.Характеристика,
	ОборотПериод.Период
;
 
 Аналог запроса остатки по периоду день на виртуальных таблицах

И наконец, на основе базового запроса публикации Заметки по SQL: Срез последних - аналог запроса, получим данные по остаткам на каждый день. Текст запроса генерирующего ряд дат ("РядДат"), можно взять из начала публикации, поэтому здесь я приводить его не буду.

ВЫБРАТЬ
	ОстаткиПоПериоду.Номенклатура КАК Номенклатура,
	ОстаткиПоПериоду.Характеристика КАК Характеристика,
	ОстаткиПоПериоду.Период КАК Период,
	ОстаткиПоПериоду.Остаток КАК Остаток,
	РядДат.Дата КАК Дата
ИЗ
	РядДат КАК РядДат
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ ОстаткиПоПериоду КАК ОстаткиПоПериоду1
			ВНУТРЕННЕЕ СОЕДИНЕНИЕ ОстаткиПоПериоду КАК ОстаткиПоПериоду
			ПО (ОстаткиПоПериоду.Номенклатура = ОстаткиПоПериоду1.Номенклатура)
				И (ОстаткиПоПериоду.Характеристика = ОстаткиПоПериоду1.Характеристика)
		ПО РядДат.Дата >= ОстаткиПоПериоду1.Период

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

ИМЕЮЩИЕ
	МАКСИМУМ(ОстаткиПоПериоду1.Период) = ОстаткиПоПериоду.Период

УПОРЯДОЧИТЬ ПО
	Период,
	Дата

Таким образом используя базовую конструкцию запроса из публикации Заметки по SQL: Срез последних - аналог запроса, имеющую ключевой элемент

МАКСИМУМ([РегистрСведений; РегистрОстатков].Период) = [РегистрСведений; РегистрОстатков].Период

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

См. также

Поинтегрируем: сервисы интеграции – новый стандарт или просто коннектор?

Обмен между базами 1C Администрирование СУБД Механизмы платформы 1С Платформа 1С v8.3 Бесплатно (free)

В платформе 8.3.17 появился замечательный механизм «Сервисы интеграции». Многие считают, что это просто коннектор 1С:Шины. Так ли это?

11.03.2024    4576    dsdred    53    

73

Как готовить и есть массивы

Механизмы платформы 1С Платформа 1С v8.3 Бесплатно (free)

Все мы используем массивы в своем коде. Это один из первых объектов, который дают ученикам при прохождении обучения программированию. Но умеем ли мы ими пользоваться? В этой статье я хочу показать все методы массива, а также некоторые фишки в работе с массивами.

24.01.2024    5301    YA_418728146    25    

63

Планы обмена VS История данных

Обмен между базами 1C Механизмы платформы 1С Платформа 1С v8.3 Бесплатно (free)

Вы все еще регистрируете изменения только на Планах обмена и Регистрах сведений?

11.12.2023    6427    dsdred    36    

112

1С-ная магия

Механизмы платформы 1С Бесплатно (free)

Язык программирования 1С содержит много нюансов и особенностей, которые могут приводить к неожиданным для разработчика результатам. Сталкиваясь с ними, программист начинает лучше понимать логику платформы, а значит, быстрее выявлять ошибки и видеть потенциальные узкие места своего кода там, где позже можно было бы ещё долго медитировать с отладчиком в поисках источника проблемы. Мы рассмотрим разные примеры поведения кода 1С. Разберём результаты выполнения и ответим на вопросы «Почему?», «Как же так?» и «Зачем нам это знать?». 

06.10.2023    18491    SeiOkami    46    

118

Дефрагментация и реиндексация после перехода на платформу 8.3.22

Механизмы платформы 1С Платформа 1С v8.3 Бесплатно (free)

Начиная с версии платформы 8.3.22 1С снимает стандартные блокировки БД на уровне страниц. Делаем рабочий скрипт, как раньше.

14.09.2023    12101    human_new    27    

74

Валидация JSON через XDTO (включая массивы)

WEB-интеграция Универсальные функции Механизмы платформы 1С Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

При работе с интеграциями рано или поздно придется столкнуться с получением JSON файлов. И, конечно же, жизнь заставит проверять файлы перед тем, как записывать данные в БД.

28.08.2023    8841    YA_418728146    6    

141

Внешние компоненты Native API на языке Rust - Просто!

Механизмы платформы 1С Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Внешние компоненты для 1С можно разработывать очень просто, пользуясь всеми преимуществами языка Rust - от безопасности и кроссплатформенности до удобного менеджера библиотек.

20.08.2023    6284    sebekerga    54    

94

Все скопируем и вставим! (Буфер обмена в 1С 8.3.24)

Механизмы платформы 1С Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Рассмотрим новую возможность 8.3.24 и как её можно эффективно использовать

27.06.2023    15998    SeiOkami    31    

103
Оставьте свое сообщение