基于先进先出定价按销售日期算利润及每日销售毛利的SQL实现方法

Alright, let's break down how to calculate profit using FIFO (First-In, First-Out) pricing based on sale dates, then dive into a solid SQL implementation for daily gross margin queries. I’ve dealt with this exact scenario in retail and warehouse inventory systems, so I’ll keep this practical and avoid overly abstract jargon.

Step-by-Step FIFO Profit Calculation #

First, let’s make sure we’re aligned on the core logic: FIFO means the oldest inventory you purchased gets sold first. To compute daily gross margin (profit before operating expenses), follow these steps:

• Track inventory purchases: You need a clear record of every batch—when you bought it, how many units, and the per-unit cost.
• Match sales to the oldest available inventory: For each sale, use up the oldest unsold inventory first. If a sale quantity exceeds one batch, move to the next oldest batch until you cover the full sold quantity.
• Calculate COGS (Cost of Goods Sold): Sum the cost of all inventory units allocated to the sale (batch quantity used × batch unit cost).
• Compute daily gross margin: Subtract total daily COGS from total daily sales revenue (sum of all sales quantity × sales price that day).

Let’s use a quick example to make this tangible:

Suppose you buy 10 units at $5 on Jan 1, then 15 units at $6 on Jan 3. On Jan 5, you sell 12 units at $10 each.

• FIFO allocates all 10 units from the Jan 1 batch ($5 each) and 2 units from the Jan 3 batch ($6 each).
• COGS = (10×$5) + (2×$6) = $50 + $12 = $62
• Sales revenue = 12×$10 = $120
• Jan 5 gross margin = $120 - $62 = $58

SQL Implementation for Daily FIFO Gross Margin #

Let’s assume we have two standard tables to work with:

1. inventory_purchases: Stores all incoming inventory batches
   • Columns: purchase_id (primary key), purchase_date, quantity_purchased, unit_cost
2. sales: Stores all customer sales transactions
   • Columns: sale_id (primary key), sale_date, quantity_sold, unit_price

We’ll use Common Table Expressions (CTEs) and window functions to map sales to the oldest inventory batches—this is the cleanest way to handle FIFO matching in SQL. Here’s the step-by-step solution:

1. Calculate Running Totals for Inventory #

First, we need to track cumulative inventory over time to see how much stock is available up to each purchase date:

WITH inventory_running_total AS (
    SELECT
        purchase_date,
        quantity_purchased,
        unit_cost,
        -- Cumulative total of inventory available after each purchase
        SUM(quantity_purchased) OVER (ORDER BY purchase_date) AS total_available
    FROM inventory_purchases
),

2. Calculate Running Totals for Sales #

Next, compute cumulative sales over time to match against our inventory running totals:

sales_running_total AS (
    SELECT
        sale_date,
        quantity_sold,
        unit_price,
        -- Cumulative total of units sold up to each sale date
        SUM(quantity_sold) OVER (ORDER BY sale_date) AS total_sold
    FROM sales
),

3. Match Sales to Inventory Batches #

Now we’ll join the two running total CTEs to figure out which inventory batches cover each sale. We’ll calculate exactly how much of each inventory batch is used for every sale:

fifo_matches AS (
    SELECT
        s.sale_date,
        s.quantity_sold,
        s.unit_price,
        i.purchase_date,
        i.unit_cost,
        -- Calculate the quantity of this inventory batch allocated to the sale
        GREATEST(
            0,
            LEAST(i.total_available, s.total_sold)
            - GREATEST(i.total_available - i.quantity_purchased, s.total_sold - s.quantity_sold)
        ) AS quantity_used
    FROM sales_running_total s
    JOIN inventory_running_total i
        -- Join condition: this inventory batch overlaps with the sale's cumulative sold range
        ON i.total_available > s.total_sold - s.quantity_sold
        AND i.total_available - i.quantity_purchased < s.total_sold
)

4. Aggregate to Get Daily Gross Margin #

Finally, we group by sale date to calculate daily totals for revenue, COGS, and gross margin:

SELECT
    sale_date,
    SUM(quantity_sold * unit_price) AS total_daily_revenue,
    SUM(quantity_used * unit_cost) AS total_daily_cogs,
    SUM(quantity_sold * unit_price) - SUM(quantity_used * unit_cost) AS daily_gross_margin
FROM fifo_matches
GROUP BY sale_date
ORDER BY sale_date;

Key Notes on the SQL #

• This assumes you never sell more inventory than you have in stock (no backorders). If you need to handle backorders, you’ll need to adjust the logic to account for future purchases matching past sales.
• The window functions (SUM() OVER (ORDER BY ...)) are critical here—they let us track cumulative inventory and sales without messy nested subqueries.
• If you have multiple sales or inventory entries on the same day, this query will still correctly aggregate all of them into daily totals.

内容的提问来源于stack exchange，提问作者Ramy Nassif