LatornLatorn
Войти

Как распределить скидку заказа по товарам в SQL без потерянной копейки

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

Как разобрать ситуацию

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

Пример и ожидаемый результат

Стоимость трёх позиций равна 33, 33 и 34 копейкам. Общая скидка — 10 копеек. Нижние части дают 3, 3 и 3, оставляя одну копейку. Она достаётся третьей позиции, поэтому итоговое распределение равно 3, 3 и 4.

WITH items(id,cents) AS(VALUES(1,33),(2,33),(3,34)), base AS(
SELECT id,10.0*cents/SUM(cents) OVER() exact 
FROM items), ranked AS(
SELECT id,CAST(exact AS INT) floor_part,ROW_NUMBER() OVER(
ORDER BY exact-CAST(exact AS INT) DESC,id) rn,SUM(CAST(exact AS INT)) OVER() assigned 
FROM base) 
SELECT id,floor_part+CASE WHEN rn<=10-assigned THEN 1 ELSE 0 END 
FROM ranked 
ORDER BY id;

Запрос возвращает (1,3), (2,3), (3,4). Сумма равна исходным десяти копейкам, а правило выбора получателя остатка воспроизводимо.

Проверка перед применением

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

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

Границы решения

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

Если запросы уже пишутся, а уверенности в кардинальности, оконных рамках и проверке результата не хватает, продолжить практику можно в нашем платном курсе

Продвинутый SQL: сложные запросы и задачи для собеседований. Перед записью сравните программу со своей задачей и подготовкой.

Техническая справка: www.sqlite.org: документация, www.sqlite.org: документация.

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