Прогрессивная комиссия в SQL: повышенная ставка только на часть продаж

Менеджер продал на 250 тысяч рублей. В положении о премировании написано: до 100 тысяч — 3%, следующие 100 тысяч — 5%, всё выше — 7%. Если взять последнюю достигнутую ставку и умножить на весь оборот, получится 17 500 рублей. По ступенчатой схеме компания должна выплатить 11 500. Оба расчёта выглядят правдоподобно, поэтому спор начинается с чтения правил, а не с исправления запроса.

Зафиксировать смысл границ

Договоримся, что ставка относится к части накопленной продажи внутри своего диапазона. Первые 100 тысяч всегда оплачиваются по 3%, даже у самого результативного сотрудника. На границе 100 тысяч повышенная ставка ещё ничего не добавляет: во второй диапазон попадает нулевая сумма. При 100 001 рубле по 5% оплачивается только один рубль. Это свойство удобно проверить до расчёта ведомости.

Альтернативная схема действительно может назначать 7% на весь оборот после достижения порога. Её обычно выбирают ради сильного стимула закрыть порог, но выплата тогда скачет. Название «прогрессивная комиссия» само по себе не различает правила. Сохраните в описании отчёта пример выплаты на пороге и сразу после него: он снимает двусмысленность лучше общего названия.

Преобразовать диапазон в оплачиваемую базу

Для каждой ступени берём меньшую из продажи и верхней границы, вычитаем нижнюю границу и заменяем отрицательный результат нулём. Получается длина пересечения отрезка накопленной продажи со ступенью. CROSS JOIN здесь намеренно создаёт по три строки на менеджера. Они представляют части одной выплаты; после расчёта частей запрос возвращает одну сумму на сотрудника.

В примере ставки заданы в базисных пунктах: 300 означает 3%. Так коэффициенты хранятся целыми числами. Верхняя граница последнего диапазона условно равна миллиарду рублей; для настоящей схемы нужен согласованный максимум либо отдельная обработка открытой границы. Незаметно обрезать большие продажи этим числом нельзя.

Пример выполняется в SQLite; функции и синтаксис ниже относятся к этому диалекту.

WITH sales(person, amount) AS (VALUES ('A',80000),('B',250000)),
tiers(lo,hi,rate_bp) AS (VALUES (0,100000,300),(100000,200000,500),(200000,1000000000,700)),
parts AS (
 SELECT person,lo, MAX(0,MIN(amount,hi)-lo) AS base,rate_bp
 FROM sales CROSS JOIN tiers
)
SELECT person, SUM(base*rate_bp)/10000.0 AS commission
FROM parts GROUP BY person ORDER BY person;

Прочитать ведомость и проверить тариф

Запрос вернёт A — 2400 рублей, B — 11500. У B база первой ступени равна 100000, второй — 100000, третьей — 50000. Произведения дают 3000, 5000 и 3500. Полезно сохранить такой детальный расчёт рядом с итоговой ведомостью: бухгалтер сможет объяснить выплату без восстановления всей логики из SQL.

Таблица ступеней должна покрывать нужный диапазон без разрывов и пересечений. Если следующая нижняя граница не равна предыдущей верхней, часть продажи пропадёт или оплатится дважды. Проверяйте соседние строки через LAG верхней границы, отдельно — первую нижнюю границу, положительную ширину и допустимую ставку. Для нескольких тарифных планов все эти проверки выполняются внутри плана и версии.

Вернуть расчёт к рабочим данным

Сначала получите оборот сотрудника за расчётный период, а затем применяйте ступени. Если присоединить тариф к каждой покупке и каждый раз начинать с нуля, продавец с десятью покупками по 50 тысяч останется на первой ставке. Это другой бизнес-процесс. Определите, обнуляются ли ступени каждый месяц, квартал или год и как переносится результат при смене подразделения.

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

В демонстрации целые рубли дают точный понятный ответ. В расчётной системе деньги лучше хранить в копейках, а округление согласовать отдельно: каждая часть или только общая комиссия. Два небольших начисления могут дать другую копейку при разных правилах. В набор приёмки включите нулевую продажу, каждую границу и значение на копейку по обе стороны.

Следующий шаг

Дальше полезно собрать весь расчётный процесс: агрегировать продажи, привязать исторический тариф и сверить итог с деталями. В платном курсе «Продвинутый SQL» есть многошаговые запросы, исторические значения, оконные функции и 109 задач с автоматической проверкой. Это практика для перехода от одной формулы к проверяемой витрине. Откройте программу курса.