В практике разработки на платформе 1С Предприятие регулярно возникают нестандартные задачи по манипуляции данными в запросах. Одна из таких классических задач — разгруппировка (или «размножение») строк. Представьте ситуацию: в регистре накопления есть всего одна запись о продаже товара, например: Бананы — 5 шт. на сумму 50 руб.. Однако для целей расчета управленческого учета, начисления сдельной заработной платы или распределения премий по шкале продаж в программе 1С:Управление нашей фирмой (УНФ) вам необходимо получить на выходе не одну, а пять полноценных строк вида: Бананы — 1 шт. на сумму 10 руб.. Поскольку расчет показателей в некоторых подсистемах жестко зашит на уровне работы конфигуратора через схемы компоновки данных (СКД) или произвольные запросы, выполнить эту операцию постобработкой в коде не всегда возможно.
В рамках этой статьи мы подробно разберем, как решить эту задачу исключительно средствами языка запросов 1С, проанализируем различные подходы, рассмотрим готовые примеры кода и укажем на неочевидные подводные камни, такие как проблемы округления и производительности СУБД.
Если мы попытаемся выполнить стандартное соединение таблицы продаж самой с собой или разделим показатели внутри одной строки, СУБД вернет нам ровно ту же самую единственную строку, но со скорректированными значениями. Количество записей в выборке не изменится. Для того чтобы физически увеличить число строк в результате запроса, нам потребуется специальная опорная таблица — так называемый генератор чисел (или таблица-счетчик). Соединяя нашу исходную строку с этой вспомогательной последовательностью чисел по условию «меньше или равно», мы заставляем СУБД продублировать исходную запись столько раз, сколько единиц товара было продано.
---
Рассмотрим элегантный способ решения задачи, который не требует передачи внешних параметров из кода 1С. Этот метод идеально подходит для тех случаев, когда алгоритм должен работать автономно (например, в консоли отчетов, СКД или в закрытых механизмах расчета зарплаты УНФ). Мы создадим виртуальную таблицу чисел от 1 до 100 с помощью объединения единичных выборок и декартова произведения, а затем соединим её с нашими продажами.
Проанализируем следующий пример запроса:
ВЫБРАТЬ
Продажи.Номенклатура КАК Номенклатура,
Продажи.Количество КАК Количество,
Продажи.Сумма КАК Сумма
ПОМЕСТИТЬ ВРТЗ_Продажи
ИЗ
РегистрНакопления.Продажи КАК Продажи;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
10 * t1.Десятки + t2.Единицы КАК НомерСтроки
ПОМЕСТИТЬ ВРТЗ_Цифры
ИЗ
(ВЫБРАТЬ 0 КАК Десятки ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3
ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7
ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК t1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
(ВЫБРАТЬ 1 КАК Единицы ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4
ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8
ОБЪЕДИНИТЬ ВЫБРАТЬ 9 ОБЪЕДИНИТЬ ВЫБРАТЬ 10) КАК t2
ПО ИСТИНА;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВРТЗ_Продажи.Номенклатура КАК Номенклатура,
1 КАК Количество,
ВРТЗ_Продажи.Сумма / ВРТЗ_Продажи.Количество КАК Сумма
ИЗ
ВРТЗ_Продажи КАК ВРТЗ_Продажи
ЛЕВОЕ СОЕДИНЕНИЕ ВРТЗ_Цифры КАК ВРТЗ_Цифры
ПО ВРТЗ_Продажи.Количество >= ВРТЗ_ЦифрыНомерСтроки
Разберем этот запрос по шагам, чтобы понять логику его работы:
РегистрНакопления.Продажи и помещаем их во временную таблицу ВРТЗ_Продажи.ОБЪЕДИНИТЬ, мы используем декартово произведение (кросс-джоин через условие ПО ИСТИНА) двух небольших таблиц t1 (десятки от 0 до 9) и t2 (единицы от 1 до 10). Формула 10 * t1.Десятки + t2.Единицы мгновенно генерирует для нас виртуальную таблицу ВРТЗ_Цифры, содержащую числа от 1 до 100. Если ваши продажи в одной строке могут превышать 100 единиц, вы можете легко масштабировать этот генератор до 1 000 или 10 000 строк, добавив еще одно соединение по аналогии (например, сотни).ВРТЗ_Продажи.Количество >= ВРТЗ_Цифры.НомерСтроки. Если количество проданного товара равно 5, то условие выполнится ровно для пяти чисел из таблицы-генератора (1, 2, 3, 4 и 5). Соответственно, СУБД вернет ровно 5 одинаковых строк. Нам остается лишь принудительно указать количество, равное 1, и рассчитать среднюю стоимость одной единицы путем несложного деления общей суммы на исходное количество.---
Если логика вашего отчета или алгоритма допускает написание программного кода на встроенном языке 1С, то генерировать последовательность чисел внутри самого SQL-запроса не обязательно. Намного эффективнее и проще подготовить готовую таблицу чисел программно и передать её в запрос в качестве параметра.
Давайте посмотрим на пример реализации такого подхода:
// Шаг 1. Создаем таблицу значений, которая будет выступать счетчиком
ТаблицаСчетчик = Новый ТаблицаЗначений;
ТаблицаСчетчик.Колонки.Добавить("Число", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(5, 0)));
// Шаг 2. Заполняем таблицу числами от 1 до максимально возможного количества продаж в строке (например, до 500)
Для Счетчик = 1 По 500 Цикл
НоваяСтрока = ТаблицаСчетчик.Добавить();
НоваяСтрока.Число = Счетчик;
КонецЦикла;
// Шаг 3. Инициализируем запрос и передаем таблицу в качестве параметра
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Счетчик.Число КАК Счетчик
|ПОМЕСТИТЬ ВТ_Счетчик
|ИЗ
| &ТаблицаСчетчик КАК Счетчик
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Продажи.Номенклатура КАК Номенклатура,
| 1 КАК Количество,
| Продажи.Сумма / Продажи.Количество КАК Сумма
|ИЗ
| РегистрНакопления.Продажи КАК Продажи
| ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Счетчик КАК ВТ_Счетчик
| ПО Продажи.Количество >= ВТ_Счетчик.Счетчик";
Запрос.УстановитьПараметр("ТаблицаСчетчик", ТаблицаСчетчик);
РезультатЗапроса = Запрос.Выполнить();
Этот способ является более производительным, так как избавляет СУБД от необходимости собирать виртуальные таблицы «на лету» из многочисленных инструкций ОБЪЕДИНИТЬ.
---
При использовании метода разделения сумм на количество записей вы обязательно столкнетесь с фундаментальной проблемой округления дробных чисел, которую разработчики часто называют «проблемой копейки».
Рассмотрим пример проблемы:
Представьте, что в исходной строке зафиксирована продажа 3 штук товара на сумму 100 рублей. При делении суммы на количество в запросе (100 / 3) мы получим цену за одну единицу, равную 33.333333... рублей. При округлении до стандартных копеек (2 знака после запятой) каждая из трех сформированных нами строк получит сумму 33.33 рублей. Если сложить эти строки обратно, мы получим общую сумму 99.99 рублей. Потеряна 1 копейка! В масштабах крупных розничных или оптовых сетей при расчете зарплаты или анализе прибыли за месяц такие копейки могут вылиться в серьезные расхождения в финансовой отчетности.
Как решить эту проблему?
СуммаПоследнейСтроки = ОбщаяСумма - (СуммаПредыдущихСтрок * (Количество - 1)).---
Обратите внимание на условие соединения в финальном запросе: ПО Продажи.Количество >= ВТ_Счетчик.Счетчик. С точки зрения баз данных (будь то MS SQL Server или PostgreSQL), соединение по неравенству (операторы >=, <=, <>) исключает возможность эффективного использования стандартных индексов таблиц. СУБД в подавляющем большинстве случаев будет вынуждена прибегнуть к операции Nested Loops (вложенные циклы), что при больших объемах данных может привести к лавинообразному росту нагрузки на процессор и жесткие диски сервера.
Чтобы избежать зависания системы при формировании отчетов, всегда придерживайтесь правила: максимально фильтруйте исходные данные до момента их соединения со счетчиком. Никогда не соединяйте всю таблицу продаж за несколько лет. Сначала выберите продажи по конкретному сотруднику или за строго определенный расчетный период во временную таблицу, и только затем размножайте полученный точечный результат.