В практике разработчика 1С регулярно возникают задачи, когда необходимо построить отчет с детализацией по дням (или другим периодам) в рамках заданного интервала. Классический пример — вывод остатков товаров или денежных средств на каждый день периода, включая те дни, по которым в базе данных не было абсолютно никаких движений. Если просто выбрать данные из регистра накопления, то дни без движений в результат не попадут.
Обычно для решения этой проблемы используют производственный календарь или специальный регистр сведений. Но что делать, если в базе данных нет заполненного календаря, или мы пишем универсальный алгоритм для распределенных и сильно кастомизированных баз? В этой статье мы подробно разберем, как сгенерировать последовательность дат «на лету» прямо в запросе, имея на входе только два параметра: &Дата1 (начало периода) и &Дата2 (конец периода).
Поскольку в языке запросов 1С нет классических циклов, мы не можем в один шаг сказать системе: «выведи даты от А до Б». Нам нужно использовать математический трюк. Мы сгенерируем последовательность целых чисел (от 0 до нужного нам предела), а затем прибавим эти числа в качестве дней к нашей стартовой дате с помощью функции ДОБАВИТЬКДАТЕ. Ограничить полученный результат мы сможем простым условием ГДЕ, отсекающим даты, превышающие конечную точку периода.
Рассмотрим подробнее два основных метода генерации чисел в запросе: двоичный и десятичный.
Этот способ основан на формировании таблицы из двух строк (0 и 1) и последующем ее многократном соединении самой с собой (декартовом произведении). Каждое новое соединение удваивает разрядность генерируемого числа.
Давайте разберем по шагам рабочий пример такого запроса:
ВЫБРАТЬ 0 КАК Б
ПОМЕСТИТЬ вт
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 1;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
вт0.Б + 2 * вт1.Б + 4 * вт2.Б + 8 * вт3.Б + 16 * вт4.Б + 32 * вт5.Б + 64 * вт6.Б + 128 * вт7.Б + 256 * вт8.Б + 512 * вт9.Б + 1024 * вт10.Б КАК Число
ПОМЕСТИТЬ втЧисла
ИЗ
вт КАК вт0,
вт КАК вт1,
вт КАК вт2,
вт КАК вт3,
вт КАК вт4,
вт КАК вт5,
вт КАК вт6,
вт КАК вт7,
вт КАК вт8,
вт КАК вт9,
вт КАК вт10;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ДОБАВИТЬКДАТЕ(&Дата1, ДЕНЬ, втЧисла.Число) КАК ПериодДень
ИЗ
втЧисла КАК втЧисла
ГДЕ
ДОБАВИТЬКДАТЕ(&Дата1, ДЕНЬ, втЧисла.Число) <= &Дата2
Проанализируем ситуацию. В первой временной таблице вт мы создали базовый набор из двух цифр: 0 и 1. Во втором запросе пакета мы перемножили эту таблицу саму на себя 11 раз (от вт0 до вт10). С помощью формулы двоичного счисления мы получили последовательность всех целых чисел в диапазоне от 0 до 2047 (всего 2048 комбинаций). Этого с запасом хватает, чтобы перекрыть период более чем в 5 лет.
В финальном запросе мы берем полученную таблицу чисел, прибавляем каждое число в виде дней к параметру &Дата1 и накладываем фильтр, чтобы полученная дата не превышала &Дата2. Решение изящное, быстрое и не требующее обращения к реальным таблицам базы данных.
Альтернативный подход предполагает создание базовой таблицы с цифрами от 0 до 9. Соединяя разряды (единицы, десятки, сотни, тысячи), мы можем получить абсолютно любую последовательность чисел в привычном нам десятичном формате. Этот вариант часто легче читать и отлаживать.
Посмотрим на пример запроса, оптимизированного под динамическое ограничение соединений:
ВЫБРАТЬ 0 КАК Цифра ПОМЕСТИТЬ ВТЦифры ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 9;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(НАЧАЛОПЕРИОДА(&НачалоИнтервала, ДЕНЬ), ДЕНЬ, Цифры.Цифра + ЕСТЬNULL(Цифры10.Цифра, 0) * 10 + ЕСТЬNULL(Цифры100.Цифра, 0) * 100 + ЕСТЬNULL(Цифры1000.Цифра, 0) * 1000 + ЕСТЬNULL(Цифры10000.Цифра, 0) * 10000), ДЕНЬ) КАК ИмяПоляПериод
ИЗ
ВТЦифры КАК Цифры
ЛЕВОЕ СОЕДИНЕНИЕ ВТЦифры КАК Цифры10
ПО (РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&НачалоИнтервала, ДЕНЬ), НАЧАЛОПЕРИОДА(&ОкончаниеИнтервала, ДЕНЬ), ДЕНЬ) > 9)
ЛЕВОЕ СОЕДИНЕНИЕ ВТЦифры КАК Цифры100
ПО (РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&НачалоИнтервала, ДЕНЬ), НАЧАЛОПЕРИОДА(&ОкончаниеИнтервала, ДЕНЬ), ДЕНЬ) > 99)
ЛЕВОЕ СОЕДИНЕНИЕ ВТЦифры КАК Цифры1000
ПО (РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&НачалоИнтервала, ДЕНЬ), НАЧАЛОПЕРИОДА(&ОкончаниеИнтервала, ДЕНЬ), ДЕНЬ) > 999)
ЛЕВОЕ СОЕДИНЕНИЕ ВТЦифры КАК Цифры10000
ПО (РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&НачалоИнтервала, ДЕНЬ), НАЧАЛОПЕРИОДА(&ОкончаниеИнтервала, ДЕНЬ), ДЕНЬ) > 9999)
ГДЕ
НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(НАЧАЛОПЕРИОДА(&НачалоИнтервала, ДЕНЬ), ДЕНЬ, Цифры.Цифра + ЕСТЬNULL(Цифры10.Цифра, 0) * 10 + ЕСТЬNULL(Цифры100.Цифра, 0) * 100 + ЕСТЬNULL(Цифры1000.Цифра, 0) * 1000 + ЕСТЬNULL(Цифры10000.Цифра, 0) * 10000), ДЕНЬ) МЕЖДУ &НачалоИнтервала И &ОкончаниеИнтервала
Выясним причину, почему здесь используются именно ЛЕВЫЕ СОЕДИНЕНИЯ с условиями в секции ПО. Это критически важный нюанс оптимизации для СУБД. Если выбранный пользователем интервал составляет, например, всего 5 дней, условие РАЗНОСТЬДАТ(...) > 9 вернет Ложь. СУБД поймет, что соединять таблицы десятков, сотен и тысяч не имеет смысла, и вернет для них значения NULL (которые превратятся в 0 благодаря функции ЕСТЬNULL). Таким образом, мы экономим ресурсы процессора и оперативной памяти сервера, избегая генерации избыточного миллиона строк в памяти СУБД.
В некоторых обсуждениях разработчики предлагают использовать появившуюся в платформе функцию АвтономерЗаписи() для автоматического получения числового ряда. Однако здесь кроется серьезный подводный камень, о котором важно знать.
Внимание! Согласно стандартам разработки фирмы "1С", платформа и СУБД не гарантируют, что нумерация строк в результирующей таблице всегда будет начинаться с единицы. Начальный номер записи зависит от внутреннего кэширования планов запросов СУБД и используемого пула соединений. При первом выполнении нумерация может начаться с 1, а при повторном — продолжиться, например, со значения 150. Если вы завяжете прибавление дней на этот номер строки, ваши даты "уплывут", и запрос вернет неверные данные или пустой результат.
Если вы всё же хотите использовать АВТОНОМЕРЗАПИСИ(), делайте это безопасно через пакет запросов:
МИНИМУМ(НомерСтроки).НомерСтроки - МинимальныйНомерСтроки. Только так расчет будет стабильным.В реальной разработке "чистый" запрос для генерации дат «из воздуха» используется не всегда. Существуют более простые и производительные альтернативы, которые мы рекомендуем рассмотреть.
Если ваш запрос выполняется в рамках программного кода (например, во встроенном языке модуля менеджера или обработки), гораздо эффективнее и проще сгенерировать список дат обычным циклом на клиенте или сервере 1С, а затем передать готовую таблицу значений в запрос как параметр.
Разберем пример кода на встроенном языке:
ТаблицаДат = Новый ТаблицаЗначений;
ТаблицаДат.Колонки.Добавить("Период", Новый ОписаниеТипов("Дата"));
ТекущаяДата = НачалоДня(Параметры.Дата1);
КонечнаяДата = КонецДня(Параметры.Дата2);
Пока ТекущаяДата <= КонечнаяДата Цикл
НоваяСтрока = ТаблицаДат.Добавить();
НоваяСтрока.Период = ТекущаяДата;
ТекущаяДата = ДобавитьВРабочуюДату(ТекущаяДата, 1); // Прибавляем 1 день (86400 секунд)
КонецЦикла;
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| втДаты.Период КАК Период
|ПОМЕСТИТЬ ВТ_Периоды
|ИЗ
| &ТаблицаДат КАК втДаты";
Запрос.УстановитьПараметр("ТаблицаДат", ТаблицаДат);
Этот метод работает быстрее декартова произведения таблиц на стороне СУБД, код становится простым для чтения, а вероятность совершить ошибку при написании сложных математических формул сводится к нулю.
Если вы разрабатываете отчет на базе Системы компоновки данных (СКД), вам вообще не нужно писать сложные запросы для дополнения дат. В СКД встроен мощный и оптимизированный механизм, который сделает все автоматически.
Выполните следующие шаги в конструкторе схемы компоновки данных:
ПериодДень) на вкладке "Роли" укажите тип периода и его семейство.День), а также укажите границы дополнения (выберите параметры начала и окончания периода отчета).СКД самостоятельно сгенерирует недостающие строки в финальном макете отчета, подставив нулевые значения в поля ресурсов для тех дней, когда по базе данных не было движений.
Мы рассмотрели различные способы генерации временных интервалов. Если перед вами стоит задача решить эту проблему строго в рамках одного изолированного запроса (например, на собеседовании или при проектировании жестко ограниченного динамического списка), смело используйте оптимизированный десятичный метод с левыми соединениями. Во всех остальных случаях в реальной практике отдавайте предпочтение передаче таблицы дат параметром или встроенному механизму дополнения периодов в СКД.