寻求在Teradata中实现FIFO报表逻辑的技术方案建议及On hand table、Receipt table输出需求

Hey there! Let's walk through how to implement FIFO (First-In-First-Out) reporting logic in Teradata, including the necessary structures for your On Hand and Receipt tables. I’ve helped teams build similar inventory reporting systems before, so here’s a practical, Teradata-specific breakdown:

First, let’s define the core tables you’ll need to track inventory receipts and current stock levels.

Required Table Structures #

Receipt Table (Tracks Incoming Inventory) #

This table captures every batch of inventory received, with critical details for FIFO ordering:

CREATE TABLE Receipts (
    Item_ID VARCHAR(50) NOT NULL,       -- Unique identifier for the inventory item
    Receipt_Date DATE NOT NULL,         -- Date the batch was received (key for FIFO)
    Batch_ID VARCHAR(50) NOT NULL,      -- Unique ID for the receipt batch
    Receipt_Qty DECIMAL(18,2) NOT NULL, -- Quantity received in this batch
    PRIMARY KEY (Item_ID, Batch_ID)     -- Ensures no duplicate batches per item
);

On Hand Table (Current Inventory Levels) #

This table stores the latest available quantity for each item—we’ll update this as we apply FIFO logic to track remaining stock:

CREATE TABLE On_Hand (
    Item_ID VARCHAR(50) NOT NULL,       -- Matches Item_ID in Receipts
    Current_Qty DECIMAL(18,2) NOT NULL, -- Current available quantity
    PRIMARY KEY (Item_ID)
);

Implementing FIFO Logic #

Teradata’s powerful analytical window functions make FIFO calculations straightforward. The core idea is to prioritize older receipt batches first when calculating inventory consumption. Here’s a step-by-step example:

Step 1: Calculate Cumulative Receipt Totals #

First, we’ll order receipts by date and compute running totals to track how much inventory is available up to each batch:

WITH Receipt_Running_Totals AS (
    SELECT
        Item_ID,
        Batch_ID,
        Receipt_Date,
        Receipt_Qty,
        -- Compute cumulative quantity for each item, ordered by receipt date
        SUM(Receipt_Qty) OVER (
            PARTITION BY Item_ID 
            ORDER BY Receipt_Date, Batch_ID 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS Cumulative_Receipt_Qty
    FROM Receipts
),

Step 2: Match Consumption to FIFO Batches #

Assuming you have an Issues table tracking inventory withdrawals, we’ll calculate how much each batch contributes to fulfilling those issues:

Issue_Running_Totals AS (
    SELECT
        Item_ID,
        Issue_Qty,
        Issue_Date,
        -- Compute cumulative withdrawn quantity for each item
        SUM(Issue_Qty) OVER (
            PARTITION BY Item_ID 
            ORDER BY Issue_Date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS Cumulative_Issue_Qty
    FROM Issues
)
SELECT
    rt.Item_ID,
    rt.Batch_ID,
    rt.Receipt_Date,
    rt.Receipt_Qty,
    ir.Issue_Date,
    ir.Issue_Qty,
    -- Calculate how much of this batch is used to fulfill the issue
    CASE
        WHEN rt.Cumulative_Receipt_Qty <= ir.Cumulative_Issue_Qty - ir.Issue_Qty THEN 0
        WHEN rt.Cumulative_Receipt_Qty - rt.Receipt_Qty >= ir.Cumulative_Issue_Qty THEN ir.Issue_Qty
        ELSE rt.Cumulative_Receipt_Qty - (ir.Cumulative_Issue_Qty - ir.Issue_Qty)
    END AS Qty_Used,
    -- Calculate remaining quantity in the batch after this issue
    rt.Receipt_Qty - 
        CASE
            WHEN rt.Cumulative_Receipt_Qty <= ir.Cumulative_Issue_Qty - ir.Issue_Qty THEN 0
            WHEN rt.Cumulative_Receipt_Qty - rt.Receipt_Qty >= ir.Cumulative_Issue_Qty THEN ir.Issue_Qty
            ELSE rt.Cumulative_Receipt_Qty - (ir.Cumulative_Issue_Qty - ir.Issue_Qty)
        END AS Remaining_Batch_Qty
FROM Receipt_Running_Totals rt
JOIN Issue_Running_Totals ir ON rt.Item_ID = ir.Item_ID
ORDER BY rt.Item_ID, rt.Receipt_Date, ir.Issue_Date;

Step 3: Update On Hand Inventory #

After calculating remaining quantities per batch, aggregate the results to update your On_Hand table:

MERGE INTO On_Hand oh
USING (
    SELECT
        Item_ID,
        SUM(Remaining_Batch_Qty) AS New_Current_Qty
    FROM (
        -- Insert the full CTE logic from above here
    ) AS FIFO_Calculations
    GROUP BY Item_ID
) AS src
ON oh.Item_ID = src.Item_ID
WHEN MATCHED THEN UPDATE SET oh.Current_Qty = src.New_Current_Qty
WHEN NOT MATCHED THEN INSERT (Item_ID, Current_Qty) VALUES (src.Item_ID, src.New_Current_Qty);

Key Teradata-Specific Tips #

• Indexing: For large datasets, create indexes on Item_ID, Receipt_Date, and Issue_Date to speed up window function partitioning and ordering—Teradata’s optimizer relies heavily on these for performance.
• Decimal Precision: Always use DECIMAL instead of FLOAT for quantity columns to avoid rounding errors, which are critical for accurate inventory tracking.
• Batch Uniqueness: The Batch_ID ensures you can distinguish between multiple batches received on the same date—this is a must for strict FIFO compliance.

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