Учет запасов требует точного отслеживания затрат по мере движения товаров через склад. FIFO (First-In-First-Out) предполагает, что наиболее старые купленные товары продаются первыми, что имеет решающее значение для товаров с ограниченным сроком хранения или в условиях инфляции. В отличие от среднего учета затрат, FIFO требует сопоставления каждой продажи с конкретными историческими закупочными партиями, что создает проблему взаимосвязи «множество-ко-многим», существующую до современных стандартов SQL.
Имея две таблицы — purchases (lot_id, quantity, unit_cost, received_at) и sales (sale_id, quantity, sold_at) — мы должны распределить каждое количество продажи на старые доступные непроданные запасы. Это создает три сложности: одна продажа может потреблять несколько частичных партий, одна партия может охватывать несколько продаж, а распределение должно учитывать хронологический порядок без процедурных циклов. Традиционные подходы JOIN не работают, потому что они не могут отслеживать состояние истощения отдельных партий через строки.
Используйте окна функций, чтобы вычислить накопительные суммы, преобразуя дискретные количества в непрерывные интервалы. Преобразуйте закупки в накопительные диапазоны [prior_cumulative+1, current_cumulative] и продажи в аналогичные диапазоны. JOIN на пересекающихся интервалах определяет, какие партии подходят для каких продаж. Длина пересечения, умноженная на цену за единицу партии, дает себестоимость. Этот подход на основе множеств избегает рекурсии и работает полностью в рамках ANSI SQL.
WITH purchase_cumulative AS ( SELECT lot_id, unit_cost, received_at, SUM(quantity) OVER (ORDER BY received_at, lot_id ROWS UNBOUNDED PRECEDING) - quantity AS cum_start, SUM(quantity) OVER (ORDER BY received_at, lot_id ROWS UNBOUNDED PRECEDING) AS cum_end FROM purchases ), sales_cumulative AS ( SELECT sale_id, sold_at, SUM(quantity) OVER (ORDER BY sold_at, sale_id ROWS UNBOUNDED PRECEDING) - quantity AS cum_start, SUM(quantity) OVER (ORDER BY sold_at, sale_id ROWS UNBOUNDED PRECEDING) AS cum_end FROM sales ) SELECT s.sale_id, p.lot_id, p.unit_cost, LEAST(s.cum_end, p.cum_end) - GREATEST(s.cum_start, p.cum_start) AS allocated_quantity, (LEAST(s.cum_end, p.cum_end) - GREATEST(s.cum_start, p.cum_start)) * p.unit_cost AS allocated_cost FROM sales_cumulative s JOIN purchase_cumulative p ON s.cum_start < p.cum_end AND s.cum_end > p.cum_start ORDER BY s.sale_id, p.received_at;
Фармацевтический дистрибьютор следит за партиями медикаментов с разными ценами оптовой закупки из-за колебаний поставщиков. Регулирование FDA требует точной прослеживаемости затрат для каждой проданной таблетки, что требует атрибуции стоимости на единицу, а не среднего учета. Склад обрабатывает тысячи транзакций в день по сотням SKU, при этом закупочные партии поступают в непредсказуемые интервалы и по разным ценам.
Первоначальный подход использовал CURSOR в хранимой процедуре, последовательно проходя по продажам и уменьшая остатки партий построчно. Хотя это работало правильно, такой метод вызывал серьезные блокировки во время пиковых часов, так как удерживал блокировки на таблицах запасов на продолжительное время. Кроме того, процедурная логика не прошла тесты на соблюдение ACID в условиях параллельных операций INSERT, что привело к фантомным чтениям и двойному расходованию партий запасов.
Команда на короткое время рассматривала использование триггеров, чтобы поддерживать таблицу текущего баланса, которая автоматически обновлялась при каждой продаже. Однако это создавало ошибки мутации таблицы в Oracle и сложное управление откладываемыми ограничениями в PostgreSQL, добавляя задержки в систему OLTP. Подход с триггерами также усложнял аудит, скрывая точную логику распределения в метаданных базы данных, а не в явном коде запросов.
Выбранное решение реализовало метод пересечения интервалов с помощью окон функций, чтобы предварительно вычислить накопительные границы. Это позволило оптимизатору базы данных использовать соединения сортировок и слияний вместо вложенных циклов, сократив время расчета стоимости для отчета о продаже 10 000 единиц с 45 секунд до 200 миллисекунд. Это решение обеспечило отчетность по стоимости проданных товаров в реальном времени во время закрытия финансового месяца без блокировки транзакций запасов, достигая полного соблюдения изоляции SERIALIZABLE.
Как вы обрабатываете крайний случай, когда события покупки и продажи имеют одинаковую метку времени, обеспечивая детерминированный порядок FIFO?
Кандидаты часто предполагают, что ORDER BY sold_at достаточно. Однако, когда метки времени совпадают, порядок распределения становится недетерминированным и может варьироваться между выполнениями запросов. Решение требует колонки «разрешающего преимущества» — обычно первичного ключа или автоинкрементируемой последовательности — в ORDER BY функции окна. Без этой строгой сортировки две продажи, происходящие одновременно, могут неверно потреблять одно и то же количество партии дважды из-за условий гонки в плане выполнения оптимизатора запросов, нарушая целостность запасов.
Почему использование FLOAT или DOUBLE PRECISION для колонок количества портит результаты распределения FIFO?
Многие кандидаты используют типы с плавающей точкой для денежных или количественных расчетов, не подозревая, что IEEE 754 с плавающей точкой не может точно представлять десятичные дроби, такие как 0.1. Эта неточность вызывает ошибки накопительных сумм, которые накапливаются среди тысяч строк, в результате чего партия, ожидаемая с точно 100 единицами, может зарегистрироваться как 99.999999 или 100.000001. В результате математика пересечения интервалов может либо пропустить валидные пересечения, либо создать фантомные отрицательные вывода. Решение требует использования типов DECIMAL или NUMERIC с явной точностью для всех колонок количества и стоимости, чтобы гарантировать целочисленную точность арифметики и предотвратить финансовые несоответствия.
Как вы исправляете накопленные ошибки округления, когда продажа охватывает дробные центры через несколько партий с разными ценами за единицу?
Когда продажа распределяется между тремя партиями по цене $0.33, $0.33 и $0.34, наивное округление каждого элемента может привести к тому, что сумма распределенных затрат будет отличаться от ожидаемой общей суммы продажи на цент. Кандидаты часто рассчитывают allocated_quantity * unit_cost напрямую, не учитывая контекст округления или остаточные остатки. Надежное решение применяет округление банкира (округление до четного) или сохраняет неокругленные значения в подзапросе, а затем применяет алгоритм коррекции в внешнем запросе. Это исправление добавляет остаточную разницу к самой большой строке распределения, принуждая сумму точно соответствовать общей стоимости продажи, сохраняя при этом точность аудита.