Объёмный вес посылки в SQL: почему лёгкая коробка стоит дороже

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

Записать учебное правило целиком

Для примера зададим условный тариф: объёмный вес в килограммах равен произведению длины, ширины и высоты в сантиметрах, делённому на 5000. Оплачиваемая масса — большая из фактической и объёмной, округлённая вверх до 500 граммов. Это выбранная модель расчёта; конкретный перевозчик может применять другой делитель, шаг, минимум и отдельные доплаты.

Здесь размеры — целые положительные сантиметры уже после согласованного измерения, а масса — целые граммы. Входная строка представляет одну физическую посылку. Не нужно складывать размеры всех товаров заказа: упаковка, пустоты и объединение позиций меняют фактические габариты. Если заказ отправляется тремя коробками, правило применяется к каждой коробке, когда именно так написано в тарифе.

Сохранить единицы при пересчёте

Кубические сантиметры, делённые на 5000, дают килограммы по нашему правилу. Для перехода к граммам множитель 1000 сокращает делитель до 5. Поэтому объёмный вес в граммах равен объёму, делённому на 5. При целых размерах выражение (объём + 4) / 5 даёт округление вверх до целого грамма в SQLite, где деление двух целых сохраняет целый результат.

Следующее выражение (вес + 499) / 500 × 500 округляет целое положительное количество граммов вверх до тарифного шага. Предварительное округление вверх до грамма не меняет последующего округления до 500 граммов. Формула рассчитана на целые положительные входы; отрицательные значения и текстовые числа следует отклонять до вычисления.

Пример выполняется в SQLite. MAX с двумя аргументами здесь выбирает большее значение внутри строки.

WITH parcels(id,l_cm,w_cm,h_cm,actual_g) AS (
 VALUES ('A',40,30,20,2000),('B',10,10,10,1201)),
weights AS (
 SELECT *, (l_cm*w_cm*h_cm+4)/5 AS volumetric_g FROM parcels
)
SELECT id,actual_g,volumetric_g,
 ((MAX(actual_g,volumetric_g)+499)/500)*500 AS billed_g
FROM weights ORDER BY id;

Прочитать две разные причины начисления

У коробки A габариты 40 × 30 × 20 см: объём 24000 см³ даёт объёмный вес 4800 г. Он больше фактических 2000 г, после тарифного округления оплачиваются 5000 г. У B объёмный вес всего 200 г, но фактическая масса 1201 г. Выбирается фактическая масса, округлённая до 1500 г.

В отчёте оставьте все четыре поля: фактическую, объёмную и оплачиваемую массу, а также версию тарифа. Одна конечная сумма не объясняет причину. Аналитик должен видеть, что у A основной резерв связан с упаковкой, а у B начисление определяется самим товаром и шагом округления. Снижение массы B на сто граммов пока не изменит оплачиваемые полтора килограмма.

Сверить счёт без потери детализации

К тарифу присоединяйте дату, маршрут, услугу и контракт. В одном счёте могут встречаться разные правила для экспресс-доставки и обычной перевозки. Если справочник выдаёт несколько тарифных строк на посылку, сначала устраните неоднозначность. Случайный MAX цены или DISTINCT по итоговым столбцам только спрячут ошибку соединения.

Отдельно храните плату за дальнюю зону, страховку и негабарит. Их нельзя объяснить повышенным весом, если договор начисляет самостоятельную доплату. Полезный результат сверки — сумма по каждому основанию и расхождение с документом перевозчика, а затем общий итог. Тогда менеджер сможет отправить предметный вопрос по конкретной коробке.

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

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

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