재고 회계는 재고가 창고를 통과하는 동안 비용을 정확하게 추적해야 합니다. FIFO(선입선출)는 가장 오래된 구매 품목이 먼저 판매된다고 가정합니다. 이는 부패하기 쉬운 상품이나 인플레이션 환경에서 중요합니다. 평균 원가 계산과 달리 FIFO는 각 판매를 특정 역사적 구매 로트와 일치시켜야 하며, 이는 현대 SQL 표준 이전부터 존재한 다대다 관계의 문제를 만듭니다.
purchases(lot_id, quantity, unit_cost, received_at) 테이블과 sales(sale_id, quantity, sold_at) 테이블 두 개가 주어졌을 때, 각 판매 수량을 가장 오래된 판매되지 않은 재고에 배분해야 합니다. 이는 단일 판매가 여러 부분 로트를 소모할 수 있고, 단일 로트가 여러 판매에 걸쳐 있을 수 있으며, 배분이 절차적 루프 없이 시간 순서를 존중해야 하는 세 가지 복잡성을 만듭니다. 전통적인 JOIN 접근 방식은 행 간의 개별 로트의 고갈 상태를 추적할 수 없기 때문에 실패합니다.
윈도우 함수를 사용하여 누적 합계를 계산하고 이산적인 수량을 연속적인 구간으로 변환합니다. 구매를 누적 범위 [prior_cumulative+1, current_cumulative]로 변환하고 판매를 유사한 범위로 변환합니다. JOIN을 통해 겹치는 구간을 확인하여 어떤 로트가 어떤 판매에 공급되는지 식별합니다. 교차 길이를 로트의 unit_cost로 곱하면 원가 기준이 됩니다. 이 집합론적 접근 방식은 재귀를 피하고 전적으로 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를 사용하여 저장 프로시저에서 판매를 순차적으로 반복하고 로트 잔고를 행별로 감소시키는 것이었습니다. 기능적으로는 올바르지만 이 방법은 피크 시간 동안 인벤토리 테이블에 대해 오랜 시간 동안 잠금을 유지하여 심각한 잠금 경합을 초래했습니다. 또한 절차적 논리는 동시 INSERT 작업에서 ACID 준수 테스트에 실패하여 팬텀 판독 및 재고 로트의 이중 지출을 초래했습니다.
팀은 판매 시마다 자동으로 업데이트되는 실행 중 잔고 테이블을 유지하기 위해 트리거 사용을 잠시 고려했습니다. 하지만 이는 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의 세 개의 로트를 가로지르며 나뉘었을 때, 각 항목에 대한 단순 반올림은 할당된 비용의 합이 판매의 총 예상 값에서 1센트 차이가 나도록 만들 수 있습니다. 후보자들은 종종 allocated_quantity * unit_cost를 직접 계산하나, 반올림 맥락이나 잔여 값을 고려하지 않습니다. 강력한 솔루션은 은행가 반올림(반올림)을 적용하거나 서브 쿼리에서 반올림 되지 않은 값을 보존한 후 외부 쿼리에서 수정 알고리즘을 적용합니다. 이 조정은 잔여 차이를 가장 큰 할당 항목에 추가하여 합계가 총 판매 값과 정확하게 일치하도록 강제합니다. 동시에 감사 추적 정확성을 유지합니다.