Имеется следующая задача:
Тысячи клиентов, тысячи артикулов. Каждый клиент имеет свою скидку на категорию товаров, причем клиенту она предоставляется в виде 3-х целых последовательных скидок, например 25%, 7% и 6%, что в результате дает реальную скидку 34.435%, которая и используется для внутренних расчетов, а клиенту даются только 3 числа в виде целых чисел.
Создается система автоматического приведения в порядок всей этой вакханалии со скидками, где нужно по заданному примерному проценту общей скидки подобрать ближайшую комбинацию из подходящих целых чисел.
Сейчас подбор сделан "в лоб" - есть таблица со всеми комбинациями, на каждую строку с клиентом/артикулом вызывается пользовательская функция, которая выбирает первую запись из таблицы комбинаций по between и отсортированной по убыванию абсолютной разницы заданной общей скидки и существующей в таблице комбинаций. Работает, но медленно. Наверняка чисто SQL выражение работало бы быстрее.
Есть идеи как можно было бы организовать подбор ближайших значений более быстрым способом, чем вызов функции на каждую строку?

Mina Anna Mazzini- Уже на этапе ее создания можно и нужно было услышать скрип открывающейся двери в палату #6

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

Mina Anna Mazzini- Расшифруй на пальцах. Т.к. после загадочного between это означает "по ворастанию скидки существующей в таблице комбинаций", учитывая, что в ней четыре поля.


between только по общей скидке, а читаем возвращаемые целые поля

Mina Anna MazziniВместо таблицы генерить в VBA массив и работать с ним.


Вот это хорошая идея! Поиск, например, методом дихотомии на отсортированном массиве или коллекции скидок, должно работать быстро. Попробуем.

Да уж... своеобразненько :) Ну и на том спасибо.

По поводу кучи вопросов про выбор. Есть 2 режима - ручной и автоматический. В ручном можно выбрать любую комбинацию из многих в пределах разброса +- 1% от заданной скидки, см скриншот. Отсортировано именно как я и написал - "по убыванию абсолютной разницы заданной общей скидки и существующей в таблице комбинаций", т.е. здесь это


где dblValue - заданная скидка. Здесь же можно подобрать и целые числа - записи фильтруются в зависимости от полей R1-R3

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

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

Mina Anna Mazzini,

Ок, по пунктам:
Что из этого массива ты предлагаешь клиенту?
Какую(какие) комбинацию?
Какие критерии отбора?


В автомате набор с общей скидкой, наиболее близкой к заданному значению

Как клиент может повлять на это?
Никак в автомате, только при ручном выборе

Может ли он изменить любрй из трех параметров с соответствующим изменением двух оставшихся?
Пока только выбор из существующего набора в ручном режиме фильтрами может подобрать наиболее подходящую комбинацию

Как (по какому алгоритму) ты будешь в этом случае делить единицу на пополам между двумя оставшимися параметрами?
Есть примерные пропорции, исходя из них часть из возможных комбинаций с близкими значениями общей скидки в таблице комбинаций отброшена.

Что же у тебя первично, а что вторично на самом деле?
Первична общая скидка, наборы целых вторичны и не столь важны

Про формулу написали правильно, это именно составная скидка. Поэтому я и сказал, что решение линейного уравнения с 3-мя неизвестными, возможно, будет медленнее выборки из готовой таблицы. Формулу не приводил, т.к. это не совсем по начальной теме - как это решить в рамках SQL выборку ближайшего значения к заданному, а уже по этому числу что будет вытаскиваться - вторично

ну что мешает располовинить на то, что меньше 45 и наоборот, сортирнуть каждую по RNet и сравнить две величины? хз...
Так оно примерно так и сделано, только знак убран. посмотри на список, там есть и больше 45 и меньше, чем ниже, тем дальше значение RNet от заданного. Ну или я не понял предложения.

положим, что R1 - ведущий, а R2 и R3 - ведомые
Ведущий - RNet, R1-R3 - значения, соответствующие RNet, их абсолютное значение нас в данный момент не интересует, нужно найти RNet.

Mina Anna MazziniМогу тебя приободрить - я успел с утра набросать на коленке - все летает.


Вот это да! Спасибо! Я знал, что задачка понравится :)
На меня тут понавесили по другим направлениям, так что скидки отодвинулись, сам только в выходные смогу добраться, наверное.
Ну если уж действительно занимается кто-то алгоритмом, то предпочтительная пропорция R1-R3 как скидка по умолчанию: 55-15-0, последующая скидка всегда меньше или равна предыдущей. Впрочем, от перестановки произведение не меняется.

Да, и как минимум 2 скидки должно быть

Интересная картинка...
Номер шага за сек - это время подбора? Смутили шаги с нулями - просто округление?

Не понятно, почему алгоритм с ограничениями работает медленнее.

Т.е. получается, что в среднем на подбор одного варианта уходит в районе 100 милисекунд. Сейчас проверил, существующий алгоритм апдейтит 2000 строк за примерно минуту, если брать расчётный алгоритм, вычисления займут в районе 20 секунд, плюс накладные расходы на апдейт и вызовы функции, получается выигрыш чуть больше, чем 2 раза. Неплохо, но для решения проблемы с производительностью нужно примерно 10-и кратное ускорение, чтобы не было заметных задержек при апдейтах.
Во вторник сдам версию к митингу, надеюсь, появится возможность поэкспериментировать с производительностью, попробую решение на базе массива и напишу о результатах "боевого" применения.

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

Не дотерпел до вторника. Переделал функцию на массив, время апдейта тех же 2000 строк со всеми накладными сократилось с минуты с лишним до искомых 3-х секунд, задачу можно считать решенной. Не думаю, что подбор вычислениями сможет побить средние 15 простейших циклов сравнения на каждый поиск в массиве, но вычисления могут позже понадобиться для подбора по расширенным требованиям маркетологов. С помощью чисто SQL, как я понимаю, задачу решить невозможно, только с помощью пользовательской функции.

Спасибо большое за участие и советы, было интересно!

Ты отчет-то читал? Откуда 100?

Виноват, ошибся на порядок :)

Строк чего?

Есть UPDATE, который кроме всего прочего проставляет в таблице эти скидки, вызывая функцию. Вот в этой таблице при экспериментах было 2000 строк, функция вызывается 4 раза на каждую строку, считается, естественно, только один раз на каждое искоме значение общей скидки, остальные вызовы просто возвращают R1-R3 из статических переменных. Конечно можно это оптимизировать. но пока нет времени - много запросов переделывать придется

млин... Не нужен массив... Не нужна таблица...
Об этом и пишу. Все считается on-the-fly.

Конечно понимаю, но расчет никак не сможет побить по скорости поиск в готовом массиве, ну ни при каких условиях, см бенчмарк ниже.

За тобой до сих пор еще эта самая формула расчета сложной скидки осталась.
Ты мужественно ее не хочешь озвучить.

Сорри, думал уже не требуется.
RTotal=1-(1-R1)*(1-R2)*(1-R3), где R1-R3 - числа от 0 до 1


Решил померять чистую производительность, без накладных расходов, использовал вот это для измерения:



89 потому что больше 89% скидки не требуется. В таблице готовых результатов 23000 строк с общей скидкой от 0 до 89. Второй аргумент функции - номер R или 0 для общей скидки

Результаты: 40 миллисекунд на один поиск старой функцией с использованием рекордсета и 3 МИКРОсекунды на поиск в массиве новой функцией, на 1 млн подборов ушло 3 секунды, т.е. более чем в 13 000 раз быстрее. Расчетный алгоритм, как я понимаю, примерно в 4 раза быстрее старой функции и в 3000 раз медленнее новой.

Вот основной цикл новой функции, который, собственно, ищет в массиве, загруженном в память:



В среднем для поиска в массиве из 23000 элементов требуется около 10 циклов, максимально 17. Четыре сравнения и шесть простейших арифметических операций, только две из них с плавающей точкой. Глубоко сомневаюсь, что можно вычислить скидку за сравнимое число операций. Этот алгоритм еще можно оптимизировать, например выкинуть "страховку" с i

Если увеличить число элементов в таблице до максимального - 1 млн, то максимальное число циклов возрастет до 22