В практике разработки на платформе 1С часто возникает задача классификации или сегментации данных на лету прямо в запросах. Типичный пример: у нас есть числовые номера заказов в диапазоне от 1 000 000 до 25 000 000, и нам необходимо определить, к какому «миллионному» сегменту относится каждый заказ. То есть заказы от 1 000 000 до 1 999 999 — это сегмент 1, от 2 000 000 до 2 999 999 — сегмент 2, и так далее.
В этой статье мы подробно разберем, как решить эту задачу, используя только операторы языка запросов 1С. Мы также проанализируем скрытые ловушки округления в различных СУБД (таких как PostgreSQL и MS SQL Server), рассмотрим влияние таких вычислений на производительность и выберем лучшие архитектурные практики.
Язык запросов 1С не содержит прямой функции получения целой части числа (аналога функции Цел() из встроенного языка). Однако мы можем эмулировать это поведение с помощью арифметических операций и функции приведения типов ВЫРАЗИТЬ.
Давайте рассмотрим базовую идею. Если мы просто разделим число на 1 000 000, мы получим дробное число (например, 1.25 для номера 1 250 000). Нам нужно округлить его вниз до ближайшего целого. Платформа 1С при использовании конструкции ВЫРАЗИТЬ(... КАК ЧИСЛО(X, 0)) выполняет математическое округление по правилам СУБД.
Чтобы округлить число вниз, нам нужно сместить его диапазон. Рассмотрим формулу, предложенную разработчиками на форуме:
ВЫРАЗИТЬ((НомерЗаказа - 500000) / 1000000 КАК ЧИСЛО(15, 0)) КАК Сегмент
Давайте разберем по шагам, как работает эта математика на примере конкретных чисел:
НомерЗаказа = 1 000 000. Вычитаем 500 000, получаем 500 000. Делим на 1 000 000, получаем 0.5. Округляем до ближайшего целого — получаем 1 (или 0, в зависимости от СУБД, об этом ниже).НомерЗаказа = 1 500 000. Вычитаем 500 000, получаем 1 000 000. Делим на 1 000 000, получаем 1.0. При округлении получаем 1.НомерЗаказа = 1 999 999. Вычитаем 500 000, получаем 1 499 999. Делим на 1 000 000, получаем 1.499999. При округлении по правилам математики число округляется вниз до 1.Казалось бы, решение найдено. Но здесь нас подстерегает опасная ловушка СУБД.
Конструкция ВЫРАЗИТЬ транслируется платформой 1С в оператор CAST на уровне базы данных. И здесь поведение вашей программы будет напрямую зависеть от того, под управлением какой СУБД работает ваша база.
PostgreSQL (который сейчас является стандартом для большинства инсталляций 1С) по умолчанию использует так называемое банковское округление (округление к ближайшему четному числу для значений, оканчивающихся ровно на 0.5).
Давайте посмотрим, как поведет себя формула ВЫРАЗИТЬ((НомерЗаказа - 500000) / 1000000 КАК ЧИСЛО) в PostgreSQL:
НомерЗаказа = 1 000 000 мы получаем значение 0.5. В PostgreSQL CAST(0.5 AS NUMERIC) округлится к ближайшему четному числу, то есть к 0! Мы получили неверный сегмент (0 вместо 1).НомерЗаказа = 2 000 000 мы получаем 1.5. Округляется к четному числу — то есть к 2 (верно).НомерЗаказа = 3 000 000 мы получаем 2.5. Округляется к ближайшему четному — то есть снова к 2! Произошла критическая ошибка: сегмент определился неверно.Безопасное математическое решение:
Чтобы гарантировать одинаковое и абсолютно правильное поведение на MS SQL Server, PostgreSQL и Oracle, необходимо вычитать смещение, которое строго меньше 0.5, но при этом учитывает дискретность (разрядность) наших чисел. Поскольку номера заказов — это целые числа, шаг изменения дробной части при делении на миллион равен 0.000001.
Идеальная формула, которая не боится банковского округления:
ВЫРАЗИТЬ(НомерЗаказа / 1000000 - 0.4999995 КАК ЧИСЛО(15, 0)) КАК Сегмент
Проанализируем работу этой формулы:
НомерЗаказа = 1 000 000:1 000 000 / 1 000 000 = 1.0.1.0 - 0.4999995 = 0.5000005.НомерЗаказа = 1 999 999:1 999 999 / 1 000 000 = 1.999999.1.999999 - 0.4999995 = 1.4999995.НомерЗаказа = 2 000 000:2 000 000 / 1 000 000 = 2.0.2.0 - 0.4999995 = 1.5000005.Таким образом, мы получили универсальный и надежный способ получения целой части миллионов в запросе 1С.
Теперь поговорим о производительности. Если вы планируете использовать вычисленное поле сегмента только в списке выбора (вывод на экран, отчет), то нагрузка на СУБД будет минимальной. Но если вы решите наложить отбор по вычисляемому сегменту в секции ГДЕ, система столкнется со снижением производительности.
Рассмотрим пример неправильного запроса:
// ВНИМАНИЕ: Так делать НЕ РЕКОМЕНДУЕТСЯ при больших объемах данных!
ВЫБРАТЬ
Заказы.Ссылка КАК Ссылка,
Заказы.Номер КАК Номер
ИЗ
Документ.ЗаказПокупателя КАК Заказы
ГДЕ
ВЫРАЗИТЬ(Заказы.Номер / 1000000 - 0.4999995 КАК ЧИСЛО(15, 0)) = &ИскомыйСегмент
С точки зрения теории СУБД, такое условие в секции ГДЕ является не-SARGable (Search Argument Able). База данных не может использовать индекс по полю Номер для быстрого поиска строк. СУБД будет вынуждена выполнить полное сканирование всей таблицы (Table Scan или Index Scan), произвести математическое вычисление для каждой строчки и только потом отфильтровать результат. На больших базах это гарантированно приведет к зависанию запроса.
Как сделать правильно:
Если нам нужно отфильтровать данные по конкретному сегменту, расчет границ диапазона необходимо вынести во встроенный язык 1С и передать в запрос готовые параметры границ. Это позволит СУБД использовать стандартный поиск по индексу (Index Seek).
// Вычисляем границы во встроенном языке 1С
ГраницаНачала = ИскомыйСегмент * 1000000;
ГраницаКонца = (ИскомыйСегмент + 1) * 1000000 - 1;
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Заказы.Ссылка КАК Ссылка,
| Заказы.Номер КАК Номер
|ИЗ
| Документ.ЗаказПокупателя КАК Заказы
|ГДЕ
| Заказы.Номер МЕЖДУ &ГраницаНачала И &ГраницаКонца";
Запрос.УстановитьПараметр("ГраницаНачала", ГраницаНачала);
Запрос.УстановитьПараметр("ГраницаКонца", ГраницаКонца);
В этом случае СУБД мгновенно найдет нужные записи, используя индекс по полю Номер, даже если в таблице будут десятки миллионов документов.
Иногда номера документов хранятся в виде строк. В таком случае математические операции деления непосредственно применить не удастся. Для текстовых полей оптимальным решением будет использование конструкции ВЫБОР КОГДА ... ПОДОБНО:
ВЫБРАТЬ
Заказы.Ссылка,
ВЫБОР
КОГДА Заказы.Номер ПОДОБНО "25%" ТОГДА 25
КОГДА Заказы.Номер ПОДОБНО "24%" ТОГДА 24
КОГДА Заказы.Номер ПОДОБНО "23%" ТОГДА 23
// ... продолжайте список для других сегментов
КОГДА Заказы.Номер ПОДОБНО "1%" ТОГДА 1
ИНАЧЕ 0
КОНЕЦ КАК Сегмент
ИЗ
Документ.ЗаказПокупателя КАК Заказы
Этот способ достаточно громоздок для поддержки, но работает быстро и корректно, если количество возможных сегментов ограничено.
Если ваши заказы поступают из внешней производственной базы данных, подключенной через механизм Внешних источников данных (ВИД) 1С, то попытка выполнять сложные расчеты непосредственно в запросе 1С может столкнуться со спецификой трансляции SQL во внешнюю СУБД (например, MySQL, Oracle или MS Access).
Для таких сценариев лучшей практикой является перенос логики вычислений на сторону внешней базы данных:
FLOOR(OrderNo / 1000000)).В результате в конфигураторе 1С вы получите готовую таблицу, где поле Сегмент уже будет рассчитано на уровне СУБД. Это полностью избавит кодовую базу 1С от математических костылей и обеспечит максимальную скорость работы.